基于日期的增减计数：如何编写PSQL查询统计指定年月在库人数？

看起来你需要的是基于人员的入职/离职日期，统计某个指定年月内存在的人员总数，同时还要实现按日期的增减计数（比如每月新增、离职、在职人数变化）对吧？我先基于常见的人员表结构来给你具体的查询方案，你可以根据自己的实际表结构调整。

先明确假设的表结构 #

首先假设你有一个employees表，核心字段如下：

• id: 人员唯一ID
• hire_date: 入职日期（DATE类型）
• termination_date: 离职日期（DATE类型，NULL表示当前在职）

如果你的字段名或类型不同，只需要对应替换即可。

1. 统计单个指定年月的人员数量 #

如果你只想查某一个年月（比如2024年3月）里任何时间点在职的人员总数，可以用这个查询：

SELECT COUNT(*) AS active_employee_count
FROM employees
WHERE
  -- 入职日期不晚于目标月的最后一天（说明在目标月结束前已入职）
  hire_date <= (date_trunc('month', '2024-03-01'::date) + interval '1 month - 1 day')::date
  AND (
    -- 要么还在职（无离职日期）
    termination_date IS NULL
    -- 要么离职日期不早于目标月的第一天（说明在目标月开始后才离职）
    OR termination_date >= date_trunc('month', '2024-03-01'::date)::date
  );

逻辑解释： #

这个查询筛选出的是在目标年月内至少有一天是在职状态的人员：

• 入职时间早于等于目标月最后一天：确保这个人在目标月结束前已经入职
• 离职时间要么为空（还在职），要么晚于等于目标月第一天：确保这个人在目标月开始后还没离职，或者一直在职到现在

如果你想参数化查询（比如动态传入年月），可以写个PL/pgSQL函数，这样调用更方便：

CREATE OR REPLACE FUNCTION get_monthly_active_employees(target_month DATE)
RETURNS INTEGER AS $$
BEGIN
  RETURN (
    SELECT COUNT(*)
    FROM employees
    WHERE
      hire_date <= (date_trunc('month', target_month) + interval '1 month - 1 day')::date
      AND (termination_date IS NULL OR termination_date >= date_trunc('month', target_month)::date)
  );
END;
$$ LANGUAGE plpgsql;

-- 调用示例：查询2024年3月的在职人数
SELECT get_monthly_active_employees('2024-03-01');

2. 实现基于日期的增减计数（按月统计变动） #

如果需要统计连续多个月份的人员增减情况（比如每月新增入职数、离职数、月初/月末在职数），可以用这个查询：

-- 第一步：生成你需要统计的年月范围（比如从2023年1月到2024年6月）
WITH date_series AS (
  SELECT generate_series(
    date_trunc('month', '2023-01-01'::date),
    date_trunc('month', '2024-06-01'::date),
    INTERVAL '1 month'
  ) AS month_start
),
-- 第二步：计算每个月的累计入职、累计离职数
monthly_totals AS (
  SELECT
    month_start,
    -- 截至当月末的累计入职人数
    (SELECT COUNT(*) FROM employees WHERE hire_date <= (month_start + INTERVAL '1 month - 1 day')::date) AS total_hired,
    -- 截至当月末的累计离职人数
    (SELECT COUNT(*) FROM employees WHERE termination_date IS NOT NULL AND termination_date <= (month_start + INTERVAL '1 month - 1 day')::date) AS total_terminated
  FROM date_series
)
-- 第三步：计算各项增减指标
SELECT
  to_char(month_start, 'YYYY-MM') AS year_month,
  -- 月末在职人数 = 累计入职 - 累计离职
  total_hired - total_terminated AS end_of_month_active,
  -- 月初在职人数
  (SELECT COUNT(*) FROM employees WHERE hire_date <= month_start::date AND (termination_date IS NULL OR termination_date >= month_start::date)) AS start_of_month_active,
  -- 当月新增入职人数
  (SELECT COUNT(*) FROM employees WHERE hire_date BETWEEN month_start::date AND (month_start + INTERVAL '1 month - 1 day')::date) AS new_hires,
  -- 当月离职人数
  (SELECT COUNT(*) FROM employees WHERE termination_date IS NOT NULL AND termination_date BETWEEN month_start::date AND (month_start + INTERVAL '1 month - 1 day')::date) AS terminations
FROM monthly_totals
ORDER BY month_start;

逻辑解释： #

• 用generate_series生成连续的年月序列，避免手动输入每个月份
• 通过子查询计算每个月的累计入职、离职数，从而得到月末在职数
• 同时统计当月的新增和离职人数，清晰展示每个月的人员变动情况

一些注意事项 #

• 如果你的表中用的是其他日期字段（比如start_date和end_date），只需要替换查询中对应的字段名即可
• 确保日期字段是DATE或TIMESTAMP类型，避免字符串类型导致的日期计算错误
• 如果有大量数据，建议给hire_date和termination_date建立索引，提升查询性能

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