03 -数据分析与SQL- Lesson3 SQL 聚合

03 数据分析与SQL Lesson3 SQL 聚合

1.视频:聚合简介(SQL AGGREGATIONS)

当数据很多的时候,对数据的聚合进行观察就是很必要的了(但看一行数据太多了,所以要看更大颗粒度的进行决策)。

  • Count: Counts how many rows are in a particular column
  • Sum: Add all values in a particular column
  • Min and Max: Returns the lowest and highest values in a particular column
  • Average: Calculates the average of all the values in a particular column
  • 注意:聚合的操作都是针对列的

3.视频:NULL 和聚合

这节主要讲的是 NULL 的由来,和怎么使用 WHERE 对 NULL 进行过滤(在执行 LEFT JOIN 或 RIGHT JOIN 时,NULL 经常会发生。你在上节课见到了,左侧表格中的某些行在做连接时与右侧表格中的行如果不匹配,这些行在结果集中就会包含一些 NULL 值):

WHERE primary_poc IS NULL
WHERE primary_poc IS NOT NULL

4.视频 + 文本:第一个聚合函数 - COUNT

这样使用:

SELECT COUNT(*) 
FROM accounts;

SELECT COUNT(accounts.id) 
FROM accounts;

6.视频:SUM

像 SUM() 这种聚合,只能对数值的列使用,在做数值计算时 NULL 将被忽略。如果是对行做聚合的话,使用简单算术表达式就可以了。/简单算术表达式/

9.视频:MIN 与 MAX

MIN 和 MAX 与 COUNT 相似,它们都可以用在非数字列上。MIN 将返回最小的数字、最早的日期或按字母表排序的最之前的非数字值,具体取决于列类型。MAX 则正好相反,返回的是最大的数字、最近的日期,或与“Z”最接近(按字母表顺序排列)的非数字值。同时 MIN 和 MAX 也会忽略 NULL值。

10.视频:AVG

如果你想将 NULL 当做零,则需要使用 SUM 和 COUNT。但是,如果 NULL 值真的只是代表单元格的未知值,那么这么做可能不太合适(可能需要额外的数据清理)。这里要注意 AVG 一样是不计算 NULL 的,所以和 SUM / COUNT 的值可能会不同。

12.解决方案: MIN、MAX 与 AVG

等价处理

其实使用 MIN 可以等价于输出第一个元素,以下这两个是等价的(不过处理时间不同),后面尝试执行和第一个问题一样的查询,但是不使用聚合函数:

SELECT MIN(occurred_at) 
FROM orders;

SELECT occurred_at 
FROM orders 
ORDER BY occurred_at
LIMIT 1;

中位数

SQL没有特殊求解中位数的功能,如果要取中位数,需要手动指定 LIMIT 完成:

  • 因为订单一共有6912个,因此我们需要第3456和第3457个订单
  • 所以使用一个子查询(SUBQUERY)输出3457个
  • 再对这3457个进行倒序排列
  • 最后 LIMIT 2 就是输出 3457 和 3456 的值
  • 最后对这两个数取平均数就得出了结果2482.855(这一步例子中没做)
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;

13.视频:GROUP BY(分组)

通过GROUP BY可以创建在聚合时相互独立的分组(Group By:Allows creatingsegments that will aggregate independent from one another)。可以抓取单个账户的数据之和而不是整个数据集

  • GROUP BY 可以用来在数据子集中聚合数据。例如,不同客户、不同区域或不同销售代表分组。
  • SELECT 语句中的任何一列如果不在聚合函数中,则必须在 GROUP BY 条件中。(否则会报错,因为这列和其他已经聚合的列不对应,比如课程例子中的 account_id )
  • GROUP BY 始终在 WHERE 和 ORDER BY 之间。
  • ORDER BY 有点像电子表格软件中的 SORT。

15.解决方案:GROUP BY

在这节的练习中的一个判断难点是,何时使用某个聚合函数或其他 SQL 功能最简单:

  • 哪个客户(按照名称)下的订单最早?你的答案应该包含订单的客户名称和日期。
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;

16.视频: GROUP BY(第二部分)(多列分组和顺序问题)

这节对 GROUP BY 做了扩展:

  • 你可以同时按照多列分组,正如此处所显示的那样。这样经常可以在大量不同的细分中更好地获得聚合结果。
  • ORDER BY 条件中列出的列顺序有区别。你是从左到右让列排序。
  • GROUP BY 条件中的列名称顺序并不重要,结果还是一样的。如果运行相同的查询并颠倒 GROUP BY 条件中列名称的顺序,可以看到结果是一样的。
  • 和 ORDER BY 一样,你可以在 GROUP BY 条件中用数字替换列名称。仅当你对大量的列分组时,或者其他原因导致 GROUP BY 条件中的文字过长时,才建议这么做。

18.解决方案:GROUP BY(第二部分)

  • 对于每个客户,确定他们在订单中购买的每种纸张的平均数额。结果应该有四列:客户名称一列,每种纸张类型的平均数额一列。
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 表格中针对每个地区特定渠道的使用次数。最终表格应该有三列:区域名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。
    • 此处注意 ON 的写法,左边为上面 JOIN 中的主键,右边为其他表格的主键。
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;

19.视频: DISTINCT

你可以将 DISTINCT 看做仅返回特定列的唯一值的函数。需要注意的是,在使用 DISTINCT 时,尤其是在聚合函数中使用时,会让查询速度有所减慢。

21.解决方案:DISTINCT

  • 使用 DISTINCT 检查是否有任何客户与多个区域相关联?下面的两个查询产生了相同的行数(351 行),因此我们知道每个客户仅与一个区域相关联。如果每个客户与多个区域相关联,则第一个查询返回的行数应该比第二个查询的多。
    • regin 表中的 id 值为 1-4,对应的是4个区域
    • 在使用JOIN的时候,要对不同表同名的列做区分处理,如果不加别名,后面相同名字的会覆盖前面的显示,但其实筛选还是按照两个不同表的同名列完成的(这种情况选择*也不能避免)
    • 为了避免上述的问题,后面的解决方案定义了别名
SELECT DISTINCT a.id, r.id rid, a.name, r.name rname
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;
  • 有没有销售代表要处理多个客户?实际上,所有销售代表都要处理多个客户。销售代表处理的最少客户数量是 3 个。有 50 个销售代表,他们都有多个客户。在第二个查询中使用 DISTINCT 确保包含了第一个查询中的所有销售代表。
    • 如果不对 COUNT(*)做别名,默认别名是 count
    • GROUP BY 要同时有 s.id, s.name 因为在第一行是聚合的用法
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;

ps:/本地环境 DB.Browser for SQLite//sqlite/

22.视频:HAVING

HAVING 是过滤被聚合的查询的 “整洁”方式,但是通常采用子查询的方式来实现。本质上,只要你想对通过聚合创建的查询中的元素执行 WHERE 条件,就需要使用 HAVING。(就是说想对聚合的 SELECT 元素做类似 WHERE 的筛选时,要用 HAVING 实现)。顺序上的区别是:

  • WHERE 出现在 FROM,JOIN,ON 之后,但是在 GROUP BY 之前。
  • HAVING 出现在 GROUP BY 之后,但是在 ORDER BY 之前。

24.解决方案: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;
  • 上面的代码是筛选出有多少个销售代表达到了要求(实际上是34个)。如果只想看数字,我们可以使用 SUBQUERY 方式直接得到34的结果,如下所示:
    • 首先是把上面的代码用 FROM()封装
    • 注意使用 SUBQUERY 时,要在最后增加 AS 别名,否则会报错
    • 因为第一行是 SELECT COUNT(*) 所以会给出34的答案
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;
  • 哪个客户的订单最多?
    • 这里只对客户排序,取出降序的第一个
    • 不对聚合值进行判断所以不用 HAVING
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 美元?
    • 也可以对其他的 JOIN 表中的列做 HAVING 筛选
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;
  • 哪个客户使用 facebook 作为沟通渠道的次数最多?
    • 注意 GROUP BY 可以有多个语句
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;

25.视频: DATE 函数

在 SQL 中,按照日期列分组通常不太实用,因为这些列可能包含小到一秒的交易数据。按照如此详细的级别保存信息即有好处,又存在不足之处,因为提供了非常准确的信息(好处),但是也让信息分组变得很难(不足之处)。数据库的日期存储是 YYYY-MM-DD 方式,方便进行检索:
在这里插入图片描述
附加参考资料

26.视频: DATE 函数 II(DATE_TRUNC)

实际上,要想统计一天发生了多少次,实际是将日期的数据都归为 00:00:00

在这里插入图片描述

本节主要使用的是 DATA_TRUNC 和 DATA_PART(如果拆除日期,无法分开页)

28.解决方案:DATE 函数

  • Parch & Posey 在哪一年的总销售额最高?数据集中的所有年份保持均匀分布吗?
    • 使用 DATE_PART 解析出 year
    • 如果不加别名则会命名为 data_part
    • 注意这里 GROUP BY 的方式是选中列
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 年的销量。原因是月份数据不全
    • 使用 GROUP BY 聚合了1-12月的销量
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; 
  • Walmart 在哪一年的哪一个月在铜版纸上的消费最多?
    • 注意 DATE_TRUNC 需要出现在 GROUP BY 中(因为也是聚合函数)
    • TRUNC 有截断的意思,就是将参数(比如 month)设置之后的细节全部归为一样
    • 接下来就可以使用分组信息了
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;

29.视频: CASE 语句

派生列:Derive:Take data from existing columns and modify them。之前使用的是列之间的直接运算,这里主要学习 CASE 语句(处理 IF THEN 逻辑的方式)。

  • CASE 语句始终位于 SELECT 条件中。
  • CASE 必须包含以下几个部分:WHEN、THEN 和 END。ELSE 是可选组成部分,用来包含不符合上述任一 CASE 条件的情况。
  • 你可以在 WHEN 和 THEN 之间使用任何条件运算符编写任何条件语句(例如 WHERE),包括使用 AND 和 OR 连接多个条件语句。
  • 你可以再次包含多个 WHEN 语句以及 ELSE 语句,以便处理任何未处理的条件。

大家还记得之前那个用 +0.01 避免除零的情况发生,这里就可以用 CASE 的方式解决了:

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;

30.视频:CASE 与聚合

这里主要介绍了 CASE 和 WHERE 的区别:CASE 可以输出多个条件的结果,而 WHERE 只能输出一种:使用 WHERE 条件获取相同的信息意味着一次只能从 CASE 中获取一组数据。像这样将数据分成几列有一些优势,这取决于你要执行的操作。但通常,这种级别的划分可能使用其他编程语言更简单,而不是使用 SQL。

32.解决方案:CASE

我们想要根据相关的购买量了解三组不同的客户。最高的一组是终身价值(所有订单的总销售额)大于 200,000 美元的客户。第二组是在 200,000 到 100,000 美元之间的客户。最低的一组是低于 under 100,000 美元的客户。请提供一个表格,其中包含与每个客户相关的级别。你应该提供客户的名称、所有订单的总销售额和级别。消费最高的客户列在最上面。
- 注意第二个 WHEN 并没有闭合范围,在上一节的课程中,老师建议的是每个 WHEN 都是闭合的更加规范
- 可以后面加一个 AND (total_amt_usd) <= 200000

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 个订单或总销售额超过 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;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值