使用 PostgreSQL 生成日期序列并统计事件数据的 SQL 查询

	
-- 生成过去 10 天的日期序列
WITH date_series AS (
    SELECT to_char(generate_series(CURRENT_DATE - INTERVAL '10 days', CURRENT_DATE, '1 day'), 'yyyy-mm-dd') AS date
),
-- 获取符合条件的 data_table 数据
filtered_data_table AS (
    SELECT id, name, date_series.date
    FROM data_table dt
    CROSS JOIN date_series
    WHERE dt.code = 'SAMPLE_CODE' AND dt.type = 'SAMPLE_TYPE'
),
-- 计算符合条件的 event_table 数据
filtered_event_table AS (
    SELECT event_id, to_char(event_date, 'yyyy-mm-dd') AS event_date_str, COUNT(*) AS event_count
    FROM event_table et
    WHERE et.status != 'inactive' AND et.event_type = 'type1' AND et.event_date >= (date_trunc('day', now()) - INTERVAL '10 days')
    GROUP BY event_id, to_char(event_date, 'yyyy-mm-dd')
)
-- 主查询
SELECT 
    dt.id, 
    dt.name, 
    dt.date, 
    COALESCE(et.event_count, 0) AS event_count
FROM 
    filtered_data_table dt
LEFT JOIN 
    filtered_event_table et 
ON 
    et.event_id = dt.id AND et.event_date_str = dt.date
ORDER BY 
    dt.date;