COUNT 不会考虑具有 NULL 值的行
SUM 忽略 NULL 值
- 最早的订单下于何时?
SELECT MIN(occurred_at) FROM orders;
- 尝试执行和第一个问题一样的查询,但是不使用聚合函数。
SELECT occurred_at FROM orders ORDER BY occurred_at LIMIT 1;
- 最近的 web_event 发生在什么时候?
SELECT MAX(occurred_at) FROM web_events;
- 尝试以另一种方式执行上个问题的查询,不使用聚合函数。
SELECT occurred_at FROM web_events ORDER BY occurred_at DESC LIMIT 1;
- 算出每个订单在每种纸张上消费的平均 (AVERAGE) 金额,以及每个订单针对每种纸张购买的平均数量。最终答案应该有 6 个值,每个纸张类型平均销量对应一个值,以及平均数量对应一个值。
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;
- 看过视频后,你可能对如何计算中位数感兴趣。虽然这已经超出了目前我们所学的范围,但请尝试探索这个问题:对于所有订单(orders)数据,其total_usd字段的中位数是多少?请注意,构建一个此问题的通用解决方案已经超出了目前所学的课程范围,但我们可以硬写出以下这段代码:
因为订单一共有6912个,因此我们需要第3456和第3457个订单(按total_amt_usd排序)的total_amt_usd字段的平均值。这样就能得出中位数结果,为2482.855。这显然不是一个好办法。如果我们有了新订单,再次计算时就必须修改LIMIT。SQL实际上并不会为我们计算中位数。以上代码使用了一个子查询(SUBQUERY),但你可以使用任何方法找到需要的两个值,然后再求平均即可得到中位数。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 可以用来在数据子集中聚合数据。例如,不同客户、不同区域或不同销售代表分组。
-
SELECT 语句中的任何一列如果不在聚合函数中,则必须在 GROUP BY 条件中。
-
GROUP BY 始终在 WHERE 和 ORDER BY 之间。
-
ORDER BY 有点像电子表格软件中的 SORT。
在深入了解如何使用 GROUP BY 语句聚合函数之前,需要注意的是,SQL 在 LIMIT 条件之前评估聚合函数。如果不按任何列分组,则结果是 1 行,没有问题。如果按照某列分组,该列中存在大量的唯一值,超出了 LIMIT 上限,则系统会照常计算聚合结果,但是结果中会忽略某些行。
这实际上是比较不错的方式,因为你知道你将获得正确的聚合结果。如果 SQL 将表格裁剪到 100 行,然后进行聚合,结果将完全不同。上述查询的结果超过了 100 行,因此是个很好的示例。使用该 SQL 表格并尝试删掉 LIMIT,然后再次运行查询,看看有哪些变化。
- 哪个客户(按照名称)下的订单最早?你的答案应该包含订单的客户名称和日期。
SELECT a.name, o.occurred_at FROM accounts a JOIN orders o ON a.id = o.account_id ORDER BY occurred_at LIMIT 1;
- 算出每个客户的总销售额(单位是美元)。答案应该包括两列:每个公司的订单总销售额(单位是美元)以及公司名称。
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;
- 最近的 web_event 是通过哪个渠道发生的,与此 web_event 相关的客户是哪个?你的查询应该仅返回三个值:日期、渠道和客户名称。
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 DESC LIMIT 1;
- 算出 web_events 中每种渠道的次数。最终表格应该有两列:渠道和渠道的使用次数。
SELECT w.channel, COUNT(*) FROM web_events w JOIN accounts a ON a.id = w.account_id GROUP BY w.channel
- 与最早的 web_event 相关的主要联系人是谁?
SELECT a.primary_poc FROM web_events w JOIN accounts a ON a.id = w.account_id ORDER BY w.occurred_at LIMIT 1;
- 每个客户所下的最小订单是什么(以总金额(美元)为准)。答案只需两列:客户名称和总金额(美元)。从最小金额到最大金额排序。
奇怪的是,很多订单没有美元金额。我们可能需要检查下这些订单。SELECT a.name, MIN(total_amt_usd) smallest_order FROM accounts a JOIN orders o ON a.id = o.account_id GROUP BY a.name ORDER BY smallest_order;
- 算出每个区域的销售代表人数。最终表格应该包含两列:区域和 sales_reps 数量。从最少到最多的代表人数排序。
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;
- 对于每个客户,确定他们在订单中购买的每种纸张的平均数额。结果应该有四列:客户名称一列,每种纸张类型的平均数额一列。
SELECT a.name, AVG(o.standard_qty) avg_stand, AVG(gloss_qty) avg_gloss, AVG(poster_qty) avg_post FROM accounts a JOIN orders o ON a.id = o.account_id GROUP BY a.name;
- 对于每个客户,确定在每个订单中针对每个纸张类型的平均消费数额。结果应该有四列:客户名称一列,每种纸张类型的平均消费数额一列。
SELECT a.name, AVG(o.standard_amt_usd) avg_stand, AVG(gloss_amt_usd) avg_gloss, AVG(poster_amt_usd) avg_post FROM accounts a JOIN orders o ON a.id = o.account_id GROUP BY a.name;
- 确定在 web_events 表格中每个销售代表使用特定渠道的次数。最终表格应该有三列:销售代表的名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。
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;
- 确定在 web_events 表格中针对每个地区特定渠道的使用次数。最终表格应该有三列:区域名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。
SELECT r.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 JOIN region r ON r.id = s.region_id GROUP BY r.name, w.channel ORDER BY num_events DESC;
-
使用 DISTINCT 检查是否有任何客户与多个区域相关联?
下面的两个查询产生了相同的行数(351 行),因此我们知道每个客户仅与一个区域相关联。如果每个客户与多个区域相关联,则第一个查询返回的行数应该比第二个查询的多。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;
and
SELECT DISTINCT id, name FROM accounts;
-
有没有销售代表要处理多个客户?
实际上,所有销售代表都要处理多个客户。销售代表处理的最少客户数量是 3 个。有 50 个销售代表,他们都有多个客户。在第二个查询中使用 DISTINCT 确保包含了第一个查询中的所有销售代表。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;
and
SELECT DISTINCT id, name FROM sales_reps;
HAVING 是过滤被聚合的查询的 “整洁”方式,但是通常采用子查询的方式来实现。本质上,只要你想对通过聚合创建的查询中的元素执行 WHERE 条件,就需要使用 HAVING。
-
有多少位销售代表需要管理超过 5 个客户?
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;
实际上,我们可以使用 SUBQUERY 获得这一结果,如下所示。其他查询也可以使用这一逻辑,下面就不显示了。
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;
-
有多少个客户具有超过 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;
-
哪个客户的订单最多?
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 ORDER BY num_orders DESC LIMIT 1;
-
有多少个客户在所有订单上消费的总额超过了 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;
-
有多少个客户在所有订单上消费的总额不到 1,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) < 1000 ORDER BY total_spent;
-
哪个客户消费的最多?
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;
-
哪个客户消费的最少?
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 LIMIT 1;
-
哪个客户使用
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;
-
哪个客户使用
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;
- 哪个渠道是客户最常用的渠道?
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_TRUNC 使你能够将日期截取到日期时间列的特定部分。常见的截取依据包括日期
、月份
和 年份
。。
DATE_PART 可以用来获取日期的特定部分,但是注意获取 month
或 dow
意味着无法让年份按顺序排列。而是按照特定的部分分组,无论它们属于哪个年份。
处理日期
-
Parch & Posey 在哪一年的总销售额最高?数据集中的所有年份保持均匀分布吗?
SELECT DATE_PART('year', occurred_at) ord_year, SUM(total_amt_usd) total_spent FROM orders GROUP BY 1 ORDER BY 2 DESC;
对于
2013
年和2017
年来说,每一年只有一个月的销量(2013 年为 12,2017 年为 1)。 因此,二者都不是均匀分布。销量一年比一年高,2016 年是到目前为止最高的一年。按照这个速度,我们预计 2017 年可能是最高销量的一年。
-
Parch & Posey 在哪一个月的总销售额最高?数据集中的所有月份保持均匀分布吗?
为了保持公平,我们应该删掉 2013 年和 2017 年的销量。原因如上。SELECT DATE_PART('month', occurred_at) ord_month, SUM(total_amt_usd) total_spent FROM orders WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01' GROUP BY 1 ORDER BY 2 DESC;
12 月的销量最高。
-
Parch & Posey 在哪一年的总订单量最多?数据集中的所有年份保持均匀分布吗?
SELECT DATE_PART('year', occurred_at) ord_year, COUNT(*) total_sales FROM orders GROUP BY 1 ORDER BY 2 DESC;
同样,到目前为止,2016 年的订单量最多,但是与数据集中的其他年份相比,2013 年和 2017 年的分布不均匀。
- Parch & Posey 在哪一个月的总订单量最多?数据集中的所有年份保持均匀分布吗?
12 月依然是销量最多的月份,但是有趣的是,11 月是销量第二多的月份。为了保持公平,删掉了 2017 年和 2013 年的数据。SELECT DATE_PART('month', occurred_at) ord_month, COUNT(*) total_sales FROM orders WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01' GROUP BY 1 ORDER BY 2 DESC;
Walmart
在哪一年的哪一个月在铜版纸上的消费最多?
在 2016 年 5 月,Walmart 在铜版纸上的消费做多。SELECT DATE_TRUNC('month', o.occurred_at) ord_date, SUM(o.gloss_amt_usd) tot_spent FROM orders o JOIN accounts a ON a.id = o.account_id WHERE a.name = 'Walmart' GROUP BY 1 ORDER BY 2 DESC LIMIT 1;
GROUP BY 1,2,3 数字表示按哪一列排序
- CASE 语句始终位于 SELECT 条件中。
- CASE 必须包含以下几个部分:WHEN、THEN 和 END。ELSE 是可选组成部分,用来包含不符合上述任一 CASE 条件的情况。
- 你可以在 WHEN 和 THEN 之间使用任何条件运算符编写任何条件语句(例如 WHERE),包括使用 AND 和 OR 连接多个条件语句。
- 你可以再次包含多个 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 来暂时解决错误这一方法更准确。
方案:CASE
-
我们想要根据相关的购买量了解三组不同的客户。最高的一组是终身价值(所有订单的总销售额)
大于 200,000
美元的客户。第二组是在200,000 到 100,000
美元之间的客户。最低的一组是低于under 100,000
美元的客户。请提供一个表格,其中包含与每个客户相关的级别。你应该提供客户的名称、所有订单的总销售额和级别。消费最高的客户列在最上面。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 GROUP BY a.name ORDER BY 2 DESC;
-
现在我们想要执行和第一个问题相似的计算过程,但是我们想要获取在
2016
年和2017
年客户的总消费数额。级别和上一个问题保持一样。消费最高的客户列在最上面。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;
- 我们想要找出绩效最高的销售代表,也就是有超过 200 个订单的销售代表。创建一个包含以下列的表格:销售代表名称、订单总量和标为
top
或not
的列(取决于是否拥有超过 200 个订单)。销售量最高的销售代表列在最上面。
值得注意的是,上述语句假定每个名称是唯一的,好几次都是这么假定的。否则需要根据名称和 ID 拆分表格。SELECT s.name, COUNT(*) num_ords, CASE WHEN COUNT(*) > 200 THEN 'top' ELSE 'not' 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 2 DESC;
- 之前的问题没有考虑中间水平的销售代表或销售额。管理层决定也要看看这些数据。我们想要找出绩效很高的销售代表,也就是有超过
200
个订单或总销售额超过750000
美元的销售代表。中间
级别是指有超过 150 个订单或销售额超过500000
美元的销售代表。创建一个包含以下列的表格:销售代表名称、总订单量、所有订单的总销售额,以及标为top
、middle
或low
的列(取决于上述条件)。在最终表格中将销售额最高的销售代表列在最上面。SELECT s.name, COUNT(*), SUM(o.total_amt_usd) total_spent, CASE WHEN COUNT(*) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top' WHEN COUNT(*) > 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;