如何基于行号与Person ID计算月份差值并输出列间差值？

Hey there! Let's break down your two requirements step by step—starting with the straightforward column difference calculation, then diving into the core month difference logic tied to row numbers and Person IDs.

This part is pretty straightforward regardless of the tool you're using. Here are examples for common scenarios:

• SQL: If you have two numeric columns (say ColA and ColB) and want to output their difference to a new column ColDiff, use a simple arithmetic operation:

  SELECT ColA, ColB, ColA - ColB AS ColDiff
  FROM your_table;
  

• Python (Pandas): For a DataFrame with columns ColA and ColB, just assign the difference directly:

  import pandas as pd
  df['ColDiff'] = df['ColA'] - df['ColB']
  

Since you mentioned row numbers correspond to increasing months (higher row number = later month), we need to group data by PersonID, maintain the row number order, then compute the month difference between rows. Below are solutions for SQL (since you referenced a code testing environment) and Pandas:

SQL Solution #

Assuming your table has RowNumber, PersonID, and a month column (either a date type like '2023-01-01' or a numeric YYYYMM format like 202301):

Case 1: Difference from the previous row (same PersonID) #

Use the LAG() window function to fetch the previous row's month value, then calculate the difference with DATEDIFF (adjust based on your SQL dialect):

SELECT
  RowNumber,
  PersonID,
  MonthColumn,
  -- Calculate months between current row and the prior row in the same PersonID group
  DATEDIFF(month, LAG(MonthColumn) OVER (PARTITION BY PersonID ORDER BY RowNumber), MonthColumn) AS MonthDiff
FROM your_table;

Case 2: Difference from the first row in the PersonID group #

If you want the difference relative to the earliest month (smallest row number) for each person, use FIRST_VALUE() instead:

SELECT
  RowNumber,
  PersonID,
  MonthColumn,
  DATEDIFF(month, FIRST_VALUE(MonthColumn) OVER (PARTITION BY PersonID ORDER BY RowNumber), MonthColumn) AS MonthDiffFromFirst
FROM your_table;

If your MonthColumn is a numeric YYYYMM value #

If your month is stored as a number (e.g., 202301 for Jan 2023), compute the difference by splitting the year and month components:

SELECT
  RowNumber,
  PersonID,
  MonthColumn,
  -- Calculate year difference *12 plus month difference
  (FLOOR(MonthColumn / 100) - FLOOR(LAG(MonthColumn) OVER (PARTITION BY PersonID ORDER BY RowNumber)/100))*12 +
  (MOD(MonthColumn, 100) - MOD(LAG(MonthColumn) OVER (PARTITION BY PersonID ORDER BY RowNumber), 100)) AS MonthDiff
FROM your_table;

Pandas Solution #

For a Python Pandas DataFrame, first sort by PersonID and RowNumber, then use groupby and date operations:

import pandas as pd

# Ensure your MonthColumn is a datetime type first
df['MonthColumn'] = pd.to_datetime(df['MonthColumn'])

# Sort to maintain row number order per person
df = df.sort_values(['PersonID', 'RowNumber'])

# Calculate month difference from previous row
df['MonthDiff'] = df.groupby('PersonID')['MonthColumn'].apply(
    lambda x: x.dt.to_period('M').astype(int) - x.dt.to_period('M').shift(1).astype(int)
)

# Or difference from the first row in the group
df['MonthDiffFromFirst'] = df.groupby('PersonID')['MonthColumn'].apply(
    lambda x: x.dt.to_period('M').astype(int) - x.dt.to_period('M').iloc[0].astype(int)
)

Just adjust the logic if your month column uses a non-standard format—key is to first normalize it to a date/period type that supports arithmetic operations.

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