如何用SQL查询employee.db中各部门频次最高的员工姓名

核心SQL查询语句 #

通过统计频次+窗口函数排名的方式即可实现需求，SQLite支持窗口函数，具体语句如下：

WITH employee_counts AS (
    SELECT 
        Department,
        Name,
        COUNT(*) AS occurrence_count
    FROM employee
    GROUP BY Department, Name
),
ranked_employees AS (
    SELECT 
        Department,
        Name,
        occurrence_count,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY occurrence_count DESC) AS rank
    FROM employee_counts
)
SELECT Department, Name AS most_frequent_employee
FROM ranked_employees
WHERE rank = 1;

嵌入Python代码的完整实现 #

将上述SQL语句整合到已有的数据库连接代码中，完整代码如下：

from sqlalchemy import create_engine, text
import pandas as pd

# 创建数据库引擎
engine = create_engine('sqlite:///employee.db', echo=True)

# 执行查询并输出结果
with engine.connect() as conn:
    # 执行SQL查询
    result = conn.execute(text("""
        WITH employee_counts AS (
            SELECT 
                Department,
                Name,
                COUNT(*) AS occurrence_count
            FROM employee
            GROUP BY Department, Name
        ),
        ranked_employees AS (
            SELECT 
                Department,
                Name,
                occurrence_count,
                ROW_NUMBER() OVER (PARTITION BY Department ORDER BY occurrence_count DESC) AS rank
            FROM employee_counts
        )
        SELECT Department, Name AS most_frequent_employee
        FROM ranked_employees
        WHERE rank = 1;
    """))
    # 将查询结果转为DataFrame并打印
    df_result = pd.DataFrame(result.fetchall(), columns=result.keys())
    print(df_result)

# 关闭数据库连接
engine.dispose()

逻辑说明 #

1. 第一个CTE employee_counts：统计每个部门下各员工的出现次数；
2. 第二个CTE ranked_employees：用ROW_NUMBER()窗口函数给每个部门内的员工按出现次数降序排名；
3. 最终筛选：取出每个部门排名为1的记录，即为该部门出现频次最高的员工。若存在多名员工出现次数并列最高的情况，ROW_NUMBER()会随机返回其中一个，若需返回所有并列最高的员工，可将ROW_NUMBER()替换为RANK()。

内容的提问来源于stack exchange，提问作者eljamba