N月最後のイベントを拾うSQL

SQLで業務ロジックを書くことはないですが、稼動確認とか、不正データ、怪しいデータを検出したい時とかはSQLを使うと便利だと思うことがあります。自分が最近使ったパターンを紹介します。

前提

こんな感じで、エンティティに発生したイベントを記録しているテーブルがあったとします。

event_entity

column	data type	etc
id	NUMERIC	auto_increment
entity_id	NUMERIC
event_type	VARCHAR	CHECK('create', 'start', 'suspend', 'resume', 'end')
occurred_at	DATE

なお、IDが戻ることはありません。

月別に最後の状態をとる

月の最後の状態を見てなにかを判定する（課金をだすなど）ってことはよくあると思います。自分は以下のクエリを使います。 注意：Oracleです

SELECT * FROM entity_event;

    ID  ENTITY_ID EVENT_TYPE       OCCURRED_AT
---------- ---------- -------------------- -------------------
     1      1 create           2018/01/01 00:00:00
     2      1 start        2018/01/31 23:59:59
     3      1 suspend          2018/02/01 00:00:00
     4      2 create           2018/01/01 23:59:59
     5      2 start        2018/03/31 23:59:59
     6      3 create           2018/01/01 00:00:00
     7      3 start        2018/01/01 00:00:01
     8      3 suspend          2018/03/31 23:59:59
     9      3 resume           2018/06/30 23:59:59


SELECT
    DISTINCT
        t2.entity_id
        , t2.event_type
        , TRUNC(t2.occurred_at, 'MM') as month
FROM (
         SELECT
             max(id) over( partition by entity_id, TRUNC(occurred_at, 'MM') ) as max_id
         FROM
             entity_event
     ) t1 JOIN entity_event t2
     ON t1.max_id = t2.id
ORDER BY t2.entity_id, month;

 ENTITY_ID EVENT_TYPE       MONTH
---------- -------------------- -------------------
     1 start        2018/01/01 00:00:00
     1 suspend      2018/02/01 00:00:00
     2 create       2018/01/01 00:00:00
     2 start        2018/03/01 00:00:00
     3 start        2018/01/01 00:00:00
     3 suspend      2018/03/01 00:00:00
     3 resume       2018/06/01 00:00:00

ただ、これだとイベントがない月はレポートから漏れしまう。そこで、月の配列を返す関数を定義して、クロス結合させればイベントのない月も表示できる。

-- 月の配列型の定義
CREATE OR REPLACE TYPE MONTH_ARRAY AS VARRAY(2000) of DATE;
/

-- 月の配列を返す関数を定義
CREATE OR REPLACE FUNCTION range_month(start_at IN DATE, end_at IN DATE)
    RETURN MONTH_ARRAY
  IS
    data MONTH_ARRAY:=MONTH_ARRAY();
    idx_month DATE:=TRUNC(start_at, 'dd');
    end_month DATE:=TRUNC(end_at, 'dd');
    invalid_range_exception EXCEPTION;
    PRAGMA EXCEPTION_INIT(invalid_range_exception, -6502);
  BEGIN
    -- 1999 equals type length minus one,
    -- becouse months_between function return range(not include end_at month)
    IF (MONTHS_BETWEEN(end_month, idx_month) >= 1999) THEN
        RAISE invalid_range_exception;
    END IF;
    IF (idx_month IS NULL OR end_month IS NULL OR idx_month >= end_month) THEN
        RAISE invalid_range_exception;
    END IF;
    WHILE(idx_month <= end_month) LOOP
        -- DBMS_OUTPUT.PUT_LINE(idx_month);
        -- DBMS_OUTPUT.PUT_LINE(end_month);
        data.extend();
        data(data.count()):=idx_month;
        idx_month:=ADD_MONTHS(idx_month, 1);
    END LOOP;
    return data;
  END;
/

SELECT
    DISTINCT
        t2.entity_id
        , t2.event_type
        , t3.column_value as month
FROM (
         SELECT
             max(id) over( partition by entity_id, TRUNC(occurred_at, 'MM') ) as max_id,
             lead(TRUNC(occurred_at, 'MM'), 1, '9999/1/1 00:00:00') OVER(partition by entity_id order by id) as next_event_at
         FROM
             entity_event
     ) t1 JOIN entity_event t2
     ON t1.max_id = t2.id
     CROSS JOIN (
         SELECT
             *
         FROM
             TABLE(
                 range_month(TO_DATE('201801', 'YYYYMM'),
                 TO_DATE('201807', 'YYYYMM')))
     ) t3
WHERE
    t3.column_value < t1.next_event_at
    AND TRUNC(t2.occurred_at, 'MM') <= t3.column_value
ORDER BY t2.entity_id, month;

 ENTITY_ID EVENT_TYPE       MONTH
---------- -------------------- -------------------
     1 start        2018/01/01 00:00:00
     1 suspend      2018/02/01 00:00:00
     1 suspend      2018/03/01 00:00:00
     1 suspend      2018/04/01 00:00:00
     1 suspend      2018/05/01 00:00:00
     1 suspend      2018/06/01 00:00:00
     1 suspend      2018/07/01 00:00:00
     2 create       2018/01/01 00:00:00
     2 create       2018/02/01 00:00:00
     2 start        2018/03/01 00:00:00
     2 start        2018/04/01 00:00:00
     2 start        2018/05/01 00:00:00
     2 start        2018/06/01 00:00:00
     2 start        2018/07/01 00:00:00
     3 start        2018/01/01 00:00:00
     3 start        2018/02/01 00:00:00
     3 suspend      2018/03/01 00:00:00
     3 suspend      2018/04/01 00:00:00
     3 suspend      2018/05/01 00:00:00
     3 resume       2018/06/01 00:00:00
     3 resume       2018/07/01 00:00:00

ちなみにPostgreSQLとかだと、確かunnestって関数を使えばわざわざ関数を自分で作らなくても同じことができるはず。