[SQL, QuickSight] 누적일 비교 쿼리문 (전 월 누적일, 전 년 동 월 누적일)

IT/SQL

[SQL, QuickSight] 누적일 비교 쿼리문 (전 월 누적일, 전 년 동 월 누적일)

marketinkerbell 2022. 9. 20. 15:51

AWS QuickSight 에서 데이터 시각화 하기 위해 작성한 상세 쿼리문 공유합니다.

테이블명, 컬럼명은 DB 마다 다르겠지만 어떻게 쿼리문이 작성 되는지 살펴보시면 도움 되실거에요

퀵사이트 기본 사용 방법은 이 곳에서 👉 1. 데이터세트 생성하기

👉 2. 분석, 대시보드 만들기

1. 전 월 누적일 비교 표 (전 월 동기간)

- MTD 와 전 월 동일 기간 비교

SELECT visit.month
, gl_visit
, gl_order
, gl_total_price
, gl_total_price * (SELECT data FROM "s2_s2_api_currency_cms" WHERE currency = 'USD' ORDER BY db_date desc limit 1) AS "gl_total_price(KRW)"
, gl_customer_transaction
FROM
((SELECT LEFT(vi_date,7) AS month
,COUNT(DISTINCT vi_ip) AS gl_visit
FROM "s2_g5_visit"  
WHERE vi_date >= LEFT(current_date,7)+'-01' OR vi_date < DATE(ADD_MONTHS(current_date,-1)) AND vi_date BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND 'current_date'
AND LEFT(vi_ip,9) !='42.49.180' 
GROUP BY LEFT(vi_date,7)
ORDER BY month ASC) AS visit

JOIN

(SELECT LEFT(od_receipt_time,7) AS month
,COUNT(*) AS gl_order
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS gl_total_price
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS gl_customer_transaction
FROM "s2_g5_shop_order" 
WHERE LEFT(od_receipt_time,10) >= LEFT(current_date,7)+'-01' OR LEFT(od_receipt_time,10) < DATE(ADD_MONTHS(current_date,-1)) AND LEFT(od_receipt_time,10) BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND 'current_date' AND od_status IN ('입금','준비','배송','완료') 
GROUP BY LEFT(od_receipt_time,7)
ORDER BY month ASC) AS price
  
ON visit.month = price.month)

GROUP BY visit.month, gl_visit, gl_order, gl_total_price, "gl_total_price(KRW)", gl_customer_transaction
ORDER BY visit.month ASC

2. 전 월 누적일 그래프_방문 (전 월 동기간 비교 그래프)

- MTD 와 전 월 동일 기간 비교

SELECT this_month.day, this_month_visit, this_month_sum, last_month_visit, last_month_sum
FROM
((SELECT RIGHT(LEFT(vi_date,10),2) AS day
,COUNT(DISTINCT vi_ip) AS this_month_visit
,SUM(this_month_visit) over (order by day rows unbounded preceding) AS this_month_sum
FROM "s2_g5_visit"  
WHERE vi_date BETWEEN LEFT(current_date,7)+'-01' AND 'current_date'
AND LEFT(vi_ip,9) !='42.49.180' 
GROUP BY RIGHT(LEFT(vi_date,10),2)
ORDER BY day ASC) AS this_month

JOIN

(SELECT RIGHT(LEFT(vi_date,10),2) AS day
,COUNT(DISTINCT vi_ip) AS last_month_visit
,SUM(last_month_visit) over (order by day rows unbounded preceding) AS last_month_sum
FROM "s2_g5_visit"  
WHERE vi_date BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND DATE(ADD_MONTHS(current_date,-1))
AND LEFT(vi_ip,9) !='42.49.180' 
GROUP BY RIGHT(LEFT(vi_date,10),2)
ORDER BY day ASC) AS last_month

ON this_month.day = last_month.day)

GROUP BY this_month.day, this_month_visit, this_month_sum, last_month_visit, last_month_sum
ORDER BY this_month.day

3. 전 월 누적일 그래프_주문 (전 월 동기간 주문 비교 그래프)

SELECT this_month.day, this_month_order, this_month_order_sum, this_month_total_price, this_month_total_price_sum, this_month_customer_transaction, last_month_order, last_month_order_sum, last_month_total_price, last_month_total_price_sum, last_month_customer_transaction
FROM
((SELECT RIGHT(LEFT(od_receipt_time,10),2) AS day
,COUNT(*) AS this_month_order
,sum(this_month_order) over (order by day rows unbounded preceding) AS this_month_order_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS this_month_total_price
,sum(this_month_total_price) over (order by day rows unbounded preceding) AS this_month_total_price_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS this_month_customer_transaction
FROM "s2_g5_shop_order" 
WHERE LEFT(od_receipt_time,10) BETWEEN LEFT(current_date,7)+'-01' AND 'current_date' AND od_status IN ('입금','준비','배송','완료') 
GROUP BY RIGHT(LEFT(od_receipt_time,10),2)
ORDER BY day ASC) AS this_month

JOIN
  
(SELECT RIGHT(LEFT(od_receipt_time,10),2) AS day
,COUNT(*) AS last_month_order
,sum(last_month_order) over (order by day rows unbounded preceding) AS last_month_order_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS last_month_total_price
,sum(last_month_total_price) over (order by day rows unbounded preceding) AS last_month_total_price_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS last_month_customer_transaction
FROM "s2_g5_shop_order" 
WHERE LEFT(od_receipt_time,10) BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND DATE(ADD_MONTHS(current_date,-1)) AND od_status IN ('입금','준비','배송','완료') 
GROUP BY RIGHT(LEFT(od_receipt_time,10),2)
ORDER BY day ASC) AS last_month
  
ON this_month.day = last_month.day)

GROUP BY this_month.day, this_month_order, this_month_order_sum, this_month_total_price, this_month_total_price_sum, this_month_customer_transaction, last_month_order, last_month_order_sum, last_month_total_price, last_month_total_price_sum, last_month_customer_transaction
ORDER BY this_month.day ASC

4. 전 년 동월 누적일 비교 표 (전 년 동월 동일 기간)

(방문 데이터 누적 안 되어 있어서 주문만 뽑음)

SELECT month
, gl_order
, gl_total_price
, gl_total_price * (SELECT data FROM "s2_s2_api_currency_cms" WHERE currency = 'USD' ORDER BY db_date desc limit 1) AS "gl_total_price(KRW)"
, gl_customer_transaction
FROM
(SELECT LEFT(od_receipt_time,7) AS month
,COUNT(*) AS gl_order
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS gl_total_price
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS gl_customer_transaction --객단가
FROM "s2_g5_shop_order" 
  
WHERE LEFT(od_receipt_time,10) >= LEFT(current_date,7)+'-01'       
  OR LEFT(od_receipt_time,10) < DATE(ADD_MONTHS(current_date,-12)) 
  
  AND LEFT(od_receipt_time,10) BETWEEN LEFT(ADD_MONTHS(current_date,-12),7)+'-01' AND 'current_date'  
  
  AND od_status IN ('입금','준비','배송','완료') 
  
  
GROUP BY LEFT(od_receipt_time,7)
ORDER BY month ASC)

5. 전 년 누적일 그래프_주문 (전 년 동 월 동일 기간 비교 그래프)

SELECT this_month.day, this_month_order, this_month_order_sum, this_month_total_price, this_month_total_price_sum, this_month_customer_transaction, last_month_order, last_month_order_sum, last_month_total_price, last_month_total_price_sum, last_month_customer_transaction
FROM
((SELECT RIGHT(LEFT(od_receipt_time,10),2) AS day
,COUNT(*) AS this_month_order
,sum(this_month_order) over (order by day rows unbounded preceding) AS this_month_order_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS this_month_total_price
,sum(this_month_total_price) over (order by day rows unbounded preceding) AS this_month_total_price_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS this_month_customer_transaction
FROM "s2_g5_shop_order" 
WHERE LEFT(od_receipt_time,10) BETWEEN LEFT(current_date,7)+'-01' AND 'current_date' AND od_status IN ('입금','준비','배송','완료') 
GROUP BY RIGHT(LEFT(od_receipt_time,10),2)
ORDER BY day ASC) AS this_month

JOIN
  
(SELECT RIGHT(LEFT(od_receipt_time,10),2) AS day
,COUNT(*) AS last_month_order
,sum(last_month_order) over (order by day rows unbounded preceding) AS last_month_order_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price) AS last_month_total_price
,sum(last_month_total_price) over (order by day rows unbounded preceding) AS last_month_total_price_sum
,sum((od_cart_price - od_receipt_point - od_coupon) + (od_send_cost + od_send_cost2) - od_refund_price)/COUNT(*) AS last_month_customer_transaction
FROM "s2_g5_shop_order" 
WHERE LEFT(od_receipt_time,10) BETWEEN LEFT(ADD_MONTHS(current_date,-12),7)+'-01' AND DATE(ADD_MONTHS(current_date,-12)) AND od_status IN ('입금','준비','배송','완료') 
GROUP BY RIGHT(LEFT(od_receipt_time,10),2)
ORDER BY day ASC) AS last_month
  
ON this_month.day = last_month.day)

GROUP BY this_month.day, this_month_order, this_month_order_sum, this_month_total_price, this_month_total_price_sum, this_month_customer_transaction, last_month_order, last_month_order_sum, last_month_total_price, last_month_total_price_sum, last_month_customer_transaction
ORDER BY this_month.day ASC