SQLite按天统计记录数 包含无记录0值日期的实现方法

SQLite没有内置生成连续日期序列的函数，要拿到指定区间全量连续日期的统计值（无记录日期显示0），核心逻辑是先构造目标时间范围内的连续日期全集，再左关联业务表做计数，两种常用实现如下：

纯SQL实现（固定统计范围适用） #

用递归CTE生成连续日期，再做左连接统计。注意行业过滤条件必须放在JOIN关联段，不能放WHERE段，否则会把0值行过滤掉：

WITH RECURSIVE date_series AS (
    SELECT '2022-04-01' AS date -- 替换为你的统计开始日期
    UNION ALL
    SELECT date(date, '+1 day')
    FROM date_series
    WHERE date < '2022-04-30' -- 替换为你的统计结束日期
)
SELECT
    ds.date,
    COUNT(mt.id) AS num_records
FROM date_series ds
LEFT JOIN mytable mt
    ON ds.date = mt.date
    AND mt.industry = 'construction'
GROUP BY ds.date
ORDER BY ds.date ASC;

运行后会返回指定区间内所有日期的结果，比如你举例的场景里，2022-04-02、2022-04-03这类无记录的日期都会显示num_records为0。

Python配合实现（动态范围/适配现有脚本适用） #

你本身已经用Python做数据库定期更新，这种方式更灵活，不需要每次修改SQL里的硬编码日期：

• 用Python的datetime模块生成目标统计区间的连续日期列表
• 执行原有SQL拿到有记录日期的统计结果，存为「日期为键、计数为值」的字典
• 遍历连续日期列表，字典里不存在的日期直接补0，最终结果可以直接传给图表绘制模块使用

参考代码：

import sqlite3
from datetime import datetime, timedelta

# 按需修改统计配置
START_DATE = datetime(2022, 4, 1)
END_DATE = datetime(2022, 4, 30)
TARGET_INDUSTRY = "construction"
DB_FILE_PATH = "your_database.db"

# 生成连续日期序列
date_sequence = []
current = START_DATE
while current <= END_DATE:
    date_sequence.append(current.strftime("%Y-%m-%d"))
    current += timedelta(days=1)

# 查询已有记录的统计结果
conn = sqlite3.connect(DB_FILE_PATH)
cursor = conn.cursor()
cursor.execute(
    "SELECT date, COUNT(id) FROM mytable WHERE industry = ? GROUP BY date",
    (TARGET_INDUSTRY,)
)
record_map = {date_str: count for date_str, count in cursor.fetchall()}
conn.close()

# 组装带0值的全量统计结果
full_statistics = [
    {"date": d, "num_records": record_map.get(d, 0)} 
    for d in date_sequence
]

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