按自然月统计
-- 按自然月统计
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
年度;