Сегменты SQL Server 2012 на основе промежуточного итога

Для SQL Server 2012 я пытаюсь назначить заданные строки последовательным корзинам на основе максимального размера корзины (100 в приведенном ниже примере) и общего количества столбцов. Большинство решений, которые я нашел, разделяются по известному значению изменения столбца, например. раздел по идентификатору отдела и т. д. Однако в этой ситуации все, что у меня есть, это последовательный идентификатор и размер. Ближайшее решение, которое я нашел, обсуждается в этой теме для SQL Server 2008, и я попробовал его, но производительность очень низкая для большого набора строк, намного хуже, чем решение на основе курсора. https://dba.stackexchange.com/questions/45179/how-can-i-write-windowing-query-what-sums-a-column-to-create-discrete-buckets

Эта таблица может содержать до 10 миллионов строк. С 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

ТИА


sql-server-2012 sum cumulative-sum
person Emerald77    schedule 13.08.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Прежде чем вы сможете использовать метод промежуточного итога для присвоения номеров сегментов, вам необходимо сгенерировать этот столбец 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

Теперь используйте этот результат в качестве производной таблицы и примените SUM() OVER к is_new_seq, чтобы получить номера сегментов, вот так:

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
;
person Andriy M    schedule 15.08.2014

arrow_upward
0
arrow_downward

Вы можете легко добиться этого в 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

Подробнее об обрамлении окон вы можете прочитать в следующей статье:

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

person SQLDiver    schedule 12.04.2015