데이터분석가를 위한 SQL기법 (정리 + AddOn 중)

샐제 플젝에서 데이터분석을 돌리기 위해 원천에서 EDA 및 필요 데이터 생성할 때 

주로, 자주 사용했던 건들을 정리해 가는 중....

신청승인건수

승인과 최초자격정보 (최초소속조직)

TO_CHAR(CURRENT_DATE, 'YYYYMM')

TRIM()

||

TO_CHAR(LAST_DAY(), 'YYYYMMDD')

TRIM(SUBSTR(PaymentDate, 1, 6))|| '01'

WITH  신청승인건수 AS (

SELECT .... FROM ..........

)

NVL( PaymentDate, ApprovalDate)

STATUS_CODE IN ('01', '02', '03')

STATUS_CODE NOT IN ('01', '02', '03')

ROW_NUMBER() OVER(PARTITION BY COL1, COL2, SUBSTR( PaymentDate, 1, 6) 

                               ORDER BY DECODE( ApprovalCode, 'A4', 1, 999) AS SEQ_NO_IN_SAME_MONTH

WHERE NOT( ApprovalCode = 'A3' AND SEQ_NO_IN_SAME_MONTH <> 1)

    AND SEQ_NO_IN_SAME_MONTH = 1

WHERE NOT(BFR_QLFY_CORP_NO IS NULL OR NVL(QLFY_DATE, APPROVAL_DATE) IS NULL)

    AND REQ_QLFY_NO IS NOT NULL

WHERE NOT(SBSD_PAY_SEQ_NO = 1 AND PAY_SEQ_NO_IN_SAME_MONTH > 1)

A LEFT OUTER JOIN B 

ON A.PK = B.PK AND A.REQ_DATE > B.APPROVAL_DATE

WHERE B.REVIEW_DATE IS NOT NULL

MONTHS_BETWEEN( PaymentDate, RequestDate )

데이터의 연결고리가 없어 특정일자 A 는 특정일자 B의 3개월전 부터 12개월후 이내에 있다고 가정했을 때

BETWEEN to_char(ADD_MONTHS  (TO_DATE(PaymentBeginDate, 'YYYYMMDD') -1, -2),   'YYYYMMDD')

       AND to_char(ADD_MONTHS  (TO_DATE(PaymentEndDate, 'YYYYMMDD')-1, +12),   'YYYYMMDD')

UNION ALL

MIN(PaymentBeginDate)

NVL(COUNT(DISTINCT(SBSD_PAY_SEQ_NO)), 0) AS "과거발생건수"

SELECT * 

  FROM A 

 WHERE '20151202' BETWEEN WorkingBeginDate AND WorkingEndDate

DECODE(COL1, NULL, 0, 1) AS OOO_YN

CASE WHEN LAST_APPROVAL_TYPE = '01' THEN 1 ELSE 0 END)

해당기간동안 OO시작일자가 한번이라도 있는 수혜자의 수

NVL( (LEAD(변경일자, 1)  OVER(PARTITION BY CORP_NO ORDER BY 변경일자, 변경순서) )

CASE WHEN ISDATE(PaymentDate) = 1 AND ISDATE(ApprovalDate) = 1 AND PaymentDate >= ApprovalDate

        THEN TRUNC( 

                 MONTHS_BETWEEN( TO_DATE(PaymentDate, 'YYYYMMDD'), TO_DATE(RejectDate, 'YYYYMMDD') ) 

               ) AS OOOMonths

        ELSE NULL 

        END 

AS GAP_MONTHS

        

U  의 경우 특정일자 B가 특정일자 C 이전

M 의 경우 특정일자 A 를 기준

다만 특정일자 B 가 없는 경우 특정일자 M 까지를 OOOO기간으로 계산함

SUM(BIZ_DAYS)

TRUNC(SUM(BIZ_DAYS)/COUNT(APPROVAL))

MAX( CASE WHEN LAST_CORP_ORDER = 1 THEN PaymentDate END )

특정 시점 직전( 몇개월 이내 ) 동안 특정 Position 또는 Role 및 권한을 가진적이 있는 경우

     SUBSTR(PaymentBeginDate, 1, 6) = WORKING_YEAR_MONTH 

OR SUBSTR(PaymentBeginDate, 1, 6) = TO_CHAR(ADD_MONTHS(TO_DATE(WORKING_YEAR_MONTH ||'01', 'YYYYMMDD'), 1), 'YYYYMM')

MAX(PaymentEndDate) OVER(PARTITION BY PERSON_ID)

# 데이터가 중복적으로 존재하거나, 불가피한 조건들의 조합으로 만들어지는 조인 조건절로 인해 데이터 중복이 발생하는 경우 처리가 필요함 (단계별 데이터 검증 - 건수 외)

NVL(PaymentEndDate, '99991231')

# 이상값이 존재하는 Row 건을 버리면 문제가 되는 경우가 많다. 

  ON A.CORP_NO = B.CORP_NO

 AND A.CORP_BASE_DATE >= B.ApprovalDate

 AND TO_CHAR(ADD_MONTHS(TO_DATE( A.CORP_BASE_DATE, 'YYYYMMDD'), -12), 'YYYYMMDD')

                    <  B.PaymentValidLastDate

특정날짜 시점을 기준으로 관련 정보를 산출하고자 하는 경우

COUNT(PERSON_ID)

TRUNC( AVG(WORKING_HOURS) )

TRUNC( AVG(PERSON_AGE_MONTHS) )

가장 최근의 유효한 정보 한건을 가져와 적용하기

/* 데이터 100만건 만들기 */

  SELECT  LEVEL AS LVL 

    FROM  DUAL

CONNECT  BY LEVEL < = 1,000,000

데이터웨어하우스 툴킷 8장 정리중 

"고객 관계 관리" 에서 특정기간동안의 합을 구하는 샘플 SQL 추가

Select Customer.Customer_Name, 

         sum( least(20140101, Transaction_Fact.End_Eff_DateTime)

                - greatest(20130101, Transaction_Fact.Begin_Eff_DateTime) )

 From Transaction_Fact, Customer_dim, Status_dim

Where <joins>

   And  Status_dim.Status_Description = ‘Fraud Alert’

   And  Transaction_Fact.Begin_Eff_DateTime <   ‘20140101’

   And  Transaction_Fact.End_Eff_DateTime    >= ‘20130101’

Group By Customer.Customer_Name