oracle按照日期统计过去七天的数据，并某一天数据为0也要显示

最新推荐文章于 2025-01-06 08:18:05 发布

原创最新推荐文章于 2025-01-06 08:18:05 发布 · 862 阅读

CC 4.0 BY-SA版权

文章标签：

在Oracle中，如果你需要查询一个表中的数据，根据创建时间（假设字段名为 created_at）来筛选出近七天的数据，并且即使某一天没有数据也要统计为0，你可以使用 CONNECT BY 子句生成日期序列，然后结合外部连接（LEFT JOIN）来获取每一天的数据。

假设你的表名为 your_table，字段 created_at 是日期类型，下面是一个实现这个需求的 SQL 示例：

WITH date_series AS (
    SELECT TO_DATE(TRUNC(SYSDATE) - LEVEL + 1, 'YYYY-MM-DD') AS day
    FROM dual
    CONNECT BY LEVEL <= 7
)
SELECT 
    d.day,
    COUNT(t.created_at) AS data_count
FROM 
    date_series d
LEFT JOIN 
    your_table t
ON 
    TRUNC(t.created_at) = d.day
GROUP BY 
    d.day
ORDER BY 
    d.day DESC;

解释：

WITH date_series AS (...)：这个部分使用 CONNECT BY 来生成一个包含近七天日期的序列。TRUNC(SYSDATE) 会将当前时间的日期部分提取出来，然后通过 LEVEL 来生成过去7天的日期。LEVEL 从1开始，CONNECT BY 让其逐步生成每一天的日期，+1 是为了确保序列从今天开始。
LEFT JOIN：将 date_series 表中的每一天与 your_table 表进行连接，条件是 TRUNC(t.created_at) = d.day，这里 TRUNC 是去掉时间部分，只保留日期部分来进行比较。
COUNT(t.created_at)：对每一天的数据进行计数。如果某一天没有数据，则返回0。
GROUP BY d.day：按日期分组统计。
ORDER BY d.day DESC：按日期降序排序，使得今天的数据显示在最上面。

结果：

该查询将返回近7天内每天的数据条数，如果某一天没有数据，data_count 将为 0。

示例输出：

day	data_count
2024-11-12	5
2024-11-11	0
2024-11-10	3
2024-11-09	0
2024-11-08	2
2024-11-07	4
2024-11-06	0

这样即使某一天没有记录，也会显示为 0，而且日期会按照从今天开始的顺序显示。

一张表如何实现

如果只有一张表，你仍然可以使用类似的方法来生成日期序列，并对表进行连接。由于你只有一张表，实际上可以直接使用 CONNECT BY 来生成日期序列，然后与该表的数据进行连接。

假设你的表名是 your_table，包含字段 created_at（表示记录的创建时间），我们需要查询最近7天的数据，并确保每一天的日期都有统计，若没有数据则统计为0。

实现步骤：

通过 CONNECT BY 生成过去7天的日期序列。
用 LEFT JOIN 将生成的日期序列与表中的数据连接，并统计每一天的记录数。

完整的 SQL 查询：

WITH date_series AS (
    SELECT TO_DATE(TRUNC(SYSDATE) - LEVEL + 1, 'YYYY-MM-DD') AS day
    FROM dual
    CONNECT BY LEVEL <= 7
)
SELECT 
    d.day,
    COUNT(t.created_at) AS data_count
FROM 
    date_series d
LEFT JOIN 
    your_table t
ON 
    TRUNC(t.created_at) = d.day
GROUP BY 
    d.day
ORDER BY 
    d.day DESC;

详细解释：

WITH date_series AS (...)：生成近7天的日期序列。使用 CONNECT BY 子句从当前日期 SYSDATE 向前生成7天的日期。TRUNC(SYSDATE) 取当前日期的日期部分（不包括时间），然后通过 LEVEL 来生成每天的日期。
- LEVEL 从1开始，CONNECT BY LEVEL <= 7 使得查询结果生成7天的日期（包括今天）。
- TO_DATE(TRUNC(SYSDATE) - LEVEL + 1, 'YYYY-MM-DD')：LEVEL 逐步递减，每次减去 LEVEL - 1 天。这样我们能够获取当前日期和前六天的日期。
LEFT JOIN：将生成的日期序列与表 your_table 中的记录进行连接。TRUNC(t.created_at) = d.day 保证我们只比较日期部分，而忽略时间部分。
COUNT(t.created_at)：统计每一天的记录数。如果某一天没有记录，COUNT 会返回0。
GROUP BY d.day：按日期分组，确保每一天的数据被单独统计。
ORDER BY d.day DESC：按日期降序排序，确保最近的日期（今天）显示在最上面。

示例：

假设今天是 2024-11-12，表中有以下数据：

created_at
2024-11-12 10:00:00
2024-11-12 12:00:00
2024-11-10 09:00:00
2024-11-08 14:00:00

则查询结果会如下：

day	data_count
2024-11-12	2
2024-11-11	0
2024-11-10	1
2024-11-09	0
2024-11-08	1
2024-11-07	0
2024-11-06	0

重要说明：

TRUNC(t.created_at) 会去除时间部分，仅以日期部分进行比较。
LEVEL <= 7 确保生成过去七天的日期。
如果表中某一天没有记录，查询会返回该日期并统计为 0。

这样，即使某些日期没有数据，你的查询仍会返回这些日期，并且数据条数为 0。

更简单的方式实现

如果想要简化实现，可以通过直接使用 SYSDATE 和 INTERVAL 来避免使用 CONNECT BY 生成日期序列。可以使用 CROSS JOIN 结合日期范围来实现，减少复杂性。以下是一个简化的实现方式：

SELECT 
    TO_DATE(SYSDATE - LEVEL + 1, 'YYYY-MM-DD') AS day,
    COUNT(t.created_at) AS data_count
FROM 
    dual
LEFT JOIN 
    your_table t
ON 
    TRUNC(t.created_at) = TO_DATE(SYSDATE - LEVEL + 1, 'YYYY-MM-DD')
GROUP BY 
    TO_DATE(SYSDATE - LEVEL + 1, 'YYYY-MM-DD')
ORDER BY 
    day DESC
CONNECT BY LEVEL <= 7;

这段 SQL 使用 CONNECT BY LEVEL <= 7 来生成过去7天的日期，并且通过 TRUNC(t.created_at) 来统计每一天的记录数。

如果报错（ORA-01861）

ORA-01861: literal does not match format string 错误通常出现在日期或时间的格式不匹配时。具体来说，当 Oracle 数据库尝试将一个日期或时间与字符串进行比较时，如果格式不匹配，就会发生这个错误。

在你的查询中，TO_DATE() 函数与日期类型的比较可能导致了这个问题。尤其是当你使用 TRUNC(t.created_at) 来处理表中的日期字段时，t.created_at 可能已经是一个日期类型，而 TO_DATE() 生成的日期可能没有完全匹配的格式。

解决方案：

确保日期比较时，左边和右边的日期格式一致。
可以直接在生成日期的地方使用 TRUNC() 函数，避免使用 TO_DATE()，或者确保格式完全匹配。

修正后的 SQL：

WITH date_series AS (
    SELECT TRUNC(SYSDATE) - LEVEL + 1 AS day
    FROM dual
    CONNECT BY LEVEL <= 7
)
SELECT 
    d.day,
    COUNT(t.created_at) AS data_count
FROM 
    date_series d
LEFT JOIN 
    your_table t
ON 
    TRUNC(t.created_at) = d.day
GROUP BY 
    d.day
ORDER BY 
    d.day DESC;

解释：

TRUNC(SYSDATE) - LEVEL + 1：不再使用 TO_DATE()，而直接用 TRUNC() 处理日期，使得结果始终是日期类型。
TRUNC(t.created_at)：确保比较时只考虑日期部分，不包括时间。

这个修正后的 SQL 应该能够避免 ORA-01861 错误。

为什么这么做：

SYSDATE 返回的是一个日期时间类型（包含日期和时间）。使用 TRUNC() 可以去除时间部分，只保留日期部分。
在日期比较时，如果没有完全匹配的日期格式，就会引发 ORA-01861 错误。因此，我们避免使用 TO_DATE() 来处理日期字段，直接处理日期类型即可。

代码步骤讲解解析

这段 SQL 代码的目标是查询过去七天内每一天的记录数量。具体来说，通过生成一个日期序列来左连接你的数据表，统计每一天有多少条记录。

以下是 SQL 语句的详细步骤解析：

1. `WITH date_series AS (...)`

这部分是一个公共表表达式（CTE），用于生成一个日期序列，表示过去七天的日期。其作用是动态生成日期数据，方便后续查询中进行连接操作。

WITH date_series AS (
    SELECT TRUNC(SYSDATE) - LEVEL + 1 AS day
    FROM dual
    CONNECT BY LEVEL <= 7
)

SELECT TRUNC(SYSDATE) - LEVEL + 1 AS day：
- SYSDATE 是当前的系统日期和时间，TRUNC(SYSDATE) 会将当前日期的时间部分截断，返回当天的日期（即忽略时间）。
- LEVEL 是 Oracle 在递归查询（CONNECT BY）中为每一行生成的行号，从 1 开始。LEVEL <= 7 表示生成 7 行数据，即过去七天。
- TRUNC(SYSDATE) - LEVEL + 1 的意思是从当前日期开始，依次减去 1、2、3 等，生成过去 7 天的日期（例如，如果今天是 2024-11-12，则生成的日期会是 2024-11-12 到 2024-11-06）。
FROM dual：
- dual 是 Oracle 数据库中一个特殊的虚拟表，常用来生成常量值或进行简单的操作。
CONNECT BY LEVEL <= 7：
- CONNECT BY 用于递归查询，但在这里它被用于生成连续的数字（LEVEL）。通过这个递归机制，LEVEL 会从 1 开始，一直到 7，这样就生成了 7 行数据，对应过去 7 天的日期。

结果：生成一个日期序列，表示过去 7 天，每天都被称为 day 列，假设当前日期是 2024-11-12，生成的日期序列可能是：

2. `SELECT d.day, COUNT(t.created_at) AS data_count`

这部分查询的是每一天的记录数，COUNT(t.created_at) 计算每一天 created_at 字段有多少条记录。

SELECT 
    d.day,
    COUNT(t.created_at) AS data_count

d.day：选择日期序列 date_series 中的 day 列。d 是 date_series 的别名。
COUNT(t.created_at)：对于每个日期 d.day，统计表 your_table 中 created_at 字段不为 NULL 的记录数。COUNT 函数用于计算某个列中不为 NULL 的记录数量。

3. `FROM date_series d LEFT JOIN your_table t ON TRUNC(t.created_at) = d.day`

这部分是进行左连接操作，将生成的日期序列与目标表 your_table 中的数据进行匹配，条件是日期相等。这里使用了 TRUNC() 来确保只比较日期部分。

FROM 
    date_series d
LEFT JOIN 
    your_table t
ON 
    TRUNC(t.created_at) = d.day

FROM date_series d：表示 date_series 作为一个临时表，并且赋予它别名 d。
LEFT JOIN your_table t：左连接操作，即使右边的表 your_table 没有匹配的记录，左边的日期序列 date_series 仍然会显示出来，your_table 中没有匹配的记录将会以 NULL 方式显示。
TRUNC(t.created_at) = d.day：此条件确保在连接时只考虑日期部分（忽略时间部分）。TRUNC(t.created_at) 会去掉 created_at 中的时间部分，只留下日期进行比较。d.day 是当前日期序列中的日期。

解释：假设你有如下的 your_table 数据：

created_at
-----------
2024-11-12 09:00:00
2024-11-11 10:30:00
2024-11-09 15:00:00

在左连接操作中，日期列会和生成的日期序列进行匹配，如果某一天没有对应的记录，则 data_count 为 0。

4. `GROUP BY d.day`

这部分是对生成的日期序列进行分组，确保每一天只返回一条记录（即每个日期的统计结果）。

GROUP BY 
    d.day

按照 d.day 分组，意味着每一天会作为一个单独的组来统计 COUNT(t.created_at) 的结果。

5. `ORDER BY d.day DESC`

这部分确保返回的结果按照日期从最新的到最早的顺序排列。

ORDER BY 
    d.day DESC;

ORDER BY d.day DESC 会根据日期 d.day 降序排列，即最新的日期排在最前面。

总结

整个 SQL 查询的逻辑如下：

使用递归查询生成过去 7 天的日期序列。
将这些日期与目标表 your_table 中的 created_at 日期进行左连接。
对每一天进行统计，计算每天的记录数。
最终结果按照日期降序排列，显示每一天的记录数。

假设表 your_table 中有如下数据：

created_at
-----------
2024-11-12 09:00:00
2024-11-11 10:30:00
2024-11-09 15:00:00

返回的结果可能是：

day         | data_count
------------|------------
2024-11-12  | 1
2024-11-11  | 1
2024-11-10  | 0
2024-11-09  | 1
2024-11-08  | 0
2024-11-07  | 0
2024-11-06  | 0