如何用Teradata/Oracle窗口函数为客户标记最高销量归属州？

Core Approach #

We use window functions (instead of subqueries) to efficiently compute the rank of each state's sales volume per customer. This keeps the query concise, performant, and easy to integrate with multi-table joins.

ANSI SQL Implementation #

Works across most modern databases, including Teradata and Oracle. This query adds a StateMax column that marks the current state as the customer's highest-sales state (including ties):

SELECT
    CustomerNum,
    state,
    sales_volume,
    CASE 
        WHEN RANK() OVER (PARTITION BY CustomerNum ORDER BY sales_volume DESC) = 1 
        THEN state 
        ELSE NULL 
    END AS StateMax
FROM customer_sales;

Details: #

• RANK() assigns a rank of 1 to all states with the highest sales volume for a customer (handles ties correctly—multiple states get rank 1 if they have the same max sales).
• Replace RANK() with ROW_NUMBER() if you want to arbitrarily pick one state in case of ties (note: this will not reflect actual tie conditions).

Teradata Specific Enhancements #

Teradata supports the ANSI query directly. If you want to list all max sales states in every row (instead of null for non-max rows), use Teradata's LISTAGG with a window clause:

SELECT
    CustomerNum,
    state,
    sales_volume,
    LISTAGG(CASE WHEN rank_val = 1 THEN state END, ', ') 
        WITHIN GROUP (ORDER BY state) 
        OVER (PARTITION BY CustomerNum) AS StateMax
FROM (
    SELECT
        CustomerNum,
        state,
        sales_volume,
        RANK() OVER (PARTITION BY CustomerNum ORDER BY sales_volume DESC) AS rank_val
    FROM customer_sales
) ranked_sales;

Oracle Specific Enhancements #

Oracle also supports the ANSI query. For concatenating all max states into a single column across all rows (Oracle 12c+):

SELECT
    CustomerNum,
    state,
    sales_volume,
    LISTAGG(CASE WHEN rank_val = 1 THEN state END, ', ') 
        WITHIN GROUP (ORDER BY state) 
        OVER (PARTITION BY CustomerNum) AS StateMax
FROM (
    SELECT
        CustomerNum,
        state,
        sales_volume,
        RANK() OVER (PARTITION BY CustomerNum ORDER BY sales_volume DESC) AS rank_val
    FROM customer_sales
) ranked_sales;

• For pre-12c Oracle, use WM_CONCAT (deprecated) or a custom aggregate function, but LISTAGG is the recommended approach.

Key Tips #

• Integration with Multi-Table Joins: Replace customer_sales with your actual join query (e.g., FROM (SELECT ... FROM table1 JOIN table2 ON ...) AS customer_sales). Window functions work seamlessly with derived tables.
• Performance: Window functions are optimized in Teradata and Oracle, outperforming correlated subqueries or repeated joins for this use case.
• Tie Handling: Always use RANK() or DENSE_RANK() if you need to preserve tie information—ROW_NUMBER() will arbitrarily break ties without indication.

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