Re: grouping consecutive records

Hallo Виктор,

thanks a lot for your explanation :-)
You rock!
>
> This example corresponds to the ORDER BY user_id, sort
> while you claim you need to ORDER BY sort, user_id.
>
right, I confused the order.

> I will explain this for the ordering that matches your sample.
>
> You need to group your data, but you should first create an artificial
> grouping column.
>
> First, detect ranges of your buckets:
> WITH ranges AS (
> SELECT id, user_id, key, sort,
> CASE WHEN lag(key) OVER
> (PARTITION BY user_id ORDER BY user_id, sort) = key
> THEN NULL ELSE 1 END r
> FROM foo
> )
> SELECT * FROM ranges;
>
> Here each time a new “range” is found, «r» is 1, otherwise it is NULL.
>
> Now, form your grouping column:
> WITH ranges AS (
> SELECT id, user_id, key, sort,
> CASE WHEN lag(key) OVER
> (PARTITION BY user_id ORDER BY user_id, sort) = key
> THEN NULL ELSE 1 END r
> FROM foo
> )
> , groups AS (
> SELECT id, user_id, key, sort, r,
> sum(r) OVER (ORDER BY user_id, sort) grp
> FROM ranges
> )
> SELECT * FROM groups;
>
so the trick is to flag changes in key and afterwards count them using
the dynamic nature of a frame ending with the current row.
great :-)
Once you have a group column, it's pretty clear then.

thanks
Morus