ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

キー項目がブレイクしたタイミングでサマリ集計するSQL (ROW_NUMBER分析ファンクション)

Oracle Database

タイトルだけだと訳が解りませんな。下記表(SALES表)のレコード群を

SQL> SELECT * FROM SALES ORDER BY SALES_DATE, JAN_CODE;

SALES_DATE          JAN_C  SALES_CNT
------------------- ----- ----------
2014/10/06 00:00:00 AAA          100
2014/10/07 00:00:00 AAA          200
2014/10/08 00:00:00 BBB          100
2014/10/09 00:00:00 BBB          150
2014/10/10 00:00:00 BBB          189
2014/10/11 00:00:00 CCC          120
2014/10/12 00:00:00 CCC          111
2014/10/13 00:00:00 AAA          210
2014/10/14 00:00:00 AAA          545
2014/10/15 00:00:00 AAA           90
2014/10/16 00:00:00 CCC           90

11 rows selected.

↓のようにする。キー項目JAN_CODE がブレイクしたタイミングで、
その日付間のサマリを集計(CNT_SUM)しつつ 開始日／終了日 を付与する。

SALES_DATE_FIRST    SALES_DATE_LAST     JAN_C CNT_SUM
------------------- ------------------- ----- -------
2014/10/06 00:00:00 2014/10/07 00:00:00 AAA       300 -- 10/ 6〜10/ 7 の AAA のサマリ
2014/10/08 00:00:00 2014/10/10 00:00:00 BBB       439 -- 10/ 8〜10/10 の BBB のサマリ
2014/10/11 00:00:00 2014/10/12 00:00:00 CCC       231 -- 10/11〜10/12 の CCC のサマリ
2014/10/13 00:00:00 2014/10/15 00:00:00 AAA       845 -- 10/13〜10/15 の AAA のサマリ
2014/10/16 00:00:00 2014/10/16 00:00:00 CCC        90 -- 10/16        の CCC のサマリ


SALES_DATE          JAN_C  SALES_CNT      SALES_DATE_FIRST    SALES_DATE_LAST     JAN_C CNT_SUM
------------------- ----- ----------      ------------------- ------------------- ----- -------
2014/10/06 00:00:00 AAA          100 ┬→ 2014/10/06 00:00:00 2014/10/07 00:00:00 AAA       300
2014/10/07 00:00:00 AAA          200 ┘
2014/10/08 00:00:00 BBB          100 ┬→ 2014/10/08 00:00:00 2014/10/10 00:00:00 BBB       439
2014/10/09 00:00:00 BBB          150 ┤
2014/10/10 00:00:00 BBB          189 ┘
2014/10/11 00:00:00 CCC          120 ┬→ 2014/10/11 00:00:00 2014/10/12 00:00:00 CCC       231
2014/10/12 00:00:00 CCC          111 ┘
2014/10/13 00:00:00 AAA          210 ┬→ 2014/10/13 00:00:00 2014/10/15 00:00:00 AAA       845
2014/10/14 00:00:00 AAA          545 ┤
2014/10/15 00:00:00 AAA           90 ┘
2014/10/16 00:00:00 CCC           90 ─→ 2014/10/16 00:00:00 2014/10/16 00:00:00 CCC        90

下記の SQL でイケます。

SELECT MIN(SALES_DATE) AS SALES_DATE_FIRST
     , MAX(SALES_DATE) AS SALES_DATE_LAST
     , JAN_CODE
     , SUM(SALES_CNT)  AS CNT_SUM
  FROM (
    SELECT SALES_DATE
         , JAN_CODE
         , SALES_CNT
         , ROW_NUMBER() OVER(ORDER BY SALES_DATE) -
           ROW_NUMBER() OVER(PARTITION BY JAN_CODE ORDER BY SALES_DATE) AS DISTANCE
      FROM SALES
  )
 GROUP BY JAN_CODE, DISTANCE
 ORDER BY SALES_DATE_FIRST;

結果は↓の通り。

SQL> SELECT MIN(SALES_DATE) AS SALES_DATE_FIRST
  2       , MAX(SALES_DATE) AS SALES_DATE_LAST
  3       , JAN_CODE
  4       , SUM(SALES_CNT)  AS CNT_SUM
  5    FROM (
  6      SELECT SALES_DATE
  7           , JAN_CODE
  8           , SALES_CNT
  9           , ROW_NUMBER() OVER(ORDER BY SALES_DATE) -
 10             ROW_NUMBER() OVER(PARTITION BY JAN_CODE ORDER BY SALES_DATE) AS DISTANCE
 11        FROM SALES
 12    )
 13   GROUP BY JAN_CODE, DISTANCE
 14   ORDER BY SALES_DATE_FIRST;

SALES_DATE_FIRST    SALES_DATE_LAST     JAN_C    CNT_SUM
------------------- ------------------- ----- ----------
2014/10/06 00:00:00 2014/10/07 00:00:00 AAA          300
2014/10/08 00:00:00 2014/10/10 00:00:00 BBB          439
2014/10/11 00:00:00 2014/10/12 00:00:00 CCC          231
2014/10/13 00:00:00 2014/10/15 00:00:00 AAA          845
2014/10/16 00:00:00 2014/10/16 00:00:00 CCC           90
SQL>

上手く行ってるZe! でも↑だけだと解り辛いのでもう少し補足すると、、、

SQL>     SELECT SALES_DATE
  2           , JAN_CODE
  3           , SALES_CNT
  4           , ROW_NUMBER() OVER(ORDER BY SALES_DATE) AS SIMPLE_SEQ
  5           , ROW_NUMBER() OVER(PARTITION BY JAN_CODE ORDER BY SALES_DATE) AS PART_JAN_SEQ
  6           , ROW_NUMBER() OVER(ORDER BY SALES_DATE) -
  7             ROW_NUMBER() OVER(PARTITION BY JAN_CODE ORDER BY SALES_DATE) AS DISTANCE
  8        FROM SALES
  9       ORDER BY SALES_DATE;

SALES_DATE          JAN_C  SALES_CNT SIMPLE_SEQ PART_JAN_SEQ   DISTANCE
------------------- ----- ---------- ---------- ------------ ----------
2014/10/06 00:00:00 AAA          100          1            1          0
2014/10/07 00:00:00 AAA          200          2            2          0
2014/10/08 00:00:00 BBB          100          3            1          2
2014/10/09 00:00:00 BBB          150          4            2          2
2014/10/10 00:00:00 BBB          189          5            3          2
2014/10/11 00:00:00 CCC          120          6            1          5
2014/10/12 00:00:00 CCC          111          7            2          5
2014/10/13 00:00:00 AAA          210          8            3          5
2014/10/14 00:00:00 AAA          545          9            4          5
2014/10/15 00:00:00 AAA           90         10            5          5
2014/10/16 00:00:00 CCC           90         11            3          8

上の SQL はサマリ前(GROUP BY前)の生レコードに、
幾つかの情報(SIMPLE_SEQ, PART_JAN_SEQ)を足してます。

ポイントは以下の３つです。

・SALES_DATE値で単純ソートした連続値をROW_NUMBER()ファンクションで取得
【ROW_NUMBER() OVER(ORDER BY SALES_DATE) AS SIMPLE_SEQ】

・JAN_CODE値で区切りつつSALES_DATE値でソートした連続値を
　ROW_NUMBER()ファンクションで取得
【ROW_NUMBER() OVER(PARTITION BY JAN_CODE ORDER BY SALES_DATE) AS PART_JAN_SEQ】

・両者を引き算して【DISTANCE値】、この値とブレイク対象の
　キー項目(JAN_CODE値)でグルーピング【GROUP BY JAN_CODE, DISTANCE】

この SQL は自分では無く、同僚の人が考えました。凄い(;｀･ω･)

同僚の人曰く、SQLパズルの下記ページを見てたら作れちゃったそうです。
旅人算って、言うのか、、、世の中は広いぜ、、、

OracleSQLパズル
9-61 連続的なグルーピング
http://www.geocities.jp/oraclesqlpuzzle/9-61.html