SQL |获取每个用户的后续操作并聚合成一行

Question

我目前正在使用带有搜索引擎的系统，并且我试图查明搜索结果是否排序良好，以便用户不必向下滚动即可看到他们想要的结果。

我们必须使用记录用户操作的日志，例如：

• 搜索/搜索文本
• 他们单击的

结果，架构如下：

user_id | time                | action_type | details
--------+---------------------+-------------+--------------
jack    | 2022-02-01 15:51:33 | search      | query="text1"
sally   | 2022-02-01 15:52:00 | search      | query="text2"
sally   | 2022-02-01 15:52:10 | search      | query="text3"
jack    | 2022-02-01 15:52:20 | click       | target="system1"
sally   | 2022-02-01 15:52:30 | click       | target="system2"
mike    | 2022-02-01 15:53:22 | search      | query="text4"
...

我想要做的是找出“他们搜索了什么？”和“他们之后点击了什么？”，所以我想根据上表制作一个如下表。

user_id | search_query | click_target
--------+--------------+--------------
jack    | text1        | system1
sally   | text2        | (null)
sally   | text3        | system2
mike    | text4        | (null)

我怎样才能做到这一点？我的猜测是，我必须将表拆分为“搜索”部分和“单击”部分，然后将搜索行与最接近的点击行（但在下一个搜索行之前）进行匹配。但是我无法提出考虑 user_id 的查询。

Answer 1

您所需要的只是 lead( )over() 分析函数：

DBFiddle

select 
    v.user_id
   ,regexp_substr(details,'"([^"]+)"',1,1,null,1) as search_query 
   ,regexp_substr(click  ,'"([^"]+)"',1,1,null,1) as click_target
from (
  select
    t.*
    ,lead(case when action_type='click' then details end)
       over(partition by user_id order by time) as click
  from your_table t
) v
where action_type='search';

结果：

USER_ID    SEARCH_QUERY CLICK_TARGET
---------- ------------ ------------
jack       text1        system1
sally      text2        (null)
sally      text3        system2
mike       text4        (null)

带有测试数据的完整示例：

with your_table(user_id, time, action_type, details) as (
select 'jack' , to_date('2022-02-01 15:51:33','yyyy-mm-dd hh24:mi:ss'), 'search', 'query="text1"'    from dual union all
select 'sally', to_date('2022-02-01 15:52:00','yyyy-mm-dd hh24:mi:ss'), 'search', 'query="text2"'    from dual union all
select 'sally', to_date('2022-02-01 15:52:10','yyyy-mm-dd hh24:mi:ss'), 'search', 'query="text3"'    from dual union all
select 'jack' , to_date('2022-02-01 15:52:20','yyyy-mm-dd hh24:mi:ss'), 'click' , 'target="system1"' from dual union all
select 'sally', to_date('2022-02-01 15:52:30','yyyy-mm-dd hh24:mi:ss'), 'click' , 'target="system2"' from dual union all
select 'mike' , to_date('2022-02-01 15:53:22','yyyy-mm-dd hh24:mi:ss'), 'search', 'query="text4"'    from dual
)
select 
    v.user_id
   ,regexp_substr(details,'"([^"]+)"',1,1,null,1) as search_query 
   ,regexp_substr(click  ,'"([^"]+)"',1,1,null,1) as click_target
from (
  select
    t.*
    ,lead(case when action_type='click' then details end)
       over(partition by user_id order by time) as click
  from your_table t
) v
where action_type='search'
order by time;