如何基于current stage列生成含2至current stage-1序列的possible stages列？

Got it, let's break down how to solve this problem step by step. You need to create a new column possible stages that outputs a comma-separated continuous sequence starting at 2 and ending at current stage - 1, based on your existing current stage column (values range from 2 to 8). Below are practical solutions for two common data tools:

1. Python (Pandas) #

If you're working with a Pandas DataFrame, you can use the apply() method with a lambda function to generate the sequence for each row. The range() function is perfect here since it's left-closed and right-open, so range(2, x) will exactly give you numbers from 2 up to x-1.

import pandas as pd

# Example DataFrame
df = pd.DataFrame({'current stage': [2, 3, 5, 6, 8]})

# Generate the new column
df['possible stages'] = df['current stage'].apply(
    lambda x: ','.join(map(str, range(2, x)))
)

# Output preview
print(df)

Output: #

Note: When current stage is 2, the sequence is empty (since there are no numbers between 2 and 1), which is logically correct.

2. SQL #

If you're working directly with a database, the approach varies slightly by SQL dialect, but here are two common implementations:

PostgreSQL #

Use GENERATE_SERIES() to create the sequence and STRING_AGG() to concatenate the values:

SELECT 
    "current stage",
    STRING_AGG(CAST(n AS TEXT), ',') AS "possible stages"
FROM 
    your_table
CROSS JOIN GENERATE_SERIES(2, "current stage" - 1) AS n
GROUP BY 
    "current stage";

MySQL 8.0+ #

Use a recursive CTE to generate the base sequence, then join and aggregate:

WITH RECURSIVE stages AS (
    SELECT 2 AS n
    UNION ALL
    SELECT n + 1 FROM stages WHERE n < 8 -- Match your max current stage value
)
SELECT 
    t."current stage",
    GROUP_CONCAT(s.n ORDER BY s.n SEPARATOR ',') AS "possible stages"
FROM 
    your_table t
LEFT JOIN stages s ON s.n < t."current stage"
GROUP BY 
    t."current stage";

Both SQL solutions will produce the exact sequence you need, matching your example cases (e.g., current stage=5 gives 2,3,4).

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