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
