DuckDB中如何为关联结果设别名并统计指定范围行数

问题分析 #

你尝试的SQL无法正常运行的核心原因是：在同一个FROM/JOIN子句中，不能直接引用刚刚定义的派生表别名（t1）。派生表的作用域仅限于当前的FROM子句，无法在后续的JOIN中直接复用该别名。

修正后的SQL实现 #

我们可以通过**CTE（公共表表达式）**先将Table1和Table2的内连接结果固化为一个可复用的数据集，再基于这个数据集进行自连接统计：

WITH joined_data AS (
    SELECT 
        t1.id, 
        t1.val, 
        t2.words
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.id = t2.id
)
SELECT 
    jd.id,
    jd.val,
    jd.words,
    COUNT(jd2.id) AS cnt
FROM joined_data jd
LEFT JOIN joined_data jd2 
    ON ABS(jd.val - jd2.val) <= 1
GROUP BY jd.id, jd.val, jd.words;

关键说明 #

• 使用WITH joined_data AS (...)将内连接结果封装为一个可多次引用的CTE，彻底解决了别名作用域的问题。
• 自连接时通过ABS(jd.val - jd2.val) <= 1精准筛选出val值在±1范围内的行。
• COUNT(jd2.id)替代COUNT(*)可以避免统计左连接产生的NULL行（若需要包含NULL统计，可保留COUNT(*)）。
• GROUP BY子句包含所有非聚合列（id、val、words），符合DuckDB的SQL标准要求。

内容的提问来源于stack exchange，提问作者sautedman