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 1p3d6l-0002r2-6m for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 13:08:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3d5j-0007eh-Np for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 13:07:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3d5j-0007eY-C5 for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 13:07:03 +0000 Received: from mail-il1-x12d.google.com ([2607:f8b0:4864:20::12d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p3d5c-0002Dz-Uj for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 13:07:02 +0000 Received: by mail-il1-x12d.google.com with SMTP id m15so2599448ilq.2 for ; Fri, 09 Dec 2022 05:06:56 -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=1aKThaoMWi3r72E0UPag/VpTzTYHqozN5bIKsnuhMI8=; b=jlrE8qbhNk0WPpyuYM+tqEk2zEDyJy3OrnpVJkIKcBXM0EdCe9cMPT1DBblo6va9fC D+YXf1hePd5OrzkmhVzyvh2MSAAK7rLOYnPR7N06hxYxly70BOFfsFh7qQPrd4fMvOWc pi/WZoio/Th4GZdhNLEJcyk0WJCOxkWWbtVWBqJTuTHvRRuCgNA2Cx9NpdzGSBQsP5hj 9ivcLwp0hlQNjtVp+z7Yihc5KWuDkeKRoKXe8i9uD/i4LdffTTaEG9d4i/bz2t+aUkX3 2t9hNraOGSxK0ObU88k3H3nkzPu7fAjuKGU1PHJmz86mgyiV7FP2dAokixHj2EVvDFGW 1uXw== 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=1aKThaoMWi3r72E0UPag/VpTzTYHqozN5bIKsnuhMI8=; b=2i3EVnjFakDooDQpog49sUFKNgI4hLELSgzkTs3F0z5ImkAYMHc6l2wND6F+b7jOVi Ay2XLCWuXBlmr8Xctcko64mPrnxsNMKqQepPkveO3aCGAIpCQK4P0yNj+3QMWtYhIT48 OkmemF1MmW09T4JcvnpIEOO42OhPs9TM/YJonhK4CbXPFUUh4GgJj3VvzP+kR2h96RAZ 0DQZ/3QxruBnEwAkXMWfIqPvE8ZwYCXmorBgwiMHcNuaDl5FDwdOv0bd1RBZkRhr6M3g OjqFTWO0/aJLrTLhqpv3GtQRJPXqr8QDp49wKz05nlOSYoAp02wZB/zxtJMh1Uj4KfQf kNCg== X-Gm-Message-State: ANoB5plJEWd175BMd4xxtDv3WzC0Z90hySfeV+DZpTBgPpJQ+8QMmgx3 lP19w8vHgTi9/0GHNpC/ZkLkJ9m9TSLVvs7dU6Lymw== X-Google-Smtp-Source: AA0mqf7Kabno99i7f2Ekr5GqM9yOEbBjFcvK/hUfFbNRINLhhgtigz+Yb4qO6iwSBm7EFoUQqaKM88wyRxdpdgbPLTE= X-Received: by 2002:a05:6e02:e13:b0:303:7bea:9616 with SMTP id a19-20020a056e020e1300b003037bea9616mr4543012ilk.148.1670591215822; Fri, 09 Dec 2022 05:06:55 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Marcos Pegoraro Date: Fri, 9 Dec 2022 10:06:24 -0300 Message-ID: Subject: Re: Change detection To: Shaozhong SHI Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000fc033205ef64d3ca" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fc033205ef64d3ca Content-Type: text/plain; charset="UTF-8" > > 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) --000000000000fc033205ef64d3ca Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Data

Staff_ID=C2= =A0 =C2=A0 Name=C2=A0 =C2=A0Department=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Year
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=A01992
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), depa= rtment) <> department Changed from (Values (1, 'Tom', 'Sa= les', 1990),(2, 'Tom', 'Sales', 1991),(3, 'Tom'= , 'Sales', 1991),(4, 'Tom', 'Management', 1992),(4,= 'Tom', 'Management', 1992)) as x(Staff_ID, Name, Departmen= t, Year);
= =C2=A0staff_id | name | department | 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 | To= m =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| Management | 1992 | f
(5 rows)
=C2=A0
--000000000000fc033205ef64d3ca--