Разбить на неравномерно распределенные сегменты

Question

Каждый день мне возвращают набор из x строк (между 5 и 2000).

Мне нужно обновить столбец из этого набора на основе правил. Я думаю, что этот (не совсем рабочий) пример демонстрирует это

/* 
    35% a
    25% b
    30% c
    10% null
*/

WITH tally
(vals, updateThis, bucket)
AS
(
    SELECT
         DATEADD(DAY, - ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE())
        , NULL
        , NTILE(100) OVER (ORDER BY (SELECT NULL))
    FROM
    (
        VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS a(n)
        CROSS JOIN (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS b(n)
        CROSS JOIN (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS c(n)
    )
--UPDATE
    --SET updateThis
, updated
AS
(
    SELECT
     t.vals
    , CASE
        WHEN t.bucket <= 35 THEN 'a'
        WHEN t.bucket > 35 AND t.bucket <=60 THEN 'b'
        WHEN t.bucket > 60 AND t.bucket <=90 THEN 'c'
        WHEN t.bucket > 60 AND t.bucket <=90 THEN 'NULL'
    END AS updated
    , t.bucket
    FROM tally t
)
SELECT 
    U.updated
    , COUNT(1) AS actual
FROM 
updated u
GROUP BY U.updated

Это решение не является точным и может не обновлять все строки, даже если a + b + c составляет 100%. Также это не будет работать для наборов меньше 100 строк.

Мое текущее рабочее решение:

• Подсчитать общее количество строк
• Рассчитать фактические необходимые строки (CEILING((@totalRows * ratio) / 100)
• Обновите окончательный набор в WHILE LOOP, выбрав текущее значение и необходимые строки.

Есть ли лучшее решение на основе набора, которое поможет мне избавиться от петли?

Answer 1

Не знаю, правильно ли я понял...

Прежде всего, здесь, кажется, есть довольно очевидная ошибка:

    WHEN t.bucket > 60 AND t.bucket <=90 THEN 'NULL'

Разве это не должно быть так:

    WHEN t.bucket >90 THEN 'NULL'

Функция NTILE разложит ваши наборы в довольно ровные ведра. Проверьте мой вывод и найдите, как это ведет себя в угловых случаях. Я предлагаю использовать вычисленный процент на строку, как здесь:

WITH tally
(vals, bucket)
AS
(
    SELECT
         DATEADD(DAY, - ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE())
        ,NTILE(100) OVER (ORDER BY (SELECT NULL))
    FROM
    (
        VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS a(n)
        CROSS JOIN (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS b(n)
        CROSS JOIN (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS c(n)
    )
SELECT *
INTO #tmpBuckets
FROM Tally;

- Я использую этот #tmpBuckets-table, чтобы приблизиться к вашему. У меня есть сценарий за столом

WITH Numbered AS
(
    SELECT *
          ,ROW_NUMBER() OVER(ORDER BY vals DESC) / ((SELECT COUNT(*) FROM #tmpBuckets)/100.0)  AS RunningPercentage
    FROM #tmpBuckets
)
,ComputeBuckets AS
(
    SELECT
     t.*
    , CASE
        WHEN t.RunningPercentage <= 35 THEN 'a'
        WHEN t.RunningPercentage > 35 AND t.RunningPercentage <=60 THEN 'b'
        WHEN t.RunningPercentage > 60 AND t.RunningPercentage <=90 THEN 'c'
        WHEN t.RunningPercentage >90  THEN 'NULL'
    END AS ShnugoMethod
    , CASE
        WHEN t.bucket <= 35 THEN 'a'
        WHEN t.bucket > 35 AND t.RunningPercentage <=60 THEN 'b'
        WHEN t.bucket > 60 AND t.RunningPercentage <=90 THEN 'c'
        WHEN t.bucket > 90  THEN 'NULL'
    END AS ZikatoMethod
    FROM Numbered t
)
SELECT cb.*
FROM ComputeBuckets cb
ORDER BY cb.vals DESC

GO
DROP TABLE #tmpBuckets;

Я думаю, вы знаете, как использовать такой cte для обновления исходной таблицы. В противном случае просто вернитесь с другим вопросом:-)