查询需求分析

• 目的：统计近30天内已支付订单的GMV、订单数与客单价（GMV/订单数），并按支付方式分组输出，便于业务侧评估各支付渠道的贡献度。
• 数据范围与过滤：
  • 时间：支付时间在近30天
  • 状态：order_status in ('paid','shipped','completed')
  • 排除测试单：tag != 'test'（同时保留 tag 为 NULL 的正常订单）
  • 币种：CNY
• 输出字段：pay_method, order_cnt, gmv, arpu
• 排序：GMV 降序，其次订单数降序

注：以下以 MySQL 8.0+ 为示例；如使用 PostgreSQL，见后附替代语句。

SQL查询语句

-- MySQL 8.0+
SELECT
  om.pay_method,
  COUNT(*) AS order_cnt,
  ROUND(SUM(om.pay_amount), 2) AS gmv,
  ROUND(SUM(om.pay_amount) / NULLIF(COUNT(*), 0), 2) AS arpu
FROM order_master AS om
WHERE
  om.pay_time >= NOW() - INTERVAL 30 DAY
  AND om.order_status IN ('paid', 'shipped', 'completed')
  AND om.currency = 'CNY'
  -- 排除测试订单，且保留 tag 为空的正常订单
  AND (om.tag IS NULL OR om.tag <> 'test')
GROUP BY
  om.pay_method
ORDER BY
  gmv DESC,
  order_cnt DESC;

-- 如果您的数据库是 PostgreSQL，可使用如下等价语句

-- PostgreSQL
SELECT
  om.pay_method,
  COUNT(*) AS order_cnt,
  ROUND(SUM(om.pay_amount)::numeric, 2) AS gmv,
  ROUND((SUM(om.pay_amount) / NULLIF(COUNT(*), 0))::numeric, 2) AS arpu
FROM order_master AS om
WHERE
  om.pay_time >= NOW() - INTERVAL '30 days'
  AND om.order_status IN ('paid', 'shipped', 'completed')
  AND om.currency = 'CNY'
  AND (om.tag IS NULL OR om.tag <> 'test')
GROUP BY
  om.pay_method
ORDER BY
  gmv DESC,
  order_cnt DESC;

查询逻辑说明

• 时间过滤：使用 pay_time >= 当前时间减去30天，取滚动近30天的数据。
• 状态过滤：仅保留已付款及之后流程的订单（paid/shipped/completed）。
• 测试单排除：采用 (tag IS NULL OR tag <> 'test') 而非 tag <> 'test'，确保 NULL 不被误排除（NULL 与任何比较结果为 UNKNOWN，会在 WHERE 中被过滤）。
• 币种过滤：仅统计 CNY 订单，避免跨币种对 GMV 造成误差。
• 聚合与分组：
  • order_cnt = COUNT(*)（按订单行计数）
  • gmv = SUM(pay_amount)
  • arpu = gmv / order_cnt；用 NULLIF 防止极端情况下除零错误
• 排序：先按 gmv 降序，再按 order_cnt 降序，满足业务排序要求。
• 数值处理：ROUND 保留两位小数，便于报表展示。

说明：示例中使用列名 pay_time、pay_amount、order_status、tag、currency、pay_method；如您的实际列名不同，请相应替换。

性能建议

• 索引设计（MySQL 优先顺序示例）：
  • 推荐复合索引： (currency, order_status, pay_time, pay_method)
    • currency、order_status 为等值过滤，放前可高效定位
    • pay_time 为范围条件，放在等值条件之后有利于范围扫描
    • pay_method 用于 GROUP BY，可减少回表排序/分组的代价
  • 若该查询频繁且对延迟敏感，可考虑覆盖索引，将 pay_amount 也放入索引末尾：
    • (currency, order_status, pay_time, pay_method, pay_amount)
    • 注意：覆盖索引更宽，会增加写入成本与存储占用，需权衡。
• PostgreSQL 可选优化：
  • 部分索引（Partial Index）：在 CNY 且状态为已支付集合上建索引，缩小索引规模
    • CREATE INDEX ON order_master (pay_time, pay_method) WHERE currency='CNY' AND order_status IN ('paid','shipped','completed');
• 分区与数据冷热分离：
  • 若订单量很大，建议按日期对表进行分区（如按月），近30天查询可实现分区裁剪。
• 执行计划检查：
  • 确认使用索引范围扫描而非全表扫描（EXPLAIN/EXPLAIN ANALYZE）。
  • 确认未发生不必要的 filesort 与临时表开销（MySQL）。

使用注意事项

• 字段确认：请确认支付时间列（示例为 pay_time）与支付金额列（示例为 pay_amount）的实际名称与含义（是否含税、是否含优惠、是否含退款），避免口径偏差。
• 时区口径：确保 pay_time 时区与 NOW() 一致；跨时区部署需统一至同一时区（如 UTC）。
• 空值处理：当前语句会保留 pay_method 为空的分组。如需剔除未知支付方式，可追加 AND om.pay_method IS NOT NULL。
• 退款/逆向单：若需扣除退款或仅计算净 GMV，应在条件或金额口径上明确处理（例如排除已全额退款订单，或用净入账金额字段）。
• 安全与权限：仅统计聚合结果，无敏感个人信息字段，符合数据最小化原则。请确保执行账号仅具备只读权限。

查询需求分析

• 目的：找出在指定仓库（WH_EAST）中，处于启用状态（active）的SKU里，当前库存低于安全库存的记录，用于库存预警与补货决策。
• 输出字段：sku_id、仓库、类目、当前库存、安全库存、缺口量（安全库存-当前库存）、最近入库时间。
• 过滤条件：warehouse='WH_EAST'；sku_status='active'；safety_stock>0；current_stock<safety_stock。
• 排序：按缺口量降序；若缺口量相同，则按最近入库时间升序（空值排在最后）。

注：以下SQL对常见关系型数据库（MySQL、PostgreSQL、SQL Server、Oracle）均通用。如实际列名与示例不完全一致，请按实际列名替换（如last_inbound_time/recent_inbound_time、category/类别等）。

SQL查询语句

SELECT
  sku_id,
  warehouse,
  category,
  current_stock,
  safety_stock,
  (safety_stock - current_stock) AS shortage,
  last_inbound_time AS recent_inbound_time
FROM product_inventory
WHERE
  warehouse = 'WH_EAST'
  AND sku_status = 'active'
  AND safety_stock > 0
  AND current_stock < safety_stock
ORDER BY
  -- 缺口量降序
  shortage DESC,
  -- 将最近入库时间为空的记录排在最后，再按时间升序
  CASE WHEN last_inbound_time IS NULL THEN 1 ELSE 0 END ASC,
  last_inbound_time ASC;

查询逻辑说明

• 字段选择：
  • 直接返回sku_id、warehouse、category、current_stock、safety_stock。
  • 通过表达式(safety_stock - current_stock)计算缺口量并命名为shortage。
  • 将last_inbound_time返回并命名为recent_inbound_time，便于阅读。
• 过滤条件：
  • warehouse='WH_EAST'限定仓库范围。
  • sku_status='active'仅查询在架/启用的SKU。
  • safety_stock>0剔除无安全库存要求的SKU。
  • current_stock<safety_stock筛出低于安全库存阈值的SKU。
• 排序规则：
  • 首先按缺口量shortage降序，优先显示缺口大的SKU。
  • 缺口量相同的SKU再按最近入库时间升序排（更早入库的在前）。
  • 使用CASE WHEN last_inbound_time IS NULL THEN 1 ELSE 0 END将空时间排在最后，确保跨数据库一致性。

性能建议

• 索引建议（按通用优先级）：
  1. 为等值过滤列建立复合索引，提高选择性过滤效率：
     • CREATE INDEX idx_pi_wh_status ON product_inventory (warehouse, sku_status);
  2. 若数据量较大，且current_stock与safety_stock常用于该类对比，可考虑增加覆盖或辅助索引，便于回表成本降低：
     • PostgreSQL/SQL Server（支持包含列）：
       • CREATE INDEX idx_pi_wh_status_ss_cs ON product_inventory (warehouse, sku_status) INCLUDE (safety_stock, current_stock, category, last_inbound_time);
     • MySQL：
       • CREATE INDEX idx_pi_wh_status_ss_cs ON product_inventory (warehouse, sku_status, safety_stock, current_stock);
       • 视主键与存储引擎情况评估是否需要调整列顺序以匹配常见过滤路径。
  3. 若该类预警查询十分高频，考虑新增生成列/物化列shortage = safety_stock - current_stock，并建立索引：
     • 例如：CREATE INDEX idx_pi_wh_status_shortage ON product_inventory (warehouse, sku_status, shortage);
     • 这样既可加速过滤（shortage>0）也有利于按shortage排序。
• 统计信息与执行计划：
  • 定期更新统计信息（ANALYZE/UPDATE STATISTICS）以确保优化器选择合适的执行计划。
  • 首次上线后使用EXPLAIN/EXPLAIN ANALYZE检查是否命中索引、回表次数与排序代价。
• 排序优化：
  • 若生成列shortage已建立索引，可在ORDER BY中直接使用该列，减少排序代价。
  • 大数据量分页时建议结合索引游标或键集分页（例如按shortage、last_inbound_time进行seek）替代大offset。

使用注意事项

• 列名核对：如果实际列名与示例不同（如last_inbound_time/recent_inbound_time、category/类目、sku_status等），请按实际表结构替换。
• 空值处理：
  • 若current_stock可能为NULL且业务上应视为0，请将条件与计算改为：COALESCE(current_stock, 0) < safety_stock；并在SELECT中使用(safety_stock - COALESCE(current_stock, 0))。
• 数据类型：
  • 确保current_stock与safety_stock为数值类型（整数或数值），避免隐式类型转换导致索引失效。
• 权限与安全：
  • 执行查询的账号应仅具备对product_inventory表的只读权限，避免越权访问或数据修改风险。
• 并发与一致性：
  • 报表/预警场景建议在可接受的隔离级别下执行（如READ COMMITTED），避免长事务锁表；如需强一致可配合快照/时间点查询能力（取决于数据库）。