如何用SQL计算用户近30天消费金额并生成含TOP商品的聚合表？

现有数据表 #

transaction_details表（交易明细） #

dict_item_prices表（商品价格字典） #

目标统计表格（customer_aggr） #

计算规则 #

• 关联dict_item_prices表，采用交易发生时有效的商品单价进行计算；
• 仅统计报告生成时间前30天内有消费记录的用户；
• 最终表需包含用户ID、近30天消费总金额、该用户此期间消费金额最高的商品名称。

SQL实现语句 #

WITH transaction_with_price AS (
    SELECT
        td.customer_id,
        td.item_id,
        dip.item_name,
        td.item_number * dip.item_price AS item_total,
        td.transaction_dttm
    FROM transaction_details td
    JOIN dict_item_prices dip 
        ON td.item_id = dip.item_id
        AND td.transaction_dttm BETWEEN dip.valid_from_dt AND dip.valid_to_dt
    WHERE td.transaction_dttm >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
customer_item_summary AS (
    SELECT
        customer_id,
        item_name,
        SUM(item_total) AS item_total_amount,
        SUM(SUM(item_total)) OVER (PARTITION BY customer_id) AS amount_spent_lm,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY SUM(item_total) DESC, item_name ASC
        ) AS item_rank
    FROM transaction_with_price
    GROUP BY customer_id, item_name
)
SELECT
    customer_id,
    amount_spent_lm,
    item_name AS top_item_lm
FROM customer_item_summary
WHERE item_rank = 1
ORDER BY customer_id;

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