Mysql-5-整合数据

1.聚合函数

SQL中有一些自带的函数,其中一些叫做聚合函数

常见函数:MAX(),MIN(),AVG(),SUM(),COUNT()--这些函数我们在学算法的时候经常看到,看到名字就知道啥意思

SELECT MAX(invoice_total)
FROM invoices

比如用max()函数筛选总价最高的,执行后你会发现列名变成了

Mysql-5-整合数据

这些函数只运算非空数据

当用COUNT()函数获取数量的时候,如果想去除重复的项可以在里面加上DISTINCT

COUNT(DISTINCT client_id)--这样就去除了相同client——id的数据,只记录一次
SELECT 'First half 0f 2019' AS date_range,
SUM(invoice_total) AS total_sales,
       SUM(payment_total) AS total_payment,
       SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN'2019-01-01'AND'2019-06-30'
UNION
SELECT 'Second half 0f 2019' AS date_range,
SUM(invoice_total) AS total_sales,
       SUM(payment_total) AS total_payment,
       SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN'2019-07-01'AND'2020-01-01'
UNION
SELECT 'Total 0f 2019' AS date_range,
SUM(invoice_total) AS total_sales,
       SUM(payment_total) AS total_payment,
       SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date >'2019-01-01'

练习

GROUP BY子句

上面我们用一个简单的SUM()就算出了某时间段的销售额总数,可是如果你想知道每个客户的总销售额的话就要用到子句GROUP BY

GROUP BY 列名会根据后面的列名来进行分组比如

GROUP BY client_id

就是client_id一样的为一个组将其信息整合

Mysql-5-整合数据

默认是我们定义的那个列进行排序的,也可以用ORDER BY来调整顺序

SELECT 
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC

注意:如果要加条件语句WHERE请放在GROUP BY 前面

SELECT 
p.date,
      pm.name AS payment_method,
       SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY date,name

例子

HAVING子句

用于分组过后对数据进行筛选,因为where不能在GROUP BY 后面

SELECT 
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales>500

其也是一个条件语

注意:HAVING里面的列名必须在SELECT里面,SELECT下面没有但是表里面有的列名是不能用的

练习:

SELECT 
c.customer_id,
      c.first_name,
      c.last_name,
      c.state,
       sum(oi.quantity*oi.unit_price) AS total_spent
FROM orders o
JOIN customers c
USING (customer_id)
JOIN order_items oi
USING (order_id)
WHERE state='VA'
GROUP BY c.customer_id,
      c.first_name,
      c.last_name
HAVING total_spent>100

ROLLUP运算符

sql的WITH ROLLUP用于汇总数据

我们在GROUP BY 后面加上这个运算符系统就会计算所显示的数据的总和在后面

Mysql-5-整合数据

当然只会计算数值

练习:

SELECT 
pm.name AS payment_method,
       SUM(p.amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY pm.name WITH ROLLUP



上一篇:算法学习笔记之斜率优化


下一篇:搜索与回溯:字符序列(characts)