查找招聘人数最多的一周中的哪一天

Question

我正在努力查找雇用最多员工的员工hire_date(星期几(。在我下面的测试案例中，答案应该是星期二。

正如你所看到的，我可以列出所有的日子，但我在将结果缩小到1行时遇到了问题。

如有任何帮助，我们将不胜感激。我列出了我失败的尝试。如果有更有效的方法来重写查询，我更喜欢任何输入。

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
/* not working */
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;

Answer 1

如果DB的版本是12c+，则不需要使用子查询或联接，而只需使用ORDER BY后面的FETCH子句来按降序排序计数，例如

SELECT TO_CHAR(hire_date, 'DAY') AS day, COUNT(*) AS cnt
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY')
ORDER BY cnt DESC
FETCH FIRST 1 ROW WITH TIES

Answer 2

这对我有效：

select DAY, cnt
from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY
,count(*) cnt
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'DAY'))
where cnt = (select max(cnt)
from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY
,count(*) cnt
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'DAY')))

产生以下结果

DAY	CNT
周二	3

在一条评论中，您要求提供dense_rank示例；在这里：

SQL> with temp as
2    (select to_char(hire_date, 'Day') day,
3            count(*) cnt,
4            dense_rank() over (order by count(*) desc) rnk
5     from employees
6     group by to_char(hire_date, 'Day')
7    )
8  select day, cnt
9  from temp
10  where rnk = 1;
DAY                                         CNT
------------------------------------ ----------
Tuesday                                       3
SQL>