CODEWITHSUNDEEP
sqlpostgresql
davetapley
davetapley

Reputation: 17898

Count rows in each 'partition' of a table

Disclaimer: I don't mean partition in the window function sense, nor table partitioning; I mean it in the more general sense, i.e. to divide up.

Here's a table:

id  |  y
----+------------
1   |  1
2   |  1
3   |  1
4   |  2
5   |  2
6   |  null
7   |  2
8   |  2
9   |  null
10  |  null

I'd like to partition by checking equality on y, such that I end up with counts of the number of times each value of y appears contiguously, when sorted on id (i.e. in the order shown).

Here's the output I'm looking for:

y    | count
-----+----------
1    | 3
2    | 2
null | 1
2    | 2
null | 2

So reading down the rows in that output we have:

Upvotes: 2

Views: 1684

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try:

SELECT y, count(*)
FROM (
  SELECT y,
         sum( xyz ) OVER ( 
           ORDER BY id 
           rows between unbounded preceding
            and current row
         ) qwe
  FROM (
     SELECT *,
            case 
            when y is null and
                 lag(y) OVER ( ORDER BY id ) is null
                 then 0
            when y = lag(y) OVER ( ORDER BY id )
            then 0 
            else 1 end xyz
     FROM table1
  ) alias
) alias
GROUP BY qwe, y
ORDER BY qwe;

demo: http://sqlfiddle.com/#!15/b1794/12

Upvotes: 4

Related Questions