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って関数を使えばわざわざ関数を自分で作らなくても同じことができるはず。