MySQL 實現 RANK 窗口函數：原生 SQL 與 Python 兩種方案完整對比

背景：MySQL 5.7/8.0 前無原生窗口函數

MySQL 8.0 才引入 RANK()、ROW_NUMBER() 等窗口函數。舊版本或兼容性需求下，需通過變量模擬或應用層計算實現。

測試數據集

CREATE TABLE group_concat (
  rank INT,
  username VARCHAR(20)
);

-- 模擬數據：同一用戶多條記錄，rank 值有重複
INSERT INTO group_concat VALUES
(100,'Lucy'),(127,'Lucy'),(146,'Lucy'),(137,'Lucy'),(104,'Lucy'),(121,'Lucy'),
(136,'Lily'),(100,'Lily'),(100,'Lily'),(105,'Lily'),(136,'Lily'),
(149,'ytt'),(116,'ytt'),(116,'ytt'),(149,'ytt'),(106,'ytt'),(117,'ytt');

方案一：MySQL 原生 SQL（GROUP_CONCAT + FIND_IN_SET）

SELECT a.username, a.rank, 
       FIND_IN_SET(a.rank, b.rank_gp) AS rank_cnt
FROM group_concat AS a
JOIN (
  SELECT username, 
         GROUP_CONCAT(rank ORDER BY rank DESC SEPARATOR ',') AS rank_gp
  FROM group_concat
  GROUP BY username
) AS b ON a.username = b.username
ORDER BY a.username ASC, a.rank DESC;

原理解析

1. 子查詢按 username 分組，將同組 rank 降序拼接為逗號字符串 rank_gp（如 '149,149,117,116,116,106'）。
2. 主查詢關聯回原表，FIND_IN_SET(a.rank, b.rank_gp) 返回值在字符串中的首次出現位置（1-based），恰好對應 RANK() 邏輯（並列同名次，下一名次跳過）。

輸出結果（節選）

+----------+------+----------+
| username | rank | rank_cnt |
+----------+------+----------+
| Lily     |  136 |        1 |
| Lily     |  136 |        1 |
| Lily     |  105 |        3 |
| Lily     |  100 |        4 |
| Lily     |  100 |        4 |
| Lucy     |  146 |        1 |
| Lucy     |  137 |        2 |
| Lucy     |  127 |        3 |
| Lucy     |  121 |        4 |
| Lucy     |  104 |        5 |
| Lucy     |  100 |        6 |
| ytt      |  149 |        1 |
| ytt      |  149 |        1 |
| ytt      |  117 |        3 |
| ytt      |  116 |        4 |
| ytt      |  116 |        4 |
| ytt      |  106 |        6 |
+----------+------+----------+

局限性

• GROUP_CONCAT 默認最大長度 1024 字節（group_concat_max_len），大分組會截斷 → 結果錯誤。
• 字符串拼接與查找開銷大，百萬行級別性能不可接受。
• 不支持 PARTITION BY 多列、ORDER BY 多列複雜場景。

方案二：Python 應用層計算（通用、高性能、無長度限制）

from __future__ import print_function
import mysql.connector
import time

def db_connect(autocommit=True):
    return mysql.connector.connect(
        host='192.168.1.131', port=3306,
        user='python_user', password='python_user',
        database='t_girl', autocommit=autocommit)

def compute_rank(partition_col='username', order_col='rank DESC'):
    cnx = db_connect()
    cur = cnx.cursor()
    # 關鍵：按分區列 + 排序列 全局有序讀取
    sql = f"SELECT username, `rank` FROM group_concat ORDER BY {partition_col}, {order_col}"
    cur.execute(sql)
    rows = cur.fetchall()
    
    result = []
    i = 0
    j = 0          # 組內行號
    k = 1          # 當前 rank 值
    prev_user = None
    prev_rank = None
    
    while i < len(rows):
        user, rank_val = rows[i]
        if user == prev_user:
            j += 1
            if rank_val != prev_rank:
                prev_rank = rank_val
                k = j          # RANK 邏輯：並列同名次，下一名次 = 當前行號
        else:
            j = 1
            k = 1
            prev_user = user
            prev_rank = rank_val
        result.append((user, rank_val, k))
        i += 1
    
    # 輸出
    print(f"{'username'.center(10)}|{'rank'.center(10)}|{'rank_cnt'.center(10)}")
    print('-'*32)
    for r in result:
        print(f"{r[0].ljust(10)}|{str(r[1]).ljust(10)}|{str(r[2]).ljust(10)}")
    print(f"({len(rows)} Rows.)  Time: {time.time()-start:.3f}s")
    
    cur.close()
    cnx.close()

if __name__ == '__main__':
    start = time.time()
    compute_rank()

Python 方案優勢

• 無 GROUP_CONCAT 長度限制，支持億級數據（分批游標讀取）。
• 單次掃描 O(N)，無臨時表/文件排序開銷。
• 邏輯清晰，易擴展為 ROW_NUMBER、DENSE_RANK、多列分區/排序。
• 可集成至 ETL/報表管線，脫離數據庫版本依賴。

MySQL 8.0+ 原生寫法（對比參考）

SELECT username, `rank`,
       RANK() OVER (PARTITION BY username ORDER BY `rank` DESC) AS rank_cnt
FROM group_concat
ORDER BY username, `rank` DESC;

建議： 生產環境已升級 8.0+ 直接使用原生窗口函數；舊版本或異構系統首選 Python/應用層方案，SQL 方案僅供小數據量臨時查詢。