SQL查询语句
-- 可复用视图：抽取“有效订单”基础集（已支付、未取消、非测试、金额>0）
CREATE VIEW v_orders_valid AS
SELECT
id,
shop_id,
shop_name, -- 若shop_name不在orders表，请在最终查询中改为JOIN店铺表
payment_time,
order_amount
FROM orders
WHERE status = 'paid'
AND cancelled = FALSE
AND COALESCE(is_test, FALSE) = FALSE
AND order_amount > 0;
-- 本月店铺-日期维度成交额与订单数（以支付时间为准），可选按店铺ID列表过滤
-- 使用参数：:month_start_date（本月起始00:00:00），:next_month_start_date（下月起始00:00:00），:shop_ids（可选）
SELECT
o.shop_id,
o.shop_name,
CAST(o.payment_time AS DATE) AS order_date,
COUNT(*) AS order_count,
SUM(o.order_amount) AS gross_amount,
AVG(o.order_amount) AS avg_order_value
FROM v_orders_valid AS o
WHERE o.payment_time >= :month_start_date
AND o.payment_time < :next_month_start_date
-- 可选店铺过滤（请以安全的参数绑定方式传入列表）
AND (
:shop_ids IS NULL
OR o.shop_id IN (:shop_ids)
)
GROUP BY
o.shop_id,
o.shop_name,
CAST(o.payment_time AS DATE)
ORDER BY
order_date ASC,
o.shop_id ASC,
gross_amount DESC, -- 金额相同时按订单数降序作为最后的并列打破条件
order_count DESC;
-- 若不使用视图，等价的单条查询（完全展开条件）
SELECT
o.shop_id,
o.shop_name,
CAST(o.payment_time AS DATE) AS order_date,
COUNT(*) AS order_count,
SUM(o.order_amount) AS gross_amount,
AVG(o.order_amount) AS avg_order_value
FROM orders AS o
WHERE o.status = 'paid'
AND o.cancelled = FALSE
AND COALESCE(o.is_test, FALSE) = FALSE
AND o.order_amount > 0
AND o.payment_time >= :month_start_date
AND o.payment_time < :next_month_start_date
AND (
:shop_ids IS NULL
OR o.shop_id IN (:shop_ids)
)
GROUP BY
o.shop_id,
o.shop_name,
CAST(o.payment_time AS DATE)
ORDER BY
order_date ASC,
o.shop_id ASC,
gross_amount DESC,
order_count DESC;
语句说明
- 操作类型：查询（聚合统计）
- 涉及表名：orders（以及复用视图 v_orders_valid）
- 主要功能：
- 统计本月内（以支付时间 payment_time 为准）的有效订单（已支付、未取消、非测试、金额>0），按店铺ID与日期维度输出：
- 店铺ID、店铺名、日期
- 订单数（order_count）、成交额（gross_amount）、平均客单价（avg_order_value = AVG(order_amount)）
- 支持按店铺ID列表进行可选过滤
- 排序要求：
- 日期升序，其次店铺ID升序
- 为满足“金额相同按订单数降序”的并列打破条件，增加 gross_amount DESC、order_count DESC 作为后续排序键
- 性能提示：
- 使用时间范围谓词 payment_time >= :month_start_date AND payment_time < :next_month_start_date，保证可命中有序索引并避免函数包裹列导致不可索引（CAST 仅用于分组字段不会影响过滤谓词的可索引性）。
- 将等值过滤列置于复合索引前缀（status、cancelled、is_test），时间列作为后续列，有助于缩小扫描范围。
- 如 shop_name 不在 orders 表而在 shops 表，请以 JOIN shops 获取店铺名，并确保 shops(shop_id) 有主键/唯一索引。
参数说明
- :month_start_date：本月第一天 00:00:00（例如 2025-12-01 00:00:00）
- :next_month_start_date：下月第一天 00:00:00（例如 2026-01-01 00:00:00），用于构造左闭右开时间区间
- :shop_ids：可选的店铺ID列表参数（请使用安全的数组/批量绑定，不要拼接字符串）
- 列定义假设：
- status：订单状态，文本类型，需等于 'paid'
- cancelled：布尔/数值型取消标记，未取消为 FALSE 或 0
- is_test：布尔/数值型测试标记，测试订单需过滤（FALSE 或 0）
- order_amount：数值型订单金额，需 > 0
- payment_time：时间戳（以此为统计口径）
- shop_id：店铺ID
- shop_name：店铺名（若不在 orders，请在查询中 JOIN 店铺表）
使用示例
SELECT
o.shop_id,
s.shop_name,
CAST(o.payment_time AS DATE) AS order_date,
COUNT(*) AS order_count,
SUM(o.order_amount) AS gross_amount,
AVG(o.order_amount) AS avg_order_value
FROM orders AS o
JOIN shops AS s ON s.shop_id = o.shop_id
WHERE o.status = 'paid'
AND o.cancelled = FALSE
AND COALESCE(o.is_test, FALSE) = FALSE
AND o.order_amount > 0
AND o.payment_time >= :month_start_date
AND o.payment_time < :next_month_start_date
AND (
:shop_ids IS NULL
OR o.shop_id IN (:shop_ids)
)
GROUP BY
o.shop_id,
s.shop_name,
CAST(o.payment_time AS DATE)
ORDER BY
order_date ASC,
o.shop_id ASC,
gross_amount DESC,
order_count DESC;
索引建议
- 为高选择性过滤与时间范围扫描建立复合索引（通用DDL，不使用数据库特有语法）：
-- 过滤列 + 时间列的复合索引
CREATE INDEX idx_orders_paid_active_month
ON orders (status, cancelled, is_test, payment_time);
-- 按店铺与时间的聚合/过滤支持（便于按店铺ID列表过滤以及分组）
CREATE INDEX idx_orders_shop_time
ON orders (shop_id, payment_time);
-- 若频繁按 payment_time 分区扫描（不按店铺过滤），可增加时间单列索引
CREATE INDEX idx_orders_payment_time
ON orders (payment_time);
- 说明：
- 等值谓词列（status、cancelled、is_test）置于索引前缀，可迅速定位有效订单子集；payment_time 作为后续列可用于范围扫描本月数据。
- IN 列表过滤可利用 shop_id 前缀索引加速；与时间组合索引在本查询中也有助于分组/排序。
- 请确保 orders 主键或唯一索引已存在（如 id），并避免在查询中对 payment_time 使用非SARG可优化的函数（过滤部分不对 payment_time 做函数处理已满足该点）。
