我在雪花/dbt 中有一個表，我想在其中選擇行中不同的順序條目。例如：如果我有
| 用戶身份 | session_id | 行動 | 時間戳 |
|---|---|---|---|
| 2 | 3 | 滾動 | 21-08-01 12:00:01 |
| 2 | 3 | 滾動 | 21-08-01 12:00:02 |
| 2 | 3 | 滾動 | 21-08-01 12:00:03 |
| 2 | 3 | 點擊 | 21-08-01 12:00:04 |
| 2 | 3 | 點擊 | 21-08-01 12:00:06 |
| 2 | 3 | 滾動 | 21-08-01 12:00:10 |
| 2 | 3 | 已保存 | 21-08-01 12:00:10 |
我想在最后有這個
| 用戶身份 | session_id | 行動 | 時間戳 |
|---|---|---|---|
| 2 | 3 | 滾動 | 21-08-01 12:00:03 |
| 2 | 3 | 點擊 | 21-08-01 12:00:06 |
| 2 | 3 | 滾動 | 21-08-01 12:00:10 |
| 2 | 3 | 已保存 | 21-08-01 12:00:10 |
我嘗試使用 row_number() 和 next 限定，但即使它們不是，也會按順序計算所有動作。
uj5u.com熱心網友回復：
您可以嘗試以下將最近發生的動作分組并按照它們出現的順序選擇最近發生的動作。
SELECT
user_id,
session_id,
action,
timestamp
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY user_id,session_id,action,gn
ORDER BY timestamp DESC
) as rn
FROM (
SELECT
*,
SUM(continued) OVER (ORDER BY timestamp) as gn
FROM (
SELECT
*,
CASE
WHEN
LAG(
CONCAT(user_id,session_id,action),
1,
CONCAT(user_id,session_id,action)
) OVER (
ORDER BY timestamp
) = CONCAT(user_id,session_id,action) THEN 0
ELSE 1
END as continued
FROM
my_table
) t2
) t1
) t
WHERE rn=1
讓我知道這是否適合您
uj5u.com熱心網友回復：
我嘗試了一些與 ggordon 稍有不同的方法，使用“下一個”記錄的內容構建一個行內視圖（使用 LEAD 函式）。
select user_id, session_id, action, ts
from (
select abc.*,
lead(user_id) ignore nulls
over (order by ts, user_id, session_id, action) next_user_id,
lead(session_id) ignore nulls
over (order by ts, user_id, session_id, action) next_session_id,
lead(action) ignore nulls
over (order by ts, user_id, session_id, action) next_action,
lead(ts) ignore nulls
over (order by ts, user_id, session_id, action) next_ts
from abc
order by ts, user_id, session_id, action)
where user_id = NVL(next_user_id, user_id)
and session_id = NVL(next_session_id, session_id)
and action <> NVL(next_action, 'x')
order by ts, user_id, session_id, action;
這很有效，我能夠獲得您想要的四張唱片。
我希望這有助于...豐富
ps 如果這個（或另一個）答案對您有幫助，請花點時間“接受”有幫助的答案，方法是單擊答案旁邊的復選標記，將其從“變灰”切換為“已填寫”。
uj5u.com熱心網友回復：
這被稱為間隙和島嶼問題。這通常通過通過兩個并發行編號創建組鍵來解決。
select
user_id, session_id, action, max(timestamp)
from
(
select
user_id, session_id, action, timestamp,
row_number() over (order by timestamp, user_id, session_id, action) -
row_number() over (partition by user_id, session_id, action order by timestamp)
as grp
from mytable
)
group by grp, user_id, session_id, action
order by grp, user_id, session_id, action;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/327558.html
下一篇：SQL以全名搜索姓氏