Home > mailing lists

Thread: Change detection

Change detection

From

Shaozhong SHI

Date:

09 December 2022, 15:55:16

A record shows all staff member worked in different departments.

For instance, Tom was in sales for several years and got promoted to management. How to detect the time of this change?

Data

Staff_ID Name Department Year

1 Tom Sales 1990

2 Tom Sales 1991

3 Tom Sales 1991

4 Tom Management 1992

Regards,

David

AW: Change detection

From

Stöcker, Martin

Date:

09 December 2022, 16:02:10

select Name, Department, min(year), max(year) from data group by Name, Department

The meaning of Staff_ID is obscure because it’s not unique.

Mit freundlichen Grüßen

Martin Stöcker
-----------------------------------------
ETL Datenservice GmbH
Widdersdorfer Str. 415 | D-50933 Köln
Telefon: +49(0)2219544010
Fax: +49(0)2219544015
Email: Martin.Stoecker@etl-datenservice.de

ETL Datenservice GmbH
Widdersdorfer Str. 415 · 50933 Köln
Geschäftsführer: Dr. Dirk Goldner, ppa. Melanie Lillich, ppa. Udo Heuschmann
Amtsgericht Köln · HRB 75439 · USt.-Id: DE 122 805 685
www.etl-datenservice.de
Email: info@etl-datenservice.de

Von: Shaozhong SHI <shishaozhong@gmail.com>
Gesendet: Freitag, 9. Dezember 2022 13:55
An: pgsql-sql <pgsql-sql@lists.postgresql.org>
Betreff: Change detection

A record shows all staff member worked in different departments.

For instance, Tom was in sales for several years and got promoted to management. How to detect the time of this change?

Data

Staff_ID Name Department Year

1 Tom Sales 1990

2 Tom Sales 1991

3 Tom Sales 1991

4 Tom Management 1992

Regards,

David

Attachment

image001.png

Re: Change detection

From

Marcos Pegoraro

Date:

09 December 2022, 16:06:24

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)

Re: Change detection

From

Shaozhong SHI

Date:

09 December 2022, 16:14:15

Thanks, Marcos. It worked well. Regards, David

On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos@f10.com.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)

Re: Change detection

From

Shaozhong SHI

Date:

09 December 2022, 17:15:06

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@f10.com.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)

Re: Change detection

From

Marcos Pegoraro

Date:

09 December 2022, 20:00:08

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:

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@f10.com.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)

Re: Change detection

From

Shaozhong SHI

Date:

09 December 2022, 22:32:48

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:

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@f10.com.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)

Re: Change detection

From

Rob Sargent

Date:

09 December 2022, 22:38:07

On 12/9/22 12:32, Shaozhong SHI wrote:
> 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
Use the working query in a CTE and simply do the count(distinct 
department) on that, else there's no good place to put the number of 
departments per person.

Pretty sure you've been asked before but Please don't top post.