下面分享使用三種方法計算用戶最近兩次交易時間差。
01 數據準備
假設有一張表transactions，一列是用戶id（user_id），一列是交易時間（transaction_time），問用戶最近一次交易時間和倒數第二次交易時間之差，單位：秒。
示例表數據如下，具體建表及插入數據語句見文末附錄：
02 解題思路及SQL答案
解題思路一：使用窗口函數 — row_number() over()函數
在公共表達式中使用窗口函數ROW_NUMBER()為每個用戶的交易時間降序排列；
使用自連接，將每個用戶的每次交易時間與其倒數第二次交易時間關聯；
篩選最近一次交易時間的記錄并計算最近一次交易時間與倒數第二次交易時間相差秒數。
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;
結果如下：
解題思路二：使用公共表達式與表連接
SQL如下：
-- 第一步：找出每個用戶的最近一次交易時間 WITH LatestTransactions AS ( SELECT user_id, MAX(transaction_time) AS latest_time FROM transactions GROUP BY user_id ), -- 第二步：剔除最近一次交易時間后，找出每個用戶的最近一次交易時間（即倒數第二次） 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 ) -- 第三步：將兩個子查詢結果關聯起來，并計算時間差 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;
結果如下：
解題思路三：窗口函數、子查詢結合法
第一步：對每個用戶的交易時間降序并位移獲取下一個交易時間，這時候最近一次交易時間和倒數第二次交易時間就在每一個用戶id的第一行；
第二步：計算每個用戶的交易時間和其降序排列的下一個交易時間之差，并使用窗口函數對每個用戶的交易時間降序排列；
第三步：篩選最近一次交易時間，即可得到計算后的結果。
SQL如下：
-- 第三步：篩選最近一次交易時間，即可得到計算后的結果 SELECT user_id, transaction_time, next_time, time_difference FROM ( -- 第二步：計算每個用戶的交易時間和其降序排列的下一個交易時間之差，并使用窗口函數對每個用戶的交易時間降序排列 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 -- 第一步：對每個用戶的交易時間降序并位移獲取下一個交易時間，這時候最近一次交易時間和倒數第二次交易時間就在每一個用戶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 ；
結果如下：
附錄
建表及插入數據語句：
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
