Aggregate within a group of unchanged values

Question

I have sample data:

RowId TypeId Value
    1      1    34
    2      1    53
    3      1    34
    4      2    43
    5      2    65
    6     16    54
    7     16    34
    8      1    45
    9      6    43
   10      6    34
   11     16    64
   12     16    63

I want to count row for each type (The Value does not matter to me), but only for... neighbor TypeId

TypeId Count
     1     3
     2     2 
    16     2
     1     1
     6     2
    16     2

How to achieve this result?

You can use LAG function to create a field that will identify each group: http://stackoverflow.com/questions/16563581/increment-row-number-when-value-of-field-changes-in-oracle — Bulat, Sep 04 '14 at 10:09
Not that it matters much, but are rowids always contiguous? And see Giannis's comment. — Strawberry, Sep 04 '14 at 10:12
Are you sure that your data will always be in an order similar to what you have showed here? — Rachcha, Sep 04 '14 at 10:28
You can imagine it as files, that are loaded into the database. Typeid is their name, and value, the content of line. So I want to know how many lines are loading from individual files. Am I sure of their order? They are loaded sequentially, so RowId should provide me this certainty. — Bogusz Michałowski, Sep 04 '14 at 10:35

Bulat · Accepted Answer · 2014-09-04T11:28:20.453

4

This should give you COUNT of rows within a group of unchanged values:

SELECT TypeId, grp, COUNT(*) FROM (
  SELECT RowId, TypeId , Value, gap, SUM(gap) over (ORDER BY RowId ) grp
  FROM (SELECT RowId, TypeId , Value,
                CASE WHEN TypeId = lag(TypeId) over (ORDER BY RowId )
                   THEN 0
                   ELSE 1
                END gap 
        FROM dummy
    ) t
) tt
GROUP BY TypeId, grp;

If you prefer WITH over endless sub-query inclusions:

WITH dummy_with_groups AS (
  SELECT RowId, TypeId , Value, SUM(gap) OVER (ORDER BY RowId) grp
  FROM (SELECT RowId, TypeId , Value,
          CASE WHEN TypeId = lag(TypeId) OVER (ORDER BY RowId)
          THEN 0 ELSE 1 END gap 
        FROM dummy) t
)
SELECT TypeId, COUNT(*) as Result 
FROM dummy_with_groups
GROUP BY TypeId, grp;

http://www.sqlfiddle.com/#!6/f16e9/34

edited Sep 04 '14 at 11:28

answered Sep 04 '14 at 10:40

Bulat

6,869
1
29
52

I get `Incorrect syntax near ')'` - near last `)` http://www.sqlfiddle.com/#!6/d7b47/1 – Bogusz Michałowski Sep 04 '14 at 11:00
Well calculated, but unfortunately reorders. `6` should be below the first `16`. – Bogusz Michałowski Sep 04 '14 at 11:10
Updated the answer so results are not reshuffled – Bulat Sep 04 '14 at 11:13
1

After a short deliberation, I decided that your solution is better - it is much faster. Especially the second one. – Bogusz Michałowski Sep 05 '14 at 07:55

Rachcha · Answer 2 · 2014-09-04T11:03:29.327

Check this fiddle demo. I have renamed your columns a little.

WITH myCTE AS
         (SELECT row_id,
                 type_id,
                 ROW_NUMBER () OVER (PARTITION BY type_id ORDER BY row_id)
                     AS cnt,
                 CASE LEAD (type_id) OVER (ORDER BY row_id)
                     WHEN type_id THEN 0
                     ELSE 1
                 END
                     AS show
            FROM dummy),
     innerQuery AS
         (SELECT row_id, type_id, cnt
            FROM myCTE
           WHERE show = 1)
SELECT iq1.type_id, iq1.cnt - ISNULL (iq2.cnt, 0) CNT
  FROM innerQuery iq1
       LEFT OUTER JOIN innerQuery iq2
           ON     iq1.type_id = iq2.type_id
              AND EXISTS
                      (SELECT 1
                         FROM innerQuery iq3
                        WHERE     iq3.type_id = iq1.type_id
                              AND iq3.row_id < iq1.row_id
                       HAVING MAX (iq3.row_id) = iq2.row_id)

The output is exactly as expected.

Output is: `3, 2, 2, 4, 2, 4` And it should be: `3, 2, 2, 1, 2, 2` — Bogusz Michałowski, Sep 04 '14 at 10:56

Aggregate within a group of unchanged values

2 Answers2