SQL ORACLE 按某个规则统计

按自然月统计

-- 按自然月统计   
SELECT
	TO_CHAR (T .CREATE_TIME, 'YYYY-MM') AS 月份,
	"COUNT" (ORDER_ID) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T 
GROUP BY
	TO_CHAR (T .CREATE_TIME, 'YYYY-MM')
ORDER BY
	月份;

结果：

按天统计

-- 按天统计  
SELECT
	TO_CHAR (T .CREATE_TIME, 'YYYY-MM-DD') AS 天,
	"COUNT" (ORDER_ID) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T
GROUP BY
	TO_CHAR (T .CREATE_TIME, 'YYYY-MM-DD')
ORDER BY
	天 NULLS LAST;

结果：

日期测试：

SELECT
	T .CREATE_TIME,
	(T .CREATE_TIME + 15 / 24),
	TO_CHAR (
		T .CREATE_TIME + 15 / 24,
		'YYYY-MM-DD'
	) AS 天
FROM
	TFTC_MEMBER_ORDER_DETAIL T

结果：

按周：

-- 按自然周的日期统计   
SELECT
	TO_CHAR (
		NEXT_DAY (T.CREATE_TIME - 7, 2),
		'YYYY-MM-DD'
	) AS 周,
	SUM (1) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T
GROUP BY
	TO_CHAR (
		NEXT_DAY (T.CREATE_TIME - 7, 2),
		'YYYY-MM-DD'
	)
ORDER BY
	周;

按第几周：

-- 按自然周统计   
SELECT
	TO_CHAR (T .CREATE_TIME, 'iw') AS 周,
	SUM (1) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T
GROUP BY
	TO_CHAR (T .CREATE_TIME, 'iw')
ORDER BY
	周;

按季度：

-- 按季统计   
SELECT
	TO_CHAR (T .CREATE_TIME, 'q') 季度,
	SUM (1) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T
GROUP BY
	TO_CHAR (T .CREATE_TIME, 'q')
ORDER BY
	季度 NULLS LAST;

按年度：

--按年统计   
SELECT
	TO_CHAR (T .CREATE_TIME, 'yyyy') AS 年度,
	SUM (1) AS 数量
FROM
	TFTC_MEMBER_ORDER_DETAIL T
GROUP BY
	TO_CHAR (T .CREATE_TIME, 'yyyy')
ORDER BY
	年度;