大数据仓库与建模技术例题

Question

现有order_info订单表，表结构如下所示。按要求完成（1）-（2）小题。
order_id user_id user_name order_date order_amount
1 1001 小元 2022-01-01 10
2 1002 小海 2022-01-02 15
3 1001 小元 2022-02-03 23
4 1002 小海 2022-01-04 29
5 1001 小元 2022-01-05 46

（1）统计每个用户截止每次下单的累计下单总额。（8分）
（2）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算

Answer 1

SELECT   
    user_id,   
    user_name,   
    order_date,   
    SUM(order_amount) AS cumulative_order_amount  
FROM   
    order_info  
GROUP BY   
    user_id, user_name, order_date  
ORDER BY   
    user_id, order_date;

WITH OrderedOrders AS (  
    SELECT   
        user_id,   
        user_name,   
        order_date,   
        SUM(order_amount) OVER (PARTITION BY user_id, user_name ORDER BY order_date) AS cumulative_order_amount,  
        LAG(order_date) OVER (PARTITION BY user_id, user_name ORDER BY order_date) AS previous_order_date  
    FROM   
        order_info  
)  
SELECT   
    user_id,   
    user_name,   
    order_date,   
    CASE WHEN previous_order_date IS NULL THEN 0 ELSE DATEDIFF(day, previous_order_date, order_date) END AS days_since_previous_order  
FROM   
    OrderedOrders;