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

작업 순서

1. 출금, 이체(출금) 또는 입금,이체(입금)
2. 계좌 번호 함께 출력 하기
3. COALESCE 함수와 CAST 함수 사용해 보기
4. 입,출금에 대한 모든 정보 출력 하기 (CASE 구문에 활용)
5. 코드상에서 사용할 쿼리 확인 위 내용은 마이바티스에서 동적 쿼리(사용자 선택)를 생성하기 활동 입니다.

 

 

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

select * from history_tb;

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

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

 

 

2. 쿼리문 이해

초기 계좌 잔액

 

1111 계좌 잔액 : 1000원

2222 계좌 잔액 : 1000원

3333 계좌 잔액 : 0원

 

1번 계좌의 출금 내역 

select * from history_tb where w_account_id = 1;

 

실행 결과

 

출금의 종류 : 출금 페이지 활용(ATM 기기)한 단순 출금, 다른 계좌로 이체(송금)

 

amount : 거래 금액

w_account_id : 출금 계좌

d_account_id : 입금 계좌

w_balance : 출금 후 잔액

d_balance : 입금 후 잔액

created_at : 거래 시간

 

id 1 :  출금 계좌와 입금 계좌 PK 값 1, 2가 모두 존재합니다. 이는 1번 계좌에서 2번 계좌로 100원 이체한 것입니다. 

1번 계좌의 초기 잔액은 1000원, 2번 계좌 또한 1000원 이었습니다. 

이때 1번이 100원을 이체함으로써 출금 후 잔액이 1000 - 100 = 900원이 되었고, 

2번은 100원을 받음으로써 입금 후 잔액이 1000 + 100 = 1100원이 되었습니다. 

 

id 2 : 출금 계좌 PK 값이 1, 입금 계좌 PK 값이 null 인 것은 1번 계좌가 다른 계좌로 이체한 것이 아닌 

ATM 기기를 통해 단순 출금한 것입니다. 

1번 계좌는 앞에서 2번 계좌에게 이체한 이후의 잔액이 900원이므로

100원을 ATM기기에서 출금함으로써 출금 후 잔액이 900 - 100 = 800원이 됩니다. 

그러므로 입금 계좌 번호와 입금 후 잔액이 null로 뜬 것입니다. 

 

 

 

1번 계좌의 입금 내역 

select * from history_tb where d_account_id = 1;

 

실행 결과

 

id 3 :  출금 계좌 PK 값이 null, 입금 계좌 PK 값이 1인 것은 ATM 기기를 통해 입금한 것입니다. 

1번 계좌는 앞에서 출금한 이후의 잔액이 800원이므로

500원을 ATM 기기를 통해 입금함으로써 입금 후 잔액이 800 + 500 = 1300원이 됩니다. 

그러므로 출금 계좌 번호와 출금 후 잔액이 null로 뜬 것입니다. 

---

계좌 번호와 함께 출력하기(left join)

 

한 계좌에 대한 모든 출금 내역 확인

 

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;

 

실행 결과

 

id 1 : 계좌번호 1111이 초기 잔액 1000원 에서 100원 출금해서 출금 후 잔액이 900원 입니다.

id 2 : 계좌 번호 1111이 잔액 900원에서 100원 출금해서 출금 후 잔액이 800원 입니다.

 

 

 

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;

 

실행 결과

 

id 3 : 계좌번호 1111이 잔액 800원에서 500원 입금해서 입금 후 잔액이 1300원 입니다.

 

 

 

쿼리 실행 계획 확인

 

 

위 쿼리에서는 성능을 개선하기 위한 간단한 방법은 고민한다면

인덱스 활용, account_tb의 id 컬럼과 history_tb의 w_account_id 컬럼에 인덱스를 설정하는 방법으로

JOIN 연산과 WHERE 조건 연산의 성능을 향상시킬 수 있습니다.

또는 SELECT 절에 필요한 컬럼만 선택하여 데이터의 양을 줄일 수 있습니다.

 

 

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

COALESCE 함수는 인자로 주어진 값들 중에서 첫 번째 NULL이 아닌 값을 반환합니다.

만약 모든 인자가 NULL이라면, COALESCE 함수는 NULL을 반환합니다.

SELECT COALESCE(2, 1, 'third_value', 'fourth_value'); -- 2
SELECT COALESCE(NULL, 1, 'third_value', 'fourth_value'); -- 1
SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); -- third_value
SELECT COALESCE(NULL, NULL, NULL, 'fourth_value'); -- fourth_value
SELECT COALESCE(NULL, NULL, NULL, NULL);  -- NULL

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

 

 

CAST 함수는 하나의 데이터 타입을 다른 데이터 타입으로 변환(형 변환)하는 데 사용됩니다.

이 함수는 데이터의 표현 방식을 변경할 때 유용하며,

예를 들어 문자열을 숫자로, 날짜를 문자열로 변환하는 등의 작업에 사용될 수 있습니다.

-- select CAST('123' as INT); -- 실행 안 되지만 h2-console에서는 실행됨.
select CAST('123' as signed); -- 양수, 음수 다 포함 --> 123
select CAST(-123 as signed); -- 양수, 음수 다 포함 --> -123
select CAST('123' as unsigned); -- 양수만을 표현할 때 사용 --> 123

 

---

 

1번 계좌의 모든 출금 내역

출금이 만약 이체라면 키 값에 receiver(금액의 대상(받는 이))를 출력

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;

 

실행결과

 

id 1 : 계좌 번호 1111이  입금 받은 계좌 id(d_account_id) 2번에게 100원을 이체하고 잔액 900원이 남은 것을 알 수 있습니다.  

id 2 : 계좌 번호 1111이 ATM 기기에 100원 출금하고 잔액 800원이 남은 것을 알 수 있습니다. 

 

coalesce(CAST(h.d_account_id as char(10)), 'ATM') as receiver
=> 입금 계좌가 Null일 때 ATM 이라고 대체한다.


h.w_balance as balance로 한 이유
d_account_id 가 null(ATM 기기) 일 때 d_balance도 Null 이기 때문이고,
출금 내역을 조회하는 것이므로 출금 후 잔액을 조회해야 한다.

 

 

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;

 

실행결과

 

id 3 : 계좌 번호 1111이 ATM 기기에 500원 입금하고 잔액 800원 + 500원으로 1300원이 남은 것을 알 수 있습니다. 

 

---

 

쿼리문 파악하기

현재 1111 번 계좌는 account_tb에서 PK 값(id)은 1 입니다.

history_tb 에
w_account_id 컬럼에 값이 1이라는 의미는 1111번 계좌에 출금이나 이체(송금)이 발생했다는 의미입니다.
반대로, d_account_id 값이 1 이라는 의미는 1111번 계좌가 입금(ATM)이나 이체를 받았다는 의미로 해석될 수 있습니다.

즉, 1111 번 계좌에 출금 이력 2건, 입금이력 1건을 따로 출력했다면 이번 쿼리에서는 입금, 출금 3건 모두 출력할 수 있어야 합니다.

 

 

CAST() 함수 사용

desc history_tb;

CAST(h.w_account_id as char(10))
CAST(h.d_account_id as char(10))

 

실행결과

 

 w_account_id와 d_account_id는 int지만 char로 형변환(CAST) 함.

=> 아래 그림과 같이 model 패키지의 History Class에서 데이터 타입을 String으로 선언 가능

 

 

 

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

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

CASE 문은 SQL에서 조건에 따라 다른 값이나 표현식을 선택할 수 있게 하는 조건부 로직을 제공합니다.

기본적으로 CASE 문은 "만약... 그러면..."과 같은 결정 구조를 데이터베이스 쿼리 내에서 구현할 수 있게 해줍니다.

 

 

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;

 

실행 결과

 

id 1 : 1111 계좌에서 2222계좌로 100원 이체(잔액 1000(초기) - 100 = 900원)

id 2 : 1111 계좌에서 ATM 기기로 100원 출금(잔액 900 - 100 = 800원)

id 3 : ATM 기기에서 1111 계좌로 500원 입금(잔액 800 + 500 = 1300원)

 

when h.w_account_id = 1 then(h.w_balance)
-- > 출금 id가 1번일 때 출력 후 잔액을 출력해야 한다. (출금)

when h.d_account_id = 1 then(h.d_balance)
-- > 입금 id가 1번일 때 입금 후 잔액을 출력해야 한다. (입금)


sender와 receiver 를 같이 사용하기 위해서는 account_tb를 2번 join해야 한다. 

 

 

---

 

코드 상에서 사용할 쿼리 만들기

 

 

입금 버튼을 누르면 입금 내역만 출력되고, 

출금 버튼을 누르면 출금 내역만 출력되고, 

전체 버튼을 누르면 전체 입출금 내역이 출력되도록 하기 위해서 필요한 쿼리문을 뽑아 보았습니다. 

-- 출금 내역 쿼리 
-- 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 컬럼도 포함시켜야 합니다.

그러므로 위 쿼리문에서 수정이 필요합니다. 

 

 

출금 내역

1111 --> ATM 출금, 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;

-- da.number, d_account_id == null(ATM이 되어야 함)(받은 이)

 

실행 결과

 

coalesce(CAST(da.number as char(10)), 'ATM') as receiver
=> da.number, d_account_id 가 null일 때 입금 받은 이는 ATM 기기이므로
coalesce() 함수를 사용하여 null 부분을 ATM으로 채울 수 있도록 한다. 

 wa.number as sender
=> 출금 계좌 번호를 sender라고 명칭한다.

 

 

 

입금 내역

ATM --> 1111 계좌로 입금, 다른 계좌 --> 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;

-- wa.number, w_account_id == null(ATM이 되어야 함)(보낸이)

 

실행 결과

 

coalesce(CAST(wa.number as char(10)), 'ATM') as receiver
=> wa.number, w_account_id 가 null일 때 출금하는 장소는 ATM 기기이므로
coalesce() 함수를 사용하여 null 부분을 ATM으로 채울 수 있도록 한다. 

 da.number as receiver
=> 입금 계좌 번호를 receiver라고 명칭한다.