public inbox for [email protected]help / color / mirror / Atom feed
Tom changed his job role many times 3+ messages / 2 participants [nested] [flat]
* Tom changed his job role many times @ 2022-12-12 22:43 Shaozhong SHI <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Shaozhong SHI @ 2022-12-12 22:43 UTC (permalink / raw) To: pgsql-sql <[email protected]> Tom has changed his job role many times. We want to pick up only the records before and after his role change to show what happened. select *, coalesce(lag(department) over(partition by name order by year), department) <> department Changed from (Values (1, 'Tom', 'Sales', 1990),(2, 'Tom', 'Sales', 1991),(3, 'Tom', 'Sales', 1991),(4, 'Tom', 'Management', 1992),(4, 'Tom', 'Management', 1992), (4, 'Tom', 'Sales', 1993),(4, 'Tim', 'Finance', 1981), (4, 'Tim', 'Finance', 1982), (4, 'Tim', 'Management', 1983), (4, 'Tim', 'Management', 1984)) as x(Staff_ID, Name, Department, Year); Regards, David ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tom changed his job role many times @ 2022-12-13 01:01 David Rowley <[email protected]> parent: Shaozhong SHI <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David Rowley @ 2022-12-13 01:01 UTC (permalink / raw) To: Shaozhong SHI <[email protected]>; +Cc: pgsql-sql <[email protected]> On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI <[email protected]> wrote: > We want to pick up only the records before and after his role change to show what happened. Window functions cannot appear in the WHERE clause, but you can add a subquery and perform the filtering there. SELECT * FROM ( SELECT name,year,department, lag(department) over (partition by name order by year) last_department from mytable) t WHERE department <> last_department or last_department is null; what you put in the WHERE clause will depend on what you want to happen when the employee is first hired. last_department will be NULL because I didn't add any default to lag like you did. You may want to check that the COALESCE() is what you want. You may want to use LAG(department, 1, department) instead. These are the same if department is defined as NOT NULL, but not if there is null values in there. There are a few details in [1] about window functions and how to filter on them that you might get something out of reading. David [1] https://www.postgresql.org/docs/current/tutorial-window.html ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tom changed his job role many times @ 2022-12-13 20:33 Shaozhong SHI <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Shaozhong SHI @ 2022-12-13 20:33 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: pgsql-sql <[email protected]> Thanks, David. So many different ways to produce answers. Brilliant. Regards, David On Tue, 13 Dec 2022 at 01:02, David Rowley <[email protected]> wrote: > On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI <[email protected]> > wrote: > > We want to pick up only the records before and after his role change to > show what happened. > > Window functions cannot appear in the WHERE clause, but you can add a > subquery and perform the filtering there. > > SELECT * FROM ( > SELECT name,year,department, > lag(department) over (partition by name order by year) > last_department > from mytable) t > WHERE department <> last_department or last_department is null; > > what you put in the WHERE clause will depend on what you want to > happen when the employee is first hired. last_department will be NULL > because I didn't add any default to lag like you did. You may want to > check that the COALESCE() is what you want. You may want to use > LAG(department, 1, department) instead. These are the same if > department is defined as NOT NULL, but not if there is null values in > there. > > There are a few details in [1] about window functions and how to > filter on them that you might get something out of reading. > > David > > [1] https://www.postgresql.org/docs/current/tutorial-window.html > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-12-13 20:33 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-12-12 22:43 Tom changed his job role many times Shaozhong SHI <[email protected]> 2022-12-13 01:01 ` David Rowley <[email protected]> 2022-12-13 20:33 ` Shaozhong SHI <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox