下面分享使用三種方法計算用戶最近兩次交易時間差。
01 數(shù)據(jù)準備
假設有一張表transactions，一列是用戶id（user_id），一列是交易時間（transaction_time），問用戶最近一次交易時間和倒數(shù)第二次交易時間之差，單位：秒。
示例表數(shù)據(jù)如下，具體建表及插入數(shù)據(jù)語句見文末附錄：
02 解題思路及SQL答案
解題思路一：使用窗口函數(shù) — row_number() over()函數(shù)
在公共表達式中使用窗口函數(shù)ROW_NUMBER()為每個用戶的交易時間降序排列；
使用自連接，將每個用戶的每次交易時間與其倒數(shù)第二次交易時間關(guān)聯(lián)；
篩選最近一次交易時間的記錄并計算最近一次交易時間與倒數(shù)第二次交易時間相差秒數(shù)。
SQL如下：
WITH RankedTransactions AS ( SELECT user_id, transaction_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY transaction_time DESC) AS rn FROM transactions ) SELECT t1.user_id, t1.transaction_time as latest_time, t2.transaction_time as second_latest_time, TIMESTAMPDIFF(SECOND, t2.transaction_time, t1.transaction_time) AS time_difference_s FROM RankedTransactions t1 JOIN RankedTransactions t2 ON t1.user_id = t2.user_id AND t2.rn = 2 WHERE t1.rn = 1;
結(jié)果如下：
解題思路二：使用公共表達式與表連接
SQL如下：
-- 第一步：找出每個用戶的最近一次交易時間 WITH LatestTransactions AS ( SELECT user_id, MAX(transaction_time) AS latest_time FROM transactions GROUP BY user_id ), -- 第二步：剔除最近一次交易時間后，找出每個用戶的最近一次交易時間（即倒數(shù)第二次） SecondLatestTransactions AS ( SELECT t.user_id, MAX(t.transaction_time) AS second_latest_time FROM transactions t LEFT JOIN LatestTransactions lt ON t.user_id = lt.user_id AND t.transaction_time = lt.latest_time WHERE lt.latest_time IS NULL OR t.transaction_time < lt.latest_time GROUP BY t.user_id ) -- 第三步：將兩個子查詢結(jié)果關(guān)聯(lián)起來，并計算時間差 SELECT lt.user_id, latest_time, second_latest_time, TIMESTAMPDIFF(SECOND, slt.second_latest_time, lt.latest_time) AS time_difference FROM LatestTransactions lt JOIN SecondLatestTransactions slt ON lt.user_id = slt.user_id;
結(jié)果如下：
解題思路三：窗口函數(shù)、子查詢結(jié)合法
第一步：對每個用戶的交易時間降序并位移獲取下一個交易時間，這時候最近一次交易時間和倒數(shù)第二次交易時間就在每一個用戶id的第一行；
第二步：計算每個用戶的交易時間和其降序排列的下一個交易時間之差，并使用窗口函數(shù)對每個用戶的交易時間降序排列；
第三步：篩選最近一次交易時間，即可得到計算后的結(jié)果。
SQL如下：
-- 第三步：篩選最近一次交易時間，即可得到計算后的結(jié)果 SELECT user_id, transaction_time, next_time, time_difference FROM ( -- 第二步：計算每個用戶的交易時間和其降序排列的下一個交易時間之差，并使用窗口函數(shù)對每個用戶的交易時間降序排列 SELECT user_id, transaction_time, next_time, TIMESTAMPDIFF(SECOND, next_time, transaction_time) AS time_difference, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY transaction_time DESC) AS rn FROM -- 第一步：對每個用戶的交易時間降序并位移獲取下一個交易時間，這時候最近一次交易時間和倒數(shù)第二次交易時間就在每一個用戶id的第一行 ( SELECT user_id, transaction_time, LEAD(transaction_time) OVER (PARTITION BY user_id ORDER BY transaction_time DESC) AS next_time FROM transactions )a )a WHERE rn = 1 ；
結(jié)果如下：
附錄
建表及插入數(shù)據(jù)語句：
CREATE TABLE transactions ( user_id INT, transaction_time datetime ); insert into transactions values ('123', '2024-01-01 15:30:30'), ('123', '2024-01-02 16:50:00'), ('123', '2024-01-03 16:51:38'), ('123', '2024-01-04 15:30:30'), ('234', '2024-01-01 16:51:38'), ('234', '2024-01-04 12:35:00'), ('234', '2024-01-07 09:58:00'), ('345', '2024-01-01 16:51:38'), ('345', '2024-01-02 15:30:30'), ('345', '2024-03-16 16:51:38'), ('345', '2024-03-17 12:35:00'), ('345', '2024-03-18 16:51:38'), ('456', '2024-03-04 16:51:38'), ('456', '2024-03-08 09:58:00'), ('456', '2024-03-09 12:35:00'), ('456', '2024-03-13 15:30:30');
以上就是本次的分享，感謝觀看！
該文章在 2024/4/19 16:13:42 編輯過
400 186 1886
