Oracle按天统计交易数及多时间段分组查询问题求助

原查询问题分析 #

你当前的查询同时按CREATED_DATE和TIMS分组，会将同一天同一研究ID的记录单独计数，而非统计整个时间段内每个研究ID的总记录数，这是统计结果不正确的核心原因。若要按研究ID汇总，分组字段只需保留TIMS。

单时间段分组统计方案 #

以下针对不同数据库提供上周、上月、近6个月的统计语句，你可根据使用的数据库选择对应写法：

1. 上周数据统计 #

（注：以下示例以“周一为一周起始”为准，若需周日起始可调整对应函数参数）

• MySQL

SELECT TIMS, COUNT(*) AS week_count
FROM STUDY_HISTORY
WHERE YEARWEEK(CREATED_DATE, 1) = YEARWEEK(CURDATE(), 1) - 1
GROUP BY TIMS;

• Oracle

SELECT TIMS, COUNT(*) AS week_count
FROM STUDY_HISTORY
WHERE CREATED_DATE BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE, 'IW') - 1
GROUP BY TIMS;

• SQL Server

SELECT TIMS, COUNT(*) AS week_count
FROM STUDY_HISTORY
WHERE DATEPART(WEEK, CREATED_DATE) = DATEPART(WEEK, GETDATE()) - 1
  AND DATEPART(YEAR, CREATED_DATE) = DATEPART(YEAR, GETDATE())
GROUP BY TIMS;

2. 上月数据统计 #

• MySQL

SELECT TIMS, COUNT(*) AS month_count
FROM STUDY_HISTORY
WHERE DATE_FORMAT(CREATED_DATE, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m')
GROUP BY TIMS;

• Oracle

SELECT TIMS, COUNT(*) AS month_count
FROM STUDY_HISTORY
WHERE CREATED_DATE BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') AND LAST_DAY(ADD_MONTHS(SYSDATE, -1))
GROUP BY TIMS;

• SQL Server

SELECT TIMS, COUNT(*) AS month_count
FROM STUDY_HISTORY
WHERE DATEPART(YEAR, CREATED_DATE) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND DATEPART(MONTH, CREATED_DATE) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))
GROUP BY TIMS;

3. 近6个月数据统计 #

（指当前日期往前推6个月的所有数据）

• MySQL

SELECT TIMS, COUNT(*) AS six_month_count
FROM STUDY_HISTORY
WHERE CREATED_DATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY TIMS;

• Oracle

SELECT TIMS, COUNT(*) AS six_month_count
FROM STUDY_HISTORY
WHERE CREATED_DATE >= ADD_MONTHS(SYSDATE, -6)
GROUP BY TIMS;

• SQL Server

SELECT TIMS, COUNT(*) AS six_month_count
FROM STUDY_HISTORY
WHERE CREATED_DATE >= DATEADD(MONTH, -6, GETDATE())
GROUP BY TIMS;

多时间段合并统计方案 #

若要在同一条查询结果中展示每个研究ID在三个时间段的计数，可使用条件聚合（以MySQL为例）：

SELECT 
  TIMS,
  COUNT(CASE WHEN YEARWEEK(CREATED_DATE, 1) = YEARWEEK(CURDATE(), 1) - 1 THEN 1 END) AS week_count,
  COUNT(CASE WHEN DATE_FORMAT(CREATED_DATE, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') THEN 1 END) AS month_count,
  COUNT(CASE WHEN CREATED_DATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) THEN 1 END) AS six_month_count
FROM STUDY_HISTORY
GROUP BY TIMS
ORDER BY TIMS;

内容的提问来源于stack exchange，提问作者Jyoti Prakash Mallick