PostgreSQL查询：按活跃/非活跃日期统计月度活跃设备数量

没问题，这就给你搞定这个月度活跃设备统计的需求！我们可以通过CTE生成日期序列，再结合区间匹配来统计每个月的活跃设备数，具体实现如下：

先准备测试表和数据（如果你的表已经存在可以跳过） #

-- 创建设备表
CREATE TABLE devices (
    unit VARCHAR(10),
    date_active DATE,
    date_inactive DATE
);

-- 插入你提供的测试数据
INSERT INTO devices VALUES
('a', '2018-01-01', NULL),
('b', '2018-01-01', '2020-07-05'),
('c', '2019-02-01', '2020-01-01');

核心查询语句 #

WITH monthly_dates AS (
    -- 生成从最早活跃日期到今日的每个月第一天的日期序列
    SELECT date_trunc('month', generate_series)::DATE AS month_start
    FROM generate_series(
        (SELECT MIN(date_active) FROM devices),  -- 起始日期：表中最早的活跃日期
        CURRENT_DATE,                           -- 结束日期：今日
        INTERVAL '1 month'                      -- 步长：每月
    )
)
SELECT
    md.month_start,
    COUNT(DISTINCT d.unit) AS active_devices
FROM monthly_dates md
LEFT JOIN devices d
    -- 判断设备是否在当前统计月份处于活跃状态：活跃区间与该月有交集
    ON d.date_active <= (md.month_start + INTERVAL '1 month - 1 day')::DATE
    AND (d.date_inactive IS NULL OR d.date_inactive >= md.month_start)
GROUP BY md.month_start
ORDER BY md.month_start;

关键逻辑解释 #

• monthly_dates CTE：用generate_series生成连续的月度日期序列，确保覆盖从最早设备激活日到今天的所有月份，date_trunc('month', ...)把每个日期统一为当月第一天，方便后续统计。
• JOIN条件：判断设备的活跃周期和当前统计月份是否有重叠：
  • 设备的激活日期不能晚于当月最后一天（确保设备在当月或之前已激活）
  • 设备要么至今未停用（date_inactive IS NULL），要么停用日期不早于当月第一天（确保设备在当月至少有部分时间活跃）
• COUNT(DISTINCT d.unit)：避免同一设备在同一个月被重复统计，保证每个月的活跃设备数是唯一设备的数量。

预期输出示例 #

按照你提供的测试数据，查询结果会类似这样：

month_start | active_devices
------------|---------------
2018-01-01  | 2
2018-02-01  | 2
...
2019-02-01  | 3
...
2020-01-01  | 2
...
2020-07-01  | 1
...
2024-XX-01  | 1

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