I have a table that looks something like this:

What is needed is to get the max count value of each type grouped by date and hour.

So, the end result would look something this:

How the query looks now:

SELECT
    category,
    to_timestamp(datetime, 'y-M-d H') as date_hour,
    COUNT(*) AS number_of_occurances
FROM <my_table>
GROUP BY category, date_hour
ORDER BY number_of_occurances DESC

CodePudding user response：

Use a window function to find the top count per category and then only show the category/hours ranked #1.

SELECT category, date_hour, number_of_occurances
FROM
(
  SELECT
    category,
    TRUNC(datetime, 'HH24') AS date_hour,
    COUNT(*) AS number_of_occurances,
    RANK() OVER (PARTITION BY category ORDER BY COUNT(*) DESC) AS rnk
  FROM <my_table>
  GROUP BY category, TRUNC(datetime, 'HH24')
) ranked
WHERE rnk = 1
ORDER BY category;

CodePudding user response：

if the data type of datetime is timestamp then you can use below function to remove minute and second.

to_timestamp(from_timestamp(datetime,'yyyy/MM/dd HH'),'yyyy/MM/dd HH')

If data type of datetime is not timestamp, then first convert it to a timestamp before doing anything. And then apply above logic.

to_timestamp(datetime,'yyyy.dd.MM HH:mm:ss') datetime 

And then use your SQL to calculate count.

SELECT
    category,
    to_timestamp(from_timestamp(datetime,'yyyy/MM/dd HH'),'yyyy/MM/dd HH') as date_hour,
    COUNT(*) AS number_of_occurances
FROM <my_table>
GROUP BY category, date_hour
ORDER BY number_of_occurances DESC