-- [SQL-5-2-7-1-a]
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.prep_cmp_dtm >= '2023-04-01'::date
AND t1.prep_cmp_dtm < '2023-05-01'::date
AND t1.shop_id = 'S100'
AND t1.mbr_id = 'M70309'
ORDER BY t1.ord_no DESC;
SQL
복사
-- [SQL-5-2-7-2-a]: [SQL-5-2-7-1-a]를 카운트 처리
SELECT COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.prep_cmp_dtm >= '2023-04-01'::date
AND t1.prep_cmp_dtm < '2023-05-01'::date
AND t1.shop_id = 'S100'
AND t1.mbr_id = 'M70309';
SQL
복사
-- [SQL-5-2-7-2-b] : [SQL-5-2-7-1-a]의 조건을 두 개씩 짝지어서 카운트 처리
-- prep_cmp_dtm + shop_id: 50940
SELECT COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.prep_cmp_dtm >= '2023-04-01'::date
AND t1.prep_cmp_dtm < '2023-05-01'::date
AND t1.shop_id = 'S100';
-- prep_cmp_dtm + mbr_id: 27
SELECT COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.prep_cmp_dtm >= '2023-04-01'::date
AND t1.prep_cmp_dtm < '2023-05-01'::date
AND t1.mbr_id = 'M70309';
-- shop_id + mbr_id: 218
SELECT COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S100'
AND t1.mbr_id = 'M70309';
SQL
복사
-- [SQL-5-2-7-2-c]
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= '2023-04-01'::date
AND t1.ord_dtm < '2023-04-02'::date
AND t1.ord_st = 'PKUP';
SQL
복사
-- [SQL-5-2-7-3-a]
-- 하루에 약 50,000 번 실행(하루 픽업 데이터 회원별 집계 조회)
SELECT t1.mbr_id, t1.pay_tp, COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.pkup_dtm >= '2023-04-01'::date
AND t1.pkup_dtm < '2023-04-01'::date + 1
GROUP BY t1.mbr_id, t1.pay_tp;
-- 하루에 약 100 번 실행(특정 매장의 하루 픽업 데이터 회원별 집계 조회)
SELECT t1.mbr_id, t1.pay_tp, COUNT(*) cnt
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S100'
AND t1.pkup_dtm >= '2024-12-01'::date
AND t1.pkup_dtm < '2024-12-01'::date + 1
GROUP BY t1.mbr_id, t1.pay_tp;
-- 하루에 약 500 번 실행(특정 회원의 하루 픽업 데이터 조회)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.mbr_id = 'M17911'
AND t1.pkup_dtm >= '2024-12-01'::date
AND t1.pkup_dtm < '2024-12-01'::date + 1
ORDER BY t1.pkup_dtm DESC LIMIT 3;
SQL
복사