如何在ClickHouse中基于窗口函数结果过滤student_count>1的数据？

原始数据表 #

| University  | Student   | Program   |
| ----------- | --------- | --------- |
| University A| Student 1 | Program A |
| University A| Student 2 | Program A |
| University A| Student 3 | Program B |
| University B| Student 4 | Program A |
| University B| Student 5 | Program B |

需求目标 #

通过窗口函数按University和Program分组计算student_count，筛选出student_count > 1的记录，预期结果如下：

| University  | Student  | Program   | student_count |
| ----------- | -------- | --------- | ------------- |
| University A| Student 1| Program A | 2             |
| University A| Student 2| Program A | 2             |

尝试的错误SQL #

SELECT 
    University,
    Program, 
    Student,
    count(*) over (partition by University, Program) as student_count
FROM 
    table;
WHERE 
student_count > 1

提问：在不创建中间表的情况下，如何在ClickHouse中实现该需求？

你的SQL执行失败的核心原因是：窗口函数生成的别名无法直接在WHERE子句中使用。SQL的执行顺序中，WHERE子句的过滤逻辑早于窗口函数的计算，此时student_count字段还未生成，因此无法被引用。

在ClickHouse中，无需创建中间表，可通过以下两种方式实现需求：

方式一：子查询嵌套 #

将窗口函数的计算逻辑放在内层子查询中，外层查询基于生成的student_count进行过滤：

SELECT *
FROM (
    SELECT 
        University,
        Student,
        Program,
        count(*) OVER (PARTITION BY University, Program) AS student_count
    FROM your_table_name
) AS sub_query
WHERE student_count > 1

方式二：WITH子句定义临时数据集 #

通过WITH子句预先定义包含窗口函数计算结果的数据集，再对该数据集进行筛选：

WITH student_summary AS (
    SELECT 
        University,
        Student,
        Program,
        count(*) OVER (PARTITION BY University, Program) AS student_count
    FROM your_table_name
)
SELECT *
FROM student_summary
WHERE student_count > 1

注意：请将上述代码中的your_table_name替换为你实际使用的表名。两种方式均能得到你预期的结果，且不需要额外创建中间表。

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