Bank App 만들기 - 계좌 상세보기(쿼리학습)

계좌 상세보기 쿼리를 만들기 위한 단계 별 학습

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;

위 그림은 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인 계좌에서 다른 계좌로 이체하지 않았다는 의미이며, 이것은 단순하게 출금(ATM)했다는 의미로 판단할 수 있다. AMOUNT 거래 금액이 100원으로 확인할 수 있으며 출금 후 W_BALANCE에 800원이 남았다 라고 확인 할 수 있다.

 

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

---

2. 계좌번호 함께 출력하기

-- 2단계
-- 계좌 번호와 함께 출력 하기
-- 모든 계좌에 대한 출금 내역을 확인 한다면 ?
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;

-- 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;

-- 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) -- 1번에서 출금 시 출금 잔액 뽑기
        when h.d_account_id = 1 then (h.d_balance) -- 1번에 입금됐을 시 입금 잔액 뽑기
    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 페이지에서 전체 쿼리에 대한 결과집합의 컬럼명을 동일하게 사용할 수 있도록 쿼리 수정
-- (즉, 같은 모델 클래스에 담을 예정)
-- 출금에는 ATM 출금이 있고, 이체가 있다.
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;

-- 입금에 대한 쿼리 출력 (ATM 입금, 다른 계좌에서 받거나)
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;

 

입 출금 전체 쿼리 결과

 

입금 쿼리 결과

 

출금 쿼리 결과