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 1ofRah-0006v2-TG for pgsql-novice@arkaria.postgresql.org; Mon, 03 Oct 2022 19:59:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ofRag-0001aC-MW for pgsql-novice@arkaria.postgresql.org; Mon, 03 Oct 2022 19:59:02 +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 1ofRag-0001a2-8W for pgsql-novice@lists.postgresql.org; Mon, 03 Oct 2022 19:59:02 +0000 Received: from mail-lj1-x233.google.com ([2a00:1450:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ofRaZ-0005wT-7t for pgsql-novice@lists.postgresql.org; Mon, 03 Oct 2022 19:59:01 +0000 Received: by mail-lj1-x233.google.com with SMTP id t16so13028639ljh.3 for ; Mon, 03 Oct 2022 12:58:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=keksi-io.20210112.gappssmtp.com; s=20210112; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date; bh=MmosgVEzV8cl6r2gK56w5JudixixKJs3y987P2ZyaQo=; b=DQUQqYrAmZEXQRU+5x8YssUBQJ31T6j5IgvByd16UXGTOu+BbKqElOgtSA2aEAjjUR 4CuaQ5YRb80tc9TgTQ5zt5SRfA90AVe0EAgc/oWg5mOnpxpXKGlT17ncOR99y/S3XO3Y i83ZvnTM8zFlcVen9lfR9r8yQSbViLihrmRh0aaECcrL9Vj0Yn1x5B26R1OjrsfURWLW 9ki1+dr2EuFyaUd79HYaYWwN0X5dC7qaBrGIXaxGSuG/dBWXacuwHhT9DqU3SoXY6IVp NS8STHixtnGwVV1XLRBdYdRDFvXXItfNac7f4E+dDdO4Yf1hN1UW6fZ8vwnuVRX1h2xM QEtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date; bh=MmosgVEzV8cl6r2gK56w5JudixixKJs3y987P2ZyaQo=; b=mOff9smD56q7vAgaFuX+YCxf2vsyfBTEYqQBZ+z3MpiYM7zEVuijrcuhlbFlBkwrdt mQK6axw2V76sO1+iIgg0JBIYnmUA0z/+5D1GReUZtLcgNGJVzf4QnLj7K0Q6qniyVy7Y KHwpSOhY0aN1Tq29alubOeL0C8URtUap/k1pubWAxlVkudjGQtwfNUbKRalh5VrJXLaT WolGdrRaO+vQrPyqktdrrCDVG2M4qYWoJR0+3eO3tAh2jdGy3I4j2tearQ0wUyFupAKL wSYhinCIb0D5yxCp+McaH5qIl6SWskBOj8K8PAgXnjuUWl2ukexWyLcrBxk8cQmVCbBM L9PQ== X-Gm-Message-State: ACrzQf1onzBeiA7vg9lu0ik1mJ890powrmMvQfPOwfWLNw/N9BklGATg DSYgB3T7hl/bRw7mKTnkphIUn9GHjJIdp7up X-Google-Smtp-Source: AMsMyM6ExkYDTQsOJuqlLDXQVS8+QRHCx5W+XSpFEwtfLBMB0tYZ7QXp2Fir+y98UlA56WXB0NlS0w== X-Received: by 2002:a2e:beaa:0:b0:25e:34d0:4d57 with SMTP id a42-20020a2ebeaa000000b0025e34d04d57mr7147396ljr.329.1664827133103; Mon, 03 Oct 2022 12:58:53 -0700 (PDT) Received: from smtpclient.apple (150-12-191-90.dyn.estpak.ee. [90.191.12.150]) by smtp.gmail.com with ESMTPSA id g3-20020a2e9e43000000b0026dcdff4c15sm726074ljk.19.2022.10.03.12.58.52 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 03 Oct 2022 12:58:52 -0700 (PDT) From: Onni Hakala Message-Id: <02B67082-508E-4065-8AEE-0C3758021AF6@keksi.io> Content-Type: multipart/alternative; boundary="Apple-Mail=_808976AD-B8D6-477A-8CA8-30EC8589F459" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.1\)) Subject: Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Date: Mon, 3 Oct 2022 22:58:52 +0300 In-Reply-To: Cc: pgsql-novice@lists.postgresql.org To: Laurenz Albe References: <6E722EEC-61FE-4EC0-B17D-B4468DA34BA1@keksi.io> X-Mailer: Apple Mail (2.3696.120.41.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_808976AD-B8D6-477A-8CA8-30EC8589F459 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > PostgreSQL supports DISTICT ON, which can do this even more = ergonomically. Yes using DISTINCT ON one can solve some usecases but not nearly all. I should have selected better example here in the first place. For example I created db-fiddle of this email conversation here: = https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1 = Can you show me how you could use that to get the latest 2 events from = the events table using DISTINCT ON? CREATE TABLE events ( person TEXT, event_type TEXT , created_at TIMESTAMP ); INSERT INTO events VALUES=20 ('onni.hakala','message',now()), ('laurenz.albe','non-helpful reply',now() + interval '30 minutes'), ('onni.hakala','clarifying reply',now() + interval '35 minutes'), ('someone.else','other reply',now() + interval '40 minutes'), ('onni.hakala','other reply',now() + interval '45 minutes'), ('someone.else','other reply',now() + interval '50 minutes'); I can write this: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY person ORDER BY = created_at DESC) AS row_number FROM events ) sub WHERE row_number <=3D 2 But what I would want to write is this: SELECT * FROM events QUALIFY OVER (PARTITION BY person ORDER BY created_at DESC) AS <=3D 2 > On 3. Oct 2022, at 21:11, Laurenz Albe = wrote: >=20 > On Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote: >> I was very happy learn about QUALIFY clause in BigQuery today. >>=20 >> I have used window functions usually with subqueries like this: >> SELECT * FROM ( >> SELECT >> *, >> ROW_NUMBER OVER (PARTITION BY something ORDER BY modified_date = DESC) AS row_number >> FROM table_name >> ) >> WHERE row_number =3D 1 >>=20 >>=20 >> With QUALIFY it's much more ergonomic and cleaner to do the same = thing. >> SELECT * >> FROM table_name >> QUALIFY ROW_NUMBER() OVER (PARTITION BY something ORDER BY = modified_date DESC) =3D 1 >>=20 >>=20 >> QUALIFY is also better since it doesn't include extra column = row_number to the result. >>=20 >> Where should I send message so that Postgres maintainers would = consider adding this into the TODO page: = https://wiki.postgresql.org/wiki/Todo >=20 > PostgreSQL supports DISTICT ON, which can do this even more = ergonomically. >=20 > Yours, > Laurenz Albe > --=20 > Cybertec | https://www.cybertec-postgresql.com --Apple-Mail=_808976AD-B8D6-477A-8CA8-30EC8589F459 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
PostgreSQL supports = DISTICT ON, which can do this even more ergonomically.
Yes = using DISTINCT ON one can solve some usecases but not nearly all.

I should have selected = better example here in the first place.

For example I created db-fiddle of this = email conversation here: https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1
<= div class=3D"">
Can you show me how = you could use that to get the latest 2 events from the events table = using DISTINCT ON?

CREATE TABLE events (
    person TEXT,
    = event_type TEXT ,
    created_at = TIMESTAMP
);

INSERT INTO events = VALUES 
  = ('onni.hakala','message',now()),
  = ('laurenz.albe','non-helpful reply',now() + interval '30 = minutes'),
  ('onni.hakala','clarifying = reply',now() + interval '35 minutes'),
  = ('someone.else','other reply',now() + interval '40 minutes'),
  ('onni.hakala','other reply',now() + interval '45 = minutes'),
  ('someone.else','other = reply',now() + interval '50 minutes');

I can write this:

SELECT * = FROM (
SELECT
= *,
  ROW_NUMBER() OVER = (PARTITION BY person ORDER BY created_at DESC) AS row_number
= FROM events
) sub
WHERE = row_number <=3D 2

But what I would want to write is this:

SELECT *
FROM = events
QUALIFY OVER (PARTITION BY person ORDER BY created_at = DESC) AS <=3D 2

On 3. Oct 2022, at 21:11, = Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On = Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote:
I was very happy learn = about QUALIFY clause in BigQuery today.

I have used window functions usually with subqueries like = this:
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER OVER (PARTITION BY = something ORDER BY modified_date DESC) AS row_number
  FROM table_name
)
WHERE= row_number =3D 1


With = QUALIFY it's much more ergonomic and cleaner to do the same thing.
SELECT *
FROM table_name
QUALIFY = ROW_NUMBER() OVER (PARTITION BY something ORDER BY modified_date DESC) =3D= 1


QUALIFY is also better = since it doesn't include extra column row_number to the result.

Where should I send message so that Postgres = maintainers would consider adding this into the TODO page: https://wiki.postgresql.org/wiki/Todo

PostgreSQL supports DISTICT ON, = which can do this even more ergonomically.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

= --Apple-Mail=_808976AD-B8D6-477A-8CA8-30EC8589F459--