我有這樣的資料表
| 訂單日期 | 訂單數量 |
|---|---|
| 2021-03-10 | 40 |
| 2021-03-11 | 80 |
| 2021-03-12 | 63 |
| 2021-03-13 | 20 |
| 2021-03-14 | 80 |
| 2021-03-15 | 80 |
| 2021-03-16 | 70 |
| 2021-03-17 | 20 |
| 2021-03-18 | 80 |
| 2021-03-19 | 80 |
| 2021-03-20 | 80 |
| 2021-03-21 | 80 |
| 2021-03-22 | 80 |
我需要從第一個開始將這些資料按 10 行進行分組，但組的形式是 1-10 行，然后是 2-11，然后是 3-12。所以群體相交。我嘗試添加 ROW_NUMBER 并基于它以某種方式對行進行分組，但由于組相交我不知道如何指定組條件。
我希望結果像
| 訂單日期 | 訂單數量 |
|---|---|
| 第一組（2021-03-10至2021-03-19） | 平均（） |
| 第二組（2021-03-11至2021-03-20） | 平均（） |
| 第三組（2021-03-12至2021-03-21） | 平均（） |
| 第四組（2021-03-13至2021-03-22） | 平均（） |
我用游標解決了這個問題，但它只適用于小樣本資料，當我在 >2kk 行上嘗試它時，它花了很多時間。
有沒有辦法對行進行分組并計算每組的 AVG？
uj5u.com熱心網友回復：
您可以使用對每一行和接下來的 9 行進行操作的視窗函式來創建 10 個組：
WITH cte AS (
SELECT OrdersDate from_Date,
MAX(OrdersDate) OVER (ORDER BY OrdersDate ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING) to_Date,
AVG(1.0 * OrdersQuantity) OVER (ORDER BY OrdersDate ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING) average_qty,
ROW_NUMBER() OVER (ORDER BY OrdersDate) rn,
COUNT(*) OVER () total_rows
FROM tablename
)
SELECT from_Date, to_Date, average_qty
FROM cte
WHERE rn <= total_rows - 9 OR rn = 1;
請參閱演示。
uj5u.com熱心網友回復：
您可以嘗試使用 cte recursive 按DATEADD功能為您的邏輯范圍日期制作日歷表，然后按日期范圍使用自聯接。
查詢 1：
;WITH CTE AS (
SELECT MIN(OrdersDate) s_date,
MAX(OrdersDate) e_date
FROM T
UNION ALL
SELECT DATEADD(day , 1 , s_date) ,e_date
FROM CTE
WHERE DATEADD(day , 10 , s_date) <= e_date
)
SELECT CONCAT(t1.s_date,' to ' , t1.final_date) OrdersDate,
AVG(t2.OrdersQuantity) OrdersQuantity
FROM (
SELECT s_date,DATEADD(day , 9 , s_date) final_date
FROM CTE
) t1 INNER JOIN T t2
ON t2.OrdersDate BETWEEN t1.s_date AND t1.final_date
GROUP BY CONCAT(t1.s_date,' to ' , t1.final_date)
option ( MaxRecursion 0 );
結果：
| OrdersDate | OrdersQuantity |
|--------------------------|----------------|
| 2021-03-10 to 2021-03-19 | 61 |
| 2021-03-11 to 2021-03-20 | 65 |
| 2021-03-12 to 2021-03-21 | 65 |
| 2021-03-13 to 2021-03-22 | 67 |
uj5u.com熱心網友回復：
使用 CTE 首先使用 mod 獲取合格日期范圍的開始日期，然后按連接的開始日期和結束日期分組：
with r as (
select ordersdate StartDate, DateAdd(day,9,ordersdate) EndDate,
DateAdd(day, Count(*) over() % 10, Min(ordersdate) over()) MaxStart
from t
)
select Daterange, Avg(t.OrdersQuantity) OrdersQuantity
from r
cross apply (values( Concat(r.StartDate, ' to ', EndDate)))v(Daterange)
join t on t.ordersdate between StartDate and EndDate
where r.StartDate < = r.MaxStart
group by Daterange
演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/425865.html