ItemPrcHist 테이블 살펴보기
지금까지 살펴본 조인은 모두 같다(=) 조건을 사용했다. 그러다보니, 조인 조건에는 같다(=) 조건만 사용할 수 있다고 착각할 수 있다. 대부분 테이블 간의 PK/FK의 참조 관계에서 조인이 많이 발생하다 보니 같다(=) 조건을 많이 사용하게 될 뿐이다. 다양한 업무를 처리하다 보면, PK/FK가 아님에도 조인이 발생하게 된다. 그리고 이 과정에서 같다(=) 조건이 아닌 다른 조건을 사용해야 할 수도 있다.
아래는 Item(상품)과 ItemPrcHist(상품가격이력)의 테이블구조다. 일반적으로 상품의 가격과 같은 정보는 시점에 따라 변한다. 이러한 가격 데이터를 관리하기 위한 다양한 방법이 있는데, 그 중에 하나가 아래 ERD의 상품가격이력 테이블과 같이 가격의 시작일자와 종료일자를 추가해 관리하는 방법이다. 이처럼 시작일자와 종료일자를 사용해 데이터의 변화를 관리하는 구조를 선분이력구조라고 한다.
Tip. 선분이력구조
데이터의 구간별 변화를 시작시점과 종료시점 구간으로 관리하는 방법이다. 이와 같은 방법은 데이터 조회에 유리한 부분이 있으나, 데이터 입력시 선분이 중복되지 않도록 처리하면서, 선분일자를 조정하는 부분이 까다로운 편이다. 로직 실수로 중복된 구간이 입력되면 데이터 처리에 큰 문제가 생기기도 한다. 그럼에도 불구하고 선분이력을 선택해 조회 SQL의 복잡함을 제거하는 것이 전체적으로 더 좋을 가능성이 높다.
상품(Item) 테이블은 지금까지 계속 사용해왔기 때문에 익숙할 것이다. 상품의 가격 변화를 관리하는 상품가격이력(ItemPrcHist)의 데이터를 살펴보자.
-- [SQL-7-8-1]
-- 아메리카노(R)과 아인슈패너(R)의 가격이력을 조회
SELECT T1.ItemId ,T1.FromDt ,T1.ToDt ,T1.SalePrc
FROM startdb.ItemPrcHist T1
WHERE T1.ItemId IN ('AMR','EINR');
ItemId FromDt ToDt SalePrc
------ ---------- ---------- --------
AMR 2010-01-01 2022-12-31 4000.000
AMR 2023-01-01 2099-12-31 4500.000
EINR 2023-04-01 2023-12-31 5500.000
EINR 2024-01-01 2024-01-15 5800.000
EINR 2024-01-16 2099-12-31 6300.000
SQL
복사
ItemPrcHist를 통해 상품별로 가격 변화를 확인할 수 있다. AMR(아메리카노(R))의 경우, 2010년부터 2022년 12월말까지 4,000원이었으나, 2023년부터 4,500원으로 가격이 변경되었다. EINR(아인슈패너)은 2023년 4월부터 2023년 12월말까지는 5,500원이었고, 2024년 1월 1일부터 2024년 1월 15일까지 보름 동안은 5,800원이었다. 그리고 2024년 1월 16일부터 2099년 12월 31일까지는 6,300원이다. 2099년이 오려면 아직 멀었다. 선분이력구조에서 마지막 데이터의 끝 날자는 일부러 큰 값을 고정해서 사용한다. 해당 테이블에서 특정 시점의 데이터를 검색하려면 From과 To가 모두 조건으로 사용되므로 To 값이 반드시 채워져 있어야 하기 때문이다.
AMR과 EINR의 2023년 10월 1일 기준의 가격을 조회하려면 다음과 같이 SQL을 작성하면 된다.
-- [SQL-7-8-2]
-- 아메리카노(R)과 아인슈패너(R)의 2023년 10월 1일의 가격을 조회
SELECT T1.ItemId ,T1.FromDt ,T1.ToDt ,T1.SalePrc
FROM startdb.ItemPrcHist T1
WHERE T1.ItemId IN ('AMR','EINR')
AND T1.FromDt <= STR_TO_DATE('20231001','%Y%m%d')
AND T1.ToDt >= STR_TO_DATE('20231001','%Y%m%d');
ItemId FromDt ToDt SalePrc
------ ---------- ---------- --------
AMR 2023-01-01 2099-12-31 4500.000
EINR 2023-04-01 2023-12-31 5500.000
SQL
복사
조회하고 싶은 일자 조건을 From과 To 사이의 조건으로 사용하면 된다. 위 SQL은 아래와 같이 BETWEEN으로 처리할 수도 이다.
-- [SQL-7-8-3]
-- 아메리카노(R)과 아인슈패너(R)의 2023년 10월 1일의 가격을 조회(BETWEEN 처리)
SELECT T1.ItemId ,T1.FromDt ,T1.ToDt ,T1.SalePrc
FROM startdb.ItemPrcHist T1
WHERE T1.ItemId IN ('AMR','EINR')
AND STR_TO_DATE('20231001','%Y%m%d') BETWEEN T1.FromDt AND T1.ToDt;
SQL
복사
이번에는 Item 테이블과 ItemPrcHist를 조인해보자. Item과 ItemPrcHist는 1:M 관계다. 그러므로 조인을 하게 되면 1쪽인 Item이 M쪽인 ItemPrcHist만큼 데이터가 늘어나게 된다. 하지만 아래와 같이 ItemPrcHist쪽에 일자 조건을 적절히 사용하면 1:1 조인과 같은 결과가 만들어진다. Item 한 건에 정확히 ItemPrcHist 한 건만 조인되어 결과로 출력된다.
-- [SQL-7-8-4]
-- 아메리카노(R)과 아인슈패너(R)의 2023년 10월 1일의 가격 조회(조인 추가)
SELECT T1.ItemID ,T1.ItemNm ,T2.FromDt ,T2.ToDt ,T2.SalePrc
FROM startdb.Item T1
INNER JOIN startdb.ItemPrcHist T2
ON (T2.ItemId = T1.ItemId)
WHERE T1.ItemId IN ('AMR','EINR')
AND T2.FromDt <= STR_TO_DATE('20231001','%Y%m%d')
AND T2.ToDt >= STR_TO_DATE('20231001','%Y%m%d')
ORDER BY T1.ItemId;
ItemID ItemNm FromDt ToDt SalePrc
------ ------------- ---------- ---------- --------
AMR Americano(R) 2023-01-01 2099-12-31 4500.000
EINR Einspanner(R) 2023-04-01 2023-12-31 5500.000
SQL
복사
주문일자에 따른 가격 가져오기
Ord와 OrdDet를 조인하면서, 주문일자에 따른 ItemPrcHist 정보를 가져오려고 한다. 먼저 Ord와 OrdDet, Item, ItemPrcHist 테이블들의 ERD를 살펴보면서 어떻게 테이블들을 연결할지 생각해보기 바란다.
이제 Ord와 OrdDet를 조인한 SQL 하나를 살펴보자. S050 매장의 24년 1월 13일부터 17일까지의 주문 정보를 조회하는 SQL이다. EINR(아인슈패너(R))에 대한 주문만 조회하고 있다.
-- [SQL-7-8-5] S050 매장의 2024년1월13일~2024년1월17일의 EINR 주문 정보
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T1.OrdNo = T2.OrdNo)
WHERE T1.OrdDtm >= STR_TO_DATE('20240113','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20240118','%Y%m%d')
AND T1.ShopID = 'S050'
AND T2.ItemId = 'EINR'
ORDER BY T1.OrdNo ,T2.OrdDetNo;
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty
------ ------------------- ------ -------- ------ ------
635560 2024-01-13 14:30:00 S050 3 EINR 1
642574 2024-01-17 11:30:00 S050 2 EINR 1
SQL
복사
위 결과에 주문일시 시점에 따른 상품의 가격을 조인으로 추가하려고 한다. (해당 정보는 OrdDet 테이블에 SalePrc 컬럼으로 이미 저장되어 있다. 여기서는 SQL 연습을 위해 ItemPrcHist를 조인해보도록 한다.)
SQL의 결과를 보면 EINR 상품에 대해 2024년 1월 13일 주문과 2024년 1월 17일 주문이 있는 것을 알 수 있다. ItemPrcHist를 조인으로 추가하기 전에 ItemPrcHist를 별도로 조회해보자. 아래 SQL의 결과를 살펴보면서, 위 SQL 결과에 어떤 구간의 가격이 조인되어야 하는지 생각해보기 바란다.
-- [SQL-7-8-6] ItemPrcHist 살펴보기
SELECT T1.ItemId ,T1.FromDt ,T1.ToDt ,T1.SalePrc
FROM startdb.ItemPrcHist T1
WHERE T1.ItemId = 'EINR';
ItemId FromDt ToDt SalePrc
------ ---------- ---------- --------
EINR 2023-04-01 2023-12-31 5500.000
EINR 2024-01-01 2024-01-15 5800.000
EINR 2024-01-16 2099-12-31 6300.000
SQL
복사
2024년 1월 13일 주문에는 위 결과에서 두 번째 레코드인 5,800원이 조인되어야 하고, 2024년 1월 17일 주문에는 세번째 레코드인 6,300원이 결합되어야 한다. 이처럼 조인을 하기 위해서는 ItemPrcHist의 FromDt와 ToDt 사이에 Ord의 OrdDtm이 들어가도록 조인 조건을 처리해야 한다.
-- [SQL-7-8-7]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
,T3.FromDt ,T3.ToDt ,T3.SalePrc
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T1.OrdNo = T2.OrdNo)
INNER JOIN startdb.ItemPrcHist T3
ON (T3.ItemID = T2.ItemID
AND T3.FromDt <= T1.OrdDtm -- > 날짜 구간 조인
AND T3.ToDt >= T1.OrdDtm) -- > 날짜 구간 조인
WHERE T1.OrdDtm >= STR_TO_DATE('20240113','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20240118','%Y%m%d')
AND T1.ShopID = 'S050'
AND T2.ItemId = 'EINR'
ORDER BY T1.OrdNo;
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty FromDt ToDt SalePrc
------ ------------------- ------ -------- ------ ------ ---------- ---------- --------
635560 2024-01-13 14:30:00 S050 3 EINR 1 2024-01-01 2024-01-15 5800.000
642574 2024-01-17 11:30:00 S050 2 EINR 1 2024-01-16 2099-12-31 6300.000
SQL
복사
위 SQL에서 ItemPrcHist(T3)에 입력된 조인 조건은 다음과 같다.
•
T3.ItemId: T2(OrdDet)에서 ItemId를 같다(=)조건으로 처리중
•
T3.FromDt, T3.ToDt: T1(Ord)의 OrdDtm을 범위(>=,<=)조건으로 처리중
조인 조건을 T1과 T2, 두 테이블에서 받고 있다. 하지만 T1과 T2가 먼저 조인되어 하나의 데이터 집합이 만들어졌다고 생각해보자. 결국 “T3는 (T1T2)라는 하나의 데이터 집합에서 조인 조건을 받고 있다”라고 생각하면 된다. 주의 깊게 볼 것은 OrdDtm 조건이 FromDt와 ToDt 사이에 들어가도록 범위 조건이 조인 조건으로 사용되고 있다는 점이다. 이처럼 처리해야만, 주문일자에 해당하는 가격 정보를 적절하게 가져올 수 있다. (위 SQL에서 날짜 조건 범위는 조금 더 조정해야 한다. 관련 내용은 조금 있다 살펴보자.) 테이블을 조인하는데 반드시 같다(=) 조건만 사용해야 하는 것은 아니라는 점을 기억하기 바란다.
지금까지 “일반적”으로 테이블 간의 조인은 PK/FK 간에 이루어진다고 설명해왔다. 이같은 설명을 하면서 “일반적”을 강조하는 이유는 “항상” 그런 것이 아니기 때문이다. ItemPrcHist는 Item 테이블을 참조할 뿐, Ord와 OrdDet를 직접적으로 참조하지 않지만, 우리는 주문 정보에 가격 정보를 조인할 수 있었다.
날짜 조건 조인에 대한 고민
앞에서 OrdDtm을 사용해 FromDt, ToDt와 범위 조건으로 조인하는 방법은 문제가 있다. 어떤 문제가 있는지 살펴보도록 하자.
이번에는 S046 매장의 아인슈패너 주문을 조회해보자. 아래와 같다.
-- [SQL-7-8-8] S046 매장의 아인슈패너 주문 조회
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T1.OrdNo = T2.OrdNo)
WHERE T1.OrdDtm >= STR_TO_DATE('20240113','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20240118','%Y%m%d')
AND T1.ShopID = 'S046'
AND T2.ItemId = 'EINR'
ORDER BY T1.OrdNo;
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty
------ ------------------- ------ -------- ------ ------
635026 2024-01-13 12:00:00 S046 2 EINR 1
636802 2024-01-14 11:00:00 S046 2 EINR 1
638698 2024-01-15 11:00:00 S046 2 EINR 1
SQL
복사
세 건의 주문 데이터가 조회되었다. 24년의 1월 13일, 1월 14일, 1월 15일의 주문이 존재한다. 위 결과는 아래 그림과 같이 ItemPrcHist와 조인 처리되어야 한다.
앞에서 했던 것처럼 위 데이터에 ItemPrcHist를 조인해 SalePrc 값을 가져와 보자.
-- [SQL-7-8-9]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
,T3.FromDt ,T3.ToDt ,T3.SalePrc
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T1.OrdNo = T2.OrdNo)
INNER JOIN startdb.ItemPrcHist T3
ON (T3.ItemID = T2.ItemID
AND T3.FromDt <= T1.OrdDtm
AND T3.ToDt >= T1.OrdDtm)
WHERE T1.OrdDtm >= STR_TO_DATE('20240113','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20240118','%Y%m%d')
AND T1.ShopID = 'S046'
AND T2.ItemId = 'EINR'
ORDER BY T1.OrdNo;
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty FromDt ToDt SalePrc
------ ------------------- ------ -------- ------ ------ ---------- ---------- --------
635026 2024-01-13 12:00:00 S046 2 EINR 1 2024-01-01 2024-01-15 5800.000
636802 2024-01-14 11:00:00 S046 2 EINR 1 2024-01-01 2024-01-15 5800.000
SQL
복사
조인된 결과를 보면, 이전의 데이터 집합에서 한 건이 사라졌다. 사라진 데이터는 2024년 1월 15일 주문 데이터다. Ord 테이블의 OrdDtm은 시분초까지 관리한다. 사라진 데이터의 정확한 OrdDtm은 ‘2024-01-15 11:00:00’ 이다. 왜 사라졌는지 살펴보기 위해 아인슈페너의 ItemPrcHist를 다시 조회해보자.
-- [SQL-7-8-10]
SELECT T1.ItemId ,T1.FromDt ,T1.ToDt ,T1.SalePrc
FROM startdb.ItemPrcHist T1
WHERE T1.ItemId = 'EINR';
ItemId FromDt ToDt SalePrc
------ ---------- ---------- --------
EINR 2023-04-01 2023-12-31 5500.000
EINR 2024-01-01 2024-01-15 5800.000
EINR 2024-01-16 2099-12-31 6300.000
SQL
복사
사라진 ‘2024-01-15 11:00:00’는 2024년 1월 15일 00시보다는 크고 2024년 1월 16일 00시 보다는 작다. ItemPrcHist의 FromDt와 ToDt는 시분초는 관리되지 않는다. 그러므로 시간이 포함된 데이터와 조인하면서 조인에 실패한 경우가 발생하고 조인 결과에서 데이터가 사라지게 된 것이다.
이를 해결하기 위해서는 다음과 같이, OrdDtm을 DATE 함수로 시분초 값을 제거한 후 조인 처리해야 한다. 1월 15일 데이터도 정상적으로 나오는 것을 알 수 있다.
-- [SQL-7-8-11]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.ShopId
,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
,T3.FromDt ,T3.ToDt ,T3.SalePrc
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T1.OrdNo = T2.OrdNo)
INNER JOIN startdb.ItemPrcHist T3
ON (T3.ItemID = T2.ItemID
AND T3.FromDt <= DATE(T1.OrdDtm)
AND T3.ToDt >= DATE(T1.OrdDtm))
WHERE T1.OrdDtm >= STR_TO_DATE('20240113','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20240118','%Y%m%d')
AND T1.ShopID = 'S046'
AND T2.ItemId = 'EINR'
ORDER BY T1.OrdNo;
OrdNo OrdDtm ShopId OrdDetNo ItemId OrdQty FromDt ToDt SalePrc
------ ------------------- ------ -------- ------ ------ ---------- ---------- --------
635026 2024-01-13 12:00:00 S046 2 EINR 1 2024-01-01 2024-01-15 5800.000
636802 2024-01-14 11:00:00 S046 2 EINR 1 2024-01-01 2024-01-15 5800.000
638698 2024-01-15 11:00:00 S046 2 EINR 1 2024-01-01 2024-01-15 5800.000
SQL
복사
앞에서 성능을 위해서는 WHERE 절에서 테이블의 컬럼은 변형하면 안된다고 했다. (ON 절도 마찬가지다.) 하지만 위와 같이 시분초가 존재하는 데이터를 시분초가 없는 데이터와 비교하기 위해서는 불가피하게 컬럼 변형이 발생할 수 밖에 없다. 만약에 조인 조건에서 테이블 컬럼 변형을 피하려면, ItemPrcHist의 ToDt에 종료되는 일자가 아닌, 종료되는 일자의 +1을 한 값을 저장하는 방법이 있다. 그리고 ToDt에 대한 OrdDtm 조건을 미만(T3.ToDt >T1.OrdDtm)으로 처리하면 된다. 이와 같은 방법은 모델링, 관리체계, 표준화 등의 고려가 필요하며, 그에 맞게 데이터가 발생되도록 프로그램도 처리해야 한다. 간단하게 이렇게 해야 한다라고 말하기 어려운 부분이다. 지금 우리가 배워야 할 것은 값이 의미하는 부분을 정확히 파악하고 그에 맞게 조인을 작성하는 것이다. 특히 날짜 값에는 시분초를 어떻게 관리하고 있는지 항상 주의하고 SQL을 작성해야 한다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-8-1
‘M9269’ 회원의 2024년 1월 5일 주문 정보를 보여주세요. 어떤 상품을 주문했는지도 보여주세요. 주문 시점의 상품 가격에 대해 상품가격이력(ItemPricHist) 테이블을 사용해 보여주세요.
•
대상 테이블: 회원(Member), 주문(Ord), 주문상세(OrdDet), 상품(Item), 상품가격이력(ItemPrcHist)
•
조회 조건
◦
Member: MemberId가 M9269인 회원
◦
Ord: OrdDtm이 2024년 1월 5일
•
조회 컬럼: MemberId ,NicNm, JoinDtm, OrdDtm, OrdQty, ItemId, ItemNm, FromDt(가격시작일자), SalePrc
•
추가 조건: FromDt와 SalePrc는 Ord의 OrdDtm 시점의 ItemPrcHist 정보로 처리합니다.
MemberID NickNm JoinDtm OrdDtm OrdQty ItemId ItemNm FromDt SalePrc
-------- ------ ------------------- ------------------- ------ ------ ------------------- ---------- --------
M9269 Ice185 2022-07-01 00:00:00 2024-01-05 13:00:00 1 CMFR Chocolate Muffin(R) 2023-01-01 3500.000
M9269 Ice185 2022-07-01 00:00:00 2024-01-05 14:00:00 1 CLB Cafe Latte(B) 2023-01-01 5000.000
SQL
복사
BOOSTER QUIZ 7-8-2
[BOOSTER QUIZ 7-8-1]의 SQL을 사용합니다. 회원의 가입일시(JoinDtm) 시점의 주문한 상품의 SalePrc를 보여주세요.
•
추가되는 컬럼: FromDtAtJoin, SalePrcAtJoin
•
FromDtAtJoin, SalePrcAtJoin: 가입일시 시점의 ItemPrcHist의 FromDt와 SalePrc입니다.
◦
기존 SQL에 ItemPrcHist 조인을 하나 더 추가합니다.
◦
Member의 JoinDtm과 OrdDet의 Item을 조인 조건으로 사용합니다.
MemberID NickNm JoinDtm OrdDtm OrdQty ItemId ItemNm FromDt SalePrc FromDtAtJoin SalePrcAtJoin
-------- ------ ------------------- ------------------- ------ ------ ------------------- ---------- -------- ------------ -------------
M9269 Ice185 2022-07-01 00:00:00 2024-01-05 13:00:00 1 CMFR Chocolate Muffin(R) 2023-01-01 3500.000 2010-01-01 3000.000
M9269 Ice185 2022-07-01 00:00:00 2024-01-05 14:00:00 1 CLB Cafe Latte(B) 2023-01-01 5000.000 2010-01-01 4500.000
SQL
복사
Upper: 7. JOIN
