產品最後 4 次執行的平均值

我需要顯示產品在最近 4 次生產執行中的平均重量。除了範例之外，我不確定如何最好地描述它：假設我有下表列出了按創建日期列出的產品，以及當天產品的平均重量：

+---------+---------+--------+
| Product |  Date   | Weight |
+---------+---------+--------+
|  900000 | Jan 1   | 20.0   |
|  900000 | March 3 | 12.2   |
|  900000 | July 6  | 15.0   |
|  900000 | July 7  | 14.0   |
|  900000 | Aug 6   | 3.0    |
|  800000 | June 2  | 14.0   |
|  800000 | June 3  | 12.0   |
+---------+---------+--------+

我正在尋找的最終結果是添加一個列，其中包含該產品執行的最後 4 個日期的平均重量，因此如下所示：

+---------+---------+--------+----------------+
| Product |  Date   | Weight | Average Weight |
+---------+---------+--------+----------------+
|  900000 | Jan 1   | 20.0   | NULL           |
|  900000 | March 3 | 12.2   | NULL           |
|  900000 | July 6  | 15.0   | NULL           |
|  900000 | July 7  | 14.0   | NULL           |
|  900000 | Aug 6   | 3.0    | 15.3           | Jan1+Mar3+July6+July7/4
|  900000 | Aug 8   | 13.0   | 11.05          | Mar3+July6+July7+Aug6/4
|  800000 | June 2  | 14.0   | NULL           |
|  800000 | June 3  | 12.0   | NULL           |
|  800000 | June 4  | 12.0   | NULL           |
|  800000 | June 5  | 12.0   | NULL           |
|  800000 | June 6  | 12.0   | 12.5           | etc...
+---------+---------+--------+----------------+

NULL 就在那裡，因為在此範例中，您無法計算過去 4 次執行的平均值，因為數據不存在。

誰能指出我需要做這樣的事情的方向？

樣本數據：

CREATE TABLE dbo.Thing
(
   Product integer NOT NULL,
   TheDate date NOT NULL,
   TheWeight decimal(5, 1) NOT NULL
);

INSERT dbo.Thing
   (Product, TheDate, TheWeight)
VALUES
   (900000, CONVERT(date, '20160101', 112), 20.0),
   (900000, '20160303', 12.2),
   (900000, '20160706', 15.0),
   (900000, '20160707', 14.0),
   (900000, '20160806', 3.0 ),
   (900000, '20160808', 13.0 ),
   (800000, '20160602', 14.0),
   (800000, '20160603', 12.0),
   (800000, '20160604', 12.0),
   (800000, '20160605', 12.0),
   (800000, '20160606', 12.0);

解決方案：

這裡的總體構想是使用 SQL Server 2012 及更高版本中提供的擴展視窗聚合函式。

唯一的問題是，AVG如果視窗小於所需的四行，則不會在視窗上返回 null。為了解決這個問題，我們還使用計算在視窗中找到的行數COUNT。CASE如果視窗包含少於四行，則可以使用一個簡單的表達式返回 null：

SELECT
   T.Product,
   T.TheDate,
   T.TheWeight,
   [Average Weight] =
       CASE
           WHEN
               4 > COUNT_BIG(*) OVER (
                   PARTITION BY T.Product
                   ORDER BY T.Product, T.TheDate
                   ROWS BETWEEN 4 PRECEDING
                   AND 1 PRECEDING
                   )
               THEN NULL
           ELSE
               AVG(T.TheWeight) OVER (
                   PARTITION BY T.Product
                   ORDER BY T.Product, T.TheDate
                   ROWS BETWEEN 4 PRECEDING
                   AND 1 PRECEDING
                   )
       END
FROM dbo.Thing AS T
ORDER BY
   T.Product,
   T.TheDate;

在Stack Exchange 數據資源管理器上執行查詢

輸出：

更多資訊：

SQL Server 中的視窗函式

相關問題：

使用視窗函式的日期範圍滾動總和

引用自：https://dba.stackexchange.com/questions/131204