获取每小时的最大行数

穆罕默德·加尼姆

我有下表：

"ITEMS"   "EXPIRY_DATE"
1         30-AUG-17 07.00.00.000000000 AM
1         30-AUG-17 07.15.22.706000000 AM
1         30-AUG-17 07.44.51.794000000 AM
1         30-AUG-17 08.57.11.426000000 AM
1         30-AUG-17 09.57.24.810000000 AM
1         31-AUG-17 06.57.34.236000000 AM
1         31-AUG-17 06.57.42.121000000 AM
1         31-AUG-17 07.57.48.978000000 AM

然后，我count使用以下查询获取行数（每天每小时）：

SELECT COUNT(*), EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
FROM TABLE1 
GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
ORDER BY EXTRACT(DAY FROM EXPIRY_DATE);

使用上述示例数据进行上述查询的结果是：

"COUNT(*)"  "EXTRACT(HOURFROMEXPIRY_DATE)"  "EXTRACT(DAYFROMEXPIRY_DATE)"
3   7   30
1   8   30
1   9   30
2   6   31
1   7   31

如何返回每天每小时的最大行数？我的意思是，我想要以下输出：

"COUNT(*)"  "MAX hour"  "DAY"
3   7   30
2   6   31

拉迪姆巴查

用 HAVING

SELECT COUNT(*), EXTRACT(HOUR FROM EXPIRY_DATE) eh, EXTRACT(DAY FROM EXPIRY_DATE) ed
FROM TABLE1 
GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
HAVING COUNT(*) >= ALL
(
    SELECT COUNT(*)
    FROM TABLE1 t2
    WHERE EXTRACT(DAY FROM t2.EXPIRY_DATE) = EXTRACT(DAY FROM table1.EXPIRY_DATE)
    GROUP BY EXTRACT(HOUR FROM t2.EXPIRY_DATE), 
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。