SQLRU.net: IBM DB2, WebSphere, IMS, U2, etc » Распределение общего количества строк по группа

Начало » Использование СУБД » IBM DB2, WebSphere, IMS, U2, etc » Распределение общего количества строк по группа

Показать: Сегодняшние сообщения :: Голосования :: Навигатор по сообщениям
Отправить по e-mail

Распределение общего количества строк по группа [сообщение #1838]

Wed, 15 March 2023 19:32

AVRomanenko
Сообщений: 1
Зарегистрирован: March 2023

Junior Member

Здравствуйте.
Появилась необходимость разбить на группы список из N строк. Но не на равные доли, как делает, скажем, функция NTILE, а по какому-то признаку.
Основная схема заключалась в том, чтобы определить максимальное значение столбца CARD и итерационно насчитывать значение группы в зависимости от того, когда нарастающая сумму превысит это максимальное значение.
например, задается количество групп N = 5, тогда для первая группа присвоится первой строке, так как значение максимально, следующая группа 2 присвоится нижележащим строкам, пока их суммарное количество столбца CARD не станет больше значения первой строки, и так далее.
/index.php/fa/79/0/

Была еще другая схема, в которой определяется SUM(CARD) OVER() / N
Но этот способ, считаю, менее гибким и более сложным, так как в одну группу не должны попасть 1 и строки, например, с наибольшим количеством CARD.
Для удобства прикрепляю тестовый набор данных. Возможно, кто-то решал подобную задачу, или захочет решить. Спасибо!

SELECT 
  *
FROM
(
  VALUES
    ('TABNAME1', 44429559),
    ('TABNAME2', 22459830),
    ('TABNAME3', 19448599),
    ('TABNAME4', 14524689),
    ('TABNAME5', 13011459),
    ('TABNAME6', 12369392),
    ('TABNAME7', 11783221),
    ('TABNAME8', 9527882),
    ('TABNAME9', 9504323),
    ('TABNAME10', 9048014),
    ('TABNAME11', 9045299),
    ('TABNAME12', 7586177),
    ('TABNAME13', 7472317),
    ('TABNAME14', 6259243),
    ('TABNAME15', 5935368),
    ('TABNAME16', 5805440),
    ('TABNAME17', 5538672),
    ('TABNAME18', 3995056),
    ('TABNAME19', 3991581),
    ('TABNAME20', 3902292),
    ('TABNAME21', 3646486),
    ('TABNAME22', 2773363),
    ('TABNAME23', 2180579),
    ('TABNAME24', 1662190),
    ('TABNAME25', 1460698),
    ('TABNAME26', 1436270),
    ('TABNAME27', 1429948),
    ('TABNAME28', 1424434),
    ('TABNAME29', 1400731),
    ('TABNAME30', 1400728),
    ('TABNAME31', 1391317),
    ('TABNAME32', 1089382),
    ('TABNAME33', 939850),
    ('TABNAME34', 875704),
    ('TABNAME35', 750564),
    ('TABNAME36', 730224),
    ('TABNAME37', 635647),
    ('TABNAME38', 563241),
    ('TABNAME39', 562338),
    ('TABNAME40', 549104),
    ('TABNAME41', 513278),
    ('TABNAME42', 482878),
    ('TABNAME43', 378519),
    ('TABNAME44', 362524),
    ('TABNAME45', 361884),
    ('TABNAME46', 338258),
    ('TABNAME47', 327306),
    ('TABNAME48', 236005),
    ('TABNAME49', 186327),
    ('TABNAME50', 102931),
    ('TABNAME51', 79363),
    ('TABNAME52', 77108),
    ('TABNAME53', 63976),
    ('TABNAME54', 58045),
    ('TABNAME55', 51017),
    ('TABNAME56', 39790),
    ('TABNAME57', 29253),
    ('TABNAME58', 28964),
    ('TABNAME59', 22280),
    ('TABNAME60', 22280),
    ('TABNAME61', 14327),
    ('TABNAME62', 4427),
    ('TABNAME63', 4322),
    ('TABNAME64', 3996),
    ('TABNAME65', 3936),
    ('TABNAME66', 3832),
    ('TABNAME67', 3803),
    ('TABNAME68', 3404),
    ('TABNAME69', 3147),
    ('TABNAME70', 3015),
    ('TABNAME71', 2793),
    ('TABNAME72', 2496),
    ('TABNAME73', 2496),
    ('TABNAME74', 2494),
    ('TABNAME75', 2494),
    ('TABNAME76', 1999),
    ('TABNAME77', 1789),
    ('TABNAME78', 838),
    ('TABNAME79', 837),
    ('TABNAME80', 749),
    ('TABNAME81', 288),
    ('TABNAME82', 248),
    ('TABNAME83', 174),
    ('TABNAME84', 168),
    ('TABNAME85', 158),
    ('TABNAME86', 153),
    ('TABNAME87', 84),
    ('TABNAME88', 57),
    ('TABNAME89', 42),
    ('TABNAME90', 37),
    ('TABNAME91', 23),
    ('TABNAME92', 19),
    ('TABNAME93', 12),
    ('TABNAME94', 5)
) AS (TABNAME, CARD)

Известить модератора

Re: Распределение общего количества строк по группа [сообщение #6290 является ответом на сообщение #1838]

Mon, 23 February 2026 14:36

Alexey Kovyazin
Сообщений: 34
Зарегистрирован: June 2022

Member

WITH
-- Исходные данные с порядковым номером
SRC AS (
   SELECT
       TABNAME,
       CARD,
       ROW_NUMBER() OVER (ORDER BY CARD DESC) AS RN
   FROM (
       VALUES
           ('TABNAME1', 44429559),
           ('TABNAME2', 22459830),
           -- ... остальные строки ...
           ('TABNAME94', 5)
   ) AS T(TABNAME, CARD)
),
-- Максимальное значение (порог группы)
PARAMS AS (
   SELECT MAX(CARD) AS MAX_CARD FROM SRC
),
-- Рекурсивный обход с накоплением суммы
GROUPED (TABNAME, CARD, RN, GRP, RUNNING_SUM) AS (
   -- Базовый случай: первая строка
   SELECT
       S.TABNAME,
       S.CARD,
       S.RN,
       1 AS GRP,
       S.CARD AS RUNNING_SUM
   FROM SRC S
   WHERE S.RN = 1

   UNION ALL

   -- Рекурсивный шаг
   SELECT
       S.TABNAME,
       S.CARD,
       S.RN,
       CASE
           WHEN G.RUNNING_SUM + S.CARD > P.MAX_CARD
           THEN G.GRP + 1   -- превысили порог — новая группа
           ELSE G.GRP        -- продолжаем текущую группу
       END AS GRP,
       CASE
           WHEN G.RUNNING_SUM + S.CARD > P.MAX_CARD
           THEN S.CARD       -- сброс накопителя
           ELSE G.RUNNING_SUM + S.CARD
       END AS RUNNING_SUM
   FROM GROUPED G
   JOIN SRC S ON S.RN = G.RN + 1
   CROSS JOIN PARAMS P
)
SELECT
   TABNAME,
   CARD,
   GRP,
   RUNNING_SUM
FROM GROUPED
ORDER BY RN
;

Известить модератора

Переход к форуму:

-=] Вернуться вверх [=-

[ Сформировать XML ] [

] [

]

Текущее время: Tue Feb 24 01:44:37 GMT+3 2026

Общее время, затраченное на создание страницы: 0.00749 секунд