Re: Change detection

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(at)f10(dot)com(dot)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(at)gmail(dot)com>
> escreveu:
>
>> How about finding all changes for all people in a large record set?
>>
>> See the follwoing:
>>
>> David
>>
>> 1 Tom Sales 1990
>> 2 Tom Sales 1991
>> 3 Tom Sales 1991
>> 4 Tom Management 1992
>> 5 Tom Management 1992
>> 6 Tim finance 1982
>> 7 Tim finance 1983
>> 8 Tim management 1984
>> 9 Tim management 1985
>>
>> On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
>>
>>> Data
>>>>
>>>> Staff_ID Name Department Year
>>>> 1 Tom Sales 1990
>>>> 2 Tom Sales 1991
>>>> 3 Tom Sales 1991
>>>> 4 Tom Management 1992
>>>> 4 Tom Management 1992
>>>>
>>>> select *, 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)
>>>
>>>
>>