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 1p3j8N-0000Xz-9H for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 19:34:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3j7M-0002nt-Vd for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 19:33:08 +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 1p3j7M-0002nk-DJ for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 19:33:08 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p3j7F-0005T1-Jr for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 19:33:07 +0000 Received: by mail-ed1-x533.google.com with SMTP id f7so4463773edc.6 for ; Fri, 09 Dec 2022 11:33:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.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=Ar2oUjIWJS3D9Fs9jIBJY1frCaBZpzIA8SmtSkyCxZI=; b=nXEKXOHQZZxXeoAwtkwRT4O+1LXkGoJXLLH+LU1nTZWAfrLPDIKYkOecFjxUHeSUaX cqGn8wOp7pccMfmciux5w+wn0uVgx6+XSGC8FzwCppuT2joSMMrx90ZtGYIRuqp2LpeW ZHuUcbeRtZHtLj2wkqyYHYFmklQkSmFleH5Wn6tpAZ1kS6aG8PoOCoogatiPZp+L3aQJ FrD+MdMC1NCyiWrEgGCMQryGBMEYDTt7Cs8/jrqTkKMt4DgRQDeL7bBMhoAqhQvhPsCz TtwKqGoP1Q31W8DYvQUQKYw891MP7yTHFMPcswGmiDE6aQyU395UGGYtGDqfHhUFKSxO tlAg== 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=Ar2oUjIWJS3D9Fs9jIBJY1frCaBZpzIA8SmtSkyCxZI=; b=Z9TI8heHt70GGxjURWfkmPHIvULWK6dNNC8B9oe5MLg8nF4WuHTQmbebbYNtbBp8zz oldzXrlrEoVKi+9jQIjB43O+AMvnHSiWLp+3OcVjvbt3Im+lEzmVV2F2mxcjOXamkxgo lN/hMmpHDxE9SxlfxubM7xJ77NYsUc1qAeQMRwf8JFA8hmJRu3s6i1gCOrPU1VkMpBUe 6s7qlwrsy45ngUHod/FwvghC2oeUD+dNPV9MwVgSkcERF8vDDTrtyeXoxXXUcpx47gyZ eJfNEhc4C0SHT5GEP0El63ZvD7rbPqWyDNpVPRAbUefjSYEm15SjYMvnG5iQdWPj+yJL hV/w== X-Gm-Message-State: ANoB5pmucKYg2tXHQCe2ItPHiR4wazyuh/fweSpxE6SxoxviYeVg9bwl FkYup0KBDcKVIGQz+v87EgiLvQ6BRVe1vDd+FI0= X-Google-Smtp-Source: AA0mqf5XCuVOSAMC81RMIUE+31kXk8l/1U1MLOvWmg7LiISF5jNBNPl+N8rpxDFFqvfCnAdY7fGYIMZqG8Kbk7n9WqQ= X-Received: by 2002:a05:6402:d6:b0:458:b42e:46e6 with SMTP id i22-20020a05640200d600b00458b42e46e6mr85595237edu.375.1670614379534; Fri, 09 Dec 2022 11:32:59 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shaozhong SHI Date: Fri, 9 Dec 2022 19:32:48 +0000 Message-ID: Subject: Re: Change detection To: Marcos Pegoraro Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000a61c9205ef6a381d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a61c9205ef6a381d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 =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) >>> >>> >> --000000000000a61c9205ef6a381d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
That works well.

I just wonder whether = we can tell Tom or Tim has worked in more than 1 department.=C2=A0 Apparent= ly, PostgreSQL does not allow count(distinct department) when window functi= on is used.

Given this data set, can we do somethi= ng like count(distinct) to provide an answer to how many different departme= nt someone has worked in?

Regards,

<= /div>
David

On Fri, 9 Dec 2022 at 17:00, Marcos Pegoraro <marcos@f10.com.br> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
just change=C2=A0lag(department) over(order by = year) to=C2=A0lag(department) over(partition by n= ame order by year)
=

Atenciosamente,=C2=A0




Em sex., 9 de dez. de 2022 =C3=A0s 11:15, Shaozhon= g SHI <shish= aozhong@gmail.com> escreveu:
How about finding all change= s for all people 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=A0= 1991
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
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=A0financ= e=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01983<= br>
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 ma= nagement=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.b= r> 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 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), department) <> d= epartment Changed from (Values (1, 'Tom', 'Sales', 1990),(2= , 'Tom', 'Sales', 1991),(3, 'Tom', 'Sales',= 1991),(4, 'Tom', 'Management', 1992),(4, 'Tom', &#= 39;Management', 1992)) as x(Staff_ID, Name, Department, Year);=C2=A0staff_id | n= ame | 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 | 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| Management | 1992 | f
(5 rows)
=C2=A0
--000000000000a61c9205ef6a381d--