久事体育 APP 订单关键指标统计技能（增强版 v1.3）

核心规则（必须 100% 遵守，任何违反都视为严重错误）：

1. 1. SQL 模板固定，但允许动态调整查询维度

固定 SQL 模板（基础结构不变）：
sql
SELECT
{查询维度},
COUNT(*) AS 订单数，
COUNT(CASE WHEN orderstate IN (CREATED, PAYCANCEL, PAYFAILED, PAYWAIT, ORDER_CLOSED) THEN 1 END) AS 未支付订单数，
COUNT(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUND_PART) THEN 1 END) AS 退款订单数，
ROUND(SUM(CASE WHEN orderstate IN (PAYSUCCESS, ORDERREFUNDALL, ORDERREFUNDPART) THEN pay_amount ELSE 0 END) / 100, 0) AS 支付金额，
ROUND(SUM(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUNDPART) THEN refundedamount ELSE 0 END) / 100, 0) AS 退款金额
COUNT(DISTINCT user_id) AS 用户数
FROM jussdw.appj_order
WHERE
createtime >= @开始时间@ AND createtime < @结束时间@
{关键词筛选}
{业务版块筛选}
GROUP BY {查询维度}
ORDER BY 支付金额 DESC;

1. 2. 查询维度调整规则（用户可指定）：

1. 3. 占位符替换规则：

业务版块映射表：
| 中文名 | 数据库值 |
|--------|----------|
| 强生公交 | QIANGSHENG |
| 商城 | JIUSHI_SHOP |
| 票务 | TICKET_ORDER |
| 场馆预订 | VENUE_ORDER |
| 场馆门票 | VENUETICKETORDER |
| 场馆时间订单 | VENUETICKETTIME_ORDER |
| 场馆活动订单 | VENUEACTIVITYORDER |
| 积分商城 | JIUSHISHOPENERGY |
| 游泳馆 | SWIM_ORDER |

1. 4. 使用时机：

1. 5. 执行流程（一步都不能跳）：

Python 代码模板：
python
import mysql.connector
import pandas as pd
from tabulate import tabulate
import os

DB_CONFIG = {
host: os.getenv(JIUSHIDBHOST, rm-uf69co304tkv5htyd.mysql.rds.aliyuncs.com),
port: int(os.getenv(JIUSHIDBPORT, 3306)),
user: os.getenv(JIUSHIDBUSER, jussdwro),
password: os.getenv(JIUSHIDBPASSWORD),
database: os.getenv(JIUSHIDBNAME, juss_dw)
}

# 用户提供的参数
start_time = @开始时间@
end_time = @结束时间@
keyword = @关键词@
order_type = @业务版块@
dimension = TITLE # TITLE / DETAIL

# 根据维度动态构建 SQL 部分
if dimension == TITLE:
selectdim = ordertitle AS 订单标题
groupby = ordertitle
elif dimension == DETAIL:
selectdim = ordertitle AS 订单标题，order_desc AS 订单描述
groupby = ordertitle, order_desc
else:
raise ValueError(不支持的维度)

# 关键词筛选（使用 LIKE 模糊匹配，OR 关系）
if keyword and keyword.strip():
# 支持逗号或空格分隔多个关键词
keywords = keyword.replace(,, ).split()
conditions = [f(ordertitle LIKE %{k}% OR orderdesc LIKE %{k}%) for k in keywords]
keyword_filter = fAND ({ OR .join(conditions)})
else:
keyword_filter =

# 业务版块筛选（修复：使用 order_type 而非 keyword）
ordertypefilter = fAND ordertype={ordertype} if ordertype and ordertype.strip() else

sql = f
SELECT
{select_dim},
COUNT(*) AS 订单数，
COUNT(CASE WHEN orderstate IN (CREATED, PAYCANCEL, PAYFAILED, PAYWAIT, ORDER_CLOSED) THEN 1 END) AS 未支付订单数，
COUNT(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUND_PART) THEN 1 END) AS 退款订单数，
ROUND(SUM(CASE WHEN orderstate IN (PAYSUCCESS, ORDERREFUNDALL, ORDERREFUNDPART) THEN pay_amount ELSE 0 END) / 100, 0) AS 支付金额，
ROUND(SUM(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUNDPART) THEN refundedamount ELSE 0 END) / 100, 0) AS 退款金额，
COUNT(DISTINCT user_id) AS 用户数
FROM jussdw.appj_order
WHERE
createtime >= {starttime} AND createtime < {endtime}
{keyword_filter}
{ordertypefilter}
GROUP BY {group_by}
ORDER BY 支付金额 DESC;

try:
conn = mysql.connector.connect(DB_CONFIG)
df = pd.read_sql(sql, conn)

if df.empty:
print(查询结果为空（该时间段或关键词无匹配订单）)
else:
print(f查询参数：时间范围 {starttime} 至 {endtime}，关键词 {keyword}（若为空则无过滤），维度 {dimension})
print(\n久事体育 APP 订单关键指标统计：)
print(tabulate(df, headers=keys, tablefmt=psql, showindex=False))
print(f\n数据来源：jussdw.appj_order)
print(f共 {len(df)} 条记录，销售占比总和 100%)
except Exception as e:
print(f执行失败：{str(e)})
finally:
if conn in locals() and conn.is_connected():
conn.close()

1. 6. 安全与限制