J'ai besoin de grouper des lignes de certaines colonnes et d'une somme en cours jusqu'à ce qu'il atteigne un seuil. Le plus proche que j'ai eu était avec une requête basée sur Cette réponse , mais cette solution n'est pas aussi précise que nécessaire, car la somme doit être réinitialisée et redémarré lorsqu'elle atteint le seuil.
Voici ma tentative avec quelques données d'échantillonnage et un seuil de 100: P>
| Id | GroupId | Code | Total | Limit | Groups | GroupsExpected | |----|---------|------|-------|-------|--------|----------------| | 1 | 1 | 1111 | 20 | 0 | 1 | 1 | | 2 | 1 | 1111 | 75 | 0 | 1 | 1 | | 3 | 1 | 1111 | 40 | 1 | 2 | 2 | | 4 | 1 | 1111 | 20 | 1 | 2 | 2 | | 5 | 1 | 1111 | 20 | 1 | 2 | 2 | | 6 | 1 | 1111 | 25 | 2 | 3 | 3 | | 7 | 1 | 2222 | 20 | 2 | 4 | 4 | | 8 | 1 | 2222 | 20 | 2 | 4 | 4 | | 9 | 1 | 2222 | 20 | 2 | 4 | 4 | | 10 | 1 | 2222 | 20 | 2 | 4 | 4 | | 11 | 2 | 3333 | 10 | 2 | 5 | 5 | | 12 | 2 | 3333 | 90 | 3 | 6 | 6 | | 13 | 2 | 3333 | 90 | 4 | 7 | 7 | | 14 | 2 | 3333 | 90 | 5 | 8 | 8 | | 15 | 2 | 3333 | 90 | 6 | 9 | 9 | | 16 | 2 | 3333 | 10 | 6 | 9 | 10 | | 17 | 2 | 3333 | 10 | 6 | 9 | 10 | | 18 | 2 | 3333 | 10 | 6 | 9 | 10 | | 19 | 2 | 3333 | 10 | 6 | 9 | 10 | | 20 | 2 | 3333 | 10 | 7 | 10 | 10 | | 21 | 2 | 3333 | 10 | 7 | 10 | 10 | | 22 | 2 | 3333 | 10 | 7 | 10 | 10 | | 23 | 2 | 3333 | 10 | 7 | 10 | 10 | | 24 | 2 | 3333 | 10 | 7 | 10 | 10 | | 25 | 2 | 3333 | 10 | 7 | 10 | 11 | | 26 | 2 | 3333 | 10 | 7 | 10 | 11 | | 27 | 2 | 3333 | 10 | 7 | 10 | 11 | | 28 | 2 | 3333 | 10 | 7 | 10 | 11 | | 29 | 2 | 3333 | 10 | 7 | 10 | 11 | | 30 | 2 | 3333 | 10 | 8 | 11 | 11 | | 31 | 2 | 3333 | 10 | 8 | 11 | 11 | | 32 | 2 | 3333 | 10 | 8 | 11 | 11 | | 33 | 2 | 3333 | 10 | 8 | 11 | 11 | | 34 | 2 | 3333 | 10 | 8 | 11 | 12 | | 35 | 2 | 3333 | 10 | 8 | 11 | 12 |
3 Réponses :
ici c'est avec un curseur.
declare @table table (
Id int not null,
GroupId int not null,
Code nvarchar(14) not null,
Total int not null
)
insert into @table values
( 1, 1, '1111', 20),( 2, 1, '1111', 75),( 3, 1, '1111', 40),( 4, 1, '1111', 20),
( 5, 1, '1111', 20),( 6, 1, '1111', 25),( 7, 1, '2222', 20),( 8, 1, '2222', 20),
( 9, 1, '2222', 20),(10, 1, '2222', 20),(11, 2, '3333', 10),(12, 2, '3333', 90),
(13, 2, '3333', 90),(14, 2, '3333', 90),(15, 2, '3333', 90),(16, 2, '3333', 10),
(17, 2, '3333', 10),(18, 2, '3333', 10),(19, 2, '3333', 10),(20, 2, '3333', 10),
(21, 2, '3333', 10),(22, 2, '3333', 10),(23, 2, '3333', 10),(24, 2, '3333', 10),
(25, 2, '3333', 10),(26, 2, '3333', 10),(27, 2, '3333', 10),(28, 2, '3333', 10),
(29, 2, '3333', 10),(30, 2, '3333', 10),(31, 2, '3333', 10),(32, 2, '3333', 10),
(33, 2, '3333', 10),(34, 2, '3333', 10),(35, 2, '3333', 10)
select *
from @table
order by code,id
declare @runtotal int = 0
declare @groups int = 0
declare @code nvarchar(14)
declare @currentcode nvarchar(14) = ''
declare @total int
declare @id int
declare @output table (
Id int not null,
Groups int not null
)
declare cursor_table cursor
for select id, code, total
from @table
order by code,id
open cursor_table
fetch next from cursor_table into @id, @code, @total
while @@fetch_status = 0
begin
set @runtotal += @total
if @runtotal >= 100 or @code <> @currentcode
begin
set @runtotal = @total
set @groups += 1
set @currentcode = @code
end
insert into @output
select @id,@groups
fetch next from cursor_table into @id, @code, @total
end
select t.*,groups
from @table t
inner join @output o on o.id=t.id
close cursor_table
deallocate cursor_table
Voici un CTE récursif. Il faut que l'ID soit incrémental, sans lacunes et dans l'ordre de votre choix. Ceci est vrai dans vos données d'échantillonnage. Cependant, si ce n'est pas garanti d'être comme celui de vos données réelles, vous devez utiliser une sous-requête avec Row_Number pour obtenir un numéro séquentiel dans l'ordre de votre choix.
declare @table table (
Id int not null,
GroupId int not null,
Code nvarchar(14) not null,
Total int not null
)
insert into @table values
( 1, 1, '1111', 20),( 2, 1, '1111', 75),( 3, 1, '1111', 40),( 4, 1, '1111', 20),
( 5, 1, '1111', 20),( 6, 1, '1111', 25),( 7, 1, '2222', 20),( 8, 1, '2222', 20),
( 9, 1, '2222', 20),(10, 1, '2222', 20),(11, 2, '3333', 10),(12, 2, '3333', 90),
(13, 2, '3333', 90),(14, 2, '3333', 90),(15, 2, '3333', 90),(16, 2, '3333', 10),
(17, 2, '3333', 10),(18, 2, '3333', 10),(19, 2, '3333', 10),(20, 2, '3333', 10),
(21, 2, '3333', 10),(22, 2, '3333', 10),(23, 2, '3333', 10),(24, 2, '3333', 10),
(25, 2, '3333', 10),(26, 2, '3333', 10),(27, 2, '3333', 10),(28, 2, '3333', 10),
(29, 2, '3333', 10),(30, 2, '3333', 10),(31, 2, '3333', 10),(32, 2, '3333', 10),
(33, 2, '3333', 10),(34, 2, '3333', 10),(35, 2, '3333', 10)
;with rcte as (
select id, groupid, code, total, total as runtotal, 1 as groups
from @table
where id=1
union all
select t.id, t.groupid, t.code, t.total,
case when r.runtotal + t.total >= 100 or r.code <> t.code
then t.total
else r.runtotal + t.total
end as runtotal,
case when r.runtotal + t.total >= 100 or r.code <> t.code
then groups + 1
else groups
end as groups
from rcte r
inner join @table t on t.id = r.id + 1
)
select id, groupid, code, total, groups
from rcte
order by id
Vous pouvez le faire à l'aide d'un CTE récursif:
Peut-être récursif CTE? Je ne suis pas sûr. Je tente de penser au curseur (Yuk).
Vous dites "seuil de 100" et "ne peut pas dépasser 100". Vous soulignez que le groupe 10 n'est pas acceptable car il est égal à 100 - mais l'égalité de 100 ne dépasse pas 100. Depuis que vous indiquez dans vos résultats attendus que le total de 100 n'est pas acceptable, j'ai pris cela pour signifier «le total du fonctionnement doit Être moins de 100 ", ce qui signifie exactement 100 n'est pas acceptable.
Vous n'avez également rien dit de ce qu'il faut faire si un total individuel est de 100 ou plus.