使用Hive SQL比较两列值并按条件返回结果

Perfect, let's solve this problem where you need to categorize each customer based on their statuses (A, B, or both). Here are practical solutions for common tools you might be working with:

Excel Solution #

If you're working in Excel, you can use COUNTIFS to check for the presence of each status per customer, then use a conditional formula to return the right label.

First, extract unique customer IDs (use the UNIQUE function if you have Excel 365/2021: =UNIQUE(A2:A7)). Then apply this formula next to each unique ID:

=IF(
    AND(COUNTIFS($A$2:$A$7, D2, $B$2:$B$7, "A")>0, COUNTIFS($A$2:$A$7, D2, $B$2:$B$7, "B")>0),
    "happy",
    IF(COUNTIFS($A$2:$A$7, D2, $B$2:$B$7, "A")>0, "Avg", "Sad")
)

(Replace D2 with the cell containing your unique customer ID, and adjust the ranges A2:A7/B2:B7 to match your actual data.)

For a more concise version, use SWITCH:

=SWITCH(
    (COUNTIFS($A$2:$A$7, D2, $B$2:$B$7, "A")>0)+(COUNTIFS($A$2:$A$7, D2, $B$2:$B$7, "B")>0)*2,
    1, "Avg",
    2, "Sad",
    3, "happy"
)

SQL Solution #

In SQL, group by CustomerId and use a CASE statement to check for the presence of each status.

Using Aggregation Functions #

This method counts distinct statuses and checks for specific values:

SELECT 
    CustomerId,
    CASE
        WHEN COUNT(DISTINCT status) = 2 THEN 'happy'
        WHEN MAX(CASE WHEN status = 'A' THEN 1 ELSE 0 END) = 1 THEN 'Avg'
        ELSE 'Sad'
    END AS customer_status
FROM your_table_name
GROUP BY CustomerId;

Using EXISTS Subqueries #

If you prefer explicit existence checks, this approach works well:

SELECT DISTINCT
    t1.CustomerId,
    CASE
        WHEN EXISTS(SELECT 1 FROM your_table_name t2 WHERE t2.CustomerId = t1.CustomerId AND t2.status = 'A')
             AND EXISTS(SELECT 1 FROM your_table_name t2 WHERE t2.CustomerId = t1.CustomerId AND t2.status = 'B') THEN 'happy'
        WHEN EXISTS(SELECT 1 FROM your_table_name t2 WHERE t2.CustomerId = t1.CustomerId AND t2.status = 'A') THEN 'Avg'
        ELSE 'Sad'
    END AS customer_status
FROM your_table_name t1;

Python Pandas Solution #

For Python users, use pandas to group the data and apply a custom logic function.

First, let's set up the sample data and then process it:

import pandas as pd

# Sample data matching your example
data = {
    'Customer Id': [100, 100, 101, 102, 103, 103],
    'status': ['A', 'B', 'B', 'A', 'A', 'B']
}
df = pd.DataFrame(data)

# Define a function to determine the status label
def get_customer_status(group):
    has_a = 'A' in group['status'].values
    has_b = 'B' in group['status'].values
    if has_a and has_b:
        return 'happy'
    elif has_a:
        return 'Avg'
    else:
        return 'Sad'

# Group by Customer Id and apply the function
result_df = df.groupby('Customer Id').apply(get_customer_status).reset_index(name='customer_status')
print(result_df)

Concise Lambda Version #

You can also use a lambda function for a more compact solution:

result_df = df.groupby('Customer Id')['status'].agg(
    lambda x: 'happy' if {'A', 'B'}.issubset(x) else 'Avg' if 'A' in x else 'Sad'
).reset_index(name='customer_status')

Both pandas methods will output:

Customer Id customer_status
0          100            happy
1          101              Sad
2          102              Avg
3          103            happy

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