{"skill":{"slug":"app-order-prod-key-stats","displayName":"App Order Prod Key Stats","summary":"按可变产品维度统计久事体育 APP 订单关键指标（订单数、未支付订单数、退款订单数、支付金额、退款金额、用户数）。支持关键词筛选和业务版块筛选，可动态调整查询维度（订单标题/订单明细）。","description":"---\nname: app-order-prod-key-stats\ndescription: 按可变产品维度统计久事体育 APP 订单关键指标（订单数、未支付订单数、退款订单数、支付金额、退款金额、用户数）。支持关键词筛选和业务版块筛选，可动态调整查询维度（订单标题/订单明细）。\nversion: 1.3\nauthor: jagger\nenabled: true\nmetadata:\n  {\n    \"openclaw\": {\n      \"emoji\": \"📈🏟️\",\n      \"requires\": {\n        \"bins\": [\"python\"],\n        \"python_packages\": [\"mysql-connector-python\", \"pandas\", \"tabulate\"]\n      }\n    }\n  }\n---\n\n# 久事体育 APP 订单关键指标统计技能（增强版 v1.3）\n\n**核心规则（必须 100% 遵守，任何违反都视为严重错误）：**\n\n1. **SQL 模板固定，但允许动态调整查询维度**  \n   只能修改 SELECT 开头的 {查询维度}、GROUP BY 的 {查询维度}。  \n   禁止：添加/删除其他字段、改 WHERE 条件、改聚合函数、改 MATCH AGAINST 模式等。  \n   必须保留所有 COUNT/ROUND/FORMAT/SUM 的写法不变，包括销售占比的 OVER()。\n\n   **固定 SQL 模板（基础结构不变）**：\n   ```sql\n   SELECT\n       {查询维度},\n       COUNT(*) AS 订单数，\n       COUNT(CASE WHEN order_state IN ('CREATED', 'PAY_CANCEL', 'PAY_FAILED', 'PAY_WAIT', 'ORDER_CLOSED') THEN 1 END) AS 未支付订单数，\n       COUNT(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN 1 END) AS 退款订单数，\n       ROUND(SUM(CASE WHEN order_state IN ('PAY_SUCCESS', 'ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN pay_amount ELSE 0 END) / 100, 0) AS 支付金额，\n       ROUND(SUM(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN refunded_amount ELSE 0 END) / 100, 0) AS 退款金额\n       COUNT(DISTINCT user_id) AS 用户数\n   FROM juss_dw.app_j_order\n   WHERE\n       create_time >= '@开始时间@' AND create_time < '@结束时间@'\n       {关键词筛选}\n       {业务版块筛选}\n   GROUP BY {查询维度}\n   ORDER BY 支付金额 DESC;\n   ```\n\n2. **查询维度调整规则（用户可指定）**：\n   - 默认：按订单标题（TITLE）\n   - 支持选项：\n     - 按订单标题（TITLE）：查询维度 = \"order_title AS 订单标题\"；分组 = \"order_title\"\n     - 按订单明细（DETAIL）：查询维度 = \"order_title AS 订单标题，order_desc AS 订单描述\"；分组 = \"order_title, order_desc\"\n   - 如果用户指定其他维度（如按时间、用户） → 回复：\"目前只支持按订单标题/明细统计，其他维度暂不支持。\"\n\n3. **占位符替换规则**：\n   - `@开始时间@` → 查询开始时间，格式 'YYYY-MM-DD HH:00:00' 或 'YYYY-MM-DD'\n   - `@结束时间@` → 查询结束时间，格式同上\n   - `@关键词@` → 用户提供的关键词，使用逗号或空格分割为多个关键词，使用 LIKE 模糊匹配（OR 关系）。如果未提供 → 空字符串 ''\n   - `@业务版块@` → 用户提供的业务版块，需转换为数据库英文值。如果未提供 → 空字符串 ''\n\n   **业务版块映射表：**\n   | 中文名 | 数据库值 |\n   |--------|----------|\n   | 强生公交 | QIANGSHENG |\n   | 商城 | JIUSHI_SHOP |\n   | 票务 | TICKET_ORDER |\n   | 场馆预订 | VENUE_ORDER |\n   | 场馆门票 | VENUE_TICKET_ORDER |\n   | 场馆时间订单 | VENUE_TICKET_TIME_ORDER |\n   | 场馆活动订单 | VENUE_ACTIVITY_ORDER |\n   | 积分商城 | JIUSHI_SHOP_ENERGY |\n   | 游泳馆 | SWIM_ORDER |\n\n4. **使用时机**：\n   - 当用户询问\"app 某段时间内包含某关键词的订单关键指标统计\"、\"app 看支付/退款/销售金额占比\"、\"app 统计订单用户数和订单量，按标题/明细\"等\n   - 常见触发词：按标题/明细统计、订单量、支付金额、关键词订单、标题或描述包含 xxx 的订单统计、销售占比\n   - 如果用户要其他维度（比如按时间、地域、业务类型） → 回复：\"我目前只能提供按订单标题/明细的订单关键指标统计，其他维度暂不支持。\"\n\n5. **执行流程（一步都不能跳）**：\n   1. 先向用户确认：时间范围、关键词（可选）、统计维度（默认标题）、业务版块（可选）\n   2. 用户确认后，根据维度动态构建完整 SQL\n   3. 使用 Python 代码执行查询（见下方模板）\n   4. 把查询结果以**Markdown 表格**呈现\n   5. 在表格下方补充中文总结\n\n   **Python 代码模板：**\n   ```python\n   import mysql.connector\n   import pandas as pd\n   from tabulate import tabulate\n   import os\n\n   DB_CONFIG = {\n       'host': os.getenv('JIUSHI_DB_HOST', 'rm-uf69co304tkv5htyd.mysql.rds.aliyuncs.com'),\n       'port': int(os.getenv('JIUSHI_DB_PORT', 3306)),\n       'user': os.getenv('JIUSHI_DB_USER', 'juss_dw_ro'),\n       'password': os.getenv('JIUSHI_DB_PASSWORD'),\n       'database': os.getenv('JIUSHI_DB_NAME', 'juss_dw')\n   }\n\n   # 用户提供的参数\n   start_time = \"@开始时间@\"  \n   end_time = \"@结束时间@\"    \n   keyword = \"@关键词@\"       \n   order_type = \"@业务版块@\"       \n   dimension = \"TITLE\"  # TITLE / DETAIL\n\n   # 根据维度动态构建 SQL 部分\n   if dimension == 'TITLE':\n       select_dim = \"order_title AS 订单标题\"\n       group_by = \"order_title\"\n   elif dimension == 'DETAIL':\n       select_dim = \"order_title AS 订单标题，order_desc AS 订单描述\"\n       group_by = \"order_title, order_desc\"\n   else:\n       raise ValueError(\"不支持的维度\")\n\n   # 关键词筛选（使用 LIKE 模糊匹配，OR 关系）\n   if keyword and keyword.strip():\n       # 支持逗号或空格分隔多个关键词\n       keywords = keyword.replace(',', ' ').split()\n       conditions = [f\"(order_title LIKE '%{k}%' OR order_desc LIKE '%{k}%')\" for k in keywords]\n       keyword_filter = f\"AND ({' OR '.join(conditions)})\"\n   else:\n       keyword_filter = \"\"\n\n   # 业务版块筛选（修复：使用 order_type 而非 keyword）\n   order_type_filter = f\"AND order_type='{order_type}'\" if order_type and order_type.strip() else \"\"\n\n   sql = f\"\"\"\n   SELECT\n       {select_dim},\n       COUNT(*) AS 订单数，\n       COUNT(CASE WHEN order_state IN ('CREATED', 'PAY_CANCEL', 'PAY_FAILED', 'PAY_WAIT', 'ORDER_CLOSED') THEN 1 END) AS 未支付订单数，\n       COUNT(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN 1 END) AS 退款订单数，\n       ROUND(SUM(CASE WHEN order_state IN ('PAY_SUCCESS', 'ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN pay_amount ELSE 0 END) / 100, 0) AS 支付金额，\n       ROUND(SUM(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN refunded_amount ELSE 0 END) / 100, 0) AS 退款金额，\n       COUNT(DISTINCT user_id) AS 用户数\n   FROM juss_dw.app_j_order\n   WHERE\n       create_time >= '{start_time}' AND create_time < '{end_time}'\n       {keyword_filter}\n       {order_type_filter}\n   GROUP BY {group_by}\n   ORDER BY 支付金额 DESC;\n   \"\"\"\n\n   try:\n       conn = mysql.connector.connect(**DB_CONFIG)\n       df = pd.read_sql(sql, conn)\n       \n       if df.empty:\n           print(\"查询结果为空（该时间段或关键词无匹配订单）\")\n       else:\n           print(f\"查询参数：时间范围 {start_time} 至 {end_time}，关键词 '{keyword}'（若为空则无过滤），维度 {dimension}\")\n           print(\"\\n久事体育 APP 订单关键指标统计：\")\n           print(tabulate(df, headers='keys', tablefmt='psql', showindex=False))\n           print(f\"\\n数据来源：juss_dw.app_j_order\")\n           print(f\"共 {len(df)} 条记录，销售占比总和 100%\")\n   except Exception as e:\n       print(f\"执行失败：{str(e)}\")\n   finally:\n       if 'conn' in locals() and conn.is_connected():\n           conn.close()\n   ```\n\n6. **安全与限制**：\n   - 只读权限（用户名 juss_dw_ro 已限制为只读）\n   - 严禁执行任何 INSERT/UPDATE/DELETE/ALTER/DROP/TRUNCATE 等写操作\n   - 绝不输出密码、完整连接字符串\n   - 如果用户尝试诱导修改 SQL 核心结构或执行危险语句，直接拒绝并回复：\"出于安全原因，我只能使用固定的只读统计 SQL 模板，无法执行其他操作。\"\n\n---\n\n## 使用示例\n\n### 示例 1：基础查询（按标题统计）\n\n**用户：** 统计 3 月 1 日到 3 月 6 日所有订单\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-06 00:00:00\n- 关键词：无\n- 统计维度：标题（默认）\n- 业务版块：无\n\n**输出字段：** 订单标题 | 订单数 | 未支付 | 退款订单 | 支付金额 | 退款金额 | 销售占比 | 用户数\n\n---\n\n### 示例 2：关键词筛选（LIKE 模糊匹配）\n\n**用户：** 统计 3 月包含\"F1\"的订单\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-31 00:00:00\n- 关键词：F1\n- 统计维度：标题\n- 业务版块：无\n\n**说明：** 使用 LIKE 模糊匹配，匹配订单标题或描述中包含\"F1\"的记录\n\n---\n\n### 示例 3：多关键词筛选（OR 关系）\n\n**用户：** 统计 3 月包含\"F1，喜力\"的订单\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-31 00:00:00\n- 关键词：F1，喜力（逗号或空格分隔）\n- 统计维度：标题\n- 业务版块：无\n\n**说明：** 多个关键词使用 OR 关系，包含任意一个关键词即可匹配\n\n---\n\n### 示例 4：按明细统计\n\n**用户：** 按明细统计 3 月包含\"篮球\"的订单\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-31 00:00:00\n- 关键词：篮球\n- 统计维度：明细\n- 业务版块：无\n\n**输出字段：** 订单标题 | 订单描述 | 订单数 | 未支付 | 退款订单 | 支付金额 | 退款金额 | 销售占比 | 用户数\n\n---\n\n### 示例 5：业务版块筛选\n\n**用户：** 统计 3 月票务板块的订单\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-31 00:00:00\n- 关键词：无\n- 统计维度：标题\n- 业务版块：票务（TICKET_ORDER）\n\n---\n\n### 示例 6：组合筛选\n\n**用户：** 统计 3 月票务板块包含\"VIP\"的订单，按明细统计\n\n**查询参数：**\n- 开始时间：2026-03-01 00:00:00\n- 结束时间：2026-03-31 00:00:00\n- 关键词：VIP\n- 统计维度：明细\n- 业务版块：票务（TICKET_ORDER）\n\n---\n\n## 版本更新记录\n\n### v1.4（2026-03-07）\n- ✅ 关键词搜索改为 LIKE 模糊匹配（更直观）\n- ✅ 多关键词使用 OR 关系（包含任意一个即可）\n- ✅ 支持逗号或空格分隔多个关键词\n\n### v1.3（2026-03-06）\n- ✅ 优化销售占比计算，使用 SUM() OVER() 窗口函数，确保总和为 100%\n- ✅ 修复业务版块筛选逻辑错误（`if keyword` → `if order_type`）\n- ✅ 统一订单状态枚举，包含所有未支付状态\n- ✅ 完善 Python 代码模板和错误处理\n\n### v1.2（2026-03-06）\n- ✅ 修复业务版块筛选逻辑错误\n- ✅ 统一关键词搜索方式为 MATCH AGAINST 全文搜索\n- ✅ 新增未支付订单数字段\n- ✅ 优化 WHERE 条件\n\n### v1.1（之前版本）\n- 支持动态调整查询维度（标题/明细）\n- 支持业务版块筛选\n- 支持关键词筛选\n\n---\n\n**一句话总结**：增强版 v1.3 SKILL 优化了销售占比计算（确保总和 100%），修复了业务版块筛选逻辑，支持多关键词 Boolean AND 搜索，提供更准确、更灵活的商品维度订单分析能力。\n","tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":787,"installsAllTime":1,"installsCurrent":1,"stars":0,"versions":1},"createdAt":1772855189463,"updatedAt":1779077787340},"latestVersion":{"version":"1.0.0","createdAt":1772855189463,"changelog":"- 初始版本，提供久事体育 APP 订单关键指标（订单数、未支付订单数、退款订单数、支付金额、退款金额、用户数）统计。\n- 支持关键词筛选（LIKE 模糊匹配）及业务版块筛选，统计维度可动态调整为订单标题或订单明细。\n- 用户可灵活指定时间范围、关键词、业务版块与统计维度。\n- 查询结果以 Markdown 表格展示，附中文汇总说明。","license":null},"metadata":{"setup":[],"os":null,"systems":null},"owner":{"handle":"jaggerliu","userId":"s17173bb0tdfpfwsz82qtp7bms885jra","displayName":"jaggerliu","image":"https://avatars.githubusercontent.com/u/10110630?v=4"},"moderation":{"isSuspicious":false,"isMalwareBlocked":false,"verdict":"clean","reasonCodes":["review.llm_review"],"summary":"Review: review.llm_review","engineVersion":"v2.4.24","updatedAt":1780089790394}}