SQL Server 2012基于桶的运行总量

Question

对于SQL Server 2012，我试图根据桶的最大大小(下面的示例中为100)和列的运行总数将给定的行分配给顺序桶。我找到的大多数解决方案是按已知列更改值进行分区，例如按部门id进行分区等。然而，在这种情况下，我只有顺序id和大小。我找到的最接近的解决方案是在SQL Server 2008的这个线程中讨论的，我尝试了它，但是对于大型行集的性能非常慢，比基于游标的解决方案差得多。https://dba.stackexchange.com/questions/45179/how-can-i-write-windowing-query-which-sums-a-column-to-create-discrete-buckets

该表最多可以包含1000万行。SQL Server 2012支持SUM OVER和LAG和LEAD功能，不知道是否有人可以提出一个基于2012的解决方案。

CREATE TABLE raw_data (
id    INT PRIMARY KEY
, size  INT NOT NULL
);
INSERT INTO raw_data
(id, size)
VALUES 
  ( 1,    96) -- new bucket here, maximum bucket size is 100
, ( 2,    10) -- and here
, ( 3,    98) -- and here
, ( 4,    20)
, ( 5,    50)
, ( 6,    15)
, ( 7,    97)
, ( 8,    96) -- and here
;
--Expected output
--bucket_size is for illustration only, actual needed output is bucket only
id  size    bucket_size bucket
-----------------------------
1   100     100         1
2    10     10          2
3    98     98          3
4    20     85          4
5    50     85          4
6    15     85          4
7    97     98          5
8    1      98          5

TIA

Answer 1

在SQL Server 2012中使用窗口函数和框架可以很容易地实现这一点。语法看起来相当复杂，但概念很简单——将前面的所有行相加，直到并包括当前行。本例中的cumulative_bucket_size列用于演示，因为它是用于导出桶号的公式的一部分:

DECLARE @Bucket_Size AS INT;
SET @Bucket_Size = 100
SELECT
    id,
    size,
    SUM(size) OVER  (
                        PARTITION BY 1 ORDER BY id ASC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS cumulative_bucket_size,
    1 + SUM(size) OVER  (
                        PARTITION BY 1 ORDER BY id ASC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) / @Bucket_Size AS bucket
FROM
    raw_data

PARTITION BY子句是可选的，但如果列分组有不同的"桶集"，则会很有用。我把它添加到这里是为了完整。

结果:

id  size    cumulative_bucket_size  bucket
------------------------------------------
1   96      96                      1
2   10      106                     2
3   98      204                     3
4   20      224                     3
5   50      274                     3
6   15      289                     3
7   97      386                     4
8   96      482                     5

您可以在以下文章中阅读更多关于windows框架的内容:

https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/

Answer 2

在使用运行总数方法分配桶号之前，需要生成bucket_size列，因为数字将基于该列生成。

根据您期望的输出，桶范围为

1..10
11..85
86..100

您可以使用一个简单的CASE表达式来生成一个bucket_size列，如下所示:

CASE
    WHEN size <= 10 THEN 10
    WHEN size <= 85 THEN 85
    ELSE 100
END

然后您将使用LAG()来确定一行是否开始一个属于同一桶的新大小序列:

CASE bucket_size
    WHEN LAG(bucket_size) OVER (ORDER BY id) THEN 0
    ELSE 1
END

这两个计算可以在同一个(子)查询中使用CROSS APPLY:

SELECT
    d.id,
    d.size,
    x.bucket_size,  -- for illustration only
    is_new_seq = CASE x.bucket_size
                     WHEN LAG(x.bucket_size) OVER (ORDER BY d.id) THEN 0
                     ELSE 1
                 END
FROM dbo.raw_data AS d
CROSS APPLY
(
    SELECT
        CASE
            WHEN size <= 10 THEN 10
            WHEN size <= 85 THEN 85
            ELSE 100
        END
) AS x (bucket_size)

上面的查询将产生如下输出:

id  size  bucket_size  is_new_seq
--  ----  -----------  ----------
1   96    100          1
2   10    10           1
3   98    100          1
4   20    85           1
5   50    85           0
6   15    85           0
7   97    100          1
8   96    100          0

现在使用该结果作为派生表，并对is_new_seq应用SUM() OVER来生成桶号，如下所示:

SELECT
    id,
    size,
    bucket = SUM(is_new_seq) OVER (ORDER BY id)
FROM
(
    SELECT
        d.id,
        d.size,
        is_new_seq = CASE x.bucket_size
                         WHEN LAG(x.bucket_size) OVER (ORDER BY d.id) THEN 0
                         ELSE 1
                     END
    FROM dbo.raw_data AS d
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN size <= 10 THEN 10
                WHEN size <= 85 THEN 85
                ELSE 100
            END
    ) AS x (bucket_size)
) AS s
;