Skip to content

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 下一个.

动手试一试

常见错误

错误写法

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 个练习

练习 Aggregation 函数

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误