Besoin d'une solution basée sur un ensemble pour regrouper les rangées

Question

J'ai besoin de regrouper un ensemble de lignes en fonction de l'information suivante Category et limiter également les lignes combinées en fonction de la colonne SUM(Number) doit être inférieure ou égale à la colonne @Limit valeur.

Pour chaque personne distincte Category J'ai besoin d'identifier les "buckets" qui sont <= @limit . Si la SOMME( Number ) de toutes les lignes pour un Category sont <= @Limit alors il n'y aura qu'un seul seau pour cela Category (comme 'CCCC' dans l'exemple de données). Cependant, si la méthode SUM( Number )> @limit alors il y aura plusieurs rangées de seaux pour cela. Category (comme 'AAAA' dans les données d'exemple), et chaque godet doit être <= @Limit . Il peut y avoir autant de seaux que nécessaire. Regardez également Category dont une ligne est supérieure à la valeur "DDDD". @Limit tout seul, et est divisé en deux lignes dans le jeu de résultats.

Compte tenu de ces données simplifiées :

DECLARE @Detail table (DetailID int  primary key, Category char(4), Number int)
SET NOCOUNT ON
INSERT @Detail VALUES ( 1, 'AAAA',100)
INSERT @Detail VALUES ( 2, 'AAAA', 50)
INSERT @Detail VALUES ( 3, 'AAAA',300)
INSERT @Detail VALUES ( 4, 'AAAA',200)
INSERT @Detail VALUES ( 5, 'BBBB',500)
INSERT @Detail VALUES ( 6, 'CCCC',200)
INSERT @Detail VALUES ( 7, 'CCCC',100)
INSERT @Detail VALUES ( 8, 'CCCC', 50)
INSERT @Detail VALUES ( 9, 'DDDD',800)
INSERT @Detail VALUES (10, 'EEEE',100)
INSERT @Detail VALUES (11, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (12, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (13, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (14, 'AAAA',200) --EDIT added
SET NOCOUNT OFF

DECLARE @Limit int
SET @Limit=500

J'ai besoin d'un de ces ensembles de résultats :

DetailID  Bucket  |    DetailID  Category Bucket
--------  ------  |    --------  -------- ------
 1        1       |     1        'AAAA'   1     
 2        1       |     2        'AAAA'   1     
 3        1       |     3        'AAAA'   1     
 4        2       |     4        'AAAA'   2     
11        2       |    11        'AAAA'   2      --EDIT added
12        3       |    12        'AAAA'   3      --EDIT added
13        3       |    13        'AAAA'   3      --EDIT added
14        4       |    14        'AAAA'   4      --EDIT added
 5        5       OR    5        'BBBB'   1     
 6        6       |     6        'CCCC'   1     
 7        6       |     7        'CCCC'   1     
 8        6       |     8        'CCCC'   1     
 9        7       |     9        'DDDD'   1     
 9        8       |     9        'DDDD'   2     
10        9       |    10        'EEEE'   1   

EDIT après avoir essayé toutes les réponses

Toutes les tentatives d'une solution basée sur un ensemble ne fonctionnant pas comme il le faudrait, j'opte pour une modification de Réponse de @GalacticJello , modification notée dans le code ci-dessous. En gros, je trouve toutes les lignes où la catégorie entière entre dans le seau et je les INSERTE en utilisant un seul INSERT-SELECT, puis je boucle sur les données restantes en utilisant la boucle sans curseur @GalacticJello. Cela fonctionnera très bien dans ma situation puisqu'il n'y aura pratiquement jamais de lignes traitées par la boucle.

DECLARE @DetailTemp table (PID INT IDENTITY(1,1), DetailID int  primary key, Category char(4), Number int) 
DECLARE @DetailFinal table (DetailID int, Category char(4), Bucket int) ---<<<renamed column to Bucket

DECLARE @DetailCount int
SET @DetailCount = 0;

--------<<<optimization added starts here
;WITH AllSingleBuckets AS (
    SELECT
        Category
        FROM @Detail
        GROUP BY Category
        HAVING SUM(Number)<=@Limit

)
INSERT INTO @DetailFinal
        (DetailID, Category, Bucket)
    SELECT
        d.DetailID,d.Category,1
        FROM @Detail                    d
            INNER JOIN AllSingleBuckets s ON d.Category=s.Category
--------<<<optimization added ends here

INSERT @DetailTemp
--------<<<changed for optimization, added WHERE clause
SELECT d.DetailId, d.Category, d.Number FROM @Detail d WHERE NOT EXISTS (SELECT 1 FROM @DetailFinal f WHERE d.Category=f.Category) ORDER BY Category, DetailId
SELECT @DetailCount = @@ROWCOUNT

DECLARE @CurrentPid int
SET @CurrentPid = 1

DECLARE @ThisId int
DECLARE @ThisCategory char(4)
DECLARE @ThisNumber int

DECLARE @CurrentCategory char(4)
DECLARE @CurrentSum INT
DECLARE @CurrentBucket INT

WHILE @CurrentPid <= @DetailCount
BEGIN
    SELECT @ThisId = DetailId, @ThisCategory = Category, @ThisNumber = Number
    FROM @DetailTemp 
    WHERE PID = @CurrentPid

    IF @ThisCategory = @CurrentCategory
    BEGIN
        IF @CurrentSum + @ThisNumber > @Limit
        BEGIN
            SET @CurrentBucket = @CurrentBucket + 1
            SET @CurrentSum = @ThisNumber
        END
        ELSE
        BEGIN
            SET @CurrentSum = @CurrentSum + @ThisNumber
        END
    END
    ELSE
    BEGIN
        SET @CurrentBucket = 1
        SET @CurrentCategory = @ThisCategory
        SET @CurrentSum = @ThisNumber
    END

    WHILE @CurrentSum > @Limit
    BEGIN
        INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket
        SET @CurrentBucket = @CurrentBucket + 1
        SET @CurrentSum = @CurrentSum - @Limit
    END

    INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket

    SET @CurrentPid = @CurrentPid + 1
END

SELECT * from @DetailFinal ORDER BY Category --------<<<added order by

SORTIE :

DetailID    Category Bucket
----------- -------- -----------
1           AAAA     1
2           AAAA     1
3           AAAA     1
4           AAAA     2
11          AAAA     2
12          AAAA     3
13          AAAA     3
14          AAAA     4
5           BBBB     1
6           CCCC     1
7           CCCC     1
8           CCCC     1
9           DDDD     1
9           DDDD     2
10          EEEE     1

(15 row(s) affected)

Answer 1

Peut-être que ce qui suit vous sera utile (il ne produit pas 2 lignes pour 'DDDD' cependant ; je ne suis pas sûr que vous puissiez le faire sans insérer 2 lignes différentes. )

select detailId, category,
FLOOR((SELECT sum(Number)
from Detail where category=t2.category and detailId <= t2.detailId
)/501)+1 as bucket
from Detail t2
order by detailId;

Answer 2

Vous devez tenir un total courant pour savoir quand la @Limite est atteinte. Bien sûr, CROSS APPLY n'est pas forcément évolutif (et dépend aussi des indices).

Edit : fixed DDDD, bucket 1

;WITH cRunning AS
(
    SELECT
        D1.DetailID, D1.Category, D3.RunningTotal, D3.GroupCount
    FROM
        @Detail D1
        CROSS APPLY
        (SELECT
             Category, COUNT(*) AS GroupCount,
             CAST(SUM(Number) AS int) AS RunningTotal
        FROM @Detail D2
        WHERE D1.Category = D2.Category AND D1.DetailID >= D2.DetailID
        GROUP BY D2.Category) D3
)
SELECT
    DetailID, Category,
    RunningTotal / @Limit + 1 AS Bucket --abuse integer math
FROM
    cRunning
UNION ALL
SELECT --singletons > @Limit
    DetailID, Category, 1
FROM
    cRunning
WHERE
    GroupCount = 1 AND RunningTotal > @Limit
ORDER BY
    Category, DetailID, Bucket

Bien sûr, ma première réponse fonctionne si vous ajoutez une ligne zéro fictive pour DDDD.

...
INSERT @Detail VALUES ( xxx, 'DDDD',0)
...
SELECT
    D1.DetailID, D1.Category,
    RunningTotal / @Limit + 1 AS Bucket --abuse integer math
FROM
    @Detail D1
    CROSS APPLY
    (SELECT SUM(Number) AS RunningTotal
    FROM @Detail D2
    WHERE D1.Category = D2.Category AND D1.DetailID >= D2.DetailID
    GROUP BY D2.Category) D3

Answer 3

ENFIN !

J'ai trouvé quelques bogues dans mon code, je les ai corrigés et maintenant j'ai réussi à faire fonctionner le tout avec un CTE. Je pensais que si un détail s'étendait sur plusieurs seaux, il serait toujours réparti entre eux. Il semble que vous voulez maintenant que les détails plus grands qu'un seau soient répartis sur plusieurs seaux, mais que les autres détails soient complètement poussés vers le seau suivant. Vous réalisez que dans ce cas, vous pourriez vous retrouver avec un 50 dans un seau tout seul, n'est-ce pas ? Si le détail suivant était 500, il serait alors poussé vers l'avant et le 50 obtiendrait un seau à lui tout seul - spacieux !

Quoi qu'il en soit, je me contente d'inclure le code ici en tant que solution entièrement basée sur le jeu, au cas où quelqu'un serait intéressé :

;WITH sequence_ids AS (SELECT DetailID, Category, Number, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY DetailID) AS sequence_id FROM @Detail),
main_cte AS (
    SELECT
        D1.DetailID,
        D1.Category,
        D1.Number,
        CASE WHEN @Limit > D1.Number THEN @Limit - D1.Number ELSE 0 END AS RemainingBucket,
        CASE WHEN D1.Number > @Limit THEN D1.Number - @Limit ELSE 0 END AS RemainingDetail,
        D1.sequence_id,
        1 AS bucket
    FROM
        sequence_ids D1
    WHERE
        sequence_id = 1
    UNION ALL
    SELECT
        D2.DetailID,
        D2.Category,
        D2.Number,
        CASE WHEN COALESCE(NULLIF(RemainingBucket, 0), @Limit) > COALESCE(NULLIF(main_cte.RemainingDetail, 0), D2.Number) THEN COALESCE(NULLIF(RemainingBucket, 0), @Limit) - COALESCE(NULLIF(main_cte.RemainingDetail, 0), D2.Number) ELSE 0 END AS RemainingBucket,
        CASE WHEN COALESCE(NULLIF(main_cte.RemainingDetail, 0), D2.Number) > COALESCE(NULLIF(RemainingBucket, 0), @Limit) THEN COALESCE(NULLIF(main_cte.RemainingDetail, 0), D2.Number) - COALESCE(NULLIF(RemainingBucket, 0), @Limit) ELSE 0 END AS RemainingDetail,
        D2.sequence_id,
        CASE WHEN RemainingBucket = 0 THEN bucket + 1 ELSE bucket END
    FROM
        main_cte
    INNER JOIN sequence_ids D2 ON
        D2.Category = main_cte.Category AND
        ((main_cte.RemainingDetail > 0 AND D2.DetailID = main_cte.DetailID) OR
         (main_cte.RemainingDetail <= 0 AND D2.sequence_id = main_cte.sequence_id + 1))
)
SELECT
    *
FROM
    main_cte
ORDER BY
    Category,
    bucket,
    sequence_id

Answer 4

DECLARE @Detail table (DetailID int  primary key, Category char(4), Number int) 
SET NOCOUNT ON 
INSERT @Detail VALUES ( 1, 'AAAA',100) 
INSERT @Detail VALUES ( 2, 'AAAA', 50) 
INSERT @Detail VALUES ( 3, 'AAAA',300) 
INSERT @Detail VALUES ( 4, 'AAAA',200) 
INSERT @Detail VALUES ( 5, 'BBBB',500) 
INSERT @Detail VALUES ( 6, 'CCCC',200) 
INSERT @Detail VALUES ( 7, 'CCCC',100) 
INSERT @Detail VALUES ( 8, 'CCCC', 50) 
INSERT @Detail VALUES ( 9, 'DDDD',800) 
INSERT @Detail VALUES (10, 'EEEE',100) 
INSERT @Detail VALUES (11, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (12, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (13, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (14, 'AAAA',200) --EDIT added 
SET NOCOUNT OFF 

DECLARE @Limit int 
SET @Limit=500 

DECLARE @DetailTemp table (PID INT IDENTITY(1,1), DetailID int  primary key, Category char(4), Number int) 
DECLARE @DetailFinal table (DetailID int, Category char(4), Number int) 

DECLARE @DetailCount int
SET @DetailCount = 0;

INSERT @DetailTemp
SELECT DetailId, Category, Number FROM @Detail ORDER BY Category, DetailId
SELECT @DetailCount = @@ROWCOUNT

DECLARE @CurrentPid int
SET @CurrentPid = 1

DECLARE @ThisId int
DECLARE @ThisCategory char(4)
DECLARE @ThisNumber int

DECLARE @CurrentCategory char(4)
DECLARE @CurrentSum INT
DECLARE @CurrentBucket INT

WHILE @CurrentPid <= @DetailCount
BEGIN
    SELECT @ThisId = DetailId, @ThisCategory = Category, @ThisNumber = Number
    FROM @DetailTemp 
    WHERE PID = @CurrentPid

    IF @ThisCategory = @CurrentCategory
    BEGIN
        IF @CurrentSum + @ThisNumber > @Limit
        BEGIN
            SET @CurrentBucket = @CurrentBucket + 1
            SET @CurrentSum = @ThisNumber
        END
        ELSE
        BEGIN
            SET @CurrentSum = @CurrentSum + @ThisNumber
        END
    END
    ELSE
    BEGIN
        SET @CurrentBucket = 1
        SET @CurrentCategory = @ThisCategory
        SET @CurrentSum = @ThisNumber
    END

    WHILE @CurrentSum > @Limit
    BEGIN
        INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket
        SET @CurrentBucket = @CurrentBucket + 1
        SET @CurrentSum = @CurrentSum - @Limit
END

    INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket

    SET @CurrentPid = @CurrentPid + 1
END

SELECT * from @DetailFinal