我目前正在使用帶有搜索引擎的系統，并且正在嘗試確定搜索結果是否排序良好，以便用戶不必向下滾動即可查看他們想要的結果。
我們必須使用記錄用戶操作的日志，例如：
- 搜索/搜索文本
- 他們點擊的結果
架構如下：
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 的查詢。
uj5u.com熱心網友回復：
您所需要的只是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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432922.html
標籤：sql 数据库 甲骨文 oracle-sqldeveloper
上一篇：oracle中的索引重繪
下一篇：根據上個月的值創建條件欄位