Postgresql OVER and GROUP
Solution 1
Use a difference of row numbers approach to classify consecutive rows with the same user into one group and start over a new group when a new user is encountered. Thereafter, use group by
to get the start and end of each group.
SELECT USER,MIN(ID) AS START,MAX(ID) AS END
FROM (SELECT user,id, row_number() over(order by id)
- row_number() over (partition by user order by id) as grp
FROM tablename
) T
GROUP BY USER,GRP
Solution 2
to get start, end of id, use:
SELECT user, min(id) over (partition by user) "start", max(id) over (partition by user) "end"
from table_name;
Update My answer was based on wrong predicate and so wrong. To provide right one and not duplicate @vkp one, I made this monstrous construct:
create table so74 as
select * from (values (0, 1), (1, 1), (2, 1), (3, 2), (4, 2), (5, 2), (6, 1), (7, 1)) t(id, u);
with d as (
with c as (
with b as (
select
*
, case when lag(u) over (order by id) <> u or id = min(id) over() then id end min
, case when lead(u) over (order by id) <> u or id=max(id) over () then id end max
from so74
)
select u, min,max
from b
where coalesce(min,max) is not null
)
select u,min,lead(max) over () max
from c
)
select *
from d
where coalesce(min,max) is not null
;
u | min | max
---+-----+-----
1 | 0 | 2
2 | 3 | 5
1 | 6 | 7
(3 rows)
Time: 0.456 ms
darthzejdr
Updated on April 26, 2020Comments
-
darthzejdr about 4 years
I have a problem with grouping
I have a table(a lot more stuff in it, but not relevant) that looks something like:
id user 0 1 1 1 2 1 3 2 4 2 5 2 6 1 7 1
I'm trying to get the following value:
user start end 1 0 2 2 3 5 1 6 7
Basicaly, i need the first and last occurance of user, while not messing with order. I know i need to use OVER (PARTITION BY ...), But i've never used it and not sure how to build this query. if i "partition by user", it ignores order. And if i "partition by id, user" it again returns wrong.
Example of what i tried(doesn't even try to get what i need, but is a midpoint that shows me how to do it once i figure out "over" part):
SELECT user, count(user) over (partition by user): user count 1 5 1 5 1 5 2 3 2 3 2 3 1 5 1 5 SELECT user, count(user) over (partition by id, user): user count 1 1 1 1 1 1 2 1 2 1 2 1 1 1 1 1