22. 계좌 상세보기 - 1단계(쿼리 학습)

작업 순서

계좌 상세 보기 쿼리를 만들기 위한 단계 별 학습입니다.
(쿼리를 작성하는 실력은 꾸준한 연습이 필요 합니다. 1일1쿼리 연습을 습관화 합시다)

1. 출금, 이체(출금) 또는 입금,이체(입금)
2. 계좌 번호 함께 출력 하기
3. COALESCE 함수와 CAST 함수 사용해 보기
4. 입,출금에 대한 모든 정보 출력 하기 (CASE 구문에 활용)
5. 코드상에서 사용할 쿼리 확인

위 내용은 마이바티스에서 동적 쿼리(사용자 선택)를 생성하기 활동 입니다.

 

: 이번 프로젝트에서 가장 힘든 부분이 될 수 있습니다. 천천히 만들어 봅시다.

 

 

출금, 이체(출금) 또는 입금,이체(입금)

select * from history_tb;
-- 1번계좌에서 출금 내역 
select * from history_tb where w_account_id = 1; 
-- 1번계좌에서 입금 내역 
select * from history_tb where d_account_id = 1;

 

위 그림은 1번 계좌에서 출금 내역의 결과 집합 입니다. 결과 집합을 보고 의미를 추론할 수 있어야 합니다. 출금에 종류에는 출금 페이지를 활용하여(여기서는 ATM 기기라고 가정 합니다) 단순 **출금과 다른 계좌에 이체(송금)**한 내용이라고 생각할 수 있습니다.

 

위 그림 기반으로 설명 합니다. 1️⃣ ID 1 번은 W_ACCOUNT_ID - 출금 계좌 PK, D_ACCOUNT_ID - 입금 계좌 PK 값 1, 2 이 모두 존재 하고 있습니다. 그럼 1계좌에서 2번계좌로 1100원을 이체 처리했다라고 판단할 수 있습니다. 그리고 W_BALANCE 는 출금 후에 잔액이 900원이 남았고 D_BALANCE는 입금 받은 후 잔액이 1100원이 되었다 라고 판단을 할 수 있습니다.

 

2️⃣ ID 2 번은 W_ACCOUNT_ID 값이 1 그리고 D_ACCOUNT_ID 값이 null 인 것으로 PK 값이 1인 계좌에서 다른 계좌로 이체 하지 않았다는 의미이며 이것은 **단순하게 출금(AMT)**을 했다는 의미로 판단할 수 있습니다. AMOUNT 거래 금액이 100원으로 확인할 수 있으며 출금 후 W_BALANCE 에 800원이 남았다라고 확인할 수 있습니다.

 

위 그림은 1번 계좌에서 입금 내역의 결과 집합 입니다. 결과 집합을 확인해 보면 이체 받은 내역은 없으며 단순 입금 이력으로 거래금액 500원과 입금 후 잔액 1300원으로 확인할 수 있습니다.

 

계좌 번호 함께 출력 하기

-- 2단계 
-- 계좌 번호와 함께 출력 하기  -- 
-- 모든 계좌에 대한 출금 내역을 확인 한다면 ??
-- 1번계좌에 대한 출금 + 계좌 번호 보여줘  
select h.id, h.amount, h.w_balance, a.number, h.created_at
from history_tb as h 
left join account_tb as a on h.w_account_id = a.id
where h.w_account_id = 1;


-- 1번 계좌에 대한 
-- 입금 내역 + 계좌번호 출력하기 
select h.id, h.amount, h.d_balance, a.number, h.created_at
from history_tb as h 
left join account_tb as a on h.d_account_id = a.id
where h.d_account_id = 1;

 

쿼리 작성후 쿼리 실행 계획을 확인하는 습관을 들여 봅시다.

위 쿼리에서는 성능을 개선하기 위한 간단한 방법은 고민한다면 인덱스 활용, account_tb의 id 컬럼과 history_tb의 w_account_id 컬럼에 인덱스를 설정하는 방법으로 JOIN 연산과 WHERE 조건 연산에 성능을 향상 시킬 수 있습니다. 또는 SELECT 절에 필요한 컬럼만 선택하여 데이터의 양을 줄일 수 있습니다.

 

3. COALESCE 함수와 CAST 함수 사용해 보기

COALESCE 함수는 인자로 주어진 값들 중에서 첫 번째 NULL이 아닌 값을 반환합니다. 만약 모든 인자가 NULL이라면, COALESCE 함수는 NULL을 반환합니다.

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

이 함수는 보통 테이블 내의 NULL 값을 기본값이나 다른 값으로 대체할 때 유용하게 사용됩니다.

CAST 함수는 하나의 데이터 타입을 다른 데이터 타입으로 변환(형 변환)하는 데 사용됩니다. 이 함수는 데이터의 표현 방식을 변경할 때 유용하며, 예를 들어 문자열을 숫자로, 날짜를 문자열로 변환하는 등의 작업에 사용될 수 있습니다.

SELECT CAST('123' AS INT);  -- mysql 버전에 따라 사용 안될 수 있음
select CAST('123' as signed); -- 양수, 음수 다 포함 
select CAST('123' as unsigned); -- 양수만을 표현할 때 사용 

 

 

-- 3단계
-- 출금이 만약 이체라면 키값에 receiver : 금액에 대상을 출력  
-- 1번계좌에 대한 출금 내역을 보여줘  
select h.id, h.amount, h.w_balance as balance, a.number, h.created_at,
	  coalesce(CAST(h.d_account_id as char(10)), 'ATM') as receiver	 
from history_tb as h 
left join account_tb as a on h.w_account_id = a.id
where h.w_account_id = 1;

-- 입금 내역 쿼리 
select h.id, h.amount, h.d_balance as balance, a.number, h.created_at,
	  coalesce(CAST(h.w_account_id as char(10)), 'ATM') as sender 	 
from history_tb as h 
left join account_tb as a on h.d_account_id = a.id
where h.d_account_id = 1;

 

4. 입,출금에 대한 모든 정보 출력 하기 (CASE 구문에 활용)

 

이번에 목표는 위 그림과 같이 sender 와 receiver 둘다 출력하는 결과 집합을 만들어야 합니다.

 

 

CASE 구문에 활용

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

CASE 문은 SQL에서 조건에 따라 다른 값이나 표현식을 선택할 수 있게 하는 조건부 로직을 제공합니다. 기본적으로 CASE 문은 "만약... 그러면..."과 같은 결정 구조를 데이터베이스 쿼리 내에서 구현할 수 있게 해줍니다.

🤔 천천히 생각해 봅시다. 현재 1111 번 계좌는 account_tb PK 값은 1 입니다. history_tb 에 w_account_id 컬럼에 값이 1인 들어간다는 말은 1111 번 계좌에 출금이나 이체(송금) 이 발생 했다는 의미와 같습니다. 반대로 d_account_id 값이 1 이라는 의미는 1111번 계좌이 입금(ATM) 이나 이체를 받았다는 의미로 해석 될 수 있습니다. 즉, 1111 번 계좌에 출금이력 2건 입금이력 1건을 따로 출력 했다면 이번 쿼리에서는 입금,출금 3건 모두 출력 할 수 있어야 합니다.

1번계좌에 대한 입금, 출금 내용 전체 출력

 

select h.id, h.amount,
	case
		when h.w_account_id = 1 then (h.w_balance) 
        when h.d_account_id = 1 then (h.d_balance)
    end  as balance,
    coalesce(cast(wa.number as char(10)), 'ATM') as sender, 
    coalesce(cast(da.number as char(10)), 'ATM') as receiver,
    h.created_at
from history_tb as h 
left join account_tb as wa on h.w_account_id = wa.id
left join account_tb as da on h.d_account_id = da.id  
where h.w_account_id = 1 OR h.d_account_id = 1;

-- 1. 
-- 출금 내역 쿼리 
-- 1번계좌에 대한 출금 내역만 확인 
select h.id, h.amount, h.w_balance as balance, a.number, h.created_at,
	  coalesce(CAST(h.d_account_id as char(10)), 'ATM') as receiver	 
from history_tb as h 
left join account_tb as a on h.w_account_id = a.id
where h.w_account_id = 1;


-- 입금 내역 쿼리 
-- 1번계좌에 대한 출금 내역만 확인 
select h.id, h.amount, h.d_balance as balance, a.number, h.created_at,
	  coalesce(CAST(h.w_account_id as char(10)), 'ATM') as sender 	 
from history_tb as h 
left join account_tb as a on h.d_account_id = a.id
where h.d_account_id = 1;


-- 입출금 쿼리 
-- 1번 계좌에 대한 입금,출금 내역 전체 
select h.id, h.amount,
	case
		when h.w_account_id = 1 then (h.w_balance) 
        when h.d_account_id = 1 then (h.d_balance)
    end  as balance,
    coalesce(cast(wa.number as char(10)), 'ATM') as sender, 
    coalesce(cast(da.number as char(10)), 'ATM') as receiver,
    h.created_at
from history_tb as h 
left join account_tb as wa on h.w_account_id = wa.id
left join account_tb as da on h.d_account_id = da.id  
where h.w_account_id = 1 OR h.d_account_id = 1;

 

 

출금 내역 쿼리에 sender 컬럼도 포함시켜야 함, 입금 내역 쿼리에 receiver 컬럼도 포함시켜야 함

-- 코드상에서 사용할 쿼리 생성 
-- 출금에 대한 쿼리 출력 
-- receiver : 금액을 받는 대상, 
-- 기능적으로 하나의 JSP 페이지에서 전체 쿼리에 대한 결과 집합에 
-- 컬럼명을 동일하게 사용할 수 있도록 쿼리를 수정합니다 (같은 모델 클래스에 담을 예정)
-- 출금에는 AMT 출금, 1111 ---> 2222 이체 
select h.id, h.amount, h.w_balance AS balance, h.created_at, 
	   coalesce(cast(da.number as CHAR(10)), 'ATM')  as receiver, 
       wa.number as sender 
from history_tb as h 
left join account_tb as wa on wa.id = h.w_account_id
left join account_tb as da on da.id = h.d_account_id 
where h.w_account_id = 1; 



-- 입금에 대한 쿼리 출력 ( AMT 입금, 다른계좌에서 --> 1111계 받거나) 
select h.id, h.amount, h.d_balance as balance, h.created_at, 
	coalesce(CAST(wa.number as CHAR(10)) , 'ATM') as sender, 
    da.number as receiver
from history_tb as h 
left join account_tb as wa on wa.id = h.w_account_id
left join account_tb as da on da.id = h.d_account_id 
where h.d_account_id = 1;



-- 입,출금 전체 쿼리 
select h.id, h.amount,
	case
		when h.w_account_id = 1 then (h.w_balance) 
        when h.d_account_id = 1 then (h.d_balance)
    end  as balance,
    coalesce(cast(wa.number as char(10)), 'ATM') as sender, 
    coalesce(cast(da.number as char(10)), 'ATM') as receiver,
    h.created_at
from history_tb as h 
left join account_tb as wa on h.w_account_id = wa.id
left join account_tb as da on h.d_account_id = da.id  
where h.w_account_id = 1 OR h.d_account_id = 1;

select * from history_tb;