如何基于条件拆分单列数据至多列及跨表数据处理求助

Got it! Let's tackle this data splitting and merging task. Since you haven't shared the exact table structures, split conditions, or the specific target format you need, I'll walk you through two widely used approaches (SQL and Python Pandas) that work for most common scenarios. You can tweak these examples to fit your actual data.

Let’s assume we have two tables:

• source_data: The table with the single column you want to split (columns: user_id, metric_type, metric_value)
• target_summary: The table where you want to insert the split values (columns: user_id, daily_active, weekly_active, monthly_active)

Our goal is to split the metric_value from source_data into the three active columns in target_summary, based on the metric_type value.

Example 1: Using CASE WHEN with JOIN (Universal across most SQL dialects) #

This approach updates the target table directly by joining with the source table and mapping values using conditional logic:

UPDATE target_summary ts
SET 
  daily_active = (SELECT sd.metric_value FROM source_data sd WHERE sd.user_id = ts.user_id AND sd.metric_type = 'daily'),
  weekly_active = (SELECT sd.metric_value FROM source_data sd WHERE sd.user_id = ts.user_id AND sd.metric_type = 'weekly'),
  monthly_active = (SELECT sd.metric_value FROM source_data sd WHERE sd.user_id = ts.user_id AND sd.metric_type = 'monthly')
WHERE EXISTS (SELECT 1 FROM source_data sd WHERE sd.user_id = ts.user_id);

Example 2: Using PIVOT (For dialects that support it, like PostgreSQL 11+, SQL Server) #

If your SQL dialect supports pivot operations, this is a cleaner way to reshape the source data first, then merge it into the target:

-- First pivot the source data
WITH pivoted_source AS (
  SELECT 
    user_id,
    MAX(CASE WHEN metric_type = 'daily' THEN metric_value END) AS daily_active,
    MAX(CASE WHEN metric_type = 'weekly' THEN metric_value END) AS weekly_active,
    MAX(CASE WHEN metric_type = 'monthly' THEN metric_value END) AS monthly_active
  FROM source_data
  GROUP BY user_id
)
-- Update the target table with pivoted data
UPDATE target_summary ts
SET 
  daily_active = ps.daily_active,
  weekly_active = ps.weekly_active,
  monthly_active = ps.monthly_active
FROM pivoted_source ps
WHERE ts.user_id = ps.user_id;

If you're working with data in a notebook or script, Pandas makes this task straightforward. Let’s use the same table structure as above for consistency.

Step 1: Load your data #

import pandas as pd

# Sample source data
source_data = pd.DataFrame({
    'user_id': [1,1,1,2,2,2],
    'metric_type': ['daily', 'weekly', 'monthly', 'daily', 'weekly', 'monthly'],
    'metric_value': [10, 35, 80, 15, 40, 90]
})

# Sample target table
target_summary = pd.DataFrame({
    'user_id': [1,2,3],
    'daily_active': [0,0,0],
    'weekly_active': [0,0,0],
    'monthly_active': [0,0,0]
})

Step 2: Pivot the source data #

Reshape the source column into multiple columns using pivot:

pivoted_source = source_data.pivot(
    index='user_id',
    columns='metric_type',
    values='metric_value'
).reset_index().rename(columns={
    'daily': 'daily_active',
    'weekly': 'weekly_active',
    'monthly': 'monthly_active'
})

Step 3: Merge with the target table #

Update the target table with the pivoted values:

# Merge and fill missing values (keep existing target rows even if no source data)
final_target = target_summary.merge(
    pivoted_source,
    on='user_id',
    how='left',
    suffixes=('_old', '')
)

# Replace old columns with new values, keep original if no data
final_target['daily_active'] = final_target['daily_active'].fillna(final_target['daily_active_old'])
final_target['weekly_active'] = final_target['weekly_active'].fillna(final_target['weekly_active_old'])
final_target['monthly_active'] = final_target['monthly_active'].fillna(final_target['monthly_active_old'])

# Drop the old columns
final_target = final_target.drop(columns=['daily_active_old', 'weekly_active_old', 'monthly_active_old'])

If you can share more details—like the exact column names, split conditions, or the target format you need—I can refine this solution to match your specific use case perfectly!

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