oracle SELECT SQL中COLUMN中最常见的HOUR

xlpyo6sf 于 2022-12-11 发布在 Oracle

关注(0)|答案(1)|浏览(190)

I am trying to select the most common hour a service has been requested.
I currently have a column

CALLOUT_TIME
14:12
12:55
13:18
17:55
14:00
14:45
19:00
05:20

And I want to pull out the most called upon hour in this case (14:00, 14 or 2pm).
I was hoping to group by Hour, or at least have a a count in desc order to highlight the most called upon.
The column is currently set at VARCHAR(2)

SELECT SUBSTR(CALLOUT_TIME, 1, 2) AS HH FROM REQUESTS;

Which will bring me:

HH
14
12
13
17
14
14
19
05 etc..

I was hoping to group by Hour, or at least have a a count in desc order to highlight the most called upon.
There is no DATETIME data type option and I cannot set as TIMESTAMP. Is there a reason I have no DATETIME option, am I using the wrong version of SQL Developer?

oracle

来源：https://stackoverflow.com/questions/74694598/select-most-common-hour-in-column-in-sql

1条答案

按热度按时间

n3schb8v1#

您使用VARCHAR2来存储时间，因为在Oracle中不存在TIME数据类型。这是可以的。（您可以使用他们不恰当地称为DATE的datetime数据类型，但这样您就必须使用一个虚拟日期。唯一的好处是您将有一个内置的有效时间检查。）
你知道如何得到小时数，你说你想按小时分组，按计数排序，那么，这样做吧。

SELECT SUBSTR(callout_time, 1, 2) AS hh
FROM REQUESTS
GROUP BY SUBSTR(callout_time, 1, 2)
ORDER BY COUNT(*) DESC
FETCH FIRST ROW WITH TIES;

赞(0）回复(0）举报 2022-12-11

我来回答

oracle SELECT SQL中COLUMN中最常见的HOUR

1条答案

相关问题

热门标签

最新问答