22. 계좌 상세보기 - 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;

 

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;

 

입 출금 전체 쿼리 결과

 

입금 쿼리

 

출금 쿼리