复杂 SQL 查询：深度剖析订单、销量、销售额的环比与同比数据

问题： SELECT a.stat_date AS "订单日期", a.platform AS "平台", a.platform_user_name AS "店铺账号", a.platform_sku AS "平台sku", a.total_orders AS "订单数", a.total_qty AS "销量", a.total_price AS "销售额", a.total_ship_fee AS "运费", a.total_finalvaluefee AS "交易费", a.total_platformfee AS "手续费", a.currency AS "币种", IFNULL(((a.total_orders - b.total_orders) / b.total_orders) * 100, 0) AS "订单数环比值", IFNULL(((a.total_qty - b.total_qty) / b.total_qty) * 100, 0) AS "销量环比值", IFNULL( ( ( CASE WHEN a.currency = 'USD' THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN a.currency IN ('GBP', 'EUR') THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 0 END - CASE WHEN b.currency = 'USD' THEN (b.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN b.currency IN ('GBP', 'EUR') THEN (b.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 0 END ) / CASE WHEN b.currency = 'USD' THEN (b.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN b.currency IN ('GBP', 'EUR') THEN (b.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 1 END ) * 100, 0 ) AS "销售额环比值", IFNULL(((a.total_orders - c.total_orders) / c.total_orders) * 100, 0) AS "订单数同比值", IFNULL(((a.total_qty - c.total_qty) / c.total_qty) * 100, 0) AS "销量同比值", IFNULL( ( ( CASE WHEN a.currency = 'USD' THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN a.currency IN ('GBP', 'EUR') THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 0 END - CASE WHEN c.currency = 'USD' THEN (c.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN c.currency IN ('GBP', 'EUR') THEN (c.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 0 END ) / CASE WHEN c.currency = 'USD' THEN (c.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN c.currency IN ('GBP', 'EUR') THEN (c.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 1 END ) * 100, 0 ) AS "销售额同比值", i.ShortText1742806216550 AS "币种名称", i.Number1742806223433 AS "汇率", a.total_price * IFNULL(i.Number1742806223433,0) AS "销售额（RMB）", CASE WHEN a.currency = 'USD' THEN i.Number1742806223433 WHEN a.currency IN ('GBP', 'EUR') THEN subquery.usd_rate ELSE NULL END AS "美元汇率", ROUND( CASE WHEN a.currency = 'USD' THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / i.Number1742806223433 WHEN a.currency IN ('GBP', 'EUR') THEN (a.total_price * IFNULL(i.Number1742806223433,0)) / subquery.usd_rate ELSE 0 END, 3 ) AS "销售额（USD）" FROM ods.yc_daily_sales_by_product a LEFT JOIN ods.yc_daily_sales_by_product b ON a.platform = b.platform AND a.platform_user_name = b.platform_user_name AND a.platform_sku = b.platform_sku AND b.stat_date = DATE_SUB(a.stat_date, INTERVAL 1 DAY) LEFT JOIN ods.yc_daily_sales_by_product c ON a.platform = c.platform AND a.platform_user_name = c.platform_user_name AND a.platform_sku = c.platform_sku AND c.stat_date = DATE_SUB(a.stat_date, INTERVAL 1 YEAR) LEFT JOIN cloudpivot.i03ao_rate i ON YEAR(a.stat_date) = YEAR(i.Date1742805689343) AND MONTH(a.stat_da ######[AI问答 | 1757点数解答 | 2025-03-31 17:37:22]