안녕하세요 엘체프 입니다. 테이블 분석중 2016 6월 고객의 접속기록을 파악할 수 있는 테이블을 발견 했습니다. 김팀장님이 아래의 결과 처럼 뽑아 내도록 지시 했습니다. 풀어보죠. 답도 아래에 바로 있습니다.

문제

  • [리스트 1] 원본리스트
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE PC_BANG
    AS
    SELECT '20160601' dt, 1 id FROM dual
    UNION ALL SELECT '20160601', 2 FROM dual
    UNION ALL SELECT '20160601', 1 FROM dual
    UNION ALL SELECT '20160602', 1 FROM dual
    UNION ALL SELECT '20160602', 2 FROM dual
    UNION ALL SELECT '20160602', 2 FROM dual
    UNION ALL SELECT '20160603', 3 FROM dual
    UNION ALL SELECT '20160604', 4 FROM dual
    UNION ALL SELECT '20160604', 1 FROM dual
    UNION ALL SELECT '20160605', 1 FROM dual;
     
    SELECT * FROM PC_BANG;

  • [표 1] 원본테이블
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    DT                       ID
    ---------------- ----------
    20160601                  1
    20160601                  2
    20160601                  1
    20160602                  1
    20160602                  2
    20160602                  2
    20160603                  3
    20160604                  4
    20160604                  1
    20160605                  1

  • [표 2] 결과테이블
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT                 접속건수   접속자수 누적접속건수 누적접속자수
    ---------------- ---------- ---------- ------------ ------------
    20160801                  3          2            3            2
    20160802                  3          2            6            2
    20160803                  1          1            7            3
    20160804                  2          2            9            4
    20160805                  1          1           10            4

문제설명

<리스트 1>은 사용자 접속기록을 관리하는 테이블입니다. 사용자가 접속할 때 마다 기록이 되기 때문에 동일 사용자가 하루에 여러번 기록될 수 있습니다. 이 기록을 토대로 일별 접속 현황 통계자료를 작성해야 합니다. 접속일자 기준으로 다음 네 가지 통계를 한 화면에 보여줘야 합니다.

  • 1. 접속건수 : 접속 기록을 일별로 카운트합니다.
  • 2. 접속자수 : 동일 유저는 한번만 카운트 합니다.
  • 3. 누적접속건수 : 현재일자까지의 누적 건수입니다.
  • 4. 누적접속자수 : 현재일자까지의 누적 접속자수입니다.












정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트 2] 정답 리스트
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT dt
     , COUNT(*) 접속건수
     , COUNT(DISTINCT id) 접속자수
     , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
     , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
  FROM (SELECT dt, id
             , DECODE(
               ROW_NUMBER() OVER(
               PARTITION BY id ORDER BY dt)
               , 1, 1) x
          FROM PC_BANG
        )
 GROUP BY dt
 ORDER BY dt
;


해설

이번 문제는 네 가지 서로 다른 통계를 하나로 보여주는 문제입니다. 각각의 건수를 집계하는 방법을 살펴보고 이를 하나로 묵어보도록 하겠습니다. 우선 접속건수부터 살펴볼까요?

  • [리스트 3] 접속건수
  • ?
    1
    2
    3
    4
    5
    6
    SELECT dt
         , COUNT(*) 접속건수
      FROM PC_BANG
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 3] 접속건수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT                 접속건수
    ---------------- ----------
    20160601                  3
    20160602                  3
    20160603                  1
    20160604                  2
    20160605                  1

<리스트 3>은 GROUP BY를 사용한 집계 쿼리의 가장 기본적인 구문입니다. 설명이 필요 없죠. 다음은 접속자수입니다. 접속자 수를 구할 때 중요한 것은 중복 접속자의 제거입니다. 중복제거 키워드인 DISTINCT를 사용해 볼까요?

  • [리스트 4] 접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT dt
         , COUNT(*) 접속자수
      FROM (SELECT DISTINCT dt, id
              FROM PC_BANG
            )
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 4] 접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT                 접속자수
    ---------------- ----------
    20160601                  2
    20160602                  2
    20160603                  1
    20160604                  2
    20160605                  1

<리스트 4>은 인라인 뷰에서 DISTINCT를 이용해 동일일자 동일 ID 중복을 제거한 뒤 집계하는 형식입니다. <리스트 3>과 <리스트 4>를 어떻게 합쳐야 할지 난감합니다.

  • [리스트 5] 접속자수
  • ?
    1
    2
    3
    4
    5
    6
    SELECT dt
         , COUNT(DISTINCT id) 접속자수
      FROM PC_BANG
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 5] 접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT                 접속자수
    ---------------- ----------
    20160601                  2
    20160602                  2
    20160603                  1
    20160604                  2
    20160605                  1

<리스트 5>는 <리스트 4>의 인라인뷰를 없애고, DISTINCT를 집계함수인 COUNT 안에서 사용했습니다. 이렇게 함으로써 SQL 도 간결해지고, <리스트 3>과 자연스럽게 연결이 가능해 졌습니다. 이번에는 누적접속건수를 구해보겠습니다.

  • [리스트 6] 누적접속건수
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT dt
         , SUM(접속건수) OVER(ORDER BY dt) 누적접속건수
      FROM (SELECT dt
                 , COUNT(*) 접속건수
              FROM PC_BANG
             GROUP BY dt
            )
    ;

  • [표 6] 누적접속건수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT               누적접속건수
    ---------------- ------------
    20160601                    3
    20160602                    6
    20160603                    7
    20160604                    9
    20160605                   10

<리스트 6>에서는 <리스트 3>의 쿼리를 인라인뷰로 하여 바깥쪽에서 분석함수를 사용해 누적건수를 구하고 있습니다.

  • [리스트 7] 누적접속건수
  • ?
    1
    2
    3
    4
    5
    6
    SELECT dt
         , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
      FROM PC_BANG
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 7] 누적접속건수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT               누적접속건수
    ---------------- ------------
    20160601                    3
    20160602                    6
    20160603                    7
    20160604                    9
    20160605                   10

<리스트 7>에서는 인라인뷰 없이 GROUP BY와 분석함수를 동시에 사용합니다. 분석함수는 SQL구문에서 ORDER BY구문을 제외하고는 가장 마지막 단계에 적용이 되기 때문에 그룹바이의 집계 결과를 그대로 적용 할 수 있는 것입니다. 누적접속자수도 같은 방법으로 구해볼까요.

  • [리스트 8] 잘못된 누적접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT dt
         , COUNT(DISTINCT id) 접속자수
         , SUM(COUNT(DISTINCT id))
           OVER(ORDER BY dt) 누적접속자수
      FROM PC_BANG
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 8] 잘못된 누적접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT                 접속자수 누적접속자수
    ---------------- ---------- ------------
    20160601                  2            2
    20160602                  2            4
    20160603                  1            5
    20160604                  2            7
    20160605                  1            8

<리스트 8>에서 마찬가지로 접속자수인 COUNT(DISTINCT id)를 분석함수를 통해 누적집계 했습니다. 하지만 <표 8>의 결과를 보면 원하는 결과가 아닙니다. <리스트 7>에서 단순 건수를 구할 때는 누적 결과가 맞았지만, 중복을 제거한 건수를 구할 때는 결과가 틀립니다. 중복 제거된 건수가 그대로 누적된 결과죠.

결과가 틀린 이유는 일별로만 중복제거가 되었기 때문이죠. 다른 일자에 동인 ID가 있어도 그대로 카운트가 되는 것입니다. 즉, 전체 일자에서 ID는 오직 한번만 카운트 해야 합니다. COUNT(DISTINCT id)를 이용할 수 는 없습니다.

  • [리스트 9] ID 별 카운트 대상 확인
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT dt
         , id
         , DECODE(
           ROW_NUMBER() OVER(
           PARTITION BY id ORDER BY dt)
           , 1, 1) x
      FROM PC_BANG
    ;

  • [표 9] ID 별 카운트 대상 확인
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    DT                       ID          X
    ---------------- ---------- ----------
    20160601                  1          1
    20160601                  1
    20160602                  1
    20160604                  1
    20160605                  1
    20160601                  2          1
    20160602                  2
    20160602                  2
    20160603                  3          1
    20160604                  4          1

<리스트 9>에서는 ROW_NUMBER 분석함수를 이용해 ID별로 순번을 부여했습니다. 그리고 순번이 1인 경우만 1을 표시하도록 했습니다. <표 9>의 결과를 보면 ID별 최초 한건만 1이 표시됩니다. 이 값을 누적하여 카운트 한다면 원하는 결과를 얻을 수 있겠지요.

  • [리스트 10] 누적접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT dt
         , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
      FROM (SELECT dt, id
                 , DECODE(
                   ROW_NUMBER() OVER(
                   PARTITION BY id ORDER BY dt)
                   , 1, 1) x
              FROM PC_BANG
            )
     GROUP BY dt
     ORDER BY dt
    ;

  • [표 10] 누적접속자수
  • ?
    1
    2
    3
    4
    5
    6
    7
    DT               누적접속자수
    ---------------- ------------
    20160601                    2
    20160602                    2
    20160603                    3
    20160604                    4
    20160605                    4

<리스트 10>에서는 ID별 최초 한건을 표시해주는 X값을 카운트하고 이결과를 다시 분석함수로 누적 합산 했습니다. <표 10>의 누적접속자수가 완성되었네요.

이제 개별로 집계했던 <리스트 3>, <리스트 5>, <리스트 7>, <리스트 10>을 하나의 쿼리로 통합시키면 정답리스트가 완성됩니다.

  • [리스트 11] 통합 리스트
  • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT dt
         , COUNT(*) 접속건수
         , COUNT(DISTINCT id) 접속자수
         , SUM(COUNT(*)) OVER(ORDER BY dt) 누적접속건수
         , SUM(COUNT(x)) OVER(ORDER BY dt) 누적접속자수
      FROM (SELECT dt, id
                 , DECODE(
                   ROW_NUMBER() OVER(
                   PARTITION BY id ORDER BY dt)
                   , 1, 1) x
              FROM PC_BANG
            )
     GROUP BY dt
     ORDER BY dt
    ;

출처 : http://www.gurubee.net/lecture/2955

저작자표시

'스터디 > 쿼리' 카테고리의 다른 글

MySQL Workbench 단축키(shortcuts)  (10) 2018.10.08
테이블 생성 및 CRUD(insert, select, update, delete)  (5) 2018.03.12
오라클 계정생서시 오류 ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.  (7) 2018.03.12
Oracle 유저생성 및 권한  (7) 2018.03.12
Oracle 11g Express Edition 다운로드  (6) 2018.03.09

+ Recent posts

티스토리툴바