统计类SQL（按时间）

最新推荐文章于 2022-09-16 14:38:28 发布

cnvdkx2837

最新推荐文章于 2022-09-16 14:38:28 发布

阅读量89

点赞数

按年，月，日，季度，周分组统计某张表数据的发生量。

--按年度分组统计

SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM\')
ORDER BY CREATION_DATE DESC;

--按月份分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM\')
ORDER BY CREATION_DATE DESC;

--按日分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM-DD\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM-DD\')
ORDER BY CREATION_DATE DESC;

--按季度分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-Q\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-Q\')
ORDER BY CREATION_DATE DESC;

--按周分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-IW\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-IW\')
ORDER BY CREATION_DATE DESC;

转载于:http://blog.itpub.net/26687597/viewspace-1207519/