데이터베이스 질문과 답변
by 파갈제잉 [2018.09.11 09:53:16]
안녕하세요, 하기와 같이 SUM(A.AMT_SALES) AMT_SALES 내역에서
CD_YMD_DATE(DATE컬럼)의 일주일전, 365일전 SUM(A.AMT_SALES) AMT_SALES의 컬럼을 추가할 수 있는 방법이 있을까요??
SELECT /*+ FIRST_ROWS */ A.CD_YMD_DATE CD_YMD_DATE
, A.CD_BRAND CD_BRAND
, A.CD_FRANCHISE CD_FRANCHISE
, SUM(A.CNT_SALES) CNT_SALES
, SUM(A.QTY_SALES) QTY_SALES
, SUM(A.AMT_SALES) AMT_SALES
, SUM(A.AMT_DISCOUNT) AMT_DISCOUNT
, SUM(A.CNT_VESSEL) CNT_VESSEL
, SUM(A.QTY_SVC_SALES) QTY_SVC_SALES
, SUM(A.AMT_SVC_SALES) AMT_SVC_SALES
, SUM(A.CNT_SVC_VESSEL) CNT_SVC_VESSEL
,CASE WHEN b.NM_FRANCHISE LIKE '%(구)%' THEN replace(b.NM_FRANCHISE, '(구)', '')
WHEN b.NM_FRANCHISE LIKE '%(명변)%' THEN replace(b.NM_FRANCHISE, '(명변)', '')
WHEN b.NM_FRANCHISE LIKE '%(구1)%' THEN replace(b.NM_FRANCHISE, '(구1)', '')
WHEN b.NM_FRANCHISE LIKE '%(구2)%' THEN replace(b.NM_FRANCHISE, '(구2)', '')
WHEN b.NM_FRANCHISE LIKE '%(구3)%' THEN replace(b.NM_FRANCHISE, '(구3)', '')
WHEN b.NM_FRANCHISE LIKE '%(폐점)%' THEN replace(b.NM_FRANCHISE, '(폐점)', '')
WHEN b.NM_FRANCHISE LIKE '%(전환)%' THEN replace(b.NM_FRANCHISE, '(전환)', '')
ELSE b.NM_FRANCHISE END NM_FRANCHISE
,SYSDATE ETL_LDD
FROM FCT_STORE_SALES A, DIM_FRANCHISE B
WHERE A.cd_franchise = B.cd_franchise
GROUP BY A.CD_YMD_DATE, A.CD_BRAND, A.CD_FRANCHISE, B.NM_FRANCHISE;
, SUM(CASE WHEN TRUNC(A.CD_YMD_DATE) - 7 = TRUNC(SYSDATE) THEN A.AMT_SALES END )
, SUM(CASE WHEN TRUNC(A.CD_YMD_DATE) - 365 = TRUNC(SYSDATE) THEN A.AMT_SALES END )
SELECT a.cd_ymd_date
, a.cd_brand
, a.cd_franchise
, SUM(a.cnt_sales) cnt_sales
, SUM(a.qty_sales) qty_sales
, SUM(a.amt_sales) amt_sales
, SUM(a.amt_discount) amt_discount
, SUM(a.cnt_vessel) cnt_vessel
, SUM(a.qty_svc_sales) qty_svc_sales
, SUM(a.amt_svc_sales) amt_svc_sales
, SUM(a.cnt_svc_vessel) cnt_svc_vessel
, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(b.nm_franchise, '(구)' )
, '(명변)')
, '(구1)' )
, '(구2)' )
, '(구3)' )
, '(폐점)')
, '(전환)') AS nm_franchise
, sysdate etl_ldd
, SUM(SUM(a.amt_sales)) OVER(PARTITION BY a.cd_brand, a.cd_franchise ORDER BY a.cd_ymd_date
RANGE BETWEEN INTERVAL '7' DAY PRECEDING
AND INTERVAL '7' DAY PRECEDING) amt_sales_1weeks_ago
, SUM(SUM(a.amt_sales)) OVER(PARTITION BY a.cd_brand, a.cd_franchise ORDER BY a.cd_ymd_date
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING
AND INTERVAL '1' YEAR PRECEDING) amt_sales_1years_ago
FROM fct_store_sales a
, dim_franchise b
WHERE a.cd_franchise = b.cd_franchise
GROUP BY a.cd_ymd_date, a.cd_brand, a.cd_franchise, b.nm_franchise
;
답변 감사합니다^^
주신쿼리 select시 null값으로 나오는데 혹시 다른 방법이 있을까요
답변 감사합니다^^
주신쿼리 select시 null값으로 나오는데 혹시 다른 방법이 있을까요
구체적인 표현으로 질문해 주세요. 막연한 표현(X)
명확한 표현으로 질문해 주세요. 애매모호한 표현(X)
실질적인 예시자료를 가지고 질문해 주세요.
) 버튼을 클릭하여 작성 하시면 됩니다.