我正在嘗試獲取每天滾動的 3 天平均交易金額。我首先使用 cast 從時間戳按天對資料進行分組：
select
cast(transaction_time as Date) As Date
, SUM(transaction_amount) as total_transaction_amount
from transactions
Group by cast(transaction_time as date)
order by cast(transaction_time as date)
現在我想獲得滾動的 3 天平均值：
select *,
avg(transaction_amount) OVER(ORDER BY transaction_time
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
as moving_average
from transactions;
但不知道如何使這兩個陳述句一起作業，有什么想法嗎？
uj5u.com熱心網友回復：
您基本上已經完成了所有艱苦的作業，只需將它們粘在一起，CTE 就非常適合。
With transactions_by_day as(
select
cast(transaction_time as Date) As Date
, SUM(transaction_amount) as total_transaction_amount
from transactions
Group by cast(transaction_time as date)
order by cast(transaction_time as date))
select *,
avg(total_transaction_amount) OVER(ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
as moving_average
from transactions_by_day
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457448.html
標籤：sql