SQL - 汇总与分组

news/2024/10/18 14:16:00/

聚合函数

MySQL自带一堆内置函数，其中一些叫聚合函数，用它们汇总数据，因为它们取某一列的值并聚合它们，导出一个单一值。并且聚合函数只会运行非空值，如果列中有的值是null，它不会被算在内。

max(), min(), avg(), sum(), count()，count(*) 返回表中的所有记录数，count(distinct client_id)具有去重功能，默认不去重

sql">select 'first half of 2019' as date_range,sum(invoice_total) as total_sales,sum(payment_total) as total_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_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_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_date between '2019-07-01' and '2019-12-31'union
select 'total' as date_range,sum(invoice_total) as total_sales,sum(payment_total) as total_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_date between '2019-01-01' and '2019-12-31'

group by 子句
- group by 子句在SQL查询中用于将结果集按一个或多个列进行分组。它的主要作用是将具有相同值的行归为一组，以便对每组数据进行聚合计算（如求和、计数、平均值等）
- ```
sql">-- group by 分组
select date,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,payment_method
order by date
```

having 子句

将结果集按一个或多个列分组后，再次筛选数据，但是筛选的列名必须是是在select中的

sql">-- having 再次筛选
select date,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,payment_method
having total_payments>10
order by date

rollup 运算符
- 在分组后，每个组都有用聚合函数运算的值，对应的每个组的汇总和整个结果集的汇总可以使用到 with rollup，如group by client_id with rollup，不过这列名不能使用别名
- ```
sql">-- rollup运算符
select 
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id with rollup
```

SQL - 汇总与分组

相关文章

分析 Runtime.getRuntime() 执行阻塞原因

智云-一个抓取web流量的轻量级蜜罐docker一键启动

HiveSQL实战——大数据开发面试高频SQL题

勇闯机器学习（第三关-特征工程）

Emacs28.x版本之重要特性及用法实例(一百六十一)

Ansible可视化管理之web界面集成使用探究（未完待续）

每天一个数据分析题（四百九十二）- 主成分分析与因子分析

Pytorch cat()与stack()函数详解