技能名称: app-order-date-key-stats

详细描述:

久事体育APP 订单统计技能（灵活时间维度版）

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

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

固定SQL模板（基础结构不变）：
sql
SELECT
{时间字段}, -- ← 根据维度动态替换
COUNT(DISTINCT user_id) AS 用户数,
COUNT(*) AS 订单数量,
COUNT(CASE WHEN orderstate = ORDERCLOSED THEN 1 END) AS 未支付订单数,
COUNT(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUND_PART) THEN 1 END) AS 退款订单数,
ROUND(IFNULL(SUM(payamount), 0) / 100) AS payamount,
ROUND(IFNULL(SUM(refundedamount), 0) / 100) AS refundedamount,
ROUND(IFNULL(SUM(payamount - refundedamount), 0) / 100) AS sales_amount
FROM jussdw.appjorder FORCE INDEX (idxordertitledesc)
WHERE create_time >= @开始时间@
AND create_time < @结束时间@
{关键词筛选} -- ← 如果有关键词则替换，否则为空
GROUP BY {分组字段} -- ← 根据维度动态替换
ORDER BY {排序字段}; -- ← 根据维度动态替换

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

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

1. 4. 使用时机：

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

python
import mysql.connector
import pandas as pd
from tabulate import tabulate

DB_CONFIG = {
host: os.getenv(JIUSHIDBHOST, rm-uf69co304tkv5htydco.mysql.rds.aliyuncs.com), # 默认值兜底
port: int(os.getenv(JIUSHIDBPORT, 3306)),
user: os.getenv(JIUSHIDBUSER, jussdwro),
password: os.getenv(JIUSHIDBPASSWORD), # 必须从 env 读，无默认
database: os.getenv(JIUSHIDBNAME, juss_dw)
}

# 用户提供的参数（在实际执行前替换）
start_time = @开始时间@
end_time = @结束时间@
keyword = @关键词@
dimension = DATE # HOUR / DATE / MONTH

# 根据维度动态构建SQL部分
if dimension == DATE:
timeselect = DATE(createtime) AS order_date
groupby = DATE(createtime)
orderby = orderdate
elif dimension == HOUR:
timeselect = DATE(createtime) AS orderdate, HOUR(createtime) AS order_hour
groupby = DATE(createtime), HOUR(create_time)
orderby = orderdate, order_hour
elif dimension == MONTH:
timeselect = YEAR(createtime) AS orderyear, MONTH(createtime) AS order_month
groupby = YEAR(createtime), MONTH(create_time)
orderby = orderyear, order_month
else:
raise ValueError(不支持的维度)

# 关键词筛选（如果有关键词）
if keyword and keyword.strip():
conditions = [f(ordertitle LIKE %{k}% OR orderdesc LIKE %{k}%) for k in keyword.split()]
keyword_filter = fAND ({ OR .join(conditions)})
else:
keyword_filter =

sql = f
SELECT
{time_select},
COUNT(DISTINCT user_id) 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 支付金额,
FORMAT(SUM(CASE WHEN orderstate IN (ORDERREFUNDALL, ORDERREFUNDPART) THEN refundedamount ELSE 0 END) / 100, 0) AS 退款金额,
COUNT(DISTINCT user_id) as 用户数
FROM jussdw.appjorder FORCE INDEX (idxordertitledesc)
WHERE createtime >= {starttime}
AND createtime < {endtime}
{keyword_filter}
GROUP BY {group_by}
ORDER BY {order_by};

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(\n数据来源：jussdw.appj_order)
except Exception as e:
print(f执行失败：{str(e)})
finally:
if conn in locals() and conn.is_connected():
conn.close()

4. 把查询结果以Markdown表格呈现
5. 在表格下方补充中文总结：例如“该时间段内（