leetcode sql 经典70题总结二（聚合）

SQL的聚合为题

全局聚合： Group by
局部聚合：窗口函数

1.聚合某一个区间
2.新维度（这个维度不存在，你需要自己创造）
3.还可以求最后一个值（通过聚合结果值而不是排名序号）

聚合某一个区间

1321. Restaurant Growth

Table: Customer

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Write an SQL query to compute moving average of how much customer paid in a 7 days window (current day + 6 days before) .

The query result format is in the following example:

Return result table ordered by visited_on.

average_amount should be rounded to 2 decimal places, all dates are in the format ('YYYY-MM-DD').

Customer table:
+-------------+--------------+--------------+-------------+
| customer_id | name | visited_on | amount |
+-------------+--------------+--------------+-------------+
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
+-------------+--------------+--------------+-------------+

Result table:
+--------------+--------------+----------------+
| visited_on | amount | average_amount |
+--------------+--------------+----------------+
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
+--------------+--------------+----------------+

1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/restaurant-growth
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

窗口函数先聚合，并且在函数中选择区间

/* Write your PL/SQL query statement below */
SELECT T.*, ROUND(T.AMOUNT / 7, 2) AVERAGE_AMOUNT
  FROM (SELECT TO_CHAR(VISITED_ON, 'yyyy-mm-dd') VISITED_ON,
        SUM(AMOUNT) OVER(ORDER BY VISITED_ON, VISITED_ON ROWS BETWEEN 6 PRECEDING AND    
        CURRENT ROW)    
        AMOUNT
          FROM (SELECT VISITED_ON, SUM(AMOUNT) AMOUNT
                  FROM CUSTOMER
                 GROUP BY VISITED_ON)) T
 WHERE T.VISITED_ON >= (SELECT MIN(VISITED_ON) + 6 FROM CUSTOMER)

3.最后一个值

1204. 最后一个能进入电梯的人

有时候你的需求就是会有一个在原表中不存在的维度，这时候你需要自己创造一个维度

表: Queue

电梯最大载重量为 1000。

写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯。

查询结果如下所示 :

Queue 表
+-----------+-------------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------------+--------+------+
| 5 | George Washington | 250 | 1 |
| 3 | John Adams | 350 | 2 |
| 6 | Thomas Jefferson | 400 | 3 |
| 2 | Will Johnliams | 200 | 4 |
| 4 | Thomas Jefferson | 175 | 5 |
| 1 | James Elephant | 500 | 6 |
+-----------+-------------------+--------+------+

Result 表
+-------------------+
| person_name |
+-------------------+
| Thomas Jefferson |
+-------------------+

为了简化，Queue 表按 trun 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 将可以进入电梯,因为他们的体重和为 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一个体重合适并进入电梯的人。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/last-person-to-fit-in-the-elevator
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

/* Write your PL/SQL query statement below */
SELECT person_name
FROM Queue a
WHERE a.turn = (
    SELECT MAX(turn)
    FROM (
        SELECT a.turn, a.person_name, SUM(weight) OVER (ORDER BY turn) AS eleWeight
        FROM Queue a
    ) a
    WHERE a.eleWeight <= 1000
)

作者：wo-de-tian-bug
链接：https://leetcode-cn.com/problems/last-person-to-fit-in-the-elevator/solution/you-ren-qiang-wo-ji-fen-by-wo-de-tian-bug/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

2.新维度

1127. 用户购买平台

支出表: Spending

写一段 SQL 来查找每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

查询结果格式如下例所示：

Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1 | 2019-07-01 | mobile | 100 |
| 1 | 2019-07-01 | desktop | 100 |
| 2 | 2019-07-01 | mobile | 100 |
| 2 | 2019-07-02 | mobile | 100 |
| 3 | 2019-07-01 | desktop | 100 |
| 3 | 2019-07-02 | desktop | 100 |
+---------+------------+----------+--------+

Result table:
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop | 100 | 1 |
| 2019-07-01 | mobile | 100 | 1 |
| 2019-07-01 | both | 200 | 1 |
| 2019-07-02 | desktop | 100 | 1 |
| 2019-07-02 | mobile | 100 | 1 |
| 2019-07-02 | both | 0 | 0 |
+------------+----------+--------------+-------------+
在 2019-07-01, 用户1 同时使用桌面端和手机端购买, 用户2 仅使用了手机端购买，而用户3 仅使用了桌面端购买。
在 2019-07-02, 用户2 仅使用了手机端购买, 用户3 仅使用了桌面端购买，且没有用户同时使用桌面端和手机端购买。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/user-purchase-platform
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

代码中你必须创造一个both的维度

select temp1.spend_date, temp1.platform, 
       ifnull(temp3.total_amount, 0) total_amount, 
       ifnull(temp3.total_users,0) total_users
from
     (select distinct(spend_date), p.platform   
     from Spending,
         (select 'desktop' as platform union
          select 'mobile' as platform union
          select 'both' as platform
         ) as p 
      ) as temp1
left join 
   (select spend_date,platform, sum(amount) as total_amount, count(user_id) total_users
   from
         (select spend_date, user_id, 
             (case count(distinct platform)
             when 1 then platform
             when 2 then 'both'
             end
             ) as  platform, sum(amount) as amount
         from Spending
         group by spend_date, user_id
         ) as temp2
    group by spend_date, platform
   ) as  temp3
on temp1.platform = temp3.platform and temp1.spend_date = temp3.spend_date