62101


SQLで特定の条件を満たすレコードを抽出するSQL query を教えてください。  
(ユーザーごとにパーティションで分けたいためwindow関数を利用するかと思います。)

データセットは以下の通りとなります。

  * user_idごとにパーティションで分けます。
  * start_timestampを昇順に並べ替えたものを利用します。
  * action_idが-1のレコードではget_idは-1以外になります。
  * action_idが-1のレコードのstart_timestampとend_timestampは同値です。

抽出したいレコードですが、  
action_idが-1のレコードのstart_timestampが直前のどのaction_idのstart_timestampとend_timestampに含まれるか、含まれるaction_idを別列として定義したい。

[code]

    select
    user_id
    ,start_timestamp
    ,end_timestamp
    ,action_id
    ,get_id
    from 
    (
    select '1234' as user_id,'2017-03-10 09:12:31.881' as start_timestamp,'2017-03-10 09:12:31.881' as end_timestamp,'a1' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:13:25.709' as start_timestamp,'2017-03-10 09:15:07.805' as end_timestamp,'a2' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:15:07.805' as start_timestamp,'2017-03-10 10:08:18.762' as end_timestamp,'a3' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:15:07.805' as start_timestamp,'2017-03-10 09:15:07.805' as end_timestamp,'a4' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:44.440' as start_timestamp,'2017-03-10 09:24:44.440' as end_timestamp,'a5' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:44.440' as start_timestamp,'2017-03-10 09:24:54.450' as end_timestamp,'a6' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:54.450' as start_timestamp,'2017-03-10 09:25:01.457' as end_timestamp,'a7' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:25:01.457' as start_timestamp,'2017-03-10 09:25:01.457' as end_timestamp,'a8' as action_id ,'-1' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:32:53.000' as start_timestamp,'2017-03-10 09:32:53.000' as end_timestamp,'-1' as action_id ,'3' as get_id from dual union all
    select '1234' as user_id,'2017-03-10 09:44:03.500' as start_timestamp,'2017-03-10 09:49:08.473' as end_timestamp,'a10' as action_id,'-1' as get_id  from dual union all
    select '1234' as user_id,'2017-03-10 09:45:53.000' as start_timestamp,'2017-03-10 09:45:53.000' as end_timestamp,'-1' as action_id ,'4' as get_id from dual union all
    select '2345' as user_id,'2017-03-10 09:44:03.500' as start_timestamp,'2017-03-10 09:49:08.473' as end_timestamp,'a1' as action_id,'-1' as get_id  from dual union all
    select '2345' as user_id,'2017-03-10 19:44:03.500' as start_timestamp,'2017-03-10 19:49:08.473' as end_timestamp,'a2' as action_id,'-1' as get_id  from dual union all
    select '2345' as user_id,'2017-03-10 19:45:03.500' as start_timestamp,'2017-03-10 19:45:03.473' as end_timestamp,'a3' as action_id,'-1' as get_id  from dual union all
    select '2345' as user_id,'2017-03-10 19:45:05.500' as start_timestamp,'2017-03-10 19:45:05.500' as end_timestamp,'-1' as action_id,'11' as get_id  from dual 
        ) a
    order by user_id,start_timestamp
    ;
    
[/code]

最終的に得られる結果は以下のようになると思います。  
新たに定義したin_action_idの-1はnullでも構いません。

[code]

    select
    user_id
    ,start_timestamp
    ,end_timestamp
    ,action_id
    ,get_id
    ,in_action_id
    from 
    (
    select '1234' as user_id,'2017-03-10 09:12:31.881' as start_timestamp,'2017-03-10 09:12:31.881' as end_timestamp,'a1' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:13:25.709' as start_timestamp,'2017-03-10 09:15:07.805' as end_timestamp,'a2' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:15:07.805' as start_timestamp,'2017-03-10 10:08:18.762' as end_timestamp,'a3' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:15:07.805' as start_timestamp,'2017-03-10 09:15:07.805' as end_timestamp,'a4' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:44.440' as start_timestamp,'2017-03-10 09:24:44.440' as end_timestamp,'a5' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:44.440' as start_timestamp,'2017-03-10 09:24:54.450' as end_timestamp,'a6' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:24:54.450' as start_timestamp,'2017-03-10 09:25:01.457' as end_timestamp,'a7' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:25:01.457' as start_timestamp,'2017-03-10 09:25:01.457' as end_timestamp,'a8' as action_id ,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:32:53.000' as start_timestamp,'2017-03-10 09:32:53.000' as end_timestamp,'-1' as action_id ,'13' as get_id ,'a3' as in_action_id from dual union all
    select '1234' as user_id,'2017-03-10 09:44:03.500' as start_timestamp,'2017-03-10 09:49:08.473' as end_timestamp,'a10' as action_id,'-1' as get_id ,'-1' as in_action_id  from dual union all
    select '1234' as user_id,'2017-03-10 09:45:53.000' as start_timestamp,'2017-03-10 09:45:53.000' as end_timestamp,'-1' as action_id ,'24' as get_id ,'a10' as in_action_id from dual union all 
    select '2345' as user_id,'2017-03-10 09:44:03.500' as start_timestamp,'2017-03-10 09:49:08.473' as end_timestamp,'a1' as action_id,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '2345' as user_id,'2017-03-10 19:44:03.500' as start_timestamp,'2017-03-10 19:49:08.473' as end_timestamp,'a2' as action_id,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '2345' as user_id,'2017-03-10 19:45:03.500' as start_timestamp,'2017-03-10 19:45:03.473' as end_timestamp,'a3' as action_id,'-1' as get_id ,'-1' as in_action_id from dual union all
    select '2345' as user_id,'2017-03-10 19:45:05.500' as start_timestamp,'2017-03-10 19:45:05.500' as end_timestamp,'-1' as action_id,'11' as get_id ,'a2' as in_action_id from dual 
    ) a
    order by user_id,start_timestamp
    ;
    
[/code]


CC BY-SA 4.0
2020-01-08T08:52:46.927
62065
2020-01-09T10:50:19.907
2020-01-09T10:50:19.907
8431
37367
question
sql oracle
SQLのwindow関数について


インラインビューが表 `a` として存在するものとします。  
自己相関で `a2` に `action_id` の候補を絞り、`start_timestamp` が直前となる一件を `in_action_id`
とします。  
該当する `action_id` がなければ、`in_action_id` は `NULL` になります。

[code]

    SELECT DISTINCT a1.user_id
    , a1.start_timestamp
    , a1.end_timestamp
    , a1.action_id
    , a1.get_id
    , CASE a1.action_id
      WHEN '-1' THEN first_value(a2.action_id) OVER (
      PARTITION BY a1.user_id, a1.get_id
      ORDER BY a2.start_timestamp DESC
      )
      ELSE '-1'
      END in_action_id
    FROM a a1
    LEFT JOIN a a2
    ON a2.user_id = a1.user_id
      AND a2.get_id = '-1'
      AND a2.start_timestamp < a1.start_timestamp
      AND a2.end_timestamp > a1.start_timestamp
    ORDER BY a1.user_id
    , a1.start_timestamp
    ;
    
[/code]


CC BY-SA 4.0
2020-01-09T09:52:40.653
62101
2020-01-09T09:52:40.653
8431
62065
answer