DuckDB Window 窗口函数语法糖 - QUALIFY - window filter

标签

PostgreSQL , DuckDB , window , filter

背景

quality支持直接过滤窗口函数的结果, 雷同于聚合函数的having用法. 非常简便. 窗口函数和聚合函数一样, 在OLAP场景较为多见, DuckDB支持quality语法是很关心用户的.

https://duckdb.org/docs/sql/query_syntax/qualify

例子

create table tbl (gid bigint, v numeric, crt_time timestamp);  
  insert into tbl select t1.generate_series, random()*1000, now() + (t1.generate_series*t2.generate_series||' second')::interval  from     
(select * from generate_series(1,10) ) t1,     
(select * from generate_series(1,100000)) t2;     

qualify用法

select *, row_number() over w as rn   
from tbl   
window w as (partition by gid order by crt_time desc)   
qualify rn <2   
order by gid   
limit 10;  
  或者  
  select * from tbl   
window w as (partition by gid order by crt_time desc)   
qualify (row_number() over w ) <2   
order by gid   
limit 10;  

explain select * from tbl window w as (partition by gid order by crt_time desc) qualify (row_number() over w ) <2 order by gid limit 10;  
  ┌─────────────────────────────┐  
│┌───────────────────────────┐│  
││       Physical Plan       ││  
│└───────────────────────────┘│  
└─────────────────────────────┘  
┌───────────────────────────┐  
│           TOP_N           │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│           Top 10          │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│   CAST((tbl.gid - 1) AS   │  
│        UTINYINT) ASC      │  
└─────────────┬─────────────┘                               
┌─────────────┴─────────────┐  
│         PROJECTION        │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│            gid            │  
│             v             │  
│          crt_time         │  
└─────────────┬─────────────┘                               
┌─────────────┴─────────────┐  
│         PROJECTION        │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│             #0            │  
│             #1            │  
│             #2            │  
└─────────────┬─────────────┘                               
┌─────────────┴─────────────┐  
│           FILTER          │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│     row_number() OVER     │  
│(PARTITION BY tbl.gid ...  │  
│    .crt_time DESC) < 2    │  
└─────────────┬─────────────┘                               
┌─────────────┴─────────────┐  
│           WINDOW          │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│     ROW_NUMBER() OVER     │  
│(PARTITION BY gid ORDE...  │  
│      DESC NULLS FIRST)    │  
└─────────────┬─────────────┘                               
┌─────────────┴─────────────┐  
│          SEQ_SCAN         │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│            tbl            │  
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │  
│            gid            │  
│          crt_time         │  
│             v             │  
└───────────────────────────┘   

如果是PostgreSQL, 目前还不支持qualify, 只能这样写SQL, 使用子查询或者with

select gid,v,crt_time from (  
  select *, row_number() over w as rn from tbl window w as (partition by gid order by crt_time desc)   
) t  
where rn <2   
order by gid  
limit 10;  
  或者  
  with t as (  
  select *, row_number() over w as rn from tbl window w as (partition by gid order by crt_time desc)   
)  
select gid,v,crt_time from t where rn <2 order by gid limit 10;  

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出.