【笔记】SQL——聚合

前期探索：

NULL
NULL指的是没有数据，与0或者空格不同
用法：与WHERE 和 IS 搭配（因为NULL并不是一个值）
例：

SELECT *
FROM orders
WHERE primary_poc IS NULL        # WHERE primary_poc IS NOT NULL

COUNT

COUNT指的是某一特定列有多少行

例：计算数据集共有多少行（结果只返回一行）

SELECT COUNT(*) AS order_count      #当*中填入某一特定列的名称时，计算的该列非空记录的数量
   FROM demo.orders                 #与所有行数相比，可以知道’ **NULL** ’的数量
WHERE occurred_at >= ' 2016-12-01'
      AND occurred_at < ' 2017-01-01'

SUM

SUM指对某一特定列的所有值进行求和
需要说明具体列的名称，而不能用 *
SUM会将空值当做0来处置。
例：
算出 orders 表格中的 poster_qty 纸张总订单量。

SELECT SUM(poster_qty) AS total_poster_sales
FROM orders;

算出 orders 表格中每个订单在 standard 和 gloss 纸张上消费的数额。结果应该是表格中每个订单的金额。

SELECT standard_qty + gloss_qty AS total_standard_gloss
FROM orders;

每个订单的 price/standard_qty 纸张各不相同。得出 orders 表格中每个销售机会的这一比例。

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

MIN and MAX

MIN和MAX返回某一特定列的最低和最高的值
结果只有一行
例：
最早的订单下于何时？（ORDER BY也可以实现）

SELECT MIN(occurred_at) 
FROM orders;

最近的 web_event 发生在什么时候？

SELECT MAX(occurred_at)
FROM web_events;

AVG

AVG计算某一特定列中所有值的平均值
结果只有一行
例：
算出每个订单在每种纸张上消费的平均 (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;

GROUP BY

通过GROUP BY创建分组，在聚合时相互独立
GROUP BY可以只抓取单个账户的数据之和，而不是整个数据集
GROUP BY 永远处于WHERE(如果有)和ORDER BY 之间
例：
算出每个客户的总销售额（单位是美元）。

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

算出 web_events 中每种渠道的次数。最终表格应该有两列：渠道和渠道的使用次数。

SELECT w.channel, COUNT(*)
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
GROUP BY w.channel

每个客户所下的最小订单是什么（以总金额（美元）为准）。答案只需两列：客户名称和总金额（美元）。从最小金额到最大金额排序。

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;

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

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;

确定在 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

对几列内容进行分组，但不包括任何 聚合，可以用 DISTINCT 代替
例：
有没有销售代表要处理多个客户？
实际上，所有销售代表都要处理多个客户。销售代表处理的最少客户数量是 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
#等价于
SELECT DISTINCT id, name
FROM sales_reps;

HAVING

由于WHERE不能用于过滤聚合列（比如SUM等），聚合列则需要用到HAVING
HAVING永远出现在GROUP BY 的后面
例：
有多少位销售代表需要管理超过 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;

有多少个客户在所有订单上消费的总额超过了 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;

DATE

DATE_TRUNC
按照不同的细致度进行分类，例如：‘second’、‘day’、‘week’、‘month’、‘quarter’、‘year’，
来得到不同的截断。
例：
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;

DATE_PART
DATE_PART函数不能区分4月份是2016年的4月份还是2017年的4月份；也不能确定周一是哪个星期的周一，若求和（SUM）则是所有星期一（‘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;

Parch & Posey 在哪一个月的总销售额最高？数据集中的所有月份保持均匀分布吗？
ps：为了保持公平，我们应该删掉 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; 

Parch & Posey 在哪一年的总订单量最多？数据集中的所有年份保持均匀分布吗？

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

Parch & Posey 在哪一个月的总订单量最多？数据集中的所有年份保持均匀分布吗？

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; 

CASE

CASE是SQL中的IF-THEN逻辑的方式，表达为CASE WHEN-THEN-END。

例：
我们想要根据相关的消费量了解三组不同的客户。最高的一组是终身价值（所有订单的总销售额）大于 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 个订单）。销售量最高的销售代表列在最上面。

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;