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

2022. 12. 10. 00:00·Development Study/Backend
728x90

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

728x90
저작자표시 비영리 변경금지 (새창열림)

'Development Study > Backend' 카테고리의 다른 글

[Nest.JS] TypeScript 기반 프레임워크, Nest.JS에 대해 알아보자  (0) 2023.05.21
[배경지식] Django, Flask, FastAPI의 차이점을 알아보자  (0) 2022.12.31
[SQL] 실습 정리  (0) 2022.12.03
[SQL] 실습 정리 (View)  (0) 2022.11.26
[SQL] 실습 정리(SUB_QUARY, UPDATE)  (0) 2022.11.19
'Development Study/Backend' 카테고리의 다른 글
  • [Nest.JS] TypeScript 기반 프레임워크, Nest.JS에 대해 알아보자
  • [배경지식] Django, Flask, FastAPI의 차이점을 알아보자
  • [SQL] 실습 정리
  • [SQL] 실습 정리 (View)
ThreeLight
ThreeLight
ThreeLight Studio의 블로그, TimeMap.exe에 오신 것을 환영합니다.
  • ThreeLight
    TimeMap.exe
    ThreeLight
  • 전체
    오늘
    어제
    • 분류 전체보기 (245)
      • Checkpoint (1)
      • (3D)Dev Deep Dive (0)
        • Templates & Guides (9)
        • Frontend origin (9)
        • Backend origin (1)
        • TroubleShootings (4)
      • Development Study (95)
        • Frontend (36)
        • Backend (21)
        • CS(Computer Science) (2)
        • Background Knowledges (11)
        • Algorithm (2)
        • Mobile (3)
        • AWS (6)
        • Python (6)
        • MSW(MapleStoryWorlds) (8)
      • Coding Test (59)
        • 문제.zip (1)
        • BaekJoon_JavaScript (0)
        • Programmers_JavaScript (9)
        • BaekJoon_Python (23)
        • Programmers_Python (10)
        • Undefined_Python (3)
        • Programmers_SQL (13)
      • 활동내역.zip (43)
        • 개인 (21)
        • Techeer (12)
        • Bootcamp (7)
        • Hackathon (1)
        • TeamProjects (2)
      • 여기 괜찮네??(사이트 | App) (5)
      • 재미있는 주제들 (8)
      • 개발 외 공부 저장소 (11)
        • 생산운영관리 (3)
        • 생활속의금융 (6)
        • 경영정보시스템 (2)
  • 링크

    • TimeMap.dmg (Portfolio)
    • GitHub 바로가기
    • 오픈프로필(카카오톡)
    • Medium 바로가기
    • Disquiet 바로가기
    • LinkedIn 바로가기
  • 인기 글

  • 태그

    react
    TypeScript
    Python
    CSS
    프로그래머스
    programmers
    JavaScript
    Baek Joon
    HTML
    SQL
  • 최근 글

  • 최근 댓글

  • hELLO· Designed By정상우.v4.10.1
ThreeLight
[SQL] 실습 + 빅데이터 분석 방법들
상단으로

티스토리툴바