Re: Change detection - Mailing list pgsql-sql
That works well.
I just wonder whether we can tell Tom or Tim has worked in more than 1 department. Apparently, PostgreSQL does not allow count(distinct department) when window function is used.
Given this data set, can we do something like count(distinct) to provide an answer to how many different department someone has worked in?
Regards,
David
On Fri, 9 Dec 2022 at 17:00, Marcos Pegoraro <marcos@f10.com.br> wrote:
just change lag(department) over(order by year) to lag(department) over(partition by name order by year)Atenciosamente,Em sex., 9 de dez. de 2022 às 11:15, Shaozhong SHI <shishaozhong@gmail.com> escreveu:How about finding all changes for all people in a large record set?See the follwoing:David1 Tom Sales 19902 Tom Sales 19913 Tom Sales 19914 Tom Management 19925 Tom Management 19926 Tim finance 19827 Tim finance 19838 Tim management 19849 Tim management 1985On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos@f10.com.br> wrote:DataStaff_ID Name Department Year1 Tom Sales 19902 Tom Sales 19913 Tom Sales 19914 Tom Management 19924 Tom Management 1992select *, coalesce(lag(department) over(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)) as x(Staff_ID, Name, Department, Year);staff_id | name | department | year | changed
----------+------+------------+------+---------
1 | Tom | Sales | 1990 | f
2 | Tom | Sales | 1991 | f
3 | Tom | Sales | 1991 | f
4 | Tom | Management | 1992 | t
4 | Tom | Management | 1992 | f
(5 rows)
