Oracle如何按分钟Group By分组 替代按天统计逻辑

之前修改后未达预期，大概率是只调整了查询结果列的日期格式化规则，GROUP BY和ORDER BY子句中的日期处理逻辑没有同步调整，仍在按天维度聚合、排序。

你可以直接调整所有日期处理的格式化字符串到分钟精度，修改后的SQL如下：

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SELECT to_CHAR(created_date, 'yyyy/mm/dd hh24:mi') AS minute_dim, status_code, COUNT(workflow_txn_id_log) AS record_count
FROM workflow_txn_log
WHERE status_code = 'DOWNLOAD_ALL' AND created_date > '2021/08/11'
GROUP BY  to_CHAR(created_date, 'yyyy/mm/dd hh24:mi'), status_code
ORDER BY to_CHAR(created_date, 'yyyy/mm/dd hh24:mi') ASC;

如果担心字符串排序出现异常，更推荐用trunc函数做日期维度截断，仅在最终展示时做格式化处理，性能和稳定性更好：

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SELECT to_CHAR(trunc(created_date, 'mi'), 'yyyy/mm/dd hh24:mi') AS minute_dim, status_code, COUNT(workflow_txn_id_log) AS record_count
FROM workflow_txn_log
WHERE status_code = 'DOWNLOAD_ALL' AND created_date > '2021/08/11'
GROUP BY  trunc(created_date, 'mi'), status_code
ORDER BY trunc(created_date, 'mi') ASC;

trunc(created_date, 'mi')会直接把日期的秒部分清零，得到对应分钟的时间戳，分组时天然按分钟维度聚合，排序时用日期类型计算也不会出现字符串排序的逻辑异常。

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