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

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

고객의 사고는 이렇다. "집결장소가 어디든, 특정월에 가장싼 서울 구경 여행상품을 구매하고 싶다.(그레잇 서울여행) "

실시간 DB를통한 검색도 가능하지만, 가격이 수시로변경되는것은 아니기때문에

집계처리의 결과를 루씬과같은 검색엔진의 인덱스와 결합을 시켜 DB검색비용을 줄이는 방식이 권장이된다.(탈 중앙화는 어쨋든 개발복잡도가 증가한다.)


제약db: sql2008


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마다 너무도 다른 차이를 보인다.


사고방식:

  • 시뮬레이션을 위해 문제해결을 위한 명확한 데이터 샘플링을 준비
  • 3월과 4월이  3:7 비율이라고 가정했을시 계산에 사용되는 집합도 3:7로 데이터 범위 비율이 변경되면 안됨
  • 집계에 사용된 id가 유지되어야함으로 group by가 아닌 over절로  각각 달에대한 범위계산처리가필요
  • 전체 결과의 행수는 변경이 없으며..., 마지막에 최저값이 아닌 값들을 다버리고 최소의 상태에서 언제든 조인가능한 상태로 대기


개선 고려 사항:

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





  • No labels