SQL----chapter5:summarizing data

最新推荐文章于 2024-10-17 17:35:50 发布

ttkelly

最新推荐文章于 2024-10-17 17:35:50 发布

阅读量64

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/ttkelly/article/details/130853786

版权

1.聚合函数

输入一系列值并聚合为一个结果的函数

use sql_invoicing;

select
    max(invoice_date) as latest_date,   
    ---select选择的不仅可以是列，也可以是数字、列间表达式、列的聚合函数
    min(invoice_total) lowest,
    sum(invoice_total) average,
    count(*) total_records,
    count(invoice_total) number_of_invoices,
    count(payment_date) number_of_payments,
    count(distinct client_id) number_of_distinct_clients
    ---distinct client_id筛掉了该列的重复值，再count计数，不同顾客数
from invoices
where invoice_date > '2019-07-01'   ---想只统计下半年的结果

---思路：很明显要 分类子查询+聚合函数+UNION

use sql_invoicing;

select
    '1st_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 invoice_date between '2019-01-01' and '2019-06-30'

union

select
    '2st_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 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_payments,
    sum(invoice_total - payment_total) as what_we_expect
from invoices
where invoice_date between '2019-01-01' and '2019-12-31'

2.group by 子句

---按一列或多列分组，注意语句的位置。
---案例1：按一个字段分组
---在发票记录表中按不同顾客分组统计各个顾客下半年总销售额并降序排列

use sql_invoicing;

select
    client_id,
    sum(invoice_total) as total_sales
……

---只有聚合函数是按client_id分组时，这里选择client_id列才有意义（分组统计语句里SELECT通常都是选择分组依据列+目标统计列的聚合函数，选别的列没意义）。若未分类，结果会是一条总total_sales和一条client_i（该client_id无意义），即client_id会被压缩为只显示一条而非SUM广播为多条，可以理解为聚合函数比较强势吧。（要SUM扩散为多条得用OVER，即窗口函数）

……
from invoices
shere invoice_date >= '2019-07-01'  ---筛选，过滤器
group by client_id  ---分组
order by invoice_total DESC

---若省略排序语句就会默认按分组依据排序（后面一个例子发现好像也不一定，所以最好别省略）
---记住语句顺序很重要 WHERE GROUP BY ORDER BY，分组语句在排序语句之前，调换顺序会报错

---案例2：按多个字段分组
---算各州各城市的总销售额
---如前所述，一般分组依据字段也正是 select …… 里的选择字段，如下面例子里的 state 和 city

use sql_invoicing;

select
    state,
    city,
    sum(invoice_total) as total_sales
from invoices
join clients using (client_id)   ---【别忘了USING之后是括号，太容易忘了】
group by state, city
-- 逗号分隔就行
-- 这个例子里group by里去掉state结果一样
order by state

---其实上面的例子里一个城市只能属于一个州中，所有归根结底还是算的各城市的销售额，group by …… 里去掉state 只写 city （但select和order by里保留state）结果是完全一样的（包括结果里的state列），下面这个例子更能说明以多个字段为分组依据进行分组统计的意义

---在 payments 表中，按日期和支付方式分组统计总付款额
---每个分组显示一个日期和支付方式的独立组合，可以看到某特定日期并有某特定支付方式的总付款额。这个例子里每一种支付方式可以在不同日子里出现，每一天也可以出现多种支付方式，这种情况，才叫真·多字段分组。【不过上一个例子里那种假·多字段分组，把state加在分组依据里也没坏处还能落个心安，也还是加上别省比较好,而且像PostgreSQL之类的甚至强制要求只能 select在分组依据中出现过的字段（以及聚合函数），所以有时仅仅为了在 select 里选择的目的也该在 group by 里包含某些字段，即便它们不会起到实质的分组依据的作用】

use sql_invoicing;
select
    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, payment_method
order by date

3.having 子句

having和 where 都是是条件筛选语句，条件的写法相通，数学比较（包括特殊比较）逻辑运算都可以用（如and、regexp等等）
两者本质区别:

where 是对 from join 里原表中的列进行事前筛选，所以 where 可以对没选择的列进行筛选，但必须用原表列名而不能用 select 中确定的列别名。
相反 having …… 对 select …… 查询后（通常是分组并聚合查询后）的结果列进行事后筛选，若 select 里起了别名的字段则必须用别名进行筛选，且不能对 select 里未选择的字段进行筛选。唯一特殊情况是，当 having 筛选的是聚合函数时，该聚合函数可以不 select 里显性出现，见最后补充。

use sql_invoicing;

select
    client_id,
    sum(invoice_total) as total_sales,
    count(*/invoice_total/invoice_date) as number_of_invoices
from invoices
group by client_id
having total_sales > 500 and number_of_invoices > 5

若写：where total_sales > 500 and number_of_invoices > 5，会报错：Error Code: 1054. Unknown column 'total_sales' in 'where clause'

---在sql_store数据库（有顾客表、订单表、订单项目表等）中，找出在'VA'州且消费总额超过100美元的顾客（这是一个面试级的问题，还很常见）
---思路：
---1. 需要的信息在顾客表、订单表、订单项目表三张表中，先将三张表合并
---2. 事前筛选 'VA'州的
---3. 按顾客分组，并选取所需的列并聚合得到每位顾客的付款总额
---4. 事后筛选超过100美元的

use sql_store;

select
    c.customer_id,
    c.first_name,
    c.last_name,
    sum(oi.quantity * oi.unit_price) as total_sales
from customers c
join orders o USING (customer_id) -- 别忘了括号，特容易忘
join order_items oi USING (order_id)
shere state = 'VA'
group by
    c.customer_id,
    【c.first_name,
    c.last_name】  ---因为 select 中需要选，所以在 group by 里最好加上，虽然按 id 分组本已足够
having total_sales > 100

4.rollup 运算符

froup by …… with roll up 自动汇总型分组（对 sum 之类的聚合值进行分组汇总），若是多字段分组的话汇总也会是多层次的，注意这是MySQL扩展语法，不是SQL标准语法。

---分组查询各客户的发票总额以及所有人的总发票额

use sql_invoicing;

select
    client_id,
    sum(invoice_total)
from invoices
group by client_id with rollup
---当然，总发票额那一行 client_id 为空

---多字段分组 例1：分组查询各州、市的总销售额（发票总额）以及州层次和全国层次的两个层次的汇总额

select
    state,
    city,
    sum(invoice_total) as total_sales
from invoices
join clients using (client_id)
group by state, city with ROLLUP
---先按 city 汇总，再按 state 汇总，与分组顺序相反（当然，分组和汇总本来就是相反的两个过程）

---多字段分组 例2：分组查询特定日期特定付款方式的总支付额以及单日汇总和整体汇总

use sql_invoicing;
select
    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, pm.name with rollup
-- 注意这儿 group by 里若使用列别名 payment_method 则结果没有每日层次的汇总。
-- group by 分组依据和 select 选择字段（除聚合函数外）最好是能一一对应，这是最保险的。

---分组计算各个付款方式的总付款 并汇总

select
    pm.name as payment_method,
    sum(amount) as total
from payments p
join payment_methods pm
    on p.payment_method = pm.payment_method_id
group by pm.name with rollup

★总结
根据之后三篇参考文章，据说标准的 SQL 查询语句的执行顺序应该是下面这样的：

1. FROM JOIN 选择和连接本次查询所需的表
2. ON/USING WHERE 按条件筛选行
3. GROUP BY 分组
4. SELECT 筛选列（注意若进行了分组，这一步常常要聚合）
---注意：SELECT 和 HAVING 在 MySQL 里的执行顺序我还有点疑问，见后面的叙述
5. HAVING （分组聚合后）按条件筛选行
6. DISTINCT 去重
7. UNION 纵向合并
8. ORDER BY 排序
9. LIMIT 限制

" select 是在大部分语句执行了之后才执行的，严格的说是在 from、where 和 group by （以及having*？）之后执行的。理解这一点是非常重要的，这就是你不能在 where 中使用在 select 中设定别名的字段作为判断条件的原因。"

这个顺序可以由下面这个例子的缩进表现出来（出右往左）（注意 distinct 放不进去了只有以注释的形式展示出来，另外 select 还是选择放在了 having 之前）

use sql_invoicing;

            select name, sum(invoice_total) as total_sales
        -- distinct
                        from invoices join clients using (client_id)
                    where due_date < '2019-07-01'
                group by name
          having total_sales > 150

        union

            select name, sum(invoice_total) as total_sales
        -- distinct
                        from invoices join clients using (client_id)
                    where due_date > '2019-07-01'
                group by name
          having total_sales > 150

    order by total_sales
limit 2

关于 SELECT 的位置

1. 如后面几篇参考文章所说，按标准 SQL 的执行顺序， SELECT 是在 HAVING 之后
2. 但根据前面的内容，似乎在 MySQL 里，SELECT 的执行顺序是在 WHERE GROUP BY 之后，而在 HAVING之前 —— 因而 WHERE GROUP BY 要用原列名（后来发现只是WHERE必须用原列名，GROUP BY 是原列名或列别名都可用（甚至可以用1，2来指代 SELECT 中的列，不过 Mosh 不建议这样做））而 HAVING 必须用 SELECT 里的列别名（聚合函数除外）

按实践经验来看，就按 2 来记忆和理解是可行的，但之后最好还是要去看书看资料把这个执行顺序的疑惑彻底搞清楚，这个还挺重要的。