COUNT() OVER()总结

COUNT() OVER() 是 Impala 中非常重要的窗口函数用法，它与普通的聚合函数 COUNT() 有本质区别。

核心概念

COUNT() OVER() 不是在查询结果上做整体聚合，而是为每一行计算一个计数值，同时保留所有原始行的详细信息。

基本语法

COUNT(*) OVER (
    [PARTITION BY column1, column2...]
    [ORDER BY column3, ...]
    [window_frame_clause]
)

---

主要用法和示例

假设我们有 sales 表：

sale_id	product	category	sale_date	amount
1	iPhone	Phone	2024-01-01	1000
2	Galaxy	Phone	2024-01-02	900
3	iPad	Tablet	2024-01-03	800
4	MacBook	Laptop	2024-01-04	2000
5	Pixel	Phone	2024-01-05	700

1. 简单的 COUNT() OVER() - 计算总行数

SELECT 
    sale_id,
    product,
    amount,
    COUNT(*) OVER() as total_count
FROM sales;

结果：

sale_id	product	amount	total_count
1	iPhone	1000	5
2	Galaxy	900	5
3	iPad	800	5
4	MacBook	2000	5
5	Pixel	700	5

说明： 每行都显示总行数 5，但保留了所有原始数据。

2. 使用 PARTITION BY - 按分组计数

SELECT 
    sale_id,
    product,
    category,
    amount,
    COUNT(*) OVER(PARTITION BY category) as category_count
FROM sales;

结果：

sale_id	product	category	amount	category_count
4	MacBook	Laptop	2000	1
1	iPhone	Phone	1000	3
2	Galaxy	Phone	900	3
5	Pixel	Phone	700	3
3	iPad	Tablet	800	1

说明： 在每个 category 分组内分别计数，Phone 类别有3条记录，Laptop 和 Tablet 各有1条。

3. 使用 ORDER BY - 累计计数

SELECT 
    sale_id,
    product,
    sale_date,
    amount,
    COUNT(*) OVER(ORDER BY sale_date) as running_count
FROM sales;

结果：

sale_id	product	sale_date	amount	running_count
1	iPhone	2024-01-01	1000	1
2	Galaxy	2024-01-02	900	2
3	iPad	2024-01-03	800	3
4	MacBook	2024-01-04	2000	4
5	Pixel	2024-01-05	700	5

说明： 按日期排序后，每行显示到当前行为止的累计行数。

4. 结合 PARTITION BY 和 ORDER BY - 分组内累计

SELECT 
    sale_id,
    product,
    category,
    sale_date,
    amount,
    COUNT(*) OVER(
        PARTITION BY category 
        ORDER BY sale_date
    ) as running_count_in_category
FROM sales;

结果：

sale_id	product	category	sale_date	amount	running_count_in_category
4	MacBook	Laptop	2024-01-04	2000	1
1	iPhone	Phone	2024-01-01	1000	1
2	Galaxy	Phone	2024-01-02	900	2
5	Pixel	Phone	2024-01-05	700	3
3	iPad	Tablet	2024-01-03	800	1

说明： 在每个 category 分组内，按日期顺序进行累计计数。

---

实际应用场景

场景1：计算占比

SELECT 
    product,
    amount,
    COUNT(*) OVER() as total_orders,
    ROUND(amount * 100.0 / SUM(amount) OVER(), 2) as amount_percent
FROM sales;

场景2：识别重复数据

SELECT *
FROM (
    SELECT 
        *,
        COUNT(*) OVER(PARTITION BY product, category) as duplicate_count
    FROM sales
) t
WHERE duplicate_count > 1;

场景3：分析数据密度

SELECT 
    sale_date,
    amount,
    COUNT(*) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as last_3_days_count
FROM sales;

---

与普通 COUNT() 的关键区别

特性	普通 COUNT()	COUNT() OVER()
返回结果	单行聚合结果	为每行返回一个计数值
数据保留	丢失原始行细节	保留所有原始列数据
使用场景	整体统计	行级分析、排名、占比计算
分组方式	GROUP BY	PARTITION BY

总结

COUNT() OVER() 的强大之处在于：

1. 保留明细：不像 GROUP BY 那样折叠数据
2. 灵活分组：通过 PARTITION BY 实现多维度分析
3. 支持排序：通过 ORDER BY 实现累计计算
4. 窗口控制：可以定义更复杂的计算范围（如最近N行）

这使得它成为数据分析、报表制作和复杂业务逻辑实现的利器。