leetcode sql 經(jīng)典70題總結(jié)二（聚合）

SQL的聚合為題

全局聚合： Group by
局部聚合：窗口函數(shù)

  • 1.聚合某一個區(qū)間
  • 2.新維度（這個維度不存在，你需要自己創(chuàng)造）
  • 3.還可以求最后一個值（通過聚合結(jié)果值而不是排名序號）
聚合某一個區(qū)間

1321. Restaurant Growth

Table: Customer

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| visited_on | date |
| amount | int |
+---------------+---------+
(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) have visited the restaurant.
amount is the total paid by a 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
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。

窗口函數(shù)先聚合，并且在函數(shù)中選擇區(qū)間

/* 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. 最后一個能進(jìn)入電梯的人

有時(shí)候你的需求就是會有一個在原表中不存在的維度，這時(shí)候你需要自己創(chuàng)造一個維度

表: Queue

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| person_id | int |
| person_name | varchar |
| weight | int |
| turn | int |
+-------------+---------+
person_id 是這個表的主鍵。
該表展示了所有等待電梯的人的信息。
表中 person_id 和 turn 列將包含從 1 到 n 的所有數(shù)字，其中 n 是表中的行數(shù)。

電梯最大載重量為 1000。

寫一條 SQL 查詢語句查找最后一個能進(jìn)入電梯且不超過重量限制的 person_name 。題目確保隊(duì)列中第一位的人可以進(jìn)入電梯 。

查詢結(jié)果如下所示 :

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) 將可以進(jìn)入電梯,因?yàn)樗麄兊捏w重和為 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一個體重合適并進(jìn)入電梯的人。

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/last-person-to-fit-in-the-elevator
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。

/* 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）
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
2.新維度

1127. 用戶購買平臺

支出表: Spending

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| spend_date | date |
| platform | enum |
| amount | int |
+-------------+---------+
這張表記錄了用戶在一個在線購物網(wǎng)站的支出歷史，該在線購物平臺同時(shí)擁有桌面端（'desktop'）和手機(jī)端（'mobile'）的應(yīng)用程序。
這張表的主鍵是 (user_id, spend_date, platform)。
平臺列 platform 是一種 ENUM ，類型為（'desktop', 'mobile'）。

寫一段 SQL 來查找每天 僅 使用手機(jī)端用戶、僅 使用桌面端用戶和 同時(shí) 使用桌面端和手機(jī)端的用戶人數(shù)和總支出金額。

查詢結(jié)果格式如下例所示：

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 同時(shí) 使用桌面端和手機(jī)端購買, 用戶2 僅 使用了手機(jī)端購買，而用戶3 僅 使用了桌面端購買。
在 2019-07-02, 用戶2 僅 使用了手機(jī)端購買, 用戶3 僅 使用了桌面端購買，且沒有用戶 同時(shí) 使用桌面端和手機(jī)端購買。

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/user-purchase-platform
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。

代碼中你必須創(chuàng)造一個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
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容