SQL语句统计每小时，每天、每月、每年的_数据

最新推荐文章于 2025-08-31 23:03:19 发布

原创最新推荐文章于 2025-08-31 23:03:19 发布 · 1.4w 阅读

14 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

17 篇文章

订阅专栏

本文提供了一组SQL查询示例，用于从小时、天、周、月、季度和年等多个时间维度分析T_SYS_OPER_LOG表中的操作记录数据。通过这些查询，可以洞察不同时间段内的操作趋势。

部署运行你感兴趣的模型镜像

--by hours
SELECT TO_CHAR(oper_time, 'hh24') AS "Each HOUR", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time, 'hh24')

--by day
SELECT TO_CHAR(oper_time,'DD')AS "Each day", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time,'DD')

--by week
SELECT TO_CHAR(oper_time,'iw')AS "Each Week", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time,'iw')

--by month
SELECT TO_CHAR(oper_time,'MM')AS "Each MOnth", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time,'MM')

--by quart
SELECT TO_CHAR(oper_time,'q')AS "Each MOnth", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time,'q')

--by year
--by quart
SELECT TO_CHAR(oper_time,'YYYY')AS "Each MOnth", COUNT(id) AS qty
FROM T_SYS_OPER_LOG
WHERE oper_time BETWEEN TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2014-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(oper_time,'YYYY')