SQL Running total group with sum threshold reset

Question

I have the table :

PersonID    FirstName   PersonAge
1           Pras        2
2           Deep        3
3           Test        4
4           Prash       2
5           ser         1
6           df          8
7           ddf         5
8           vvv         4
9           ddd         1
10          eww         6
11          vvv         3
12          vbbb        7
13          Prabbbbs    6

I want to group them such that total age of group of doesn't exceed 10.

I can do this with recursion but it is inefficient on larger tables.

  ;WITH cte AS
(
    SELECT PersonID, PersonAge, 1 AS [Group], PersonAge AS RunningTotal FROM POP where PersonId=1
    UNION ALL
    SELECT data.PersonId, data.PersonAge, 
        CASE WHEN cte.RunningTotal + data.PersonAge > 10 THEN cte.[Group] + 1 ELSE cte.[Group] END, 
        -- Reset the running total for each new group
        data.PersonAge + CASE WHEN cte.RunningTotal + data.PersonAge > 10 THEN 0 ELSE cte.RunningTotal END
    FROM POP data INNER JOIN cte ON data.PersonId = cte.PersonID + 1
)
SELECT * FROM cte

So , output I need will be like :

PersonID    PersonAge   Group   RunningTotal
1           2           1       2
2           3           1       5
3           4           1       9
4           2           2       2
5           1           2       3
6           8           3       8
7           5           4       5
8           4           4       9
9           1           4       10
10          6           5       6
11          3           5       9
12          7           6       7
13          6           7       6

Is there a good non recursive solution?

EDIT : Trial #1 : Thinkling along the lines of running totals , I got a table with CurrRunningTotal and RunningTotal till previous row.

WITH TE
AS (SELECT
  PersonId,
  FirstName,
  PersonAge,
  SUM(PersonAge) OVER (ORDER BY PersonId
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  AS PrevRunningTotal,
  SUM(PersonAge) OVER (ORDER BY PersonId
  ROWS UNBOUNDED PRECEDING)
  AS RunningTotal
FROM POP),
MergedGroup
AS (SELECT
  *,
  SUM(CASE
    WHEN RunningTotal > @total THEN RunningTotal - @total
    ELSE PersonAge
  END) OVER (ORDER BY PersonId) AS Total
FROM TE)
SELECT
  *
FROM MergedGroup

I feel like using PreviousRunningTotal I can do some magic when my threshold is met to get a total with padding i.e When threshold is crossed , add the 10 to current row to offset total.Still close but no cigar.

score 1 · Answer 1 · edited Jan 10 '22 at 10:27

1

Yes, you can meet your conditions trivially, but non-optimally. Just group by personid.

Presumably, you intend something more like "starting at the beginning and using adjacent records" or "minimizing the number of groups". The latter is a bin-packing problem and no known algorithm exists with efficient performance.

The former -- unfortunately -- requires cycling through the data from the beginning, and that is what recursive CTEs do. I am not aware of any other generic approach for solving the problem. There may be specific approaches, depending on the exact constraints of your problem.

edited Jan 10 '22 at 10:27

Nimantha

6,405
6
28
69

answered May 24 '18 at 19:46

Gordon Linoff

1,242,037
58
646
786

thanks . the whole problem comes down to resetting total when threshold is crossed . that looks like it can only be done using recursion. not sure where I should group by PersonId? Because the groups I am looking for could be across PErsonIds eg. PersonIds 1,2,3 are within Group 1? – Ace McCloud May 24 '18 at 20:01
1

Is that a typo Gordon? What would grouping by `personid` help accomplish? – Tab Alleman May 24 '18 at 20:26
@TabAlleman . . . It will put one row in each group. The OP is asking for a "grouping" so that is one way of doing it. – Gordon Linoff May 25 '18 at 03:03

SQL Running total group with sum threshold reset

1 Answers1