Oracle数据库如何统计过去4周内表中每周新增的记录数量

Oracle实现过去4周每周新增记录统计SQL #

核心逻辑是将TIMESTAMP字段按周截断，过滤近28天数据后按周分组，再给周分配1~4的序号即可匹配需求输出。

基础实现（仅统计有数据的周）

SELECT
  COUNT(*) AS numofrows,
  DENSE_RANK() OVER(ORDER BY TRUNC("TIMESTAMP", 'IW')) AS week
FROM tab
WHERE "TIMESTAMP" >= SYSDATE - INTERVAL '28' DAY
GROUP BY TRUNC("TIMESTAMP", 'IW')
ORDER BY week;

参数说明：

• IW为ISO周标准，每周从周一开始计算，若业务周从周日起始，替换为DAY即可
• TIMESTAMP是Oracle内置关键字，因此用双引号包裹避免语法报错

补零实现（无数据的周也显示0条记录）

如果需要补齐没有数据的周，使用CTE生成连续4周的时间区间再左关联业务表即可：

WITH weeks AS (
  SELECT
    TRUNC(SYSDATE - (LEVEL-1)*7, 'IW') AS week_start,
    5 - LEVEL AS week
  FROM dual
  CONNECT BY LEVEL <= 4
)
SELECT
  COUNT(t."TIMESTAMP") AS numofrows,
  w.week
FROM weeks w
LEFT JOIN tab t
  ON TRUNC(t."TIMESTAMP", 'IW') = w.week_start
  AND t."TIMESTAMP" >= SYSDATE - INTERVAL '28' DAY
GROUP BY w.week
ORDER BY w.week;

内容的提问来源于stack exchange，提问作者Anaidm