SQLでxxxの後にyyyが行われたデータを抽出したい

日付情報を持つ複数のレコードがあって、例えば以下のようなテーブルがあり、id順でactionが「meal」の前に「wake」が行われたユーザー名を探したいみたいな状況があった。

その場合はlag関数を使用することで解決できる。

id	user	action
1	sato	sleep
2	sato	wake
3	gojo	meal
4	yamada	wake
5	sato	meal
6	yamada	sleep
7	gojo	sleep
8	yamada	meal

select
  user_name
from
  test_table
where
  id in (
  select
    id as target_id
  from
  (
    select
      id
      , action as now_action
      , lag(action) over (partition by user_name order by id asc) as before_action
    from
      test_table
  ) as subquery
  where
    now_action = 'meal' and before_action = 'wake'
  );

SQL実行結果

以下のSQL FiddleというDBを使用して、SQL Serverで確認した。

さいごに

lag関数は1個上のレコードの値を取得してくるが、逆に1個下のレコードを取得するlead関数もある。
lead関数も同じような感じで使用できる。