构建用户旅程：如何通过循环自连接实现用户路径聚合？

开发者阿泽

2026-4-29

Got it, let's tackle this problem step by step. You want to reconstruct full user journeys from your webPages table, and there are two output formats you're targeting. I'll cover both SQL and Python approaches since you mentioned either is fine.

SQL Approach

Recursive Common Table Expressions (CTEs) are perfect for this scenario—they handle variable-length user journeys far better than fixed-number self-joins, which would break if some users have longer paths than others.

Form 2: Concatenated Journey as Array/String

Let's start with this format since it's more flexible. Below are examples for popular databases:

PostgreSQL (Array Output)

WITH RECURSIVE user_journeys AS (
    -- Start with the first step: all journeys beginning at Homepage
    SELECT
        userid,
        ARRAY[beforePage, afterPage] AS journey,
        afterPage AS current_page,
        1 AS step_count
    FROM webPages
    WHERE beforePage = 'Homepage'

    UNION ALL

    -- Recursively append subsequent pages to the journey
    SELECT
        uj.userid,
        uj.journey || wp.afterPage,
        wp.afterPage,
        uj.step_count + 1
    FROM user_journeys uj
    JOIN webPages wp 
        ON uj.userid = wp.userid 
        AND uj.current_page = wp.beforePage
)
-- Pick the longest journey for each user (discards intermediate steps like Homepage -> Page A)
SELECT
    userid,
    journey AS pagesConcat
FROM (
    SELECT
        userid,
        journey,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY step_count DESC) AS rn
    FROM user_journeys
) subquery
WHERE rn = 1;

MySQL (String Output)

MySQL doesn't support native arrays, so we'll build a formatted string instead:

WITH RECURSIVE user_journeys AS (
    SELECT
        userid,
        CONCAT('[', beforePage, ', ', afterPage) AS journey,
        afterPage AS current_page,
        1 AS step_count
    FROM webPages
    WHERE beforePage = 'Homepage'

    UNION ALL

    SELECT
        uj.userid,
        CONCAT(uj.journey, ', ', wp.afterPage),
        wp.afterPage,
        uj.step_count + 1
    FROM user_journeys uj
    JOIN webPages wp 
        ON uj.userid = wp.userid 
        AND uj.current_page = wp.beforePage
)
SELECT
    userid,
    CONCAT(journey, ']') AS pagesConcat
FROM (
    SELECT
        userid,
        journey,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY step_count DESC) AS rn
    FROM user_journeys
) subquery
WHERE rn = 1;

Form 1: Pivoted Columns for Each Step

To get the multi-column format, we first generate each step via recursion, then pivot the results. Note: You'll need to adjust column names if your longest user journey has more steps than the example.

WITH RECURSIVE user_journeys AS (
    -- Start with the initial Homepage entry
    SELECT
        userid,
        beforePage AS page,
        0 AS step
    FROM webPages
    WHERE beforePage = 'Homepage'

    UNION ALL

    -- Add each subsequent page with incremented step number
    SELECT
        uj.userid,
        wp.afterPage,
        uj.step + 1
    FROM user_journeys uj
    JOIN webPages wp 
        ON uj.userid = wp.userid 
        AND uj.page = wp.beforePage
),
step_named AS (
    -- Label each step with the column name you want
    SELECT
        userid,
        page,
        CASE step
            WHEN 0 THEN 'beforePage'
            ELSE CONCAT('afterPage_', step)
        END AS column_name
    FROM user_journeys
)
-- Pivot the labeled steps into columns
SELECT
    userid,
    beforePage,
    afterPage_1 AS afterPage,
    afterPage_2 AS afterPage,
    afterPage_3 AS afterPage,
    afterPage_4 AS afterPage
FROM step_named
PIVOT (
    MAX(page) FOR column_name IN (beforePage, afterPage_1, afterPage_2, afterPage_3, afterPage_4)
) AS pivot_table;

Python Approach (Using Pandas)

If you prefer processing data in Python, Pandas makes grouping and building journeys straightforward.

First, load your data into a DataFrame:

import pandas as pd

# Example data matching your table
data = [
    (1, 'Homepage', 'Page A'),
    (1, 'Page A', 'Page B'),
    (1, 'Page B', 'Page C'),
    (1, 'Page C', 'Checkout'),
    (2, 'Homepage', 'Page A'),
    (2, 'Page A', 'Page B')
]
df = pd.DataFrame(data, columns=['userid', 'beforePage', 'afterPage'])

Form 2: Concatenated Journey as List

def build_user_journey(user_data):
    # Start the journey at Homepage
    journey = ['Homepage']
    current_page = 'Homepage'
    
    # Loop to append each subsequent page
    while True:
        next_page = user_data[user_data['beforePage'] == current_page]['afterPage'].values
        if len(next_page) == 0:
            break
        journey.append(next_page[0])
        current_page = next_page[0]
    return journey

# Group by user and apply the journey-building function
journey_df = df.groupby('userid').apply(build_user_journey).reset_index(name='pagesConcat')
print(journey_df)

Output:

userid                                  pagesConcat
0       1  [Homepage, Page A, Page B, Page C, Checkout]
1       2                [Homepage, Page A, Page B]

Form 1: Pivoted Columns

# First build all journeys, then format into columns
journey_records = []
max_journey_length = df.groupby('userid').size().max() + 1  # +1 to include the starting Homepage

for user_id, group in df.groupby('userid'):
    journey = ['Homepage']
    current_page = 'Homepage'
    
    while True:
        next_page = group[group['beforePage'] == current_page]['afterPage'].values
        if len(next_page) == 0:
            break
        journey.append(next_page[0])
        current_page = next_page[0]
    
    # Pad shorter journeys with None to match max length
    journey += [None] * (max_journey_length - len(journey))
    # Create column names: beforePage + repeated afterPage
    columns = ['userid', 'beforePage'] + ['afterPage'] * (max_journey_length - 1)
    # Add user ID and journey to records list
    journey_records.append([user_id] + journey)

# Convert to DataFrame
pivot_df = pd.DataFrame(journey_records, columns=columns)
# Replace None with empty strings if desired
pivot_df = pivot_df.fillna('')
print(pivot_df)