MySQL 在每個群組中取 N 筆資料 ( Get 1…N Results From Each Group )

背景

如果只想撈取 GROUP BY 後，每一個 GROUP 裡面的前 1…n 筆資料
例如:

Person	Height	Weight	UpdateTime
Andy	150	52	2018-06-01
Andy	155	55	2019-08-01
Andy	160	58	2020-09-01
Bob	162	57	2018-06-01
Bob	164	62	2019-08-01
Bob	168	65	2020-09-01

這是一張記錄身高體重的表
而決定每筆資料是唯一的 PK 就是Person + UpdateTime

問題

假設想要取得每個人最新的體態資料
那麼直覺就會想到先把他們 GROUP BY 後
再來用 ORDER BY
再來用 LIMIT
再來用….. 到底要用啥
最後腦袋一片渾沌地組出一坨勉強通過語法檢查的式子

SELECT 
    Person, Height, Weight, UpdateTime
FROM
    BodyInfoTable 
GROUP BY
    Person, UpdateTime
ORDER BY
    UpdateTime DESC
LIMIT 1

然後就可以開開心心地看見…
各種錯誤與不是你想要的結果 :)

解法一，使用 Variables

那麼再來就是這次的主題
針對每一個 GROUP 取幾筆資料
查詢了 StackOverflow 上的問答後的解法
Using LIMIT within GROUP BY to get N results per dynamic group
mysql limit inside group
套用在範例的情境中就會長這樣

SELECT 
    Person, Height, Weight, UpdateTime
FROM
    (SELECT 
        x.*,
            CASE
                WHEN @prev = Person THEN @i:=@i + 1
                ELSE @i:=1
            END i,
            @prev:=Person
    FROM
        BodyInfoTable x, (SELECT @prev:=NULL, @i:=0) vars
    ORDER BY Person, UpdateTime ) a
WHERE
    i <= 1;

最關鍵的部分就是它使用了Variables搭配case-when來完成
當 Person 欄位重覆的時候 i 就會 +1
調整 ORDER BY 與 i <= 多少
就可以決定想要按照什麼條件來取得前 n 筆資料了

Variables 變數:

@prev:=Person

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

出現另一個問題

原本想說大功告成，這長得這麼醜複雜
把它儲存成 View 吧
登愣~ 直接噴出一個大大的錯誤
Error 1351: View's SELECT contains a variable or parameter SQL Statement:...
直接拿錯誤餵狗後才知道
View 裡面是不能使用 Variables 的!!
如果堅持要用，就必須用很 tricky 的寫法
看到這邊…我只能說
「我OK，你先用」

馬上跳槽用別的寫法

解法二，使用 ROW_NUMBER()

原來這種類型的操作
是因為在MySQL 8以前才需要這麼克難、展現手做職人精神
MySQL 8以後就直接用超棒棒函數就好啦
In MySQL 8 or later just use the RANK, DENSE_RANK or ROW_NUMBER functions:
套用在範例的SQL就會長這樣

SELECT 
    Person, Height, Weight, UpdateTime
FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (partition by Person ORDER BY Person, UpdateTime) AS num
    FROM TainanParking.GridStatic
    ) as a
WHERE num <= 1

撈出來應該就會像是

Person	Height	Weight	UpdateTime
Andy	160	58	2020-09-01
Bob	168	65	2020-09-01

這種寫法真的是好多了
而且這個需求應該很常用到，趕緊筆記起來
終於打完了~ 下課啦~