這是表格：https : //dbfiddle.uk/markdown?rdbms=sqlserver_2019&fiddle=effc94afe681b2dfdb3e2c02c2b005ea
我想找到每個客戶的最后 3 個值（我的意思是最后 3 個 OrderID）的平均總金額。如果客戶沒有 3 次操作，結果應該為空。
這是我的答案（T-SQL）：
SELECT s.CustomerID,avg(s.TotalAmount) as AverageofLast3_operation
FROM (SELECT OrderID, CustomerID, EventDate, TotalAmount,
ROW_NUMBER() over (partition by CustomerID ORDER BY OrderID asc) as Row_num
FROM CustomerOperation
)s
WHERE s.Row_num>3
GROUP BY CustomerID
結果是：
| 顧客ID | AverageofLast3_operation |
|---|---|
| 1 | 7833 |
| 2 | 1966年 |
根據問題，我也應該有這樣的一行：
| 顧客ID | AverageofLast3_operation |
|---|---|
| 3 | 空值 |
如何使用 T-SQL 實作此目的？
uj5u.com熱心網友回復：
您需要條件聚合：
SELECT CustomerID,
AVG(CASE WHEN counter >= 3 THEN TotalAmount END) AS AverageofLast3_operation
from (
SELECT OrderID, CustomerID, EventDate, TotalAmount,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS Row_num,
COUNT(*) OVER (PARTITION BY CustomerID) counter
FROM CustomerOperation
) s
WHERE Row_num <= 3
GROUP BY CustomerID;
或者：
SELECT CustomerID,
CASE WHEN COUNT(*) = 3 THEN AVG(TotalAmount) END AS AverageofLast3_operation
from (
SELECT OrderID, CustomerID, EventDate, TotalAmount,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS Row_num
FROM CustomerOperation
) s
WHERE Row_num <= 3
GROUP BY CustomerID;
請參閱演示。
uj5u.com熱心網友回復：
您可以像這樣使用條件平均值：
with t as (
select customerId,
case when
Row_Number() over(partition by customerid order by orderid desc) <=3 then totalamount
else 0 end TotalAmount,
Count(*) over (partition by customerid) cnt
from CustomerOperation
)
select customerId, Avg(case when cnt>=3 then totalamount end) as Average
from t
where totalAmount>0
group by CustomerId
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/352377.html
標籤：sql sql-server 查询语句 分组
上一篇：使用Jax-Rs將PrometheusMetricsEndpoint添加到Java應用程式
下一篇：如何進行查詢以獲取比率？