월별 상품최저가

외래키로 연관이 있는 대략적인 부모상품(시단위) 정보와, 출발일,출발가격이 있는 실제 판매 상품정보가 있으며

부모상품기준으로 하위에 여러가지 여행상품이 존재한다.

고객의 사고는 이렇다. "3월에 가장싼 서울 구경 여행상품을 구매하고 싶다."

실시간검색도 가능하지만 배치후 검색엔진에 태우기위한 용도....

sql버젼에따른 제약: http://d2.naver.com/helloworld/907716

mssql 2008기준이며, 윈도우함수가 2008부터 지원하지만 표준스펙을 모두 지원하지 않는다.
mysql에 사용시 with절 사용불가며, 5.X 이하버젼에서는 윈도우함수도 지원되지 않는다.

use study;

select * from tbl_basegoods;
select * from tbl_subgoods;

-- 기본+하위 상품 조인하여 모두 나타내기
select ts.id,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
inner join tbl_basegoods tb on tb.id = ts.pid 
where ts.startdate   BETWEEN '2018-01-01' AND '2018-12-30';

-- 3월 4월 기본상품별 출발 최저가 : 편의를 위해 년도 생략 2018년이라고 가정....입력조건은 특정월....
with tmpView as(
	select DISTINCT MIN(ts.price)  OVER(PARTITION BY tb.id) as minprice,
	ts.id as sid,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
	inner join tbl_basegoods tb on tb.id = ts.pid 
	where MONTH(ts.startdate) = 03
UNION ALL
	select DISTINCT MIN(ts.price)  OVER(PARTITION BY tb.id) as minprice,
	ts.id as sid,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
	inner join tbl_basegoods tb on tb.id = ts.pid 
	where MONTH(ts.startdate) = 04
)
select tv.sid,tv.price,tv.goodsname, case when tv.minprice=tv.price then
CAST(MONTH(tv.startdate) AS VARCHAR(5)) +'월' else null end as bestmonth  from tmpView as tv where tv.minprice=tv.price

결과

참고 : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/select-over-clause-transact-sql

버젼별로 차이를 파악하는것도 힘들지만, 윈도우함수는 dbms마다 너무도 다른 차이를 보인다.

개선 고려 사항:

다중 월옵션에대한 중복쿼리 제거
윈도우함수와 커서를 적절하게 사용하여, 전체상품 속에 bestmonth필드를 확장하기
배치를위해 어차피 발생하는 순차 풀스캔에서, 어플리케이션에서 메모리내에서 집계하기

Content

Space Tools