PostgreSQL统计近7天数据SQL语句写法

        近期碰到一个应用场景，需要根据当前日期统计最近7天数据，但是表中实际只有3天数据，其余四天数据字段补全默认为0，例如查询表filerecord中近7天edittime字段变更的统计，7天内有些日期没有产生数据，但需要补全输出数据为0。请看以下案例

示例表filerecord和数据展示：

使用generate_series函数来创建生成一个包含最近7天日期的序列，将这个序列与你的filerecord表进行左连接（LEFT JOIN），以统计每天的新增数据条数，并补全缺失的天数为0，

其中filerecord字段id为表主键。

SQL语句如下：

WITH date_range AS (  
    SELECT generate_series(  
        CURRENT_DATE - INTERVAL '6 days',  
        CURRENT_DATE,  
        '1 day'::interval  
    )::date AS date  
)  
SELECT  
    dr.date AS edittime_date,  
    COALESCE(COUNT(fr.id), 0) AS new_records_count  
FROM  
    date_range dr  
LEFT JOIN  
    filerecord fr ON fr.edittime::date = dr.date  
GROUP BY  
    dr.date  
ORDER BY  
    dr.date;

查询结果：

附：

（1）generate_series函数用于生成一个从CURRENT_DATE - INTERVAL '6 days'（即当前日期的前6天）到CURRENT_DATE（即当前日期）的日期序列

（2）使用了::date来将generate_series生成的timestamp转换为date，以便与filerecord表中的edittime字段（假设它是timestamp类型）进行比较。如果你的edittime字段已经是date类型，那么你就不需要转换了

（3）GROUP BY和ORDER BY子句分别用于按日期分组结果和按日期排序