오늘은 (주)브랜디 와 기업협업 프로젝트에서 중요 데이터의 이력관리를 위해 선분이력을 채택 하면서 값을 한번에 처리하기 위해서 사용했던 서브쿼리 두가지를 다뤄보고자 한다.
INSERT문 안의 SELECT문
셀러 관리 어드민 페이지에서 마스터권한을 가진 관리자가 한명의 셀러의 상태를 변경하는 상황을 가정해보자.
'입점대기' 상태의 셀러인 'seller2'의 '입점승인' action을 취해주면 데이터베이스에 'seller'의 상태는 어떻게 변할까?
선분이력을 사용하지 않는 상황에서는 seller 테이블에 많은 컬럼중 selle_status 컬럼이 sql UPDATE문에 의해서 수정될 것이다.
하지만 선분이력을 사용하는 상황에서는 seller의 이력관리용 테이블에 새로운 row가 추가된다.
위와같이 하나의 데이터가 수정 될 때 마다(이 경우는 셀러상태이름) 하나의 row를 생성해준다. 그리고 한명의 셀러의 데이터 이전버전의 선분이력끝시간을 새로운 이력이 생성된 시간으로 맍춰준다.
여기서 주의할 점은 새로운 이력은 '셀러상태이름' 컬럼에 해당하는 value 만 바뀌고 다른 데이터는 이전의 이력과 동일하다.
그렇다면 새로운 이력을 생성할 때 마다 클라이언트에게서 바꾸고자하는 데이터와 이전의 데이터를 전부 받아와야 할 까?
정답은 sql의 서브쿼리를 사용하면 이전이력의 데이터를 그대로 가져오고 변화된 새로운 값을 넣어서 새로운 row를 생성할 수 있다.(선분을 현재시간으로 끊어주고, foreign key관계를 가지는 테이블을 업데이트 해주는 과정은 여기서 다루지 않도록 한다.)
update_seller_status_statement = """
[0] INSERT INTO seller_infos
(
seller_account_id,
profile_image_url,
seller_status_id,
seller_type_id,
product_sort_id,
name_kr,
name_en,
modifier
)
[1] SELECT
seller_account_id,
profile_image_url,
[2] %(seller_status_id)s,
seller_type_id,
product_sort_id,
name_kr,
name_en,
[3] %(modifier)s
FROM
seller_infos
[4] WHERE
seller_account_id = %(seller_account_id)s
AND
close_time = '2037-12-31 23:59:59'
AND
is_deleted = 0
"""[0] 새로운 이력을 '생성' 하는것이기 때문에 update가 아닌 insert문을 사용해준다. 생성하고자 하는 column을 지정해준다.
[1] 여기서부터 서브쿼리가 실행된다. select문으로 원하는 값을 가져와서 insert문에 들어가게 된다.
[2][3] select문의 column이 들어갈자리에 바인딩될 파라미터를 넣어준다. 컬럼자리에 파라미터의 고정된 값이 들어가게 되면 그 자리는 그대로 고정값이 되어서 select문에 영향을 받지 않게 된다.
[4] select문에 where조건을 걸어서 원하는 새로운 이력이 생성되기 바로 직전의 이력의 seller 번호를 넣어준다.
이렇게 select문을 통해서 이전 이력의 모든 정보를 가져오고 변경하고자 하는값만 파라미터 바인딩을 통해서 클라이언트로부터 데이터를 전부 받아오지 않고 쿼리로 새로운 이력 생성이 가능해진다.
SELECT문 스칼라 서브쿼리
한명의 셀러가 가지고있는 상품의 개수를 count해서 데이터베이스에서 가져오려면 어떻게 해야할까?
- 셀러 테이블 밑에있는 데이터를 가져오고 상품테이블에 있는 row를 count해서가져온다.
-> 쿼리가 길어진다.- group by를 통해서 한명의 셀러가 가지는 상품테이블의 row를 count해서가져온다.
-> 삼품의 갯수문 count해서 가져오면되는데 group by에 의해서 필요없는 다른 컬럼들 까지 group에 영향을 받게된다.- select문에 스칼라 서브쿼리를 사용한다.
-> select문안에서 서브쿼리를 통해서 다른 컬럼에 영향을 주지 않고 상품만 count해서 가져올 수 있다.
이번 경우에 가장 적합한 3번의 스칼라 서브쿼리를 사용해서 셀러정보와 셀러가 가지고있는 상품의 갯수를 데이터베이스에서 한번에 가져와보자.
select_seller_list_statement = '''
SELECT
[0] seller_account_id,
accounts.login_id,
name_en,
name_kr,
brandi_app_user_id,
seller_statuses.name as seller_status,
seller_status_id,
seller_types.name as seller_type_name,
site_url,
[1] (
SELECT COUNT(0)
FROM product_infos
WHERE product_infos.seller_id = seller_infos.seller_account_id
AND product_infos.close_time = '2037-12-31 23:59:59'
) as product_count
[2] FROM seller_infos
[3] right JOIN seller_accounts ON seller_accounts.seller_account_no = seller_infos.seller_account_id
LEFT JOIN accounts ON seller_accounts.account_id = accounts.account_no
LEFT JOIN seller_statuses ON seller_infos.seller_status_id = seller_statuses.status_no
LEFT JOIN seller_types ON seller_infos.seller_type_id = seller_types.seller_type_no
[4] WHERE seller_infos.close_time = '2037-12-31 23:59:59.0'
AND accounts.is_deleted = 0
AND seller_accounts.is_deleted = 0
'''[0] 가져오고자 하는 컬럼의 값을 넣어준다. [2]에서와 같이 seller_infos(셀러 이력관리용 테이블)에서 값을 셀러데이터를 가져오도록 한다. 가장최근이력 하나만 가져오면 되기때문에 [4]에서와 같이 이력종료시간이 2037년인 값을 조건으로 걸어준다(가장최근이력의 이력종료시간은 2037년으로 동일하게 설정함.)
[1] select문안에 컬럼이름이 들어가는 자리에 스칼라 서브쿼리를 컬럼이름 product_count로 넣어준다. 위에서 select insert에서도 알 수 있듯이 select문 안에 특정한 값이 고정적으로 들어가면 그 값을 결과값에 포함 시킨다.
[3] 셀러 정보테이블에서 foreignkey 값을 가지는 테이블을 join으로 붙혀줌. 서브쿼리문 안의 where문에서 상품정보테이블의 seller_account번호와 seller_account테이블의 primary_key를 일치시켜 줌으로 인해서 각 row에 해당하는(각각의 셀러가 가지고 있는) products 테이블의 row가(상품의 갯수가) count되어 들어온다.
이제 가져온 값을 확인해보자.
위에서 지정해주었던 product_count가 seller_accounts
이렇게 seller_infos 테이블에 가장 최근이력의 값들에 상품갯수를 하나의 row로 추가해주기 위해서는 product_count 값에 하나의 값(스칼라값)이 들어가도록 값을 맞춰주어야하며 이 경우는 count라는 sql집계함수를 통해서 값을 하나로 만들었기 때문에 select문에 스칼라서브쿼리를 사용할 수 있었다.
