Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3gju-0007P9-0e for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 17:00:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3gjs-00008i-Up for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 17:00:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3gjs-00008Z-IF for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 17:00:44 +0000 Received: from mail-il1-x132.google.com ([2607:f8b0:4864:20::132]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p3gjp-0005lW-VL for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 17:00:44 +0000 Received: by mail-il1-x132.google.com with SMTP id z18so190691ils.3 for ; Fri, 09 Dec 2022 09:00:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=f10-com-br.20210112.gappssmtp.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=21+DnfGixQt9LonyLZTkiTXq/n46aCV2xCcfQRKAvTA=; b=Sehevjp3lH5EpDtofbAt+n4F9YQT+IYhe25EKdYDUCmlnd6GnSineaCvuX+hETESgV 1bCxAcqgYTWfFk2hTY9RsUf3A8de5pK7U+yrk6vURu6Hlhaig507O3XAA8jefYq+6f7p cjGq47lFTGHW2L/qkYXX+onDaPNFy/3kql7BcHVO3ebuedBtv9/kqnu2i5IDusYpgCZ8 amHcpCdVdpqlUvlJO8AsLtEP0lNyGKGlb4XnJvenrpPBPOEwXB1+Vpk6cpsN7bY/4eBz 5VDdQAKB6+wENjB1hxItD9QTj+qi0lg5eejVf0/tiOz6dijIYONc2QUrzpE4qLBOgiZR dxxA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=21+DnfGixQt9LonyLZTkiTXq/n46aCV2xCcfQRKAvTA=; b=RJdRJNTeXBIa10ff6Vu+OhQhaC2gYU7Z0xXdITGoiMAKJ7FZw22Vaa/6MucLwpwM7C 6hRJk1KSOx7mBxnjiS9ynhHoAkqmgGSRbkL5/S359OxSB1mKoP6guw15CThSVCmSCBAn YdiuoPjI147AfPJ1t+U5qfkF4D5reUcbWGJlt6cibSZ+qXIyKopof33+uXK1f17xImlo z4J8tp7YVPbtrfY6Uj3/LL8HwWQWudBqGR97vnOcxiMXXBnEiSoKWqvVR/lm0B7RSmjw jMnoh/QBqzz+k5ZUmma+Kqo8Wluk8snJUjB5DBkNmNLtoI1DLO7RU4nGUaP7NbQY+5sg uySQ== X-Gm-Message-State: ANoB5pkQxAGNhDp7LnfVegvTkzxHlz6KHM0cofGps3FCbe2c84TjkWj3 X73AK5N9aXa8Pp8U/xTQ/zP6f+oc5w/6e6i5aKceOA== X-Google-Smtp-Source: AA0mqf7WcCMQWHu+0h+POm8ELcxQIUmj6PEjQIV5y1O+JulwCql8uUbQco9RvsESGNaKOmrvjGDNSp2VqagIz3r0biE= X-Received: by 2002:a92:ce43:0:b0:303:91d0:19f9 with SMTP id a3-20020a92ce43000000b0030391d019f9mr819048ilr.229.1670605239631; Fri, 09 Dec 2022 09:00:39 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Marcos Pegoraro Date: Fri, 9 Dec 2022 14:00:08 -0300 Message-ID: Subject: Re: Change detection To: Shaozhong SHI Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000de5a7705ef681755" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000de5a7705ef681755 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =C3=A0s 11:15, Shaozhong SHI 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 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) >> >> > --000000000000de5a7705ef681755 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
just change=C2=A0lag(departmen= t) over(order by year) to=C2=A0lag(department) ov= er(partition by name order by year)

Atenciosamente,=C2=A0


=


Em sex., 9 de dez. de 2022 =C3=A0s 11:15, Sha= ozhong SHI <shishaozhong@gmail= .com> escreveu:
=
How about finding all changes for all peo= ple in a large record set?

See the follwoing:

David

1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tom=C2=A0 =C2=A0 = Sales=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A01990
2=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Sales=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01991
3=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01991
4=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Management=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01= 992
5=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0Management=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 1992
6= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tim=C2=A0 =C2=A0 = =C2=A0finance=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A01982
7=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tim=C2=A0 =C2=A0 =C2=A0finance=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01983
8=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Tim=C2=A0 =C2=A0 =C2=A0management=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01984
9=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tim=C2=A0 =C2=A0 management= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1985

On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos@f10.com.br>= ; wrote:
Data

Staff_ID=C2=A0 =C2= =A0 Name=C2=A0 =C2=A0Department=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Ye= ar
1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tom= =C2=A0 =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A01990
2=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01991
3=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0= Sales=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= 1991
4=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Management=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= 1992
4=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Tom=C2=A0 =C2=A0 =C2=A0Management=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 1992

select *, coalesce(lag(department) over(order by year), department) <> department= Changed from (Values (1, 'Tom', 'Sales', 1990),(2, 'To= m', 'Sales', 1991),(3, 'Tom', 'Sales', 1991),(4= , 'Tom', 'Management', 1992),(4, 'Tom', 'Manage= ment', 1992)) as x(Staff_ID, Name, Department, Year);
=C2=A0staff_id | name | dep= artment | year | changed
----------+------+------------+------+--------= -
=C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | Tom =C2=A0| Sales =C2=A0 =C2=A0 =C2=A0= | 1990 | f
=C2=A0 =C2=A0 =C2=A0 =C2=A0 2 | Tom =C2=A0| Sales =C2=A0 =C2= =A0 =C2=A0| 1991 | f
=C2=A0 =C2=A0 =C2=A0 =C2=A0 3 | Tom =C2=A0| Sales = =C2=A0 =C2=A0 =C2=A0| 1991 | f
=C2=A0 =C2=A0 =C2=A0 =C2=A0 4 | Tom =C2= =A0| Management | 1992 | t
=C2=A0 =C2=A0 =C2=A0 =C2=A0 4 | Tom =C2=A0| M= anagement | 1992 | f
(5 rows)
= =C2=A0
--000000000000de5a7705ef681755--