如何查询雇佣人数最多的星期几？Oracle SQL实现求助

我需要找出员工雇佣人数最多的星期几（测试案例答案应为Tuesday）。目前已能列出各星期的雇佣人数，但无法将结果筛选为单行。以下是测试表创建语句、可行的分组查询语句及失败的尝试，恳请提供高效的SQL改写方案。

测试表创建语句 #

CREATE TABLE employees (employee_id, first_name, last_name, hire_date) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03' FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04' FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05' FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06' FROM DUAL UNION ALL
SELECT 5, 'Nancy', 'Turner', DATE '2001-04-07' FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08' FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Gold', DATE '2001-04-10' FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11' FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17' FROM DUAL;

可行的分组查询及结果 #

SELECT TO_CHAR(HIRE_DATE,'DAY') DAY, count(*) cnt FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY');

查询结果：

DAY                CNT
TUESDAY      3
FRIDAY       1
SUNDAY       1
SATURDAY     1
WEDNESDAY    2
THURSDAY     1

失败的尝试（无法正常运行） #

SELECT  e.*
FROM EMPLOYEES e
INNER JOIN
    (SELECT employee_id, TO_CHAR(HIRE_DATE,'DAY') DAY
    FROM EMPLOYEES
    GROUP BY TO_CHAR(HIRE_DATE,'DAY')
    HAVING COUNT(1)=(SELECT MAX(COUNT(1))FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY'))) AS empdays
    ON TO_CHAR(e.HIRE_DATE, 'DAY') = empdays.DAY;

问题分析与解决方案 #

失败尝试的问题 #

子查询中GROUP BY TO_CHAR(HIRE_DATE,'DAY')后，SELECT了employee_id，这违反了GROUP BY的规则：分组后只能选择分组字段或聚合函数处理后的字段，不能直接选择未聚合的非分组字段（每个星期对应多个员工ID，无法返回单个值）。

高效改写方案 #

方案1：使用窗口函数（推荐，支持并列情况）

利用RANK()窗口函数可以直接标记出人数最多的星期，若有多个星期人数相同，会全部返回；如果只需要一个结果，可替换为ROW_NUMBER()。

WITH daily_hire_stats AS (
    SELECT
        TO_CHAR(hire_date, 'DAY') AS day_name,
        COUNT(*) AS hire_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
    FROM employees
    GROUP BY TO_CHAR(hire_date, 'DAY')
)
SELECT day_name, hire_count
FROM daily_hire_stats
WHERE rank_num = 1;

方案2：嵌套子查询筛选最大值

先统计各星期的雇佣人数，再取最大值，最后筛选出人数等于最大值的星期：

SELECT
    TO_CHAR(hire_date, 'DAY') AS day_name,
    COUNT(*) AS hire_count
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY')
HAVING COUNT(*) = (
    SELECT MAX(hire_cnt)
    FROM (
        SELECT COUNT(*) AS hire_cnt
        FROM employees
        GROUP BY TO_CHAR(hire_date, 'DAY')
    ) temp
);

两种方案都能高效返回结果，方案1在处理并列场景时更灵活，适合大多数需求。

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