SQL根据订单数据计算 新老客购买各个货品的人数和金额

假设有一个名为orders的订单表，包含以下字段：order_id（订单ID）、customer_id（顾客ID）、product_id（商品ID）、order_date（订单日期）和amount（订单金额）。

则根据订单数据计算新老客购买各个货品的人数和金额的SQL语句如下：

-- 计算新客购买各个货品的人数和金额
SELECT 
    product_id, 
    COUNT(DISTINCT customer_id) AS new_customer_count,
    SUM(CASE WHEN DATEDIFF(order_date, first_order_date) = 0 THEN amount ELSE 0 END) AS new_customer_amount
FROM 
    (
        SELECT 
            order_id, 
            customer_id, 
            product_id, 
            order_date, 
            amount, 
            MIN(order_date) OVER (PARTITION BY customer_id) AS first_order_date
        FROM orders
    ) AS t
GROUP BY product_id;

-- 计算老客购买各个货品的人数和金额
SELECT 
    product_id, 
    COUNT(DISTINCT customer_id) AS old_customer_count,
    SUM(CASE WHEN DATEDIFF(order_date, first_order_date) > 0 THEN amount ELSE 0 END) AS old_customer_amount
FROM 
    (
        SELECT 
            order_id, 
            customer_id, 
            product_id, 
            order_date, 
            amount, 
            MIN(order_date) OVER (PARTITION BY customer_id) AS first_order_date
        FROM orders
    ) AS t
GROUP BY product_id;

这里使用了一个子查询来计算每个顾客的第一次下单时间（即first_order_date），然后根据订单日期和first_order_date的差值判断顾客是新客还是老客。最后按照product_id分组求出新老客购买各个货品的人数和金额。