【数据分析面试】63.计算订单总金额-升级版（SQL复杂计算优化）

题目: 计算订单总金额（升级版）

假设你在经营一个电子商务平台，需要计算订单的总金额。orders表包含了订单的相关信息。

编写一个查询，以获取每个订单的总金额，考虑不同商品的单价、数量和应用的优惠券折扣，以及所有订单的平均总金额（以美元为单位）。

优惠券分为两种类型：商品折扣优惠券，以及订单总金额满50减10的优惠券。优惠券可以同时适用于多个商品。

示例：

输入：

orders表

输出：

结果显示如下：

解题思路

这次我们需要考虑不同商品的单价、数量以及两种类型的优惠券折扣，然后计算每个订单的总金额，并最终计算所有订单的平均总金额。

答案代码

我们可以使用SQL查询来实现此目的。下面是一个示例SQL查询：

SELECT 
    order_id,
    ROUND(SUM(unit_price * quantity * (1 - CASE WHEN coupon_type = 'product_discount' THEN coupon_discount ELSE 0 END) - 
          CASE WHEN SUM(unit_price * quantity * (1 - CASE WHEN coupon_type = 'order_discount' AND SUM(unit_price * quantity) >= 50 THEN 0.1 ELSE 0 END)) >= 50 THEN 10 ELSE 0 END), 2) AS total_amount,
    ROUND(AVG(SUM(unit_price * quantity * (1 - CASE WHEN coupon_type = 'product_discount' THEN coupon_discount ELSE 0 END) - 
          CASE WHEN SUM(unit_price * quantity * (1 - CASE WHEN coupon_type = 'order_discount' AND SUM(unit_price * quantity) >= 50 THEN 0.1 ELSE 0 END)) >= 50 THEN 10 ELSE 0 END)) OVER (), 2) AS avg_total_amount
FROM 
    orders
GROUP BY 
    order_id;

• 这个查询首先根据订单号对订单进行分组。
• 然后对每个订单，计算每种商品的总金额，考虑了商品单价、数量和应用的商品折扣优惠券折扣。
• 接着，根据订单的总金额是否满足订单总金额满50减10的优惠券条件，进行优惠券的折扣计算。
• 最后，使用SUM函数对每个订单的总金额求和，并使用AVG函数计算所有订单的平均总金额。
  这个查询会返回每个订单的总金额以及所有订单的平均总金额，以美元为单位，考虑了不同商品的单价、数量以及两种类型的优惠券折扣。

但是同时，会发现上面的代码非常难读，而且很难理解，可以使用CTE方法对代码进行优化：

WITH order_details AS (
    SELECT 
        order_id,
        SUM(unit_price * quantity * (1 - CASE WHEN coupon_type = 'product_discount' THEN coupon_discount ELSE 0 END)) AS subtotal
    FROM 
        orders
    GROUP BY 
        order_id
),
order_totals AS (
    SELECT 
        order_id,
        subtotal,
        CASE WHEN subtotal >= 50 THEN 10 ELSE 0 END AS order_discount
    FROM 
        order_details
)
SELECT 
    order_id,
    ROUND(subtotal - order_discount, 2) AS total_amount,
    ROUND(AVG(subtotal - order_discount) OVER (), 2) AS avg_total_amount
FROM 
    order_totals;

通过使用CTE，可以将复杂的计算逻辑分解为多个步骤，使得代码更具可读性和可维护性。：

• order_details CTE：计算每个订单在应用商品折扣后的小计（subtotal）。
• order_totals CTE：计算是否符合订单总金额满50减10的优惠券条件，并计算相应的订单折扣（order_discount）。
• 最终查询：计算每个订单的总金额（total_amount），并计算所有订单的平均总金额（avg_total_amount）。

更多详细答案可关注公众号查阅。