Aggregate Functions | 汇总数据 | 聚合函数 | MySQL

Aggregate Functions

今天学习如何为数据汇总编写查询
在这之前先看看内置函数的使用

内置函数

SELECT 
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average
FROM invoices

这些函数也试用于日期，例如

SELECT 
MAX(payment_date) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average
FROM invoices

SELECT 
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(invoice_total) AS number_of_invoices -- 计数
FROM invoices

注意

• 聚合函数只允许非空值 因此如果所选列有空值，该列不会被算在函数里
• 思考一下排除非空值可能会出现的问题
  这里举个例子

  SELECT 

COUNT(invoice_total) AS number_of_invoices,
COUNT(payment_date) AS count_of_payments  --  payment_date列存有空值
FROM invoices

得到7是因为并不是所有的发票都结了帐，所以count函数返回了非空payment dates的记录数

• 如果想得到表格中的所有条目，不管是不是空值，可以这样表示
  
• 很多时候我们在内置函数中使用列名，但其实这里也可以写表达式

SELECT 
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total *1.1) AS total,
COUNT(*) AS total_records
FROM invoices

当SQL执行查询，它会最先得到列值，并对每个值*1.1，之后这些值在SUM（）进行累加，也可以添加”筛选器“进行更具体的加和

SELECT 
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total *1.1) AS total,
COUNT(*) AS total_records
FROM invoices
WHERE invoice_date >'2019-07-01' -- 只看下半年的发票

这里会出现一个问题，total_records包含了所有的列数，就算我修改函数列名为COUNT(*) AS total_records → COUNT(client_id) AS total_records
也依然得到返回值7，因为invoices表格中client_id存有重复值

因此这里会用到distinct关键词

SELECT 
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total *1.1) AS total,
COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date >'2019-07-01' -- 只看下半年的发票

现在得到的数值为3，因为表格中只有3个唯一客户拥有这个日期以后的发票

练习

请对invoices表格编写一个查询，以得到下述结果

其中what_we_expect列为total_sales列和total_payments列的差值

SELECT
	'First half of 2019' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE payment_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION 
SELECT
	'second half of 2019' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE payment_date BETWEEN '2019-07-01' AND '2019-12-30'
UNION 
SELECT
	'total' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE payment_date BETWEEN '2019-01-01' AND '2019-12-30'

可以回顾一下:

• 怎么自己创建一个列 ‘值名’ AS 列名
• 连接行Unions | 联合 | MySQL
• 之前注意里提到的，聚合函数括号内是可以用计算的