如何用PostgreSQL/Rails按月计算员工流失率并生成留存率图表数据？

嘿，针对你需要生成Rails/PostgreSQL应用中按月统计的员工留存率图表数据的需求，我整理了两种常见留存率计算逻辑的实现方案，包括原生SQL和Rails ActiveRecord两种方式，完美适配你的employees表结构：

首先得明确员工留存率的定义，这里提供两种最常用的计算逻辑，你可以根据业务需求选择：

1. 月度在职留存率：每个月末在职员工数占当月初在职员工数的比例，适合追踪整体团队规模的留存趋势
2. 新员工月度留存率：某月份入职的员工，在后续N个月后仍在职的比例，适合评估招聘质量和员工融入情况

一、月度在职留存率（覆盖最近12个月） #

1. 原生SQL实现（推荐，性能更优） #

这个查询会生成过去12个月每个月的在职员工数，并计算出月度留存率：

WITH monthly_dates AS (
  -- 生成最近12个月的每月首尾日期
  SELECT 
    generate_series(
      date_trunc('month', CURRENT_DATE - INTERVAL '11 months'),
      date_trunc('month', CURRENT_DATE),
      INTERVAL '1 month'
    ) AS month_start,
    (date_trunc('month', CURRENT_DATE - INTERVAL '11 months') + INTERVAL '1 month - 1 day') AS month_end
),
monthly_employees AS (
  -- 统计每月末的在职员工数：入职时间≤月末，且未离职/离职时间>月末
  SELECT
    md.month_start,
    md.month_end,
    COUNT(e.id) AS active_employees
  FROM monthly_dates md
  LEFT JOIN employees e
    ON e.hired_datetime <= md.month_end
    AND (e.terminated_datetime IS NULL OR e.terminated_datetime > md.month_end)
  GROUP BY md.month_start, md.month_end
  ORDER BY md.month_start
),
previous_month_data AS (
  -- 获取上月在职员工数，用于计算留存率
  SELECT
    month_start,
    active_employees,
    LAG(active_employees) OVER (ORDER BY month_start) AS previous_month_active
  FROM monthly_employees
)
-- 最终计算留存率，保留两位小数
SELECT
  TO_CHAR(month_start, 'YYYY-MM') AS month,
  active_employees,
  previous_month_active,
  ROUND((active_employees::FLOAT / previous_month_active) * 100, 2) AS retention_rate
FROM previous_month_data
WHERE previous_month_active IS NOT NULL; -- 排除第一个月（无上月数据）

2. Rails ActiveRecord 实现 #

方式1：链式查询（适合简单场景）

# 定义时间范围：最近12个月
start_date = 11.months.ago.beginning_of_month
end_date = Date.today.end_of_month

# 统计每月在职员工数
monthly_data = Employee.select(
  "TO_CHAR(date_trunc('month', hired_datetime), 'YYYY-MM') AS month",
  "COUNT(id) FILTER (WHERE hired_datetime <= date_trunc('month', hired_datetime) + INTERVAL '1 month - 1 day' AND (terminated_datetime IS NULL OR terminated_datetime > date_trunc('month', hired_datetime) + INTERVAL '1 month - 1 day')) AS active_employees"
).where(hired_datetime: start_date..end_date)
.group("date_trunc('month', hired_datetime)")
.order("date_trunc('month', hired_datetime)")

# 计算月度留存率
retention_data = monthly_data.each_with_index.map do |month_record, idx|
  next if idx == 0 # 跳过第一个月（无上月数据）
  prev_record = monthly_data[idx-1]
  {
    month: month_record.month,
    current_active: month_record.active_employees,
    prev_active: prev_record.active_employees,
    retention_rate: (month_record.active_employees.to_f / prev_record.active_employees * 100).round(2)
  }
end.compact

方式2：直接执行原生SQL（推荐，性能更好）

sql = <<-SQL
WITH monthly_dates AS (
  SELECT 
    generate_series(
      date_trunc('month', CURRENT_DATE - INTERVAL '11 months'),
      date_trunc('month', CURRENT_DATE),
      INTERVAL '1 month'
    ) AS month_start,
    (date_trunc('month', CURRENT_DATE - INTERVAL '11 months') + INTERVAL '1 month - 1 day') AS month_end
),
monthly_employees AS (
  SELECT
    md.month_start,
    md.month_end,
    COUNT(e.id) AS active_employees
  FROM monthly_dates md
  LEFT JOIN employees e
    ON e.hired_datetime <= md.month_end
    AND (e.terminated_datetime IS NULL OR e.terminated_datetime > md.month_end)
  GROUP BY md.month_start, md.month_end
  ORDER BY md.month_start
),
previous_month_data AS (
  SELECT
    month_start,
    active_employees,
    LAG(active_employees) OVER (ORDER BY month_start) AS previous_month_active
  FROM monthly_employees
)
SELECT
  TO_CHAR(month_start, 'YYYY-MM') AS month,
  active_employees,
  previous_month_active,
  ROUND((active_employees::FLOAT / previous_month_active) * 100, 2) AS retention_rate
FROM previous_month_data
WHERE previous_month_active IS NOT NULL;
SQL

retention_data = ActiveRecord::Base.connection.execute(sql).to_a

二、新员工月度留存率（追踪入职员工的后续留存） #

如果需要统计某月份入职的员工在后续月份的留存情况，可以用这个方案：

1. 原生SQL实现 #

WITH hired_months AS (
  -- 获取最近12个月的入职月份
  SELECT DISTINCT date_trunc('month', hired_datetime) AS hire_month
  FROM employees
  WHERE hired_datetime >= CURRENT_DATE - INTERVAL '12 months'
),
employee_hire_termination AS (
  -- 整理每个员工的入职/离职月份（未离职的用当前月份替代）
  SELECT
    id,
    date_trunc('month', hired_datetime) AS hire_month,
    COALESCE(date_trunc('month', terminated_datetime), date_trunc('month', CURRENT_DATE)) AS termination_month
  FROM employees
  WHERE hired_datetime >= CURRENT_DATE - INTERVAL '12 months'
),
monthly_retention AS (
  -- 统计每个入职月份的员工在后续每个月的留存数
  SELECT
    hm.hire_month,
    (hm.hire_month + INTERVAL '1 month' * n) AS retention_month,
    COUNT(e.id) AS retained_employees
  FROM hired_months hm
  CROSS JOIN generate_series(0, 11) AS n
  LEFT JOIN employee_hire_termination e
    ON e.hire_month = hm.hire_month
    AND e.termination_month >= (hm.hire_month + INTERVAL '1 month' * n)
  GROUP BY hm.hire_month, n
  ORDER BY hm.hire_month, n
),
hire_totals AS (
  -- 获取每个入职月份的总入职人数
  SELECT
    hire_month,
    COUNT(id) AS total_hired
  FROM employee_hire_termination
  GROUP BY hire_month
)
-- 计算新员工留存率
SELECT
  TO_CHAR(hm.hire_month, 'YYYY-MM') AS hire_month,
  TO_CHAR(mr.retention_month, 'YYYY-MM') AS retention_month,
  mr.retained_employees,
  ht.total_hired,
  ROUND((mr.retained_employees::FLOAT / ht.total_hired) * 100, 2) AS retention_rate
FROM monthly_retention mr
JOIN hire_totals ht ON mr.hire_month = ht.hire_month
ORDER BY mr.hire_month, mr.retention_month;

2. Rails ActiveRecord 实现 #

sql = <<-SQL
WITH hired_months AS (
  SELECT DISTINCT date_trunc('month', hired_datetime) AS hire_month
  FROM employees
  WHERE hired_datetime >= CURRENT_DATE - INTERVAL '12 months'
),
employee_hire_termination AS (
  SELECT
    id,
    date_trunc('month', hired_datetime) AS hire_month,
    COALESCE(date_trunc('month', terminated_datetime), date_trunc('month', CURRENT_DATE)) AS termination_month
  FROM employees
  WHERE hired_datetime >= CURRENT_DATE - INTERVAL '12 months'
),
monthly_retention AS (
  SELECT
    hm.hire_month,
    (hm.hire_month + INTERVAL '1 month' * n) AS retention_month,
    COUNT(e.id) AS retained_employees
  FROM hired_months hm
  CROSS JOIN generate_series(0, 11) AS n
  LEFT JOIN employee_hire_termination e
    ON e.hire_month = hm.hire_month
    AND e.termination_month >= (hm.hire_month + INTERVAL '1 month' * n)
  GROUP BY hm.hire_month, n
  ORDER BY hm.hire_month, n
),
hire_totals AS (
  SELECT
    hire_month,
    COUNT(id) AS total_hired
  FROM employee_hire_termination
  GROUP BY hire_month
)
SELECT
  TO_CHAR(hm.hire_month, 'YYYY-MM') AS hire_month,
  TO_CHAR(mr.retention_month, 'YYYY-MM') AS retention_month,
  mr.retained_employees,
  ht.total_hired,
  ROUND((mr.retained_employees::FLOAT / ht.total_hired) * 100, 2) AS retention_rate
FROM monthly_retention mr
JOIN hire_totals ht ON mr.hire_month = ht.hire_month
ORDER BY mr.hire_month, mr.retention_month;
SQL

new_hire_retention_data = ActiveRecord::Base.connection.execute(sql).to_a

小提示 #

• 如果需要覆盖固定12个月（而非最近12个月），只需把SQL中的CURRENT_DATE - INTERVAL '11 months'替换为固定起始日期，比如'2023-01-01'
• 拿到数据后，用Chart.js、Highcharts这类库就能快速生成折线图/柱形图，完美适配图表需求

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