Re: grouping consecutive records
От | Morus Walter |
---|---|
Тема | Re: grouping consecutive records |
Дата | |
Msg-id | 20130204114641.4af1f320@tucholsky.experteer.muc обсуждение исходный текст |
Ответ на | Re: grouping consecutive records (Виктор Егоров <vyegorov@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: