Aggregation 函数
Summarize 数据使用 COUNT, SUM, AVG, MIN, and MAX aggregate 函数.
你将学到
- 什么 aggregate 函数是 and 当到 use 它们
- COUNT, SUM, AVG, MIN, MAX — five 核心 aggregates
- difference 之间 COUNT(*) and COUNT(列)
- 组合 aggregates 使用 WHERE 到筛选之前 aggregating
- 为什么 NULL 值 affect aggregate results
概念讲解
Aggregate Functions
An aggregate function takes many rows and returns a single value. They are the foundation of reporting and analytics — "如何许多顺序?", "什么是平均 price?", "什么是总计 revenue?". The five core aggregates are:
| Function | Returns | Example | |----------|---------|---------| | COUNT(*) | Number of rows | COUNT(*) FROM 顺序 | | SUM(col) | Total of numeric values | SUM(总计) FROM 顺序 | | AVG(col) | Average value | AVG(price) FROM products | | MIN(col) | Smallest value | MIN(price) FROM products | | MAX(col) | Largest value | MAX(created_at) FROM 顺序 |
COUNT(*) vs COUNT(column)
COUNT(*) counts all rows, including those with NULL values. COUNT(列) counts only non-NULL values in that column. COUNT(DISTINCT 列) counts unique non-NULL values. This distinction matters when your column has NULLs — COUNT(email) will skip rows where email is NULL.
Filtering with WHERE
You can combine aggregates with WHERE to filter rows before aggregating. SELECT AVG(price) FROM products WHERE 分类 = 'electronics' gives the average price of electronics only. The WHERE clause is applied first, then the aggregate is computed over the remaining rows.
NULL Handling
All aggregate functions except COUNT(*) ignore NULL values. AVG(price) sums non-NULL prices and divides by the count of non-NULL prices — NULL rows do not pull the average toward zero. This is usually what you want, but be aware of it when your data has many NULLs.
示例代码
-- Core aggregates over the orders table
SELECT
COUNT(*) AS total_orders,
SUM(total) AS revenue,
AVG(total) AS avg_order_value,
MIN(total) AS smallest_order,
MAX(total) AS largest_order
FROM orders
WHERE status = 'completed';
-- Count distinct categories and total products
SELECT
COUNT(DISTINCT category) AS unique_categories,
COUNT(*) AS total_products
FROM products;
-- Revenue per category (preview of GROUP BY)
SELECT category, SUM(total) AS revenue
FROM orders
GROUP BY category;
首先查询 computes five aggregates in one 通过. 其次 shows COUNT(DISTINCT). 第三 previews GROUP BY — coming 下一个.
动手试一试
- SQL Formatter
格式查询使用多个 aggregate 函数.
- CSV 到 JSON
导出 aggregated 查询 results as JSON for charts.
常见错误
错误写法
Using COUNT(列) 当你 平均值 COUNT(*)
正确做法
COUNT(列) skips NULLs. 如果你 想要总计数字的 行 regardless 的 NULLs, use COUNT(*).
错误写法
选择非-aggregated 列 alongside aggregates 不使用 GROUP BY
正确做法
你 cannot mix plain 列使用 aggregates 除非你 GROUP BY 那些列. 数据库将 错误 or 返回 unpredictable results.
练习 Aggregation 函数
18 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。