【用于数据分析的 SQL（三）】【SQL 聚合】

一、 前言

将学习如何使用SUM、AVG和COUNT等SQL功能聚合数据，还有CASE、日期和HAVE功能。

二、 NULL

NULL是一种数据类型，可指定 SQL 中不存在数据的位置。

三、 COUNT

计算表中的行数。

查找帐户表中所有行

SELECT COUNT(*)
FROM accounts;

选择一个列的行数

SELECT COUNT(accounts.id)
FROM accounts;

COUNT不考虑具有空值的行。

四、 SUM

与计数不同，只能在数字列上使用SUM，SUM将忽略 NULL 值并视为0。

聚合器仅垂直聚合 - 列的值

在订单表中查找订购的poster_qty纸总量。

SELECT SUM(poster_qty) AS total_poster_sales
FROM orders;

五、 MAX、MIN和AVG

1.最早的订单是什么时候下的？您只需要返回日期。

SELECT MIN(occurred_at) 
FROM orders;

2.尝试执行与问题 1 相同的查询，而无需使用聚合功能。

SELECT occurred_at 
FROM orders 
ORDER BY occurred_at
LIMIT 1;

3.最近的（最新的）web_event是什么时候发生的？

SELECT MAX(occurred_at)
FROM web_events;

4.尝试在不使用聚合功能的情况下执行前一个查询的结果。

SELECT occurred_at
FROM web_events
ORDER BY occurred_at DESC
LIMIT 1;

5.查找每个纸张类型上每个订单的平均（平均）金额，以及每个订单购买的每个纸张类型的平均金额。您的最终答案应有 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;

六、 GROUP BY

GROUP BY可用于在数据子集内聚合数据。例如，为不同的帐户、不同的区域或不同的销售代表分组。

SELECT语句中不在聚合器内的任何列必须包含在 GROUP BY条款中。

1.哪个帐户（按名称）下了最早的订单？您的解决方案应具有帐户名称和订单日期。

SELECT a.name, o.occurred_at
FROM accounts a
JOIN orders o
ON a.id = o.account_id
ORDER BY occurred_at
LIMIT 1;

2.查找每个帐户的总销售额。您应该包括两个列 - 每家公司的订单总销售额以美元和公司名称。

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.通过哪个渠道发生了最近的（最新的）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;

4.查找使用web_events的每个类型的通道的总次数。您的最终表应有两个列 -通道和使用频道的次数。

SELECT w.channel, COUNT(*)
FROM web_events w
GROUP BY w.channel

5.按美元总额计算，每个账户下的订单最小。只提供两列 - 帐户名称和总美元。从最小的美元订购量最大。

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;

6.对于每个帐户，确定他们在订单中购买的每种类型的纸张的平均金额。您的结果应有四个列 - 一个用于帐户名称，一个用于每个帐户的每个纸张类型购买的平均数量。

SELECT a.name, AVG(o.standard_qty) avg_stand, AVG(o.gloss_qty) avg_gloss, AVG(o.poster_qty) avg_post
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name;

7.确定每个销售代表在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;

七、 DISTINCT

在SELECT语句中始终使用DISTINCT，SELECT语句中写的所有列提供了唯一的行。

使用"与众不同"来测试是否有与多个区域相关的帐户。

SELECT DISTINCT id, name
FROM accounts;

使用DISTINCT，特别是在聚合中，可以显著降低您的查询速度。

八、 HAVING

HAVING是过滤已聚合的查询的方法，但这也是通常使用子查询完成的。从本质上讲，每当您想要在由聚合创建的查询元素上执行时，您都需要使用HAVING

1.有多少销售代表拥有超过 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;

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.哪个帐户在我们身边花费最多？

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;

九、 日期

DATE_TRUNC允许您将日期截断到日期时间列的特定部分。

DATE_PART可用于提取日期的特定部分，但通知拉取或一周中的某一天（）意味着您不再保持年份井然有序。相反，无论某些组件属于哪一年，您都会对它们进行分组

1.以每笔订单的总金额计算，从最大订单到最低订单的销售额。您是否注意到年度销售总额的任何趋势？

 SELECT DATE_PART('year', occurred_at) ord_year,  SUM(total_amt_usd) total_spent
 FROM orders
 GROUP BY 1
 ORDER BY 2 DESC;

2.就订单总数而言，哪一年的销售额最大？所有年数均均由数据集表示吗？

SELECT DATE_PART('year', occurred_at) ord_year,  COUNT(*) total_sales
FROM orders
GROUP BY 1
ORDER BY 2 DESC;

十、 CASE

案例陈述始终在"选择"条款中。

案例必须包括以下组件：时间、时间和结束。ELSE 是一个可选组件，可捕获不符合任何其他以前的 CASE 条件的案例。

您可以使用任何有条件的操作员（如何时何地）进行任何有条件的声明。这包括使用 AND 和 OR 将多个有条件的语句串在一起。

您可以包括多个"当"语句，以及 ELSE 语句，以处理任何未解决的条件。

1.根据订单是 3000 美元或更多，还是低于 3000 美元，编写查询以显示每个订单、帐户 ID、订单总数和订单级别（“大"或"小”）。

SELECT account_id, total_amt_usd,
CASE WHEN total_amt_usd > 3000 THEN 'Large'
ELSE 'Small' END AS order_level
FROM orders;

2.根据每个订单的项目数量，编写查询以显示三个类别中每个类别的订单数量。这三类是：“至少2000年”，“1000至2000年之间"和"少于1000”。

SELECT CASE WHEN total >= 2000 THEN 'At Least 2000'
   WHEN total >= 1000 AND total < 2000 THEN 'Between 1000 and 2000'
   ELSE 'Less than 1000' END AS order_category,
COUNT(*) AS order_count
FROM orders
GROUP BY 1;

3.我们想找出表现最好的销售代表，他们是与200多个订单相关的销售代表。创建一个表与销售代表的名称，订单总数，和一个列，或取决于他们是否有超过200个订单。将顶级销售人员放在最后一张表格中。值得一提的是，这假定每个名字都是独一无二的

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;