[SQL] 월별 주문, 방문, 가입, 신규주문/ 재주문 쿼리문

 

 

1. 월별 주문수, 주문금액

 

 

SELECT SUBSTRING(od_receipt_time,1,7) AS od_month 
, COUNT(SUBSTRING(od_receipt_time,1,7)) AS gl_od_cnt
, SUM(od_receipt_price) AS "gl_od_amount($)"
, SUM(od_receipt_price) * (SELECT data FROM s2_s2_api_currency_cms WHERE currency = 'USD' ORDER BY db_date desc limit 1) AS "gl_od_amount(krw)"  
FROM "dev"."gl_test"."s2_g5_shop_order" 
WHERE SUBSTRING(od_receipt_time,1,10) BETWEEN '2020-01-01' AND 'current_month' 
AND od_status IN ('완료','입금','배송','준비')
GROUP BY SUBSTRING(od_receipt_time,1,7)
ORDER BY od_month ASC

 

위 쿼리문에서 4행은 서브쿼리문을 사용했는데, 

달러를 가장 최근날짜 환율로 계산해서 원화로 만들어주는 작업 진행한 것

서브쿼리 사용법은 여기서 확인  👉  서브쿼리 사용법

 

 

 

 

 

---

 

2. 월별 가입

 

 

 

SELECT
 substring(mb_datetime, 1,7) AS month,
 COUNT(*) AS register_qty 
FROM s2_g5_member
WHERE substring(mb_datetime, 1,7) BETWEEN '2020-01' AND 'current_month' 
GROUP BY month 
ORDER BY month ASC

 

 

 

 

---

 

 

3. 월별 방문 UV

 

 

 

SELECT LEFT(vi_date,7) AS month
,COUNT(DISTINCT vi_ip) AS gl_visit
FROM s2_g5_visit
WHERE vi_date BETWEEN '2021-01-01' AND 'current_date' 
AND LEFT(vi_ip,9) !='**.**.180' 
GROUP BY LEFT(vi_date,7)
ORDER BY month ASC

 

 

 

 

 

---

 

 

 

 

 

 

4. 신규 / 재 주문

 

 

 

row_number() over 함수와

case 문 사용 

 

select month
, count(*) as total_order_
, sum(case when o_rn = 1 then 1 else 0 end) as new_order   
, sum(case when o_rn = 1 then 0 else 1 end) as re_order   
, sum(od_receipt_price) as total_price
, sum(case when o_rn = 1 then od_receipt_price else 0 end) as new_price
, sum(case when o_rn = 1 then 0 else od_receipt_price end) as re_price
from 
(select substring(od_receipt_time, 1,7) as month
, mb_id
, od_receipt_price
, row_number() over (partition by mb_id order by od_receipt_time) as o_rn
from s2_g5_shop_order
) s1

where month between '2022-01' and 'current_month'
group by month
order by 1

 

 

 

 

재주문비율 필드는 수식으로 만듦 (쿼리문으로 뽑지않고)