如何在SAS Proc Sql中按条件拆分航空公司列并分组汇总金额？

Got it, let's tackle this problem step by step. Based on your requirement to split airlines into two groups, aggregate amounts per client, and generate the ranked output, here are two common solutions using SQL and Pandas:

This is ideal if your data is stored in a relational database. We'll use conditional aggregation to split the airline groups, then calculate totals and ranks.

Query Code #

WITH client_aggregations AS (
    SELECT
        Client_Name,
        -- Sum amounts for Group1 (Air India + Jet Airways)
        SUM(CASE WHEN Airlines IN ('Air India', 'Jet Airways') THEN Amout ELSE 0 END) AS Group1,
        -- Sum amounts for Group2 (all other airlines)
        SUM(CASE WHEN Airlines NOT IN ('Air India', 'Jet Airways') THEN Amout ELSE 0 END) AS Group2,
        -- Total amount for the client
        SUM(Amout) AS Total
    FROM your_table_name  -- Replace with your actual table name
    GROUP BY Client_Name
)
SELECT
    -- Assign rank based on total amount (highest first)
    RANK() OVER (ORDER BY Total DESC) AS Rank,
    Client_Name,
    Group1,
    Group2,
    Total
FROM client_aggregations
ORDER BY Rank;

Breakdown #

• CTE client_aggregations: This groups data by each client. We use CASE statements to filter and sum amounts only for the relevant airline groups.
• Main Query: The RANK() function assigns a rank to each client based on their total spend (descending order). Finally, we reorder the results to match your desired output structure.

If you're working with a local dataset in Python, this approach will get you the same result.

Code #

import pandas as pd

# Replace this sample data with your actual dataset
sample_data = {
    'Loc': ['BBI', 'BBI', 'MAA', 'HYD', 'BOM'],
    'Client_Name': ['A_1ABC2', 'A_1ABC2', 'Th_1ABC2', 'Ma_1ABC2', 'Vi_1ABC2'],
    'Airlines': ['Air India', 'Air India', 'Spice Jet Airlines', 'Jet Airways', 'Air India'],
    'Amout': [41302, 41302, 288713, 365667, 552506]
}
df = pd.DataFrame(sample_data)

# Calculate aggregated values per client
client_summary = df.groupby('Client_Name').agg(
    Group1=('Amout', lambda x: x[df['Airlines'].isin(['Air India', 'Jet Airways'])].sum()),
    Group2=('Amout', lambda x: x[~df['Airlines'].isin(['Air India', 'Jet Airways'])].sum()),
    Total=('Amout', 'sum')
).reset_index()

# Add rank column (highest total gets rank 1)
client_summary['Rank'] = client_summary['Total'].rank(ascending=False, method='min').astype(int)

# Reorder columns to match your desired output
client_summary = client_summary[['Rank', 'Client_Name', 'Group1', 'Group2', 'Total']].sort_values('Rank')

# Print or export the result
print(client_summary)

Breakdown #

• Groupby & Aggregation: We group the DataFrame by Client_Name and use lambda functions to filter and sum amounts for each airline group.
• Rank Calculation: The rank() function assigns ranks with ascending=False to prioritize clients with higher totals. method='min' ensures tied totals get the same rank.
• Column Reordering: We rearrange the columns to match the exact output structure you specified.

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