本文介绍在sqlite中通过sql语句高效计算“按部门分组后，各组内出现频次最高的姓名”，涵盖窗口函数、子查询与聚合组合等核心技巧，并提供可直接运行的完整代码示例。
本文介绍在sqlite中通过sql语句高效计算“按部门分组后，各组内出现频次最高的姓名”，涵盖窗口函数、子查询与聚合组合等核心技巧，并提供可直接运行的完整代码示例。
要获取每个部门中最常见的员工姓名（即众数），SQLite本身不支持 MODE() 聚合函数，但可通过标准SQL模式实现：先按部门和姓名分组统计频次，再对每个部门内频次排序取首行。
以下是推荐的两种稳健方案（均兼容 SQLite 3.25+）：
✅ 方案一：使用窗口函数（推荐，简洁高效）
SELECT Department, Name
FROM (
SELECT
Department,
Name,
COUNT(*) AS cnt,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY COUNT(*) DESC, Name ASC) AS rn
FROM employee
GROUP BY Department, Name
) ranked
WHERE rn = 1;
- GROUP BY Department, Name 统计每部门内每位员工的出现次数；
- ROW_NUMBER() OVER (PARTITION BY Department ...) 在每个部门内按频次降序（频次相同时按姓名升序确保结果确定性）编号；
- 外层筛选 rn = 1 即得各部门最高频姓名。
? 注意：若某部门存在多个姓名并列最高频（如 IT 部门中 Jane 和 Mary 各出现 2 次），ROW_NUMBER() 仅返回其一；如需全部并列结果，改用 RANK() 并将 WHERE rn = 1 替换为 WHERE rn = 1（RANK() 对并列值赋予相同排名）。
✅ 方案二：使用相关子查询（兼容旧版 SQLite）
SELECT DISTINCT e1.Department, (SELECT e2.Name FROM employee e2 WHERE e2.Department = e1.Department GROUP BY e2.Name ORDER BY COUNT(*) DESC, e2.Name ASC LIMIT 1) AS Most_Common_Name FROM employee e1;
- 外层遍历所有部门；
- 内层子查询针对当前部门，统计姓名频次并取最高频者。
? 完整 Python 执行示例
from sqlalchemy import create_engine, text
import pandas as pd
engine = create_engine('sqlite:///employee.db')
with engine.connect() as conn:
result = conn.execute(text("""
SELECT Department, Name
FROM (
SELECT
Department,
Name,
COUNT(*) AS cnt,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY COUNT(*) DESC, Name ASC) AS rn
FROM employee
GROUP BY Department, Name
) ranked
WHERE rn = 1;
"""))
df_mode = pd.DataFrame(result.fetchall(), columns=['Department', 'Most_Common_Name'])
print(df_mode)
# 输出：
# Department Most_Common_Name
# 0 HR John
# 1 IT Jane
# 2 Finance Adam
# 3 Sales Frank
⚠️ 关键注意事项
- 避免存储 Age 字段：如答案中强调，年龄是动态值，应存储 birth_date 并在查询时用 strftime('%Y', 'now') - strftime('%Y', birth_date) 计算（需处理跨年逻辑）；
- 空值处理：若 Name 或 Department 含 NULL，GROUP BY 会将其单独分组，建议预清洗或添加 WHERE Name IS NOT NULL AND Department IS NOT NULL；
-
性能优化：对大表，应在 (Department, Name) 上建立复合索引：
CREATE INDEX idx_dept_name ON employee(Department, Name);
掌握此模式后，可轻松扩展至其他“每组 Top-N”场景（如各部门前3高频姓名、最高薪员工等），是 SQL 数据分析的基石能力之一。
