day4 體育館的人流

uccs發表於2022-01-28

本題使用的是 MySQL8.0，沒有在 MySQL5.6 版本中測驗過，不保證正確。

題目

題目來源：體育館的人流

查詢出每行人數大於 100 且 id 連續的三行或者更多好記錄

create table stadium (
    id int,
    visit_date date,
    people int
)

insert into stadium values
(1, '2017-01-01', 10),
(2, '2017-01-02', 109),
(3, '2017-01-03', 150),
(4, '2017-01-04', 99),
(5, '2017-01-05', 145),
(6, '2017-01-06', 1455),
(7, '2017-01-07', 199),
(8, '2017-01-09', 188);

SQL：方法一

select * from (
    select *, count(*) over(partition by diff) as counts from (
        select *, id - row_number() over(order by id) as diff from stadium
        where people >= 100
    ) as base
) as s where counts >= 3;

解析

問題的關鍵在於如何知道哪幾天的 id 是連續的。

這裡用的方法是先篩選出 peope 大於等於 100 的資料，然後對這些資料進行排名，在用 id 減去排名，如果他們之間的差一樣，就說明那幾天是連續的。

具體步驟：

  • 先篩選出 people 大於等於 100 的資料
  • 使用 row_number() 對 id 計算出排名
  • 在用 id 減去排名，計算出 id 和排名之間的差（作為臨時表 base）

  • 對 base 進行查詢並按照 diff 進行分組，命名為 counts（作為臨時表 s）

    • 這裡使用 over(partition by diff) 比 group by 更加準確。因為 group by 需要和 select 欄位一一對應。
  • 對 s 表進行查詢，篩選出 counts 大於等於 3 的資料

SQL：方法二

select * from (
    select *, (id - (@rrk:=@rrk + 1)) as diff
    from stadium, (select @rrk:=0) as init where people >= 100
) as s1 where diff in (
    select diff from (
        select (id - (@rk:=@rk + 1)) as diff
        from stadium, (select @rk:=0) as init where people >= 100
    ) as s2 group by diff having count(*) >= 3
);

解析

和方法一的思路是一樣的，實現的方式不一樣，這裡是通過 group by 進行分組，所以相對於使用 partition by 的方式步驟更加複雜一點。

  • 先篩選出 people 大於等於 100 的資料。
  • 然後使用變數計算出 diff （也可以使用 row_number()），作為臨時表 s1 。
  • 查詢 s1 表，篩選出我們想要的資料
  • 這裡我們想到如果有一個 diff 的分組就好了，我們可以通過 in 來篩選。
  • 這一步就是重複上面計算 diff 的步驟，然後作為臨時表 s2 。

  • 這裡外面還要在包一層查詢 diff ，就是 select diff from s2 ，使用 group by 對 diff 進行分組，並用 having 篩選出大於等於 3 的 diff

    • group by 需要和 select 欄位一一對應，如果不做這一次查詢，使用 group by 將會有問題。
  • 查詢 s1 表，使用 in 操作符，就能把資料查出來了。

Tips

row_number()語法

在一條 SQL 中不能用兩個一樣的變數

更多解題參考：https://github.com/astak16/bl...

相關文章