SQL（二）

最新推荐文章于 2024-06-29 12:12:59 发布

ZLuby

最新推荐文章于 2024-06-29 12:12:59 发布

阅读量633

点赞数

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： sql聚合

本文链接：https://blog.youkuaiyun.com/weixin_38300566/article/details/86362905

sql 专栏收录该内容

7 篇文章

订阅专栏

本文深入探讨SQL聚合函数如COUNT(), SUM(), AVG(), MIN()和MAX()的应用，讲解GROUP BY, DISTINCT, HAVING和DATE函数等高级查询技巧，帮助读者掌握数据分组、筛选和日期操作的实战技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

NULL简介

NULL 是一种数据类型，表示 SQL 中没有数据。它们经常在聚合函数中被忽略了。注意，NULL 与零不同，它们表示不存在数据的单元格。

在 WHERE 条件中表示 NULL 时，我们写成 IS NULL 或 IS NOT NULL。我们不使用 =，因为 NULL 在 SQL 中不属于值。它是数据的一个属性。

注意：在以下两种常见情况下，你可能会遇到 NULL：

（1）在执行 LEFT JOIN 或 RIGHT JOIN 时，NULL 经常会发生。左侧表格中的某些行在做连接时与右侧表格中的行如果不匹配，这些行在结果集中就会包含一些 NULL 值。（2）NULL 也可能是因为数据库中缺失数据。

聚合函数

COUNT()

计算表格中全部数据的行数。以下是计算 accounts 表格中的行数示例：

SELECT COUNT(*)
FROM accounts;

COUNT(*)特性是COUNT函数特有的，其他函数并不能将*号作为参数。

我们也可以轻松地选择一列来放置聚合函数，这样得到是该列的非空行数：

SELECT COUNT(accounts.id)
FROM accounts;

COUNT 不会考虑具有 NULL 值的行。因此，可以用来快速判断哪些行缺少数据。？？？

COUNT函数的结果根据参数的不同而不同，COUNT(*)会得到包含NULL的数据行数，而COUNT(列名)会得到NULL之外的数据行数。

SUM（）

与 COUNT 不同，你只能针对数字列使用 SUM。但是，SUM 将忽略 NULL 值，其他聚合函数也是这样。

重要注意事项：聚合函数只能垂直聚合，即聚合列的值。如果你想对行进行计算，可以使用简单算术表达式。

练习：

算出 orders 表格中的 poster_qty 纸张总订单量。
算出 orders 表格中 standard_qty 纸张的总订单量。
根据 orders 表格中的 total_amt_usd 得出总销售额。
算出 orders 表格中每个订单在 standard 和 gloss 纸张上消费的数额。结果应该是表格中每个订单的金额。

select sum(poster_qty) AS P,
sum(standard_qty) AS S,
sum(total_amt_usd) AS T
from orders;

SELECT standard_qty + gloss_qty AS total_standard_gloss
FROM orders;

SELECT SUM(standard_amt_usd)/SUM(standard_qty) AS standard_price_per_unit
FROM orders;

MIN（）和 MAX（）

MIN 和 MAX 聚合函数也会忽略 NULL 值。

从功能上来说，MIN 和 MAX 与 COUNT 相似，它们都可以用在非数字列上。MIN 将返回最小的数字、最早的日期或按字母表排序的最之前的非数字值，具体取决于列类型。MAX 则正好相反，返回的是最大的数字、最近的日期，或与“Z”最接近（按字母表顺序排列）的非数字值。

AVG（）

AVG 返回的是数据的平均值，即列中所有的值之和除以列中值的数量。该聚合函数同样会忽略分子和分母中的 NULL 值。

如果你想将 NULL 当做零，则需要使用 SUM 和 COUNT。但是，如果 NULL 值真的只是代表单元格的未知值，那么这么做可能不太合适。注意，中值可能是更好的衡量方式，

练习

SELECT MIN(occurred_at) 
FROM orders;

SELECT occurred_at 
FROM orders 
ORDER BY occurred_at
LIMIT 1;

SELECT MAX(occurred_at)
FROM web_events;

SELECT occurred_at
FROM web_events
ORDER BY occurred_at DESC
LIMIT 1;

SELECT AVG(standard_qty) mean_standard, AVG(gloss_qty) mean_gloss, 
           AVG(poster_qty) mean_poster, AVG(standard_amt_usd) mean_standard_usd, 
           AVG(gloss_amt_usd) mean_gloss_usd, AVG(poster_amt_usd) mean_poster_usd
FROM orders;

SELECT *
FROM (SELECT total_amt_usd
      FROM orders
      ORDER BY total_amt_usd
      LIMIT 3457) AS Table1
ORDER BY total_amt_usd DESC
LIMIT 2;

对表进行分组

GROUP BY

(1) 主要知识点包括：

1、GROUP BY 先把表分成几组，然后再进行汇总处理。可以用来在数据子集中聚合数据。

2、SELECT 语句中的任何一列如果不在聚合函数中，则必须在 GROUP BY 条件中。

3、GROUP BY 始终在 WHERE 和 ORDER BY 之间。

在深入了解如何使用 GROUP BY 语句聚合函数之前，需要注意的是，SQL 在 LIMIT 条件之前评估聚合函数。如果不按任何列分组，则结果是 1 行。如果按照某列分组，该列中存在大量的唯一值，超出了 LIMIT 上限，则系统会照常计算聚合结果，但是结果中会忽略某些行。这是因为不使用 GROUP BY 子句时，是将表中的所有数据作为一组来对待的。而使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。

练习：

哪个客户（按照名称）下的订单最早？你的答案应该包含订单的客户名称和日期。
算出每个客户的总销售额（单位是美元）。答案应该包括两列：每个客户的订单总销售额（单位是美元）以及客户名称。
最近的 web_event 是通过哪个渠道发生的，与此 web_event 相关的客户是哪个？你的查询应该仅返回三个值：日期、渠道和客户名称。
算出 web_events 中每种渠道的次数。最终表格应该有两列：渠道和渠道的使用次数。
每个客户所下的最小订单是什么（以总金额（美元）为准）。答案只需两列：客户名称和总金额（美元）。从最小金额到最大金额排序。
算出每个区域的销售代表人数。最早表格应该包含两列：区域和 sales_reps 数量。从最少到最多的代表人数排序。

1
select a.name,o.occurred_at
from accounts a
join orders o
on a.id = o.account_id
order by o.occurred_at,a.name
LIMIT 1;

2
select a.name,sum(o.total_amt_usd) 
from orders o
join accounts a
on o.account_id = a.id
group by a.name;


SELECT a.name, SUM(total_amt_usd) total_sales
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.name;

3
select w.occurred_at,w.channel,a.name
from web_events w
join accounts a
on w.account_id = a.id
order by w.occurred_at
limit 1;

4
select channel,count(channel)
from web_events
group by channel;

5
SELECT a.name,min(total_amt_usd)
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.name
ORDER BY o.total_amt_usd;

6
select r.name,count(s.name) sales_reps_num
from region r
join sales_reps s
on s.region_id = r.id
group by r.name
order by sales_reps_num;

SELECT r.name, COUNT(*) num_reps
FROM region r
JOIN sales_reps s
ON r.id = s.region_id
GROUP BY r.name
ORDER BY num_reps;

(2) 主要知识点：

你可以同时按照多列分组，这样经常可以在大量不同的细分中更好地获得聚合结果。ORDER BY 条件中的列顺序有区别。你是从左到右让列排序。

1、GROUP BY 条件中的列名称顺序并不重要，结果还是一样的。如果运行相同的查询并颠倒 GROUP BY条件中列名称的顺序，可以看到结果是一样的。（不对把）
2、和 ORDER BY 一样，你可以在 GROUP BY 条件中用数字替换列名称。仅当你对大量的列分组时，或者其他原因导致 GROUP BY 条件中的文字过长时，才建议这么做。
3、SELECT 中出现的列，如果该列不在聚合函数中，必须出现在 GROUP BY 语句中。如果忘记了，可能会遇到错误。但是，即使查询可行，最后的结果可能也不会正确！

练习：

1、对于每个客户，确定他们在订单中购买的每种纸张的平均数额。结果应该有四列：客户名称一列，每种纸张类型的平均数额一列。

2、对于每个客户，确定在每个订单中针对每个纸张类型的平均消费数额。结果应该有四列：客户名称一列，每种纸张类型的平均消费数额一列。

3、确定在 web_events 表格中每个销售代表使用特定渠道的次数。最终表格应该有三列：销售代表的名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。

4、确定在 web_events 表格中针对每个地区特定渠道的使用次数。最终表格应该有三列：区域名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。

select a.name,avg(standard_qty) sq,avg(gloss_qty) gq,avg(poster_qty) pq
from accounts a
join orders o
on a.id = o.account_id
group by a.name;

select a.name,avg(standard_amt_usd) sq,avg(gloss_amt_usd) gq,avg(poster_amt_usd) pq
from accounts a
join orders o
on a.id = o.account_id
group by a.name;

SELECT s.name, w.channel, COUNT(*) num_events
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY num_events DESC;

select r.name,w.channel,count(channel) num_channel
from region r
join sales_reps s
on s.region_id = r.id
join accounts a
on a.sales_rep_id = s.id
join web_events w
on w.account_id = a.id
group by r.name,w.channel
order by r.name,num_channel DESC;

DISTINCT

可以将 DISTINCT 看做仅返回特定列的唯一值的函数。可以和聚合函数一起使用，减少重复行。

需要注意的是，在使用 DISTINCT 时，尤其是在聚合函数中使用时，会让查询速度有所减慢。

练习：

1、使用 DISTINCT 检查是否有任何客户与多个区域相关联？

SELECT DISTINCT a.id, r.id, a.name, r.name
FROM accounts a
JOIN sales_reps s
ON s.id = a.sales_rep_id
JOIN region r
ON r.id = s.region_id;

SELECT DISTINCT id, name
FROM accounts;

上面的两个查询产生了相同的行数（351 行），因此我们知道每个客户仅与一个区域相关联。如果每个客户与多个区域相关联，则第一个查询返回的行数应该比第二个查询的多。

2、有没有销售代表要处理多个客户？

SELECT s.id, s.name, COUNT(*) num_accounts
FROM accounts a
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.id, s.name
ORDER BY num_accounts;

SELECT DISTINCT id, name
FROM sales_reps;

实际上，所有销售代表都要处理多个客户。销售代表处理的最少客户数量是 3 个。有 50 个销售代表，他们都有多个客户。在第二个查询中使用 DISTINCT 确保包含了第一个查询中的所有销售代表。

HAVING

HAVING 是过滤被聚合的查询的 “整洁”方式。本质上，只要你想对通过聚合创建的查询中的元素执行 WHERE 条件，就需要使用 HAVING。

（1）有多少位销售代表需要管理超过 5 个客户？

select s.name,s.id,count(*) num_account
from sales_reps s
join accounts a
on a.sales_rep_id = s.id
group by s.name,s.id
having count(*) >5
order by num_account;

子查询的方法：

SELECT COUNT(*) num_reps_above5
FROM(SELECT s.id, s.name, COUNT(*) num_accounts
     FROM accounts a
     JOIN sales_reps s
     ON s.id = a.sales_rep_id
     GROUP BY s.id, s.name
     HAVING COUNT(*) > 5
     ORDER BY num_accounts) AS Table1;

（2）有多少个客户具有超过 20 个订单？

SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING COUNT(*) > 20
ORDER BY num_orders;

（3）有多少个客户在所有订单上消费的总额超过了 30,000 美元？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING SUM(o.total_amt_usd) > 30000
ORDER BY total_spent;

（4）哪个客户消费的最多？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_spent DESC
LIMIT 1;

（5）哪个客户使用 facebook 作为与消费者沟通的渠道超过 6 次？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name, w.channel
HAVING COUNT(*) > 6 AND w.channel = 'facebook'
ORDER BY use_of_channel;

（6）哪个客户使用 facebook 作为沟通渠道的次数最多？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE w.channel = 'facebook'
GROUP BY a.id, a.name, w.channel
ORDER BY use_of_channel DESC
LIMIT 1;

（7）哪个渠道是客户最常用的渠道？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name, w.channel
ORDER BY use_of_channel DESC
LIMIT 10;

DATE 函数

在 SQL 中，按照日期列分组通常不太实用，因为这些列可能包含小到一秒的交易数据。按照如此详细的级别保存信息即有好处，又存在不足之处，因为提供了非常准确的信息（好处），但是也让信息分组变得很难（不足之处）。幸运的是，有很多 SQL 内置函数可以帮助我们改善日期处理体验。

DATE_TRUNC

DATE_TRUNC 使你能够将日期截取到日期时间列的特定部分。常见的截取依据包括日期、月份 和 年份。

DATE_PART

DATE_PART 可以用来获取日期的特定部分，但是注意获取 month 或 dow 意味着无法让年份按顺序排列。而是按照特定的部分分组，无论它们属于哪个年份。截取年份时，它会将2016年4月到2017年4月都保留下来。

其他日期函数：https://www.postgresql.org/docs/9.1/functions-datetime.html

练习：

1、Parch & Posey 在哪一年的总销售额最高？

select DATE_TRUNC('year',occurred_at) as year,
sum(total_amt_usd) as total_amt
from orders
group by 1
order by 2 DESC;

select DATE_PART('year',occurred_at) as year,
sum(total_amt_usd) as total_amt
from orders
group by 1
order by 2 DESC;

2、Parch & Posey 在哪一个月的总销售额最高？

/*得到的是不同年份的各月份*/
select DATE_TRUNC('month',occurred_at) as year,
sum(total_amt_usd) as total_amt
from orders
group by 1
order by 2 DESC;

SELECT DATE_PART('month',occurred_at) as month,
sum(total_amt_usd) as total_amt
from orders
group by 1
order by 2 DESC;

CASE语句

1、CASE 语句始终位于 SELECT 条件中。

2、CASE 必须包含以下几个部分：WHEN、THEN 和 END。ELSE 是可选组成部分，用来包含不符合上述任一 CASE 条件的情况。
3、你可以在 WHEN 和 THEN 之间使用任何条件运算符编写任何条件语句（例如 WHERE），包括使用 AND 和 OR 连接多个条件语句。
4、你可以再次包含多个 WHEN 语句以及 ELSE 语句，以便处理任何未处理的条件。

练习：

创建一列用于将 standard_amt_usd 除以 standard_qty，以便计算每个订单的标准纸张的单价，将结果限制到前 10 个订单，并包含 id 和 account_id 字段。注意 - 如果你的答案正确，系统将显示一个错误，这是因为你除以了 0。当你在下个部分学习 CASE 语句时，你将了解如何让此查询不会报错。
我们来看看如何使用 CASE 语句来避免这一错误。

SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price
FROM orders
LIMIT 10;

现在我们使用一个 CASE 语句，这样的话，一旦 standard_qty 为 0，我们将返回 0，否则返回 unit_price。

SELECT account_id, CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0
                        ELSE standard_amt_usd/standard_qty END AS unit_price
FROM orders
LIMIT 10;

该语句的第一部分将捕获任何分母为 0 并导致错误的情况，其他部分将按照常规步骤相除。你将发现对于标准纸张，所有客户的单价是 4.99 美元。这样比较合理，不会波动，并且比在上节课中向分母上加 1 来暂时解决错误这一方法更准确。

1、我们想要根据相关的消费量了解三组不同的客户。最高的一组是终身价值（所有订单的总销售额）大于 200,000 美元的客户。第二组是在 200,000 到 100,000 美元之间的客户。最低的一组是低于 under 100,000 美元的客户。请提供一个表格，其中包含与每个客户相关的级别。你应该提供客户的名称、所有订单的总销售额和级别。消费最高的客户列在最上面。

select a.name, sum(o.total_amt_usd) total_amt,
case when sum(o.total_amt_usd) > 200000 then 'TOP'
     when sum(o.total_amt_usd) > 100000 and sum(o.total_amt_usd) < 200000 then 'MIDDLE'   
     else 'LOW' 
end as account_level
from accounts a
join orders o
on o.account_id = a.id
group by a.name
order by 2 DESC

2、现在我们想要执行和第一个问题相似的计算过程，但是我们想要获取在 2016 年和 2017 年客户的总消费数额。级别和上一个问题保持一样。消费最高的客户列在最上面。

SELECT a.name,sum(o.total_amt_usd) total_amt,
case when sum(o.total_amt_usd)>200000 then 'top'
     when sum(o.total_amt_usd)>100000 then 'middle'
     else 'low'
end as account_level
from accounts a
join orders o
on o.account_id = a.id
where DATE_PART('year',o.occurred_at) in (2016,2017)
group by a.name
order by 2 DESC;

SELECT a.name, SUM(total_amt_usd) total_spent, 
     CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
     WHEN  SUM(total_amt_usd) > 100000 THEN 'middle'
     ELSE 'low' END AS customer_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE occurred_at > '2015-12-31' 
GROUP BY 1
ORDER BY 2 DESC;

3、我们想要找出绩效最高的销售代表，也就是有超过 200 个订单的销售代表。创建一个包含以下列的表格：销售代表名称、订单总量和标为 top 或 not 的列（取决于是否拥有超过 200 个订单）。销售量最高的销售代表列在最上面。

select s.name,COUNT(o.total) total_order,
case when COUNT(o.total)>200 then 'top'
     else 'not' end as jixiao
from sales_reps s
join accounts a
on a.sales_rep_id = s.id
join orders o
on o.account_id = a.id
group by s.name
order by 2 DESC;

4、我们想要找出绩效很高的销售代表，也就是有超过 200 个订单或总销售额超过 750000 美元的销售代表。中间级别是指有超过 150 个订单或销售额超过 500000 美元的销售代表。创建一个包含以下列的表格：销售代表名称、总订单量、所有订单的总销售额，以及标为 top、middle 或 low 的列（取决于上述条件）。

SELECT s.name, COUNT(o.total) total_order, SUM(o.total_amt_usd) total_spent, 
     CASE WHEN COUNT(o.total) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top'
     WHEN COUNT(o.total) > 150 OR SUM(o.total_amt_usd) > 500000 THEN 'middle'
     ELSE 'low' END AS sales_rep_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name
ORDER BY 3 DESC;