Oracle中的统计数量

1.按天、月、周、年统计数据量

--统计数量by day,  EVENTTIME:2020-10-13 16:51:58

WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT COUNT(*) FROM AA GROUP BY EVENTTIME;

--按天统计
WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT COUNT(*) FROM AA GROUP BY TO_CHAR(TO_DATE(SUBSTR(EVENTTIME,0,10), 'YYYY-MM-DD'), 'YYYY-MM-DD');

--按月统计
WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT COUNT(*) FROM AA GROUP BY TO_CHAR(TO_DATE(SUBSTR(EVENTTIME,0,7), 'YYYY-MM'), 'YYYY-MM');

--按年统计
WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT COUNT(*) FROM AA GROUP BY TO_CHAR(TO_DATE(SUBSTR(EVENTTIME,0,4), 'YYYY'), 'YYYY');

--按季度统计
WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT COUNT(*) FROM AA GROUP BY TO_CHAR(TO_DATE(EVENTTIME,'YYYY-MM-DD HH24:MI:SS'), 'q');

--按周统计
WITH AA AS (SELECT A.* FROM LOT_MULTIHOLD_HISTORY A WHERE EVENTTIME > SYSDATE-30 AND HOLDOWNER='EAP')
SELECT  COUNT(*) FROM AA GROUP BY TO_CHAR(TO_DATE(EVENTTIME,'YYYY-MM-DD HH24:MI:SS'), 'IW');