MySQL函数：`COUNT()`在窗口函数中计算分组内的行数。

MySQL窗口函数：COUNT()详解

大家好，今天我们来深入探讨MySQL窗口函数中的COUNT()函数。COUNT()函数在SQL中是一个非常常见且重要的聚合函数，用于计算行数。而当它与窗口函数结合使用时，其功能会得到极大的扩展，能够计算分组内的行数，为数据分析提供更强大的能力。

COUNT()的基本用法

首先，我们回顾一下COUNT()函数的基本用法。COUNT(*)用于统计表中的所有行数，包括包含NULL值的行。COUNT(column_name)用于统计指定列中非NULL值的行数。

例如，我们有一个名为employees的表，包含以下数据：

id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	60000
3	Charlie	Marketing	70000
4	David	Marketing	80000
5	Eve	IT	90000
6	Frank	IT	NULL

执行以下SQL语句：

SELECT COUNT(*) AS total_employees FROM employees;

结果为：

total_employees
6

执行以下SQL语句：

SELECT COUNT(salary) AS employees_with_salary FROM employees;

结果为：

employees_with_salary
5

窗口函数简介

窗口函数（Window Functions）允许我们对与当前行相关的行集（称为窗口）执行计算，而无需像GROUP BY那样进行分组聚合。窗口函数不会减少行的数量，而是为每一行返回一个计算结果。

窗口函数的基本语法如下：

function_name(arguments) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column1, column2, ...]
    [frame_clause]
)

• function_name(): 要使用的窗口函数，例如COUNT(), SUM(), AVG(), RANK()等。
• OVER(): 指示这是一个窗口函数。
• PARTITION BY (可选): 将数据划分为多个分区。窗口函数将在每个分区内独立计算。类似于GROUP BY，但不会减少行数。
• ORDER BY (可选): 定义分区内行的排序方式。某些窗口函数（如RANK()和LAG()）需要排序才能正确工作。
• frame_clause (可选): 定义当前行的窗口范围。例如，可以指定窗口包含当前行之前或之后的若干行。

COUNT()作为窗口函数

现在，我们将COUNT()函数与窗口函数结合使用。COUNT()作为窗口函数时，可以计算当前窗口内的行数。

1. COUNT()与PARTITION BY

PARTITION BY子句将数据划分为多个分区，COUNT()函数将在每个分区内计算行数。

例如，我们要计算每个部门的员工人数，并在每行中显示该部门的总人数。

SELECT
    id,
    name,
    department,
    salary,
    COUNT(*) OVER (PARTITION BY department) AS department_employee_count
FROM
    employees;

结果为：

id	name	department	salary	department_employee_count
5	Eve	IT	90000	2
6	Frank	IT	NULL	2
3	Charlie	Marketing	70000	2
4	David	Marketing	80000	2
1	Alice	Sales	50000	2
2	Bob	Sales	60000	2

可以看到，对于每个员工，都显示了其所在部门的总人数。

2. COUNT()与ORDER BY

ORDER BY子句定义了分区内行的排序方式。当COUNT()函数与ORDER BY子句一起使用时，可以计算累计行数。

例如，我们要计算每个部门员工的累计人数，按照id排序。

SELECT
    id,
    name,
    department,
    salary,
    COUNT(*) OVER (PARTITION BY department ORDER BY id) AS cumulative_employee_count
FROM
    employees;

结果为：

id	name	department	salary	cumulative_employee_count
5	Eve	IT	90000	1
6	Frank	IT	NULL	2
3	Charlie	Marketing	70000	1
4	David	Marketing	80000	2
1	Alice	Sales	50000	1
2	Bob	Sales	60000	2

可以看到，cumulative_employee_count列显示了每个部门截止到当前行的员工人数。

3. COUNT()与frame_clause

frame_clause子句定义了当前行的窗口范围。常用的frame clause包括：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口包括分区的第一行到当前行。
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 窗口包括当前行到分区的最后一行。
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 窗口包括整个分区。
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: 窗口包括当前行之前的N行和当前行。
• ROWS BETWEEN CURRENT ROW AND N FOLLOWING: 窗口包括当前行和当前行之后的N行。

例如，我们要计算每个部门员工的移动平均人数，窗口大小为3行（包括当前行和前后各一行）。为了简化示例，我们假设每个部门的员工数都大于等于3。

由于我们的数据量较少，这里我们修改一下数据，让每个部门至少有3个员工。

id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	60000
7	Carol	Sales	55000
3	Charlie	Marketing	70000
4	David	Marketing	80000
8	Grace	Marketing	75000
5	Eve	IT	90000
6	Frank	IT	NULL
9	Henry	IT	95000

SELECT
    id,
    name,
    department,
    salary,
    COUNT(*) OVER (PARTITION BY department ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average_count
FROM
    employees;

结果为：

id	name	department	salary	moving_average_count
5	Eve	IT	90000	2
6	Frank	IT	NULL	3
9	Henry	IT	95000	2
3	Charlie	Marketing	70000	2
4	David	Marketing	80000	3
8	Grace	Marketing	75000	2
1	Alice	Sales	50000	2
2	Bob	Sales	60000	3
7	Carol	Sales	55000	2

可以看到，moving_average_count列显示了每个部门的移动平均人数。对于第一行和最后一行，由于窗口大小不足3行，所以结果有所不同。例如，对于IT部门，id为5的Eve，窗口只包含id为5和6的行，所以moving_average_count为2。

4. COUNT(column_name) 作为窗口函数

与基本用法类似，COUNT(column_name)作为窗口函数时，只计算指定列中非NULL值的行数。

例如，我们要计算每个部门中salary不为NULL的员工人数。

SELECT
    id,
    name,
    department,
    salary,
    COUNT(salary) OVER (PARTITION BY department) AS department_non_null_salary_count
FROM
    employees;

结果为：

id	name	department	salary	department_non_null_salary_count
5	Eve	IT	90000	2
6	Frank	IT	NULL	2
9	Henry	IT	95000	2
3	Charlie	Marketing	70000	3
4	David	Marketing	80000	3
8	Grace	Marketing	75000	3
1	Alice	Sales	50000	3
2	Bob	Sales	60000	3
7	Carol	Sales	55000	3

5. 组合使用

可以将PARTITION BY, ORDER BY和frame_clause组合使用，以实现更复杂的计算。

例如，我们要计算每个部门中salary不为NULL的员工的累计人数，按照id排序，并限制窗口为当前行和之前的一行。

SELECT
    id,
    name,
    department,
    salary,
    COUNT(salary) OVER (PARTITION BY department ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS cumulative_non_null_salary_count
FROM
    employees;

结果为：

id	name	department	salary	cumulative_non_null_salary_count
5	Eve	IT	90000	1
6	Frank	IT	NULL	1
9	Henry	IT	95000	2
3	Charlie	Marketing	70000	1
4	David	Marketing	80000	2
8	Grace	Marketing	75000	3
1	Alice	Sales	50000	1
2	Bob	Sales	60000	2
7	Carol	Sales	55000	3

应用场景

COUNT()窗口函数在数据分析中有很多应用场景，例如：

• 计算每个用户的订单数量，并在每行订单数据中显示该用户的总订单数。
• 计算每个产品的销售额占总销售额的比例，并在每行产品销售数据中显示该比例。
• 计算每个地区的客户数量，并在每行客户数据中显示该地区的总客户数。
• 计算一段时间内的活跃用户数量，并在每行用户活动数据中显示该时间段的活跃用户数。
• 计算股票的移动平均成交量，并在每行股票交易数据中显示该移动平均值。

注意事项

• 窗口函数只能在SELECT子句和ORDER BY子句中使用。
• 窗口函数不能与GROUP BY子句一起使用。如果需要分组聚合，可以使用子查询或公共表表达式（CTE）。
• frame_clause子句只能与ORDER BY子句一起使用。
• COUNT(DISTINCT column_name)在某些MySQL版本中可能不支持作为窗口函数。

总结

我们详细介绍了MySQL窗口函数中COUNT()的用法，包括与PARTITION BY、ORDER BY和frame_clause的结合使用，以及COUNT(column_name)的用法。希望通过今天的讲解，大家能够更好地理解和应用COUNT()窗口函数，提升数据分析的能力。

进一步学习的方向

掌握窗口函数之后，可以进一步学习其他的窗口函数，例如SUM(), AVG(), RANK(), LAG(), LEAD()等，以及如何将它们组合使用，以解决更复杂的数据分析问题。