MySQL / Clickhouse 查詢「每個ＯＯ中，前幾個ＸＸ」的情境

clickhouse 有個很方便的語法——limit by

以官方的範例為例

假設我有一 table example_table，資料如下：

id	val
1	10
1	11
1	12
2	20
2	21

如果我要取得「每個 id 中，最小的兩個 val」，在 clickhouse 可以這樣寫：

SELECT *
FROM `example_table`
ORDER BY id ASC,
         val ASC
LIMIT 2 BY id

但 MySQL 呢？

經查詢，MySQL 在 8.4 新增 PARTITION BY 的語法可以做到相同事。同樣的情境，語法如下：

SELECT id,
       val
FROM
  (SELECT id,
          val,
          ROW_NUMBER() OVER (PARTITION BY id
                             ORDER BY id ASC, val ASC) AS n
   FROM `example_table`) t
WHERE n <= 2

參考資料

LIMIT BY Clause. (2023, April 19). ClickHouse Docs. Retrieved September 10, 2024, from https://clickhouse.com/docs/en/sql-reference/statements/select/limit-by

Overview of partitioning in MySQL. (n.d.). MySQL 8.4 Reference Manual. Retrieved September 10, 2024, from https://dev.mysql.com/doc/refman/8.4/en/partitioning-overview.html