sql - 在 T-SQL 中使用 Window 函数计算类别列

Question

我的问题很简单，但我找不到解决方案 - 可能使用窗口函数，我有以下示例数据：

ID  OrderDate   TotalOrder
--------------------------
1   2019-01-01   5
2   2019-01-02   3
3   2019-01-03   1
4   2019-01-04  15
5   2019-01-05  20
6   2019-01-06  18
7   2019-01-07   2
8   2019-01-08   4
9   2019-01-09   9

所需的结果是：

ID  OrderDate   TotalOrder  Category
-------------------------------------
1   2019-01-01   5          1
2   2019-01-02   3          1
3   2019-01-03   1          1
4   2019-01-04  15          2
5   2019-01-05  20          2
6   2019-01-06  18          2
7   2019-01-07   2          3
8   2019-01-08   4          3
9   2019-01-09   9          3

该Category列是根据TotalOrder值的范围计算的。

如果该TotalOrder值小于 10，Category则增加 的值。

标签： sqlsql-servertsqlwindow-functions

Answer 1

你可以使用LAGand SUM OVER()：

-- Create sample data
CREATE TABLE #Tbl(
    ID          INT,
    OrderDate   DATE,
    TotalOrder  INT
);
INSERT INTO #Tbl VALUES
(1, '2019-01-01', 5),   (2, '2019-01-02', 3),   (3, '2019-01-03', 1),
(4, '2019-01-04', 15),  (5, '2019-01-05', 20),  (6, '2019-01-06', 18),
(7, '2019-01-07', 2),   (8, '2019-01-08', 4),   (9, '2019-01-09', 9);

-- Query
WITH Cte AS(
    SELECT *,
        LessThanTen = CASE WHEN TotalOrder < 10 THEN 1 ELSE 0 END
    FROM #Tbl
),
CteLag AS (
    SELECT *,
        Prev = LAG(LessThanTen, 1, NULL) OVER(ORDER BY OrderDate, ID)
    FROM Cte
)
SELECT
    ID,
    OrderDate,
    TotalOrder,
    Category = SUM(CASE WHEN Prev = LessThanTen THEN 0 ELSE 1 END) OVER(ORDER BY OrderDate, ID)
FROM CteLag
ORDER BY OrderDate, ID;

-- Cleanup
DROP TABLE #Tbl;

ONLINE DEMO