我有包含以下列的事務表：
TRANSACTION_ID、USER_ID、MERCHANT_NAME、TRANSACTION_DATE、AMOUNT
-)查詢計算每個客戶當前訂單和上一個訂單之間的時間差（以天為單位） -)每個客戶的兩個訂單之間的平均時間差。
注意：排除單筆交易的用戶
我嘗試了以下代碼來獲取查詢的第一部分，但它看起來太亂了
with t1 as
(Select USER_ID,TRANSACTION_DATE,Dense_rank() over(partition by USER_ID order by TRANSACTION_DATE desc) as r1
from CDM_Bill_Details
order by USER_ID, TRANSACTION_DATE desc)
Select t11.USER_ID, datediff(t11.TRANSACTION_DATE,t111.TRANSACTION_DATE) from t1 as t11,t1 as t111
where (t11.r1=1 and t111.r1=2) and (t11.USER_ID=t111.USER_ID)
uj5u.com熱心網友回復：
請試試這個：
with t2 as (select *,
lag(t1.TRANSACTION_DATE, 1) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE) AS previous_date,
datediff(t1.TRANSACTION_DATE, lag(t1.TRANSACTION_DATE, 1) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE)) AS diff_prev_curr
from CDM_Bill_Details t1)
select *,
avg(diff_prev_curr) OVER (PARTITION BY USER_ID) AS avg_days_diff
from t2
where previous_date is not null
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/433445.html
上一篇：當有多個要考慮的組時找到最大值
下一篇：列出所有通過或失敗的學生