Spotfire基于日期的行比较：员工数量变动计算方法

嘿，这个需求我之前帮别人处理过好几次，其实用SQL或者Python Pandas都能轻松搞定，我给你分两种常用方案详细说说：

用SQL实现 #

首先我们需要先按日期统计每天的唯一员工数量（毕竟一个员工当天可能关联多个Project ID，得去重统计），然后用窗口函数获取前一天的员工数，最后计算两者的差值。

代码示例 #

-- 先统计每日员工数，再计算差异
WITH daily_employee_counts AS (
    SELECT 
        date,
        COUNT(DISTINCT employee_id) AS employee_count
    FROM your_table_name  -- 替换成你的表名
    WHERE date BETWEEN '2024-01-01' AND '2024-01-22'
    GROUP BY date
    ORDER BY date
)
SELECT 
    date,
    employee_count,
    -- 获取前一天的员工数，第一天会返回NULL
    LAG(employee_count) OVER (ORDER BY date) AS previous_day_count,
    -- 计算当日与前一天的员工数差异
    employee_count - LAG(employee_count) OVER (ORDER BY date) AS employee_diff
FROM daily_employee_counts;

补充说明 #

• 如果希望1月1日的差异值显示为0而不是NULL，可以用COALESCE函数处理：

  COALESCE(employee_count - LAG(employee_count) OVER (ORDER BY date), 0) AS employee_diff
  

• 要是想把差异值关联回原始数据的每一行，可以将上面的结果和原表做JOIN操作。

用Python Pandas实现 #

如果你习惯用Python处理数据，Pandas的shift()方法刚好能满足这个“取前一天数据”的需求，步骤同样是先统计每日员工数，再计算差异。

代码示例 #

import pandas as pd

# 假设你的数据已经读取到df中，包含date、employee_id、project_id列
df['date'] = pd.to_datetime(df['date'])  # 确保日期是datetime格式，避免排序出错

# 按日期分组，统计每日唯一员工数
daily_counts = df.groupby('date')['employee_id'].nunique().reset_index(name='employee_count')

# 按日期排序，保证顺序正确
daily_counts = daily_counts.sort_values('date')

# 获取前一天的员工数，计算差异
daily_counts['previous_day_count'] = daily_counts['employee_count'].shift(1)
daily_counts['employee_diff'] = daily_counts['employee_count'] - daily_counts['previous_day_count']

# 可选：把第一天的NaN差异值替换为0
daily_counts['employee_diff'] = daily_counts['employee_diff'].fillna(0)

# 查看结果
print(daily_counts)

补充说明 #

• 如果需要把差异值添加到原始数据的每一行，用merge关联即可：

  df_with_diff = pd.merge(df, daily_counts, on='date', how='left')
  

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