HiveQL跨行条件求和：查询客户交易前历史退货次数

Got it, let's tackle this problem step by step. You need to calculate, for each transaction per customer, how many returns (marked as transaction_item_return='yes') happened before that transaction's purchase timestamp.

First, let's clarify the core logic: we want to group records by customer_id, sort them by purchase time, and then for each transaction, count all prior returns that occurred before its purchase time.

Solution 1: Using Window Functions (Most Efficient) #

Window functions are perfect here because they let us compute running totals without expensive joins. Here's the query:

SELECT
    customer_id,
    purchase_timestamp,
    transaction_item_return,
    return_timestamp,
    -- Calculate the number of returns before the current transaction's purchase time
    SUM(CASE WHEN transaction_item_return = 'yes' THEN 1 ELSE 0 END)
        OVER (
            PARTITION BY customer_id
            ORDER BY purchase_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prior_return_count
FROM
    your_transaction_table  -- Replace with your actual table name
ORDER BY
    customer_id, purchase_timestamp;

Breakdown of the query:

• PARTITION BY customer_id: Groups all records by each customer, so we only calculate returns for the same customer.
• ORDER BY purchase_timestamp: Sorts each customer's transactions by their purchase time, so we process them in chronological order.
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING: Specifies we only look at all rows before the current transaction (excluding the current row itself).
• The SUM(CASE...) counts how many of those prior rows are marked as returns (transaction_item_return='yes').

Solution 2: If You Need to Use Return Timestamp Instead of Purchase Timestamp #

If your requirement is to count returns where the return timestamp (not the purchase timestamp of the return transaction) is before the current transaction's purchase time, adjust the query like this:

SELECT
    customer_id,
    purchase_timestamp,
    transaction_item_return,
    return_timestamp,
    SUM(CASE 
            WHEN transaction_item_return = 'yes' 
                AND return_timestamp < purchase_timestamp 
            THEN 1 ELSE 0 
        END)
        OVER (
            PARTITION BY customer_id
            ORDER BY purchase_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prior_return_count
FROM
    your_transaction_table
ORDER BY
    customer_id, purchase_timestamp;

This adds a check that the return actually happened before the current transaction was purchased, which is more accurate if returns can happen days after the original purchase.

Notes: #

• If some return_timestamp values are NULL (for non-return transactions), the CASE statement will ignore them since NULL < purchase_timestamp evaluates to NULL, which is treated as 0 in the sum.
• If you ever need to include the current transaction in the count (though your question specifies "before", so this isn't needed here), just remove the ROWS BETWEEN... clause (the default window range is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

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