[SQL] 실습 + 빅데이터 분석 방법들

WINDOWS 함수를 다루는 방법을 다루고 있다

 

TABLE 정보 불러보기

select *
from cust_ord
limit 100;

 

고객 별 가장 많이 구매한 상품만 불러오고 싶다면??

고객별로 잘라서 구매한 금액별로 랭크를 매겨서 그 중 1등만 뽑아내서 가져오면 된다

- WINDOW > LAG 함수 - lag(orddate)? orddate의 다음 날짜를 가져오라

LAG에서 partition by == group by

select custid, orddate, 
        LAG(orddate) over
         (partition by custid order by prod_amt) as next
FROM cust_ord;

오류가 뜨지만 잘 실행되는 모습

구매 주기는 구매일의 차를 계산하여 구매할 수 있다

 

고객의 방문 주기 구해보기

- WINDOW > LAG -

select * from lag_date;

임의로 만든 래그데이트 테이블

고객의 방문 주기를 구하기 위해 null이면 이전 날짜를 불러와보기

select custid, orddate, ifnull(predate,orddate) 
from lag_date;

null일 때는 orddate가 나온다

이러고 이제 빼는 작업을 해 주면 된다

 

고객의 방문 주기를 구하기 위해 두 날짜의 차이를 빼보기

select custid, 
       Datediff(
            Date_format(
                        orddate, "%Y%m%d"
                        ), 
            Date_format(
                        ifnull(predate,orddate), "%Y%m%d"
                        )
               ) as revisit,
       ifnull(predate,orddate)
from lag_date;

두 날의 차이가 나타난다

이렇듯, 이전에 출력한 것을 가지고오고 싶다면 LAG함수를 이용하면 된다

같은 고객에서만 가지고오게 하는 것이 over(partition by ________) 의 방법이라고 볼 수 있다

하지만 이 때 첫 번째 항목일 경우 이전 항목이 없어 null이 나오므로

바로 기존 항목을 출력하는 방법을 쓰면 된다

select custid, 
       orddate,
       ifnull(lag(orddate) over(partition by custid), orddate)
from lag_date;

 

LAG의 반대는 LEAD이므로 이를 잘 이용하도록 하자

select custid, 
       orddate,
       ifnull(lead(orddate) over(partition by custid), orddate),
       ifnull(lag(orddate) over(partition by custid), orddate)
from lag_date;

use as

오류가 보이지만 작동에는 이상이 없다

RANK함수라는 것이 있다

함수를 이용한 이후 Rank를 이용하여 유의미한 데이터를 뽑아낼 수 있다

동률의 등수는 같은 등수로 표시하되, 비어있는 등수 없이 건너뛴다

select custid, 
       prod_cd,
       rank() over (partition by custid order by prod_amt desc) as ranking,
       prod_amt,
from cust_ord
where custid in(556, 988, 135)
order by custid, prod_amt desc;

ROW_NUM함수라는 것이 있다

이 함수는 같은 수라도 같은 등수를 매기지 않는 특징이 있다

공동 2등이 나오더라도 한명은 2등, 한명은 3등이 되는 것이다

DENCE_RANK함수라는 것이 있다 - dance 아님

등수에 빈틈이 없다 2등이 여러명이라도 그 다음은 3등이 되는 것이다

 

각 함수 별 차이

10점 4명, 9점 3명, 8점 5명일 경우

• RANK
  • 1등 4명, 5등 3명, 8등 5명
• ROW_NUM
  • 1~12등까지 각각 한 명씩
• DENCE_RANK
  • 1등 4명, 2등 3명, 3등 5명

#1. rank function -> 동률 동순위 (공동순위만큼 건너뜀)
select custid,prod_cd,
       rank() over( partition by custid order by prod_amt desc) _rank,
       prod_amt
 from cust_ord
#where custid = 135
 order by custid,3, prod_amt desc;
#2. row_number -> 동률순위 임의처리 (행번호)
select custid,prod_cd,
       row_number() over( partition by custid order by prod_amt desc) _rownum,
       prod_amt
 from cust_ord
#where custid = 135
 order by custid; 
 
 #3. dense_rank -> 동률순위  (순위에 빈틈없어) )
 select custid,prod_cd,
       dense_rank() over( partition by custid order by prod_amt desc ) _dencerank,
       prod_amt
 from cust_ord
 #where custid in (135,556,988)
 order by custid, prod_amt;
 
 #4. lead n칸 당기기
 select custid,
        orddate,
        lead(orddate) over(partition by custid order by prod_amt desc),
        ifnull( lead(orddate) over(partition by custid order by prod_amt desc ), orddate)
  from cust_ord;
  
 #5. lag :n칸 미루기
 
  select custid,
        orddate,
        lag(orddate) over(partition by custid order by prod_amt desc ),
        ifnull( lag(orddate) over(partition by custid order by prod_amt desc ), orddate)
  from cust_ord;

#1 result

#2 result

#3 result