在DuckDB中跨不同QUALIFY条件的查询复用分区是否可行？

当然可以实现这类需求，以下是贴合你需求的具体SQL示例：

基础实现（含窗口复用） #

如果需要在分区排序后结合窗口函数（比如行号）进行过滤，可提前在CTE中定义窗口计算逻辑，后续查询直接复用：

WITH some_partition AS (
  -- 读取Parquet文件，提前定义分区排序的窗口计算
  SELECT 
    id,
    group_id,
    ordering_var,
    var1,
    var2,
    -- 按group_id分区、ordering_var升序生成行号，后续查询可复用该窗口逻辑
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ordering_var ASC) AS row_num
  FROM 'your_target_file.parquet'
),
query1 AS (
  SELECT id, group_id, var1, row_num
  FROM some_partition
  -- 应用var1非空的QUALIFY过滤条件
  QUALIFY var1 IS NOT NULL
),
query2 AS (
  SELECT id, group_id, var2, row_num
  FROM some_partition
  -- 应用var2非空的QUALIFY过滤条件
  QUALIFY var2 IS NOT NULL
)
-- 按id执行全连接合并结果
SELECT 
  COALESCE(q1.id, q2.id) AS id,
  q1.group_id AS query1_group_id,
  q1.var1,
  q2.group_id AS query2_group_id,
  q2.var2
FROM query1 q1
FULL OUTER JOIN query2 q2 ON q1.id = q2.id;

简化实现（仅字段过滤） #

如果你的QUALIFY条件仅为字段非空判断，无需结合窗口函数，可简化为：

WITH some_partition AS (
  -- 读取Parquet文件，仅提取所需字段
  SELECT id, group_id, ordering_var, var1, var2
  FROM 'your_target_file.parquet'
),
query1 AS (
  SELECT id, group_id, var1
  FROM some_partition
  QUALIFY var1 IS NOT NULL
),
query2 AS (
  SELECT id, group_id, var2
  FROM some_partition
  QUALIFY var2 IS NOT NULL
)
SELECT 
  COALESCE(q1.id, q2.id) AS id,
  q1.group_id,
  q1.var1,
  q2.group_id,
  q2.var2
FROM query1 q1
FULL OUTER JOIN query2 q2 ON q1.id = q2.id;

关键说明 #

• some_partition CTE作为基础数据集，实现了Parquet文件读取与分区排序逻辑的统一定义，避免后续查询重复编写相同代码。
• DuckDB原生支持QUALIFY语法，可直接用于过滤窗口函数结果或单纯字段条件；若仅需字段过滤，QUALIFY效果与WHERE类似，但保留了后续扩展窗口逻辑的灵活性。
• 最终通过FULL OUTER JOIN（全连接）按id合并两个查询结果，COALESCE用于处理id的空值情况，确保所有id都能被保留。

内容的提问来源于stack exchange，提问作者Lay González