一天到晚摆弄SQL的我们，要么是做电商的小伙伴，要么是电商的打工人……我们日常是不是总被各种数据查询、业务统计搞得头大？商品库存怎么实时更新？订单状态怎么清晰展示给用户？高价值客户该怎么精准筛选？为此，我在这里整理了71条电商高频SQL语句模板，从商品上架、订单处理到用户分析、营销活动，甚至库存调拨、售后退款，每个场景都有对应的SQL语句。每条语句都结合实际场景说明用法，我们跟着修改参数就能直接套用。不管是我们开发同学搭建功能模块，还是我们运营同学做数据复盘，这份“电商SQL语句模板”都能帮我们省去从零编写SQL的时间，从基础的库存余量查询，到复杂的用户复购率分析，再到促销活动的效果追踪，她覆盖电商日常90%以上的数据需求。

一、商品与类目管理

1、商品基础操作

（1）查询商品列表（带分页）

SELECT 
  product_id,  -- 商品唯一标识
  product_name,  -- 商品名称（前端展示用）
  price,  -- 当前售价
  stock_quantity,  -- 实时库存数量
  category_id  -- 所属分类ID
FROM 
  products  -- 商品主表
WHERE 
  category_id = 101  -- 筛选指定分类（如："电子产品"分类ID=101）
  AND is_active = 1  -- 仅查询激活状态（未下架）的商品
ORDER BY 
  create_time DESC  -- 按创建时间倒序，确保最新商品在前
LIMIT 0, 20;  -- 分页逻辑：从第0条开始取20条（对应前端第1页）

注释：电商商品列表页核心查询，通过WHERE条件过滤无效商品，ORDER BY控制展示顺序，LIMIT实现分页加载，减轻数据库压力。

（2）新增商品

INSERT INTO products (
  product_name, 
  price, 
  original_price,  -- 原价（用于显示折扣力度，如："原价399，现价299"）
  stock_quantity,  -- 初始库存
  category_id,  -- 所属分类
  is_active,  -- 状态：1=激活（上架），0=未激活（下架）
  create_time  -- 创建时间
) 
VALUES (
'2024新款无线耳机',  -- 商品名称需清晰描述特性
  299.99,  -- 售价（保留两位小数，符合金额规范）
  399.99,  -- 原价（高于售价，突出折扣）
  500,  -- 初始库存500件
  101,  -- 归类到"电子产品"
  1,  -- 直接上架
  NOW()  -- 自动记录当前时间
);

注释：商品上架时的基础信息录入，original_price用于营销展示，NOW()确保创建时间准确，便于后续库存和销售追踪。

（3）批量更新商品库存

UPDATE products 
SET 
  stock_quantity = stock_quantity - 5,  -- 库存减少5个（动态计算，避免硬编码）
  update_time = NOW()  -- 更新时间戳，记录库存变动时间
WHERE 
  product_id IN (1001, 1002, 1003);  -- 批量操作多个商品（如：同一订单中的多件商品）

注释：订单确认后扣减库存的核心语句，stock_quantity - 5的写法确保即使库存被并发修改，也能基于最新值计算，减少超卖风险。

（4）查询商品分类及下属商品数

SELECT 
  c.category_id,  -- 分类ID
  c.category_name,  -- 分类名称（如："手机"、"电脑"）
  COUNT(p.product_id) AS product_count  -- 下属商品数量（别名便于前端调用）
FROM 
  categories c  -- 分类表（别名c简化代码）
LEFT JOIN 
  products p  -- 商品表（别名p）
  ON c.category_id = p.category_id  -- 关联条件：分类ID匹配
WHERE 
  c.parent_id = 0  -- 仅查询一级分类（parent_id=0表示顶级分类）
GROUP BY 
  c.category_id, c.category_name;  -- 按分类分组统计

注释：通过LEFT JOIN关联分类与商品表，确保即使分类下无商品也会被列出（product_count为0），GROUP BY+COUNT()统计商品数量，用在分类导航页展示。

2、商品分析

（5）查询商品类目销售TOP3（按金额）

-- 按类目分组，取销售额前三的类目
SELECT 
  c.category_id,  -- 类目ID
  c.category_name,  -- 类目名称（如："手机"、"家电"）
  SUM(o.order_amount) AS total_sales  -- 类目总销售额
FROM orders o
-- 关联商品表（获取商品所属类目）
JOIN products p ON o.product_id = p.product_id
-- 关联类目表（获取类目名称）
JOIN categories c ON p.category_id = c.category_id
WHERE o.order_status = 'paid'  -- 仅统计有效订单
GROUP BY c.category_id, c.category_name  -- 按类目分组
ORDER BY total_sales DESC  -- 按销售额降序
LIMIT 3;  -- 取前三名

场景：识别核心热销类目，我们便可针对性加大推广资源（如：首页置顶、活动倾斜），同时确保库存充足，避免热销类目缺货。

（6）分析商品库存预警（低于阈值的商品）

-- 筛选库存低于预警阈值的商品（如：低于10件）
SELECT 
  product_id,
  product_name,
  stock_quantity,  -- 当前库存
  stock_warning_threshold  -- 预警阈值（可从配置表获取或硬编码）
FROM products
WHERE 
  stock_quantity <= stock_warning_threshold  -- 库存不足
  AND is_active = 1  -- 仅关注上架商品
ORDER BY stock_quantity ASC;  -- 按库存升序（最紧缺的商品在前）

场景：供应链补货提醒，库存低于阈值的商品需优先补货，避免因缺货导致的订单流失，尤其适用于热销商品。

（7）查询商品的平均评分及评价数量

-- 计算每个商品的用户平均评分和总评价数
SELECT 
  p.product_id,
  p.product_name,
  AVG(r.rating) AS avg_rating,  -- 平均评分（1-5分）
  COUNT(r.review_id) AS review_count  -- 评价数量
FROM products p
-- 左连接：包含无评价商品
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name
ORDER BY avg_rating DESC, review_count DESC;  -- 按评分和评价数降序

场景：商品详情页展示评分，帮助用户决策；同时为我们运营提供商品质量反馈，低评分商品需排查原因（如：质量、描述不符）并优化。

（8）分析购物车商品转化率（加入购物车后最终购买的比例）

-- 计算购物车商品被购买的比例
SELECT 
  p.product_id,
  p.product_name,
  COUNT(DISTINCT c.cart_id) AS cart_count,  -- 加入购物车次数
  COUNT(DISTINCT o.order_id) AS buy_count,  -- 最终购买次数
  -- 转化率=购买次数/加购次数（保留2位小数）
  ROUND(COUNT(DISTINCT o.order_id)/COUNT(DISTINCT c.cart_id)*100, 2) AS cart_conversion
FROM cart_items c  -- 购物车表
-- 左连接：关联同一用户购买该商品的订单
LEFT JOIN orders o ON c.product_id = o.product_id 
  AND c.user_id = o.user_id 
  AND o.order_time >= c.add_time  -- 订单时间晚于加购时间
  AND o.order_status = 'paid'  -- 有效订单
-- 关联商品表获取名称
JOIN products p ON c.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING cart_count > 0  -- 排除无加购记录的商品
ORDER BY cart_conversion DESC;  -- 按转化率降序

场景：识别高加购但低转化商品，可能存在价格过高、评价差等问题，我们可通过降价、优化详情页等方式提升转化率。

二、订单与交易管理

1、订单基础操作

（9）新增订单主表记录

INSERT INTO orders (
  order_id,  -- 订单唯一编号（自定义规则，如："ORD+日期+序号"）
  user_id,  -- 下单用户ID
  total_amount,  -- 订单总金额（商品原价总和）
  pay_amount,  -- 实付金额（扣除优惠、运费后的金额）
  order_status,  -- 订单状态（0=待支付，1=已支付等）
  create_time,  -- 下单时间
  pay_time  -- 支付时间（未支付时为NULL）
) 
VALUES (
  CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), '001'),  -- 生成带日期的订单号（如："ORD20240520001"）
  88888,  -- 用户ID（关联用户表）
  599.98,  -- 总金额（如：两件商品299.99*2）
  549.98,  -- 实付金额（减去50元优惠券）
  0,  -- 初始状态为待支付
  NOW(),  -- 下单时间
  NULL  -- 未支付，支付时间暂为空
);

注释：CONCAT()和DATE_FORMAT()生成规则化订单号，便于我们人工识别和系统管理；pay_amount需精确计算优惠后金额，pay_time待支付后更新。

（10）新增订单详情（子订单）

INSERT INTO order_items (
  order_id,  -- 关联的订单ID（与主表orders.order_id一致）
  product_id,  -- 商品ID
  product_name,  -- 冗余存储商品名称（防止商品改名后订单显示异常）
  unit_price,  -- 购买时的单价（快照，不随商品调价变动）
  quantity,  -- 购买数量
  subtotal  -- 小计（单价*数量，用在校验总金额）
) 
VALUES 
  ('ORD20240520001', 1001, '无线耳机', 299.99, 2, 599.98);  -- 一条订单可包含多个商品（多行VALUES）

注释：订单详情表需冗余存储商品购买时的关键信息（名称、单价），避免商品信息后续修改导致订单记录不一致，subtotal用于与主表total_amount校验，确保数据准确。

（11）更改订单状态（支付成功）

UPDATE orders 
SET 
  order_status = 1,  -- 更新状态为"已支付"（1=已支付）
  pay_time = NOW(),  -- 记录支付时间
  update_time = NOW()  -- 记录状态更新时间
WHERE 
  order_id = 'ORD20240520001'  -- 按订单号定位
  AND order_status = 0;  -- 仅允许从"待支付"状态更新（防重复支付或状态错乱）

注释：更新条件包含原状态（order_status=0），是防止并发问题的关键（如：用户多次点击支付按钮），确保状态流转的原子性。

（12）查询用户的所有订单（带状态）

SELECT 
  o.order_id, 
  o.create_time,  -- 下单时间
  o.total_amount,  -- 总金额
  o.pay_amount,  -- 实付金额
  o.order_status,  -- 状态码
  -- 用CASE转换状态码为文字描述（前端直接展示，无需二次处理）
  CASE o.order_status 
    WHEN 0 THEN '待支付'
    WHEN 1 THEN '已支付'
    WHEN 2 THEN '已发货'
    ELSE '已取消'
  END AS status_text 
FROM 
  orders o 
WHERE 
  o.user_id = 88888  -- 查询指定用户的订单
ORDER BY 
  o.create_time DESC;  -- 最新订单在前

注释：CASE函数将数字状态码转换为用户易懂的文字（如：“待支付”），减少前端逻辑处理，ORDER BY确保订单按时间倒序展示，符合用户习惯。

（13）查询订单详情（含商品信息）

SELECT 
  oi.order_id,  -- 订单号
  oi.product_id,  -- 商品ID
  oi.product_name,  -- 商品名称（快照）
  oi.unit_price,  -- 购买时单价
  oi.quantity,  -- 数量
  oi.subtotal,  -- 小计
  o.order_status  -- 订单整体状态（关联主表）
FROM 
  order_items oi  -- 订单详情表
JOIN 
  orders o 
  ON oi.order_id = o.order_id  -- 关联订单主表
WHERE 
  oi.order_id = 'ORD20240520001';  -- 按订单号查询

注释：通过JOIN一次性获取订单的商品明细和整体状态，避免前端多次查询，提升性能。

2、交易分析

（14）统计不同支付方式的订单占比

-- 计算每种支付方式的订单数占总订单数的比例
SELECT 
  payment_method,  -- 支付方式（如："微信支付"、"支付宝"）
  COUNT(order_id) AS order_count,  -- 该方式订单数
  -- 占比=该方式订单数/总订单数（保留2位小数）
  ROUND(COUNT(order_id)/(SELECT COUNT(*) FROM orders WHERE order_status = 'paid')*100, 2) AS proportion
FROM orders
WHERE order_status = 'paid'  -- 仅统计有效订单
GROUP BY payment_method
ORDER BY proportion DESC;  -- 按占比降序

场景：了解用户偏好的支付方式，确保主流支付渠道稳定（如：高峰期扩容），同时可针对低占比支付方式推出优惠（如：“支付宝支付立减5元”）。

（15）查询未支付订单（超时未支付）

-- 筛选状态为“未支付”且超过30分钟未支付的订单（自动取消前提醒）
SELECT 
  order_id,
  user_id,
  order_time,  -- 下单时间
  -- 计算未支付分钟数
  TIMESTAMPDIFF(MINUTE, order_time, NOW()) AS unpaid_minutes
FROM orders
WHERE 
  order_status = 'unpaid'  -- 状态为未支付
  AND TIMESTAMPDIFF(MINUTE, order_time, NOW()) > 30  -- 超过30分钟
ORDER BY unpaid_minutes DESC;  -- 按未支付时长降序

场景：触发催付机制（如：短信、APP推送），提醒用户及时支付；若超过更长时间（如：24小时），可自动取消订单释放库存和优惠券。

（16）统计各地区订单量（按省份）

-- 按用户收货省份分组，统计订单数量
SELECT 
  ua.province,  -- 用户收货省份
  COUNT(o.order_id) AS order_count  -- 该地区订单数
FROM orders o
-- 关联收货地址表
JOIN user_addresses ua ON o.address_id = ua.address_id
WHERE o.order_status = 'paid'  -- 仅统计有效订单
GROUP BY ua.province
ORDER BY order_count DESC;  -- 按订单数降序

场景：分析订单地域分布，在订单密集地区优化仓库布局（如：增设区域仓），缩短配送时间；在订单稀疏地区可考虑合并配送或调整运费策略。

（17）统计每月退货率（退货订单数/总订单数）

-- 按月份计算退货率
SELECT 
  DATE_FORMAT(order_time, '%Y-%m') AS month,  -- 月份
  -- 退货订单数（状态为"已退货"）
  COUNT(DISTINCT CASE WHEN order_status = 'refunded' THEN order_id END) AS refund_order_count,
  COUNT(DISTINCT order_id) AS total_order_count,  -- 总订单数
  -- 退货率=退货订单数/总订单数（避免除以0，用NULLIF处理）
  ROUND(COUNT(DISTINCT CASE WHEN order_status = 'refunded' THEN order_id END) / 
  NULLIF(COUNT(DISTINCT order_id), 0)*100, 2) AS refund_rate
FROM orders
GROUP BY month
ORDER BY month;

场景：监控退货趋势，若某月份退货率突增，需排查原因（如：商品质量下降、物流问题）；高退货率商品需优化或下架。

3、购物车操作

（18）查询用户购物车商品

SELECT 
  c.cart_id,  -- 购物车项ID
  c.product_id,  -- 商品ID
  p.product_name,  -- 商品名称
  p.price,  -- 商品当前售价（实时更新）
  c.quantity,  -- 购物车中数量
  p.stock_quantity,  -- 实时库存（判断是否可购买）
  p.main_image  -- 商品主图（前端展示）
FROM 
  cart_items c  -- 购物车表
JOIN 
  products p ON c.product_id = p.product_id  -- 关联商品表
WHERE 
  c.user_id = 88888  -- 当前用户
  AND c.is_selected = 1;  -- 选中的商品（结算用）

注释：购物车列表核心查询，关联商品表获取实时价格和库存（避免用户看到的价格/库存与实际不符），is_selected筛选用户勾选的商品，用在结算页展示。

（19）更新购物车商品数量

UPDATE cart_items 
SET 
  quantity = 3,  -- 更新为3件（用户调整后的数量）
  update_time = NOW()  -- 记录更新时间
WHERE 
  cart_id = 5001  -- 购物车项ID
  AND user_id = 88888;  -- 确保是当前用户的购物车（防越权）

注释：用户调整购物车商品数量时使用，user_id条件是权限控制的关键，防止恶意修改他人购物车；数量更新后需同步校验库存（前端或后端），避免超过库存上限。

（20）清空用户购物车（选中商品）

DELETE FROM cart_items 
WHERE 
  user_id = 88888 
  AND is_selected = 1;  -- 仅删除选中的商品（结算后清空）

注释：订单提交后清空已结算的购物车商品，保留未选中的商品（用户可能后续继续购买），提升用户体验。

三、用户与会员管理

1、用户基础操作

（21）新增用户

INSERT INTO users (
  user_id, 
  username,  -- 用户名
  phone,  -- 手机号（作为登录账号）
  password_hash,  -- 密码哈希（不可逆加密）
  register_time  -- 注册时间
) 
VALUES (
  88888, 
'zhang-san',  -- 用户名（可自定义）
'138****8000',  -- 手机号（唯一，用于登录和验证）
  SHA2('user123456', 256),  -- 用SHA2加密密码（比MD5更安全）
  NOW()  -- 自动记录注册时间
);

注释：密码必须加密存储（SHA2、MD5+盐值等），禁止明文存储，phone需设置唯一索引，防止重复注册。

（22）用户登录验证

SELECT 
  user_id, 
  username 
FROM 
  users 
WHERE 
  phone = '138****8000'  -- 用户输入的手机号
  AND password_hash = SHA2('user123456', 256);  -- 输入密码加密后与数据库比对

注释：登录时将用户输入的密码加密后与数据库中的哈希值比对，验证通过则返回用户信息，避免密码在传输或验证过程中泄露。

（23）更新用户收货地址

UPDATE user_addresses 
SET 
  receiver = '李四',  -- 收货人姓名
  phone = '13900139000',  -- 联系电话
  province = '广东省', 
  city = '深圳市', 
  detail_address = '南山区科技园XX路',  -- 详细地址
  is_default = 1  -- 设为默认地址（1=默认，0=非默认）
WHERE 
  address_id = 555  -- 地址ID（唯一标识）
  AND user_id = 88888;  -- 确保是当前用户的地址（防止越权修改）

注释：更新条件必须包含user_id，是权限控制的关键，防止用户通过地址ID修改他人地址，is_default用于标记默认地址，下单时自动选中。

（24）查询用户的默认收货地址

SELECT 
  receiver,  -- 收货人
  phone,  -- 电话
  province,  -- 省份
  city,  -- 城市
  detail_address  -- 详细地址
FROM 
  user_addresses 
WHERE 
  user_id = 88888 
  AND is_default = 1;  -- 只查询默认地址

注释：下单流程中自动加载用户的默认地址，简化用户操作，提升下单效率。

（25）统计用户消费总额（用户画像）

SELECT 
  u.user_id,
  u.username,
  -- COALESCE处理NULL为0（未消费用户的总额为0）
  COALESCE(SUM(o.pay_amount), 0) AS total_spent  -- 总消费金额
FROM 
  users u  -- 用户表
LEFT JOIN 
  orders o 
  ON u.user_id = o.user_id 
  AND o.order_status IN (1, 2, 3)  -- 仅统计已支付/已完成的有效订单
GROUP BY 
  u.user_id, u.username;  -- 按用户分组

注释：LEFT JOIN确保所有用户都被统计（包括未消费用户），COALESCE将NULL转换为0，避免统计结果异常，用在用户价值评估。

2、用户行为分析

（26）分析用户复购率（按月份）

-- 统计每月用户复购率：复购用户数/总购买用户数
SELECT 
  DATE_FORMAT(order_time, '%Y-%m') AS month,  -- 按年月分组（如："2024-05"）
  -- 复购用户数（购买次数≥2的用户）
  COUNT(DISTINCT CASE WHEN buy_count >= 2 THEN user_id END) / 
  -- 总购买用户数
  COUNT(DISTINCT user_id) AS repurchase_rate  
FROM (
  -- 子查询：统计用户每月购买次数
  SELECT 
    user_id,
    DATE_FORMAT(order_time, '%Y-%m') AS month,
    COUNT(order_id) AS buy_count  -- 每月购买次数
  FROM orders
  WHERE order_status = 'paid'  -- 仅统计有效订单
  GROUP BY user_id, DATE_FORMAT(order_time, '%Y-%m')
) AS user_monthly_orders
GROUP BY month
ORDER BY month;

场景：复购率是衡量用户忠诚度的核心指标，复购率越高说明用户对平台或商品的认可度越高，我们可针对低复购月份优化运营策略（如：推出复购券）。

（27）查询用户首次购买的商品信息

-- 找出每个用户首次下单购买的商品
WITH user_first_order AS (
  -- 子查询：获取用户首次下单时间
  SELECT 
    user_id, 
    MIN(order_time) AS first_order_time  -- 首次下单时间（最早的订单时间）
  FROM orders
  WHERE order_status = 'paid'
  GROUP BY user_id
)
SELECT 
  u.user_id,
  u.first_order_time,  -- 首次下单时间
  o.order_id,  -- 首次订单ID
  p.product_name,  -- 首次购买的商品名称
  o.order_amount  -- 首次订单金额
FROM user_first_order u
-- 关联首次订单详情
JOIN orders o ON u.user_id = o.user_id 
  AND u.first_order_time = o.order_time
-- 关联商品表获取名称
JOIN products p ON o.product_id = p.product_id;

场景：分析新用户首次购买偏好（如：品类、价格带），我们可针对性优化新用户福利（如：首次购买赠券、推荐同类商品），提升新用户留存。

（28）计算用户平均客单价（按会员等级）

-- 按会员等级分组，统计用户平均每次下单的金额
SELECT 
  m.member_level,  -- 会员等级（如："普通"、"白银"、"黄金"）
  AVG(o.order_amount) AS avg_order_value  -- 平均客单价
FROM orders o
-- 关联用户表
JOIN users u ON o.user_id = u.user_id
-- 关联会员等级表
JOIN members m ON u.member_id = m.member_id
WHERE o.order_status = 'paid'  -- 仅统计有效订单
GROUP BY m.member_level
ORDER BY avg_order_value DESC;  -- 按客单价降序

场景：分析会员等级与消费能力的关系，若高等级会员客单价未显著高于低等级，我们需优化会员权益（如：专属折扣、积分翻倍），激励高等级会员消费。

（29）查询用户最近30天的消费总额

-- 统计每个用户近30天的总消费金额
SELECT 
  user_id,
  SUM(order_amount) AS total_consumption  -- 总消费
FROM orders
WHERE 
  order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)  -- 近30天
  AND order_status = 'paid'  -- 仅统计已支付订单
GROUP BY user_id
ORDER BY total_consumption DESC;  -- 按消费额降序（高消费用户在前）

场景：识别近期高价值用户，我们可定向推送专属优惠（如：“消费满额赠礼”）或邀请参与VIP活动，提升用户粘性。

（30）分析用户购买间隔（上次购买到本次购买的平均天数）

-- 计算用户两次购买之间的平均间隔天数
WITH user_order_rn AS (
  -- 子查询：给用户的订单按时间排序（生成序号）
  SELECT 
    user_id,
    order_time,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time) AS rn  -- 订单序号（1=首次，2=第二次...）
  FROM orders
  WHERE order_status = 'paid'  -- 仅统计有效订单
)
SELECT 
  user_id,
  -- 计算平均间隔天数（当前订单时间-上一次订单时间）
  AVG(DATEDIFF(curr.order_time, prev.order_time)) AS avg_interval_days
FROM user_order_rn curr
-- 关联上一次订单（序号=当前序号-1）
JOIN user_order_rn prev ON curr.user_id = prev.user_id 
  AND curr.rn = prev.rn + 1
GROUP BY user_id
HAVING COUNT(*) >= 2;  -- 仅统计购买2次以上的用户

场景：购买间隔反映用户消费周期，间隔短的用户可推送高频需求商品，间隔长的用户可在间隔期内发送唤醒优惠（如：“好久不见，专属8折券”）。

（31）统计用户访问商品详情页次数（PV）

SELECT 
  target_id AS product_id,  -- 商品ID（行为表中target_id存储商品ID）
  p.product_name,  -- 商品名称
  COUNT(*) AS view_count  -- 访问次数（PV）
FROM 
  user_behavior  -- 用户行为表（记录浏览、点击等行为）
JOIN 
  products p ON user_behavior.target_id = p.product_id  -- 关联商品表
WHERE 
  behavior_type = 'view_detail'  -- 行为类型：查看详情
  AND behavior_time BETWEEN '2024-05-01' AND '2024-05-31'  -- 限定5月
GROUP BY 
  target_id, p.product_name
ORDER BY 
  view_count DESC
LIMIT 10;  -- 取访问量前10的商品

注释：通过用户行为日志分析商品关注度，访问量高但销量低的商品可能存在转化问题（如：价格、评价），可优化详情页或调整营销策略。

（32）分析用户加购未下单商品（流失预警）

-- 加购后7天内未下单的商品
SELECT 
  c.product_id,
  p.product_name,
  COUNT(DISTINCT c.user_id) AS user_count  -- 加购未下单的用户数
FROM 
  cart_items c  -- 购物车表
-- 左连接：关联加购后7天内的订单
LEFT JOIN 
  orders o ON c.user_id = o.user_id 
  AND c.product_id = o.product_id 
  AND o.create_time BETWEEN c.update_time AND DATE_ADD(c.update_time, INTERVAL 7 DAY)
JOIN 
  products p ON c.product_id = p.product_id  -- 关联商品表
WHERE 
  o.order_id IS NULL  -- 无对应订单（未下单）
  AND c.update_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)  -- 近30天加购
GROUP BY 
  c.product_id, p.product_name
ORDER BY 
  user_count DESC;  -- 按未下单用户数降序

注释：识别高加购但低转化的商品，我们可针对性推出优惠券（如：“该商品立减20元”）或发送库存预警（如：“仅剩5件”），刺激用户下单。

（33）查询用户最近浏览的10件商品

SELECT 
  target_id AS product_id,  -- 商品ID
  p.product_name,  -- 商品名称
  p.price,  -- 当前价格
  behavior_time AS view_time  -- 浏览时间
FROM 
  user_behavior  -- 用户行为表
JOIN 
  products p ON target_id = p.product_id  -- 关联商品表
WHERE 
  user_id = 88888  -- 当前用户
  AND behavior_type = 'view_detail'  -- 行为类型：查看详情
ORDER BY 
  behavior_time DESC  -- 按浏览时间倒序（最新的在前）
LIMIT 10;  -- 取最近10件

注释：用于“最近浏览”功能，帮助用户快速找到之前关注的商品，提升复访率和转化率，尤其适用于用户未加购但有兴趣的商品。

四、营销与活动管理

1、优惠工具操作

（34）新增优惠券

INSERT INTO coupons (
  coupon_id,  -- 优惠券ID
  coupon_name,  -- 优惠券名称（如："满200减50"）
  discount_type,  -- 优惠类型（1=满减，2=折扣）
  discount_value,  -- 优惠值（满减50元则为50，9折则为90）
  min_spend,  -- 最低消费门槛（满200减50则为200）
  start_time,  -- 有效期开始时间
  end_time  -- 有效期结束时间
) 
VALUES (
'CP202405', 
'夏季大促满200减50',  -- 名称需清晰说明优惠规则
  1,  -- 满减类型
  50,  -- 减50元
  200,  -- 满200可用
'2024-05-01 00:00:00',  -- 开始时间
'2024-05-31 23:59:59'  -- 结束时间（精确到秒）
);

注释：discount_type区分优惠类型，min_spend控制使用门槛，start_time和end_time限制有效期，确保优惠券规则明确。

（35）用户领取优惠券

INSERT INTO user_coupons (
  user_id,  -- 领取用户ID
  coupon_id,  -- 优惠券ID
  get_time,  -- 领取时间
  is_used,  -- 是否使用（0=未使用，1=已使用）
  use_time  -- 使用时间（未使用则为NULL）
) 
VALUES (
  88888, 
  'CP202405',  -- 优惠券ID
  NOW(),  -- 领取时间
  0,  -- 未使用
  NULL  -- 未使用，使用时间为空
);

注释：记录用户领取的优惠券，is_used和use_time用于跟踪使用状态，避免重复使用。

（36）查询用户可用优惠券（未过期、未使用）

SELECT 
  c.coupon_id,
  c.coupon_name,  -- 优惠券名称
  c.discount_type,  -- 优惠类型
  c.discount_value,  -- 优惠值
  c.min_spend,  -- 门槛
  c.end_time  -- 有效期
FROM 
  user_coupons uc  -- 用户优惠券关联表
JOIN 
  coupons c 
  ON uc.coupon_id = c.coupon_id  -- 关联优惠券表
WHERE 
  uc.user_id = 88888 
  AND uc.is_used = 0  -- 未使用
  AND c.end_time >= NOW();  -- 未过期（当前时间在有效期内）

注释：用户下单时展示可用优惠券，条件需同时满足“未使用”和“未过期”，确保推荐有效的优惠券。

2、活动分析

（37）统计促销活动期间的销售额

SELECT 
  a.activity_id,  -- 活动ID
  a.activity_name,  -- 活动名称（如："618大促"）
  SUM(o.pay_amount) AS activity_sales,  -- 活动期间总销售额
  COUNT(DISTINCT o.order_id) AS order_count  -- 活动订单数
FROM 
  activities a  -- 活动表
-- 关联订单与活动的关联表
JOIN 
  order_promotions op ON a.activity_id = op.activity_id
-- 关联订单表
JOIN 
  orders o ON op.order_id = o.order_id
WHERE 
  a.activity_name = '618大促'  -- 指定活动
  AND o.order_status IN (1, 2, 3)  -- 有效订单
GROUP BY 
  a.activity_id, a.activity_name;

注释：评估活动效果的核心指标，我们通过对比活动前后的销售额和订单数，判断活动是否达到预期，为后续活动策划提供参考。

（38）查询活动期间使用优惠券的订单占比

SELECT 
  -- 用券订单占比=使用优惠券的订单数/总订单数
  ROUND(COUNT(DISTINCT CASE WHEN op.coupon_id IS NOT NULL THEN o.order_id END) / 
  COUNT(DISTINCT o.order_id)*100, 2) AS coupon_use_rate
FROM 
  orders o  -- 订单表
-- 左连接：关联订单使用的优惠券
LEFT JOIN 
  order_promotions op ON o.order_id = op.order_id
WHERE 
  -- 活动期间（如：618活动：6月1日-6月20日）
  o.create_time BETWEEN '2024-06-01' AND '2024-06-20'
  AND o.order_status IN (1, 2, 3);  -- 有效订单

注释：分析优惠券对活动转化的拉动作用，若用券订单占比低，可能是优惠券门槛过高或宣传不足，我们可调整优惠券规则或加强推广。

（39）查询近7天每日新增用户数

-- 统计最近7天每天的新注册用户数量
SELECT 
  DATE(register_time) AS register_date,  -- 注册日期（仅日期部分）
  COUNT(user_id) AS new_user_count  -- 新增用户数
FROM users
WHERE 
  register_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)  -- 近7天
  AND register_time < CURDATE()  -- 排除今天（未结束，数据不完整）
GROUP BY register_date
ORDER BY register_date;  -- 按日期排序

场景：评估近期拉新活动效果（如：广告投放、裂变活动），若某天新增用户骤降，需检查渠道是否异常；若骤增，可分析原因并复制成功经验。

（40）分析优惠券使用转化率（领取后使用的比例）

-- 计算每张优惠券的使用转化率：使用次数/领取次数
SELECT 
  c.coupon_id,
  c.coupon_name,  -- 优惠券名称（如："满200减50"）
  -- 使用次数（已使用的用户数）
  COUNT(DISTINCT CASE WHEN uc.is_used = 1 THEN uc.user_id END) / 
  -- 领取次数（总领取用户数）
  COUNT(DISTINCT uc.user_id) AS conversion_rate
FROM user_coupons uc  -- 用户领取优惠券表
-- 关联优惠券表获取名称
JOIN coupons c ON uc.coupon_id = c.coupon_id
GROUP BY c.coupon_id, c.coupon_name
ORDER BY conversion_rate DESC;  -- 按转化率降序

场景：评估优惠券吸引力，低转化率的优惠券可能存在门槛过高、优惠力度不足等问题，我们可调整规则（如：降低门槛、提高面额）提升使用率。

五、库存与物流管理

1、库存操作

（41）检查商品库存是否充足

SELECT 
  product_id, 
  product_name, 
  stock_quantity  -- 当前库存
FROM 
  products 
WHERE 
  product_id IN (1001, 1002)  -- 待检查的商品ID（如：订单中的商品）
  AND stock_quantity < 5;  -- 库存不足5个（可根据订单数量动态调整阈值）

注释：下单前的关键校验步骤，若查询结果不为空，说明部分商品库存不足，需提示用户或阻止下单，避免超卖。

（42）标记商品为缺货

UPDATE products 
SET 
  is_stockout = 1,  -- 1=缺货状态（前端可隐藏购买按钮）
  update_time = NOW()  -- 记录状态更新时间
WHERE 
  stock_quantity = 0;  -- 库存为0时自动标记

注释：可通过定时任务执行，当库存为0时自动标记缺货，提升用户体验（避免用户下单后发现无货），同时便于我们运营人员及时补货。

（43）批量查询商品库存（用在补货提醒）

SELECT 
  product_id,
  product_name,
  stock_quantity,  -- 当前库存
  -- 计算库存可售天数（按近30天平均销量）
  ROUND(stock_quantity / NULLIF(avg_daily_sales, 0), 1) AS sellable_days
FROM (
  -- 子查询：计算近30天平均日销量
  SELECT 
    p.product_id,
    p.product_name,
    p.stock_quantity,
    -- 总销量/30天=平均日销量（NULLIF避免除以0）
    SUM(oi.quantity) / NULLIF(30, 0) AS avg_daily_sales
  FROM 
    products p
  -- 左连接：关联近30天的订单详情
  LEFT JOIN 
    order_items oi ON p.product_id = oi.product_id
  -- 左连接：关联有效订单
  LEFT JOIN 
    orders o ON oi.order_id = o.order_id 
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND o.order_status IN (1, 2, 3)
  GROUP BY 
    p.product_id, p.product_name, p.stock_quantity
) AS temp
WHERE 
  sellable_days < 7;  -- 可售天数不足7天，需要补货

注释：供应链管理核心查询，通过历史销量预测库存耗尽时间，可售天数不足7天的商品需优先补货，避免缺货；新商品（无历史销量）可通过NULLIF处理，避免计算错误。

（44）统计仓库库存分布

SELECT 
  w.warehouse_id,  -- 仓库ID
  w.warehouse_name,  -- 仓库名称（如："上海仓"、"广州仓"）
  c.category_name,  -- 商品分类
  SUM(i.stock_quantity) AS total_stock  -- 该仓库的分类库存总量
FROM 
  inventory i  -- 库存表（分仓库存储）
JOIN 
  warehouses w ON i.warehouse_id = w.warehouse_id  -- 关联仓库表
JOIN 
  products p ON i.product_id = p.product_id  -- 关联商品表
JOIN 
  categories c ON p.category_id = c.category_id  -- 关联类目表
GROUP BY 
  w.warehouse_id, w.warehouse_name, c.category_name
ORDER BY 
  w.warehouse_id, total_stock DESC;  -- 按仓库和库存降序

注释：用于平衡各仓库的库存分布，若某类目在A仓库库存积压而在B仓库缺货，可进行调拨；同时为我们仓库选址和备货提供数据支持（如：在订单密集区增加热门类目库存）。

2、物流操作

（45）新增物流信息

INSERT INTO logistics (
  order_id,  -- 关联的订单ID
  logistics_company,  -- 快递公司（如："顺丰"、"圆通"）
  logistics_no,  -- 物流单号（唯一，用在查询轨迹）
  send_time,  -- 发货时间
  status  -- 物流状态（1=已发货，2=运输中，3=已签收等）
) 
VALUES (
'ORD20240520001', 
'顺丰速运', 
'SF1234567890123',  -- 完整物流单号（需准确无误）
  NOW(),  -- 用当前时间作为发货时间
  1  -- 初始状态为“已发货”
);

注释：物流信息需与订单一一对应，logistics_no需完整准确（便于用户查询轨迹），send_time和status用在跟踪物流进度，后续可通过UPDATE语句更新状态（如：运输中、已签收）。

（46）更新物流状态（已签收）

UPDATE logistics 
SET 
  status = 3,  -- 3=已签收状态
  receive_time = NOW(),  -- 记录签收时间
  update_time = NOW()  -- 记录状态更新时间
WHERE 
  logistics_no = 'SF1234567890123'  -- 按物流单号定位
  AND order_id = 'ORD20240520001';  -- 双重条件确保准确性（防单号重复）

注释：签收时更新状态并记录receive_time，形成完整的物流时间线；同时匹配logistics_no和order_id，避免因单号重复导致的错误更新，确保数据准确性。

（47）查询订单物流轨迹

SELECT 
  l.logistics_company,  -- 快递公司
  l.logistics_no,  -- 物流单号
  l.send_time,  -- 发货时间
  l.receive_time,  -- 签收时间
  -- 转换状态码为文字描述（提升可读性）
  CASE l.status 
    WHEN 1 THEN '已发货'
    WHEN 2 THEN '运输中'
    WHEN 3 THEN '已签收'
    ELSE '异常'
  END AS status_text,
  -- 关联收货地址信息
  ua.receiver,  -- 收货人
  ua.phone,  -- 联系电话
  CONCAT(ua.province, ua.city, ua.detail_address) AS receive_address  -- 完整收货地址
FROM 
  logistics l  -- 物流表
JOIN 
  orders o 
  ON l.order_id = o.order_id  -- 关联订单主表
JOIN 
  user_addresses ua 
  ON o.address_id = ua.address_id  -- 关联收货地址表
WHERE 
  l.order_id = 'ORD20240520001';  -- 按订单号查询

注释：通过多表关联一次性获取物流信息、收货地址和联系人，CASE函数将状态码转换为用户易懂的文字，方便用户查看完整物流轨迹，提升透明度。

（48）统计各快递公司的发货效率（平均配送时长）

SELECT 
  logistics_company,  -- 快递公司
  -- 计算平均配送时长（签收时间-发货时间，单位：小时）
  AVG(TIMESTAMPDIFF(HOUR, send_time, receive_time)) AS avg_delivery_hours,
  COUNT(logistics_no) AS delivery_count  -- 配送单量
FROM 
  logistics 
WHERE 
  status = 3  -- 仅统计已签收的订单（确保配送完成）
  AND send_time BETWEEN '2024-05-01' AND '2024-05-31'  -- 限定5月数据
GROUP BY 
  logistics_company  -- 按快递公司分组
ORDER BY 
  avg_delivery_hours;  -- 按平均时长升序（最快的在前）

注释：TIMESTAMPDIFF函数计算发货到签收的小时数，用在评估快递公司效率，结果可用在选择合作物流商（优先与配送快、单量大的公司合作），优化物流体验。

3、多仓调拨

（49）查询商品在各仓库的库存分布

-- 查看指定商品在所有仓库的库存数量
SELECT 
  w.warehouse_id,  -- 仓库ID
  w.warehouse_name,  -- 仓库名称（如："广州保税仓"、"上海自贸区仓"）
  i.stock_quantity,  -- 该仓库的库存
  i.locked_quantity  -- 已锁定库存（如：已下单未发货）
FROM 
  inventory i  -- 库存表（分仓库）
JOIN 
  warehouses w ON i.warehouse_id = w.warehouse_id  -- 关联仓库表
WHERE 
  i.product_id = 2001  -- 指定商品
ORDER BY 
  i.stock_quantity DESC;  -- 按库存降序

注释：多仓模式下，需掌握商品在各仓库的分布，为调拨决策提供依据（如：A仓库缺货时从B仓库调拨）；locked_quantity用于判断实际可调拨库存（可用库存=stock_quantity - locked_quantity）。

（50）生成库存调拨单（从A仓库调往B仓库）

-- 新增调拨单，记录调拨方向和数量
INSERT INTO inventory_transfers (
  transfer_id,  -- 调拨单ID
  product_id,  -- 调拨商品ID
  from_warehouse_id,  -- 调出仓库ID
  to_warehouse_id,    -- 调入仓库ID
  quantity,           -- 调拨数量
  transfer_status,    -- 状态（0=待审核，1=已调出，2=已入库）
  apply_time          -- 申请时间
) 
VALUES (
'TF20240601001',
  2001,  -- 商品ID
  101,   -- 广州仓（调出）
  102,   -- 上海仓（调入）
  50,    -- 调拨50件
  0,     -- 待审核
  NOW()  -- 申请时间
);

注释：调拨单需明确调出/调入仓库、数量和状态，状态流转用在跟踪调拨全流程（申请→审核→调出→入库），确保库存变动可追溯。

（51）调拨后更新两仓库库存

-- 事务：确保调出和调入仓库库存同步更新
BEGIN;  -- 开始事务

-- 调出仓库减少库存
UPDATE inventory 
SET 
  stock_quantity = stock_quantity - 50,
  update_time = NOW()
WHERE 
  product_id = 2001 
  AND warehouse_id = 101;

-- 调入仓库增加库存
UPDATE inventory 
SET 
  stock_quantity = stock_quantity + 50,
  update_time = NOW()
WHERE 
  product_id = 2001 
  AND warehouse_id = 102;

-- 更新调拨单状态为“已调出”
UPDATE inventory_transfers 
SET transfer_status = 1, out_time = NOW() 
WHERE transfer_id = 'TF20240601001';

COMMIT;  -- 提交事务（若失败则执行ROLLBACK回滚）

注释：使用事务保证库存数据一致性，避免单边更新（如：仅减少调出仓库库存而未增加调入仓库库存）导致的库存错乱；状态更新同步记录时间，形成完整的操作日志。

（52）统计仓库间调拨频率（优化调拨策略）

-- 统计各仓库间的调拨次数和总数量
SELECT 
  from_warehouse_id,
  w1.warehouse_name AS 调出仓库,
  to_warehouse_id,
  w2.warehouse_name AS 调入仓库,
  COUNT(transfer_id) AS 调拨次数,
  SUM(quantity) AS 总调拨数量
FROM 
  inventory_transfers  -- 调拨单表
JOIN 
  warehouses w1 ON from_warehouse_id = w1.warehouse_id  -- 关联调出仓库
JOIN 
  warehouses w2 ON to_warehouse_id = w2.warehouse_id  -- 关联调入仓库
WHERE 
  transfer_status = 2  -- 已完成的调拨
  AND apply_time BETWEEN '2024-01-01' AND '2024-06-30'  -- 上半年数据
GROUP BY 
  from_warehouse_id, to_warehouse_id, w1.warehouse_name, w2.warehouse_name
ORDER BY 
  总调拨数量 DESC;  -- 按总调拨数量降序

注释：分析高频调拨路线（如：A仓库→B仓库频繁调拨），可优化我们仓库备货策略（如：在B仓库增加该商品的安全库存），减少调拨频率和物流成本。

六、售后与评价管理

（53）新增商品评价

INSERT INTO product_reviews (
  review_id,  -- 评价ID
  order_id,  -- 关联的订单（确保已购买才能评价）
  product_id,  -- 被评价商品ID
  user_id,  -- 评价用户ID
  rating,  -- 评分（1-5星，5星为最佳）
  content,  -- 评价内容（文字描述）
  review_time,  -- 评价时间
  is_anonymous  -- 是否匿名（1=匿名，0=不匿名）
) 
VALUES (
'REV20240521001',
'ORD20240520001',  -- 关联订单（防刷评）
  1001,  -- 商品ID
  88888,  -- 用户ID
  5,  -- 五星好评
'音质很好，续航持久',  -- 评价内容
  NOW(),  -- 评价时间
  0  -- 不匿名
);

注释：关联order_id是防止刷评的关键（确保用户只能评价已购买的商品），is_anonymous字段保护用户隐私，前端可根据该值决定是否显示用户名。

（54）查询商品的平均评分及评价数

SELECT 
  p.product_id,  -- 商品ID
  p.product_name,  -- 商品名称
  ROUND(AVG(r.rating), 1) AS avg_rating,  -- 平均评分（保留1位小数）
  COUNT(r.review_id) AS review_count  -- 总评价数
FROM 
  products p  -- 商品表
LEFT JOIN 
  product_reviews r 
  ON p.product_id = r.product_id  -- 关联评价表
WHERE 
  p.product_id = 1001  -- 查询指定商品
GROUP BY 
  p.product_id, p.product_name;  -- 按商品分组

注释：商品详情页核心数据，LEFT JOIN确保即使商品无评价也能返回信息（平均分为NULL，前端可处理为“暂无评价”），ROUND函数使评分更易读，帮助用户决策。

（55）申请退货

INSERT INTO returns (
  return_id,  -- 退货单ID
  order_id,  -- 关联的原订单ID
  user_id,  -- 申请用户ID
  product_id,  -- 退货商品ID
  return_quantity,  -- 退货数量
  reason,  -- 退货原因（如："质量问题"、"尺寸不符"）
  apply_time,  -- 申请时间
  return_status  -- 状态（0=申请中，1=已同意，2=已拒绝）
) 
VALUES (
'RET20240522001',
'ORD20240520001',  -- 关联原订单
  88888,  -- 用户ID
  1001,  -- 商品ID
  1,  -- 退货1件
'尺寸不符',  -- 退货原因
  NOW(),  -- 申请时间
  0  -- 初始状态为“申请中”
);

注释：退货单与原订单强关联，记录退货数量和原因，便于我们售后人员处理，return_status跟踪退货进度（申请中→已同意→已退货→已退款）。

（56）同意退货后更新订单状态

-- 事务：确保退货状态和订单状态同步更新（原子性操作）
BEGIN;  -- 开始事务

-- 更新退货单状态为“已同意”
UPDATE returns 
SET 
  return_status = 1,  -- 1=已同意
  approve_time = NOW()  -- 记录同意时间
WHERE 
  return_id = 'RET20240522001';

-- 同步更新原订单状态为“退货中”
UPDATE orders 
SET 
  order_status = 4,  -- 4=退货中（自定义状态码）
  update_time = NOW()  -- 记录状态更新时间
WHERE 
  order_id = 'ORD20240520001';

COMMIT;  -- 提交事务（若失败则执行ROLLBACK回滚）

注释：使用事务（BEGIN/COMMIT）确保两个更新操作要么同时成功，要么同时失败，避免数据不一致（如：退货单同意但订单状态未更新）；订单状态同步后，前端可向用户展示“退货处理中”标识，提升透明度。

七、数据分析与报表

1、核心业务分析

（57）统计每日订单量与销售额

SELECT 
  DATE(create_time) AS order_date,  -- 按日期分组（忽略时分秒）
  COUNT(DISTINCT order_id) AS order_count,  -- 订单量（去重，避免重复统计）
  SUM(pay_amount) AS total_sales  -- 销售额（实付金额总和）
FROM 
  orders 
WHERE 
  order_status IN (1, 2, 3)  -- 已支付/已完成的有效订单
  AND create_time BETWEEN '2024-05-01' AND '2024-05-31'  -- 限定5月数据
GROUP BY 
  DATE(create_time)  -- 按日期分组
ORDER BY 
  order_date;  -- 按日期排序

注释：电商运营核心报表，DATE(create_time)将时间戳转换为日期，COUNT(DISTINCT)确保每个订单只被统计一次，用在监控每日销售趋势。

（58）热销商品TOP10（按销量）

SELECT 
  oi.product_id,  -- 商品ID
  p.product_name,  -- 商品名称
  SUM(oi.quantity) AS total_sales_quantity  -- 总销量
FROM 
  order_items oi  -- 订单详情表
JOIN 
  products p 
  ON oi.product_id = p.product_id  -- 关联商品表
JOIN 
  orders o 
  ON oi.order_id = o.order_id  -- 关联订单主表
WHERE 
   o.order_status IN (1, 2, 3)  -- 仅统计有效订单（已支付/已发货/已完成）
  AND o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)  -- 限定近30天数据
GROUP BY 
  oi.product_id, p.product_name  -- 按商品分组
ORDER BY 
  total_sales_quantity DESC  -- 按销量倒序排列
LIMIT 10;  -- 取前10名

注释：通过多表关联筛选有效订单，SUM(quantity)计算商品总销量，结果可用在首页推荐、库存调整和采购计划制定，是我们运营决策的重要依据。

（59）新用户转化率（注册后7天内下单）

SELECT 
  COUNT(DISTINCT u.user_id) AS total_new_users,  -- 总新用户数（近30天注册）
  COUNT(DISTINCT o.user_id) AS paid_users,  -- 7天内下单的用户数
  -- 转化率=下单用户数/总新用户数（保留2位小数，百分比形式）
  ROUND(COUNT(DISTINCT o.user_id)/COUNT(DISTINCT u.user_id)*100, 2) AS conversion_rate
FROM 
  users u  -- 用户表
LEFT JOIN 
  orders o 
  ON u.user_id = o.user_id
  -- 关联条件：订单创建时间在注册后7天内
  AND o.create_time BETWEEN u.register_time AND DATE_ADD(u.register_time, INTERVAL 7 DAY)
  AND o.order_status IN (1, 2, 3)  -- 仅统计有效订单
WHERE 
  u.register_time BETWEEN '2024-05-01' AND '2024-05-31';  -- 限定5月新注册用户

注释：LEFT JOIN确保所有新用户都被统计（包括未下单用户），DATE_ADD限定7天转化窗口期，ROUND函数将转化率格式化，用于我们评估拉新活动的质量和用户留存效果。

2、财务报表

（60）统计每日退款金额

SELECT 
  DATE(refund_time) AS refund_date,  -- 退款日期
  SUM(refund_amount) AS total_refund  -- 当日总退款金额
FROM 
  refunds  -- 退款表
WHERE 
  refund_status = 2  -- 2=退款成功（仅统计实际退款）
  AND refund_time BETWEEN '2024-05-01' AND '2024-05-31'  -- 限定5月
GROUP BY 
  refund_date
ORDER BY 
  refund_date;  -- 按日期排序

注释：财务报表必备指标，监控每日退款金额和趋势，若某日退款骤增，我们需排查原因（如：批量质量问题）；同时用于核算实际营收（销售额-退款金额）。

（61）查询高价值用户（年消费超10000元）

SELECT 
  u.user_id,
  u.username,
  u.phone,  -- 联系方式（用于VIP服务）
  SUM(o.pay_amount) AS annual_consumption  -- 年消费总额
FROM 
  users u  -- 用户表
JOIN 
  orders o ON u.user_id = o.user_id  -- 关联订单表
WHERE 
  -- 限定当年（2024年）
  o.create_time BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.order_status IN (1, 2, 3)  -- 有效订单
GROUP BY 
  u.user_id, u.username, u.phone
HAVING 
  annual_consumption >= 10000  -- 筛选高价值用户（年消费超10000元）
ORDER BY 
  annual_consumption DESC;  -- 按消费额降序

注释：用于VIP客户分层管理，可为高价值用户提供专属服务（如：一对一客服、优先发货、生日礼遇），提升用户忠诚度和复购率。

八、系统与特殊场景管理

1、系统管理

（62）清理过期的未支付订单

DELETE FROM orders 
WHERE 
  order_status = 0  -- 状态为未支付
  AND create_time < DATE_SUB(NOW(), INTERVAL 24 HOUR);  -- 超过24小时未支付

注释：通过定时任务（如：每日凌晨）执行，释放过期订单占用的库存、优惠券等资源，避免资源浪费；删除前可先备份订单数据（如：归档到历史表）。

（63）备份用户订单数据（按月份）

-- 创建当月订单备份表（如：2024年5月）
CREATE TABLE IF NOT EXISTS orders_backup_202405 AS
SELECT * FROM orders 
WHERE 
  create_time BETWEEN '2024-05-01' AND '2024-05-31';  -- 5月数据

注释：定期备份历史订单数据，减轻主表存储压力，提升查询性能；备份表可按月份分区，便于后续数据查询和分析（如：年度报表）。

（64）统计各表数据量（系统监控）

-- 统计核心表的数据量，用在系统监控和扩容预警
SELECT 'users' AS table_name, COUNT(*) AS row_count UNION ALL
SELECT 'products', COUNT(*) UNION ALL
SELECT 'orders', COUNT(*) UNION ALL
SELECT 'order_items', COUNT(*) UNION ALL
SELECT 'cart_items', COUNT(*);

注释：系统运维常用查询，监控核心表数据增长趋势，若某表数据量骤增（如：订单表），需提前扩容数据库或优化索引，避免性能下降。

2、跨境电商税务

（65）统计单票订单的进口关税

-- 计算订单的进口关税（关税=完税价格×关税税率）
SELECT 
  o.order_id,
  o.product_id,
  p.product_name,
  o.quantity,  -- 购买数量
  p.unit_price AS 单价,
  p.unit_price * o.quantity AS 完税价格,  -- 完税价格=单价×数量
  t.tariff_rate AS 关税税率,  -- 从税率表获取对应商品的关税税率
  (p.unit_price * o.quantity) * t.tariff_rate AS 进口关税
FROM 
  cross_border_orders o  -- 跨境订单表
JOIN 
  products p ON o.product_id = p.product_id  -- 关联商品表
JOIN 
  tariff_rates t ON p.hs_code = t.hs_code  -- 通过HS编码关联关税税率表
WHERE 
  o.order_id = 'CB20240601001';  -- 单票订单

注释：跨境订单需按商品HS编码（海关编码）匹配关税税率，完税价格通常为商品成交价格，用在我们向海关申报纳税，确保合规。

（66）按月统计增值税总额（含进口增值税）

-- 统计每月进口增值税和国内增值税总和
SELECT 
  DATE_FORMAT(o.order_time, '%Y-%m') AS 月份,
  -- 进口增值税（进口环节缴纳，税率通常为13%）
  SUM(o.dutiable_value * 0.13) AS 进口增值税,
  -- 国内增值税（平台销售环节缴纳，按不含税销售额计算）
  SUM(o.pay_amount / 1.13 * 0.13) AS 国内增值税,
  -- 合计
  SUM(o.dutiable_value * 0.13 + o.pay_amount / 1.13 * 0.13) AS 增值税总额
FROM 
  cross_border_orders o  -- 跨境订单表
WHERE 
  o.order_status = 'paid'  -- 已支付订单
  AND o.order_time BETWEEN '2024-01-01' AND '2024-12-31'  -- 当年数据
GROUP BY 
  月份
ORDER BY 
  月份;

注释：跨境电商需区分进口环节增值税（由海关征收）和国内销售环节增值税（由税务机关征收），按月份统计便于税务申报和财务核算。

（67）查询异常关税订单（税率或完税价格异常）

-- 筛选关税计算异常的订单（如：税率为0但属于应税商品）
SELECT 
  o.order_id,
  p.hs_code,  -- 商品HS编码
  p.product_name,
  t.tariff_rate,  -- 关税税率
  o.dutiable_value  -- 完税价格
FROM 
  cross_border_orders o  -- 跨境订单表
JOIN 
  products p ON o.product_id = p.product_id  -- 关联商品表
LEFT JOIN 
  tariff_rates t ON p.hs_code = t.hs_code  -- 关联关税税率表
WHERE 
  (t.tariff_rate IS NULL OR t.tariff_rate = 0)  -- 无税率或税率为0
  AND p.is_taxable = 1;  -- 商品标记为应税

注释：通过HS编码匹配异常检测，避免因税率错误导致的漏税或申报问题，确保跨境业务合规；异常订单需人工审核修正。

3、用户分层运营

（68）RFM模型用户分层（最近消费、消费频率、消费金额）

-- 按RFM指标将用户分为5层（如：高价值、潜力、流失等）
WITH rfm AS (
  SELECT 
    user_id,
    -- 最近消费时间（距今天数，越小越好）
    DATEDIFF(NOW(), MAX(order_time)) AS recency,
    -- 消费频率（订单数，越大越好）
    COUNT(order_id) AS frequency,
    -- 消费金额（总金额，越大越好）
    SUM(pay_amount) AS monetary
  FROM 
    orders
  WHERE 
    order_status = 'paid'
    AND order_time >= DATE_SUB(NOW(), INTERVAL 180 DAY)  -- 近6个月数据
  GROUP BY 
    user_id
)
SELECT 
  user_id,
  recency,
  frequency,
  monetary,
  -- 分层规则（示例）：
  -- R≤30天、F≥5次、M≥5000 → 高价值用户
  -- R≤30天、F<5次、M≥3000 → 潜力用户
  -- R>90天、F≥3 → 流失预警用户
  -- 其他 → 普通用户
  CASE 
    WHEN recency <= 30 AND frequency >= 5 AND monetary >= 5000 THEN '高价值用户'
    WHEN recency <= 30 AND frequency < 5 AND monetary >= 3000 THEN '潜力用户'
    WHEN recency > 90 AND frequency >= 3 THEN '流失预警用户'
    ELSE '普通用户'
  END AS user_level
FROM 
  rfm;

注释：RFM模型是用户分层的经典方法，通过最近消费（Recency）、消费频率（Frequency）、消费金额（Monetary）三个维度划分用户群体，为精细化运营提供依据（如：对高价值用户提供专属权益，对流失用户推送召回优惠）。

（69）高价值用户专属权益发放

-- 给高价值用户发放专属优惠券（批量操作）
INSERT INTO user_coupons (user_id, coupon_id, get_time, is_used)
SELECT 
  user_id,
  'VIP202406',  -- 高价值用户专属券（如："满1000减200"）
  NOW(),  -- 发放时间
  0  -- 未使用
FROM 
  user_levels  -- 引用RFM分层结果表（存储user_id和user_level）
WHERE 
  user_level = '高价值用户'
  -- 排除已领取用户（避免重复发放）
  AND user_id NOT IN (SELECT user_id FROM user_coupons WHERE coupon_id = 'VIP202406');

注释：针对高价值用户定向发放高力度优惠券，提升用户满意度和复购率；NOT IN条件避免重复发放，节省我们的营销成本。

（70）流失用户召回筛选（近90天未消费但历史有购买）

-- 筛选近90天未消费但历史有2次以上购买的用户（可召回）
SELECT 
  u.user_id,
  u.username,
  MAX(o.order_time) AS last_order_time,  -- 最后消费时间
  COUNT(o.order_id) AS total_orders  -- 历史订单数
FROM 
  users u  -- 用户表
JOIN 
  orders o ON u.user_id = o.user_id  -- 关联订单表
WHERE 
  o.order_status = 'paid'  -- 有效订单
  -- 近90天无消费
  AND o.order_time < DATE_SUB(NOW(), INTERVAL 90 DAY)
  -- 历史至少2单（有复购记录，值得召回）
  AND EXISTS (
    SELECT 1 FROM orders 
    WHERE user_id = u.user_id AND order_status = 'paid'
    GROUP BY user_id HAVING COUNT(*) >= 2
  )
GROUP BY 
  u.user_id, u.username;

注释：识别有复购历史但近期未消费的用户，可通过定向优惠（如：“回归礼包”、“专属折扣”）激活，挽回流失用户，提升整体复购率。

（71）新用户首单后7天复购激励

-- 筛选首单后7天内未复购的新用户，推送复购券
SELECT 
  u.user_id,
  u.register_time,  -- 注册时间
  o1.order_time AS first_order_time,  -- 首单时间
  DATEDIFF(NOW(), o1.order_time) AS days_since_first  -- 首单后天数
FROM 
  users u  -- 用户表
JOIN 
  orders o1 ON u.user_id = o1.user_id  -- 首单
-- 左连接：关联首单后7天内的复购单
LEFT JOIN 
  orders o2 ON u.user_id = o2.user_id 
  AND o2.order_time > o1.order_time 
  AND o2.order_time <= DATE_ADD(o1.order_time, INTERVAL 7 DAY)
WHERE 
  o1.is_first_order = 1  -- 标记为首单
  AND o2.order_id IS NULL  -- 7天内无复购
  -- 首单后14天内（召回窗口期，超过则效果下降）
  AND DATEDIFF(NOW(), o1.order_time) <= 14;

注释：新用户首单后7-14天是复购关键期，筛选未复购用户推送限时复购券（如：“首单后14天内再购立减30元”），可显著提升新用户留存率，培养消费习惯。

总之，这份71条SQL语句模板就是我们电商人的“数据小助手”。不管我们是搞开发、做分析，还是干运营，日常工作里碰到的商品、订单、用户这些数据问题，我们基本都能在这儿找到现成的解法。

阅读原文：原文链接