SQL学习-2.7 SQL聚合

07 练习 SUM聚合函数

聚合问题
1.
算出 orders 表格中的 poster_qty 纸张总订单量。
SELECT SUM(poster_qty) AS order_poster_number
FROM orders
# 结果返回：
order_poster_number
723646

2.
算出 orders 表格中 standard_qty 纸张的总订单量。
SELECT SUM(standard_qty) AS order_standard_number
FROM orders
# 结果返回
order_standard_number
1938346

3.
根据 orders 表格中的 total_amt_usd 得出总销售额。
SELECT SUM(total_amt_usd) AS order_total_amt
FROM orders
# 结果返回
order_total_amt
23141511.83

4.
算出 orders 表格中每个订单在 standard 和 gloss 纸张上消费的数额。结果应该是表格中每个订单的金额。
SELECT id AS id, standard_amt_usd + gloss_amt_usd AS order_twopart_amt
FROM orders
# 解释：该问题不需要利用聚合函数，直接相加，对于每个订单进行操作

5.
每个订单的 price/standard_qty 纸张各不相同。我想得出 orders 表格中每个销售机会的这一比例。
SELECT SUM(standard_amt_usd)/SUM(standard_qty) AS standard_price_per_unit
FROM orders;
# 结果返回
standard_price_per_unit
4.9900000000000000

11.练习 MIN  MAX AVG聚合函数

问题：MIN、MAX 与 AVERAGE
1.
最早的订单下于何时？
SELECT MIN(occurred_at)
FROM web_events
# 结果返回
min
2013-12-04T04:18:29.000Z

2.
尝试执行和第一个问题一样的查询，但是不使用聚合函数。
SELECT occurred_at
FROM web_events
ORDER BY occurred_at
LIMIT 1
# 结果返回
occurred_at
2013-12-04T04:18:29.000Z

3.
最近的 web_event 发生在什么时候？
SELECT MAX(occurred_at)
FROM web_events
#结果返回
max
2017-01-01T23:51:09.000Z

4.
尝试以另一种方式执行上个问题的查询，不使用聚合函数。
SELECT occurred_at
FROM web_events
ORDER BY occurred_at DESC
LIMIT 1
#结果返回
occurred_at
2017-01-01T23:51:09.000Z

5.（该题其实就是对金额和数量进行求平均）
算出每个订单在每种纸张上消费的平均 (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;
# 结果返回
mean_standard	mean_gloss	mean_poster	mean_standard_usd	mean_gloss_usd	mean_poster_usd
280.4320023148148148	146.6685474537037037	104.6941550925925926	1399.3556915509259259	1098.5474204282407407	850.1165393518518519

6.
我相信你都渴望知道在所有订单上消费的中值 total_usd 是多少？虽然这一概念已经超出我们的范围。注意，这比我们到目前为止介绍的基本内容要高深一点，但是我们可以按照以下方式对答案进行硬编码。
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;
# 结果返回
total_amt_usd
2483.16

14.练习GROUP BY

1.
SELECT a.name a_name, w.occurred_at a_time
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
ORDER BY w.occurred_at
LIMIT 1

2.
SELECT a.name a_name,SUM(o.total_amt_usd) a_toal_amt
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a_name
# 总是容易在JOIN后面的语句，用where，不用ON ，这是错误的，一定要记住。

3.
SELECT w.occurred_at a_date,w.channel a_channel,a.name a_name
FROM accounts a 
JOIN web_events w
ON a.id = w.account_id 
ORDER BY a_date DESC
LIMIT 1
# 返回结果
a_date	                    a_channel	        a_name
2017-01-01T23:51:09.000Z	organic	Molina      Healthcare


4.
SELECT w.channel, COUNT(*)
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
GROUP BY w.channel
#结果返回
channel	count
adwords	906
direct	5298
banner	476
facebook	967
organic	952
twitter	474

5.
SELECT a.name
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
ORDER BY w.occurred_at
LIMIT 1
# 结果返回
name
DISH Network

6.
SELECT a.name a_name,MIN(o.total_amt_usd) as a_toal_amt
FROM accounts a 
JOIN orders o
ON a.id = o.account_id 
GROUP BY a_name
ORDER BY a_toal_amt
# 结果返回太多，不进行展示

7.
SELECT r.name r_name,COUNT(s.name) as sale_count
FROM region r
JOIN Sales_reps s
ON r.id = s.region_id
GROUP BY r_name
ORDER BY sale_count 
# 发现region中不同的区域仅仅有4个区域。

17.练习GROUP BY （multiple columns）

1.
SELECT a.name a_name, AVG(o.standard_qty) as stand_number,AVG(o.gloss_qty) as gloss_number, AVG(o.poster_qty) as poster_number
FROM accounts a 
JOIN orders o
ON a.id= o.account_id
GROUP BY a_name

2.
SELECT a.name a_name, AVG(o.standard_amt_usd) as stand_amt,AVG(o.gloss_amt_usd) as gloss_amt, AVG(o.poster_amt_usd) as poster_amt
FROM accounts a 
JOIN orders o
ON a.id= o.account_id
GROUP BY a_name

3.
SELECT s.name s_name,w.channel channel,COUNT(*) as count
FROM Sales_reps s
JOIN accounts a
ON s.id = a.Sales_rep