Re: grouping consecutive records

Hallo =D0=92=D0=B8=D0=BA=D1=82=D0=BE=D1=80,

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

> I will explain this for the ordering that matches your sample.
>=20
> You need to group your data, but you should first create an artificial
> grouping column.
>=20
> 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) =3D key
>                 THEN NULL ELSE 1 END r
>       FROM foo
> )
> SELECT * FROM ranges;
>=20
> Here each time a new =E2=80=9Crange=E2=80=9D is found, =C2=ABr=C2=BB is 1=
, otherwise it is NULL.
>=20
> 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) =3D 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;
>=20
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