CODEWITHSUNDEEP
androidsqlsqlite
Frank Zappa
Frank Zappa

Reputation: 451

SQLite Query - Group By Contiguous Sequence

I am looking for sql to run on SQLite.

I have this table (3 integer columns) and data (here is a subset of the data)

ID  aid adur
1   1   5
2   3   7
3   3   8
4   3   5
5   2   9
6   1   9
7   1   2

I want the following adur result in which the table is a) sorted from largest ID to smallest and b) when 2 or more aids are the same continguously, the adurs are summed. 

ID  aid adur    adur 
7   1   2       11
6   1   9
5   2   9       9
4   3   5       20
3   3   8   
2   3   7   
1   1   5       5

I tried the following. It does not provide the desired result because it groups/sums all adurs in the table at once vs grouping/summing just those adurs are the same back to back (contiguously).

SELECT Min(act_test.ID) AS MinOfID, act_test.aid, Sum(act_test.adur) AS SumOfadur
FROM act_test
GROUP BY act_test.aid
ORDER BY Min(act_test.ID) DESC

Upvotes: 1

Views: 367

Answers (1)

Matt
Matt

Reputation: 14341

SELECT t.*, g.SumAdur
FROM
    Table t
    LEFT JOIN (
       SELECT
          r.aid
          ,MIN(r.Id) as MinId
          ,MAX(r.Id) as MaxId
          ,SUM(r.adur) as SumAdur
       FROM
          (
             SELECT *
                ,(SELECT COUNT(*) FROM Table tc
                    WHERE t.Id >= tc.Id AND t.aid = tc.aid) as RowNum
             FROM
                Table t
          )r
       GROUP BY
          r.aid
          ,r.Id - r.RowNum
    ) g
    ON t.Id = g.MaxId
ORDER BY
    t.Id DESC

Create A Partitioned Row Number Subtract it from the ID to create the grouping and do the math then match back. This method requires the ID to be an incremental number with no gaps. If ID could have gaps then you just need to create a Row Number across the entire table as well and then use that to create the grouping.

If ID has gaps use this one:

SELECT t.*, g.SumAdur
FROM
    Table t
    LEFT JOIN (
       SELECT
          r.aid
          ,MIN(r.Id) as MinId
          ,MAX(r.Id) as MaxId
          ,SUM(r.adur) as SumAdur
       FROM
          (
             SELECT *
                ,(SELECT COUNT(*) FROM Table tc
                    WHERE t.Id >= tc.Id AND t.aid = tc.aid) as AidRowNum
                ,(SELECT COUNT(*) FROM Table tc
                    WHERE t.Id >= tc.Id) as IdRowNum                
             FROM
                Table t
          )r
       GROUP BY
          r.aid
          ,r.IdRowNum - r.AidRowNum
    ) g
    ON t.Id = g.MaxId
ORDER BY
    t.Id DESC

And if you want the value to be shown on every row just change:

ON t.Id = g.MaxId

TO

ON t.Id BETWEEN g.MinId AND g.MaxId

Upvotes: 2

Related Questions