如何通过Unpivot操作将含Creation/Deletion日期的表转换为日期-状态表？

根据你的需求，下面给出几种常用工具的实现方案，覆盖Excel、SQL、Python三种场景：

Excel/Google Sheets 操作步骤 #

基础手动方法（适合小数据集） #

• 先处理Creation状态的日期行：
  1. 在空白列（比如C2）输入=A2，D2输入"Creation"；
  2. 选中C2，右键选择「序列」→「列」，终止值填对应行的Deletion date（比如B2），步长设为1天，自动生成该范围内的所有日期；
  3. 把D列的"Creation"批量填充到对应行。
• 再处理Deletion状态的行：
  1. 在表格下方空白行，输入每一行的Deletion date，对应状态列填"Deletion"；
  2. 把所有行合并，按Date列排序即可。

Power Query高效方法（适合大数据集） #

1. 选中原始数据，点击「数据」→「从表格/区域」进入Power Query编辑器；
2. 添加自定义列，生成日期序列：=List.Dates([Creation date], Duration.Days([Deletion date]-[Creation date])+1, #duration(1,0,0,0))；
3. 展开这个自定义列为行，添加状态列并赋值"Creation"；
4. 新建一个查询，提取原始表的Deletion date列，添加状态列"Deletion"；
5. 合并两个查询，按Date排序后加载回Excel。

SQL 实现代码 #

假设你的原始表名为event_dates，字段为creation_date和deletion_date，以下是不同数据库的实现：

PostgreSQL 版本 #

-- 生成Creation状态的全量日期行
SELECT date AS Date, 'Creation' AS Status
FROM (
    SELECT GENERATE_SERIES(creation_date, deletion_date, INTERVAL '1 day') AS date
    FROM event_dates
) AS creation_dates
UNION ALL
-- 生成Deletion状态的行
SELECT deletion_date AS Date, 'Deletion' AS Status
FROM event_dates
ORDER BY Date, Status;

MySQL 版本（递归CTE方式） #

WITH RECURSIVE date_range AS (
    SELECT creation_date AS date, deletion_date, 'Creation' AS status
    FROM event_dates
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY), deletion_date, 'Creation'
    FROM date_range
    WHERE date < deletion_date
)
SELECT date AS Date, status AS Status FROM date_range
UNION ALL
SELECT deletion_date AS Date, 'Deletion' AS Status FROM event_dates
ORDER BY Date, Status;

Python Pandas 实现代码 #

import pandas as pd

# 加载原始数据（实际使用时可替换为读取文件的代码）
raw_df = pd.DataFrame({
    'Creation date': ['2023-01-06', '2023-01-08'],
    'Deletion date': ['2023-01-07', '2023-01-08']
})

# 转换为日期格式
raw_df['Creation date'] = pd.to_datetime(raw_df['Creation date'])
raw_df['Deletion date'] = pd.to_datetime(raw_df['Deletion date'])

# 生成所有Creation状态的行
creation_df = raw_df.apply(
    lambda row: pd.date_range(start=row['Creation date'], end=row['Deletion date'], freq='D'),
    axis=1
).explode().reset_index(drop=True).to_frame(name='Date')
creation_df['Status'] = 'Creation'

# 生成所有Deletion状态的行
deletion_df = raw_df['Deletion date'].to_frame(name='Date')
deletion_df['Status'] = 'Deletion'

# 合并并排序
final_df = pd.concat([creation_df, deletion_df]).sort_values(by=['Date', 'Status']).reset_index(drop=True)

# 打印结果（或导出为文件）
print(final_df)

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