sql window function QUALIFY - ghdrako/doc_snipets GitHub Wiki

window functions

• https://www.postgresql.org/docs/current/functions-window.html

 SELECT *
   FROM …
QUALIFY ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) <= 3

The example is a top-n per group query: it returns the first three rows for each partition.

Classic solution without QUALIFY

SELECT *
  FROM (SELECT *
             , ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) AS rn
          FROM …
       ) AS sq
 WHERE sq.rn <= 3

Example prefix phone number

SELECT {columns},
{window_func} OVER (PARTITION BY {partition_key} ORDER BY {order_key})
FROM table1;

Window functions create aggregates without flattening the data into a single row. However, they replicate it for all the rows to which the grouping functions refer.

select category, count(*) over (partition by category) from posts order by category;

QUALIFY

• https://modern-sql.com/caniuse/qualify Wsparcie: BigQuery,Snowflake

create table regions( prefix varchar, name varchar );
create table numbers( number varchar );
insert into numbers select '48221234567';
insert into numbers select '48121234567'
insert into regions select '1', 'USA';
insert into regions select '977', 'Nepal';
insert into regions select '4822', 'Warsaw';
insert into regions select '4812', 'Cracow';
insert into regions select '48', 'Poland';
insert into regions select '4842', 'Łódź';
-- powiazac nr telefonu z regionem na podstawie prefixa

SELECT 
    n.number,
    r.name AS region
FROM 
    numbers n
JOIN 
    regions r 
ON 
    LEFT(n.number, LENGTH(r.prefix)) = r.prefix
-- Nadanie rankingu na podstawie długości prefiksu
QUALIFY ROW_NUMBER() OVER (PARTITION BY n.number ORDER BY LENGTH(r.prefix) DESC) = 1;

bez QUALIFY

WITH ranked_prefixes AS (
    SELECT 
        n.number,
        r.prefix,
        r.name,
        ROW_NUMBER() OVER (PARTITION BY n.number ORDER BY LENGTH(r.prefix) DESC) AS rn
    FROM 
        numbers n
    JOIN 
        regions r 
    ON 
        LEFT(n.number, LENGTH(r.prefix)) = r.prefix
)
SELECT 
    number, 
    name AS region
FROM 
    ranked_prefixes
WHERE 
    rn = 1;