如何用 SQL 实现跨城市的结果状态动态统计？完整教程与实战示例

1. 跨城市数据源与目标指标

在多地业务运营中，跨城市的状态统计是评估运营健康的重要手段。通过统一口径对不同城市的指标进行对比，可以快速发现区域差异并指导资源分配。

实现动态统计的前提，是明确时间窗、城市维度和状态维度的定义，确保同一时间单位内的数据具有可比性。只有在数据结构一致、写入节奏稳定的情况下，跨城市的结果状态统计才能准确反映真实业务表现。

1.1 数据模型设计要点

设计阶段需要确保表结构能够高效按城市、日期、状态进行分组和聚合。合理的字段组合可以显著降低查询成本并提升可扩展性。

典型字段包括 city_id、city_name、created_at、status，以及需要统计的其他指标，如 order_count、value 等。对于跨城市统计，确保时间字段具有统一时区与一致的粒度是关键。

1.2 需要统计的动态指标

常见指标有：按日分组的状态计数、跨城市同日对比、以及 日环比/周环比 的变化趋势。

为了便于前端呈现，通常需要把跨城市的日度状态数据汇总成一个标准化结构，支持直接渲染为表格、折线图或热力图等可视化控件。

2. 构建跨城市动态统计的 SQL 模板

核心思路是先按城市、日期、状态进行聚合，再通过透视或动态字段（如 JSON）实现跨城市维度的对比展示。窗口函数和 动态透视是实现的关键工具。

下面给出从简单聚合到动态透视的渐进性模板，帮助你在真实场景中快速落地。

2.1 聚合与分组策略

基于 city_id、date(created_at)、status 的聚合，得到每日各状态的计数。这个阶段的结果可以作为后续透视或比较的底层数据。

在设计时应考虑时区、数据完整性以及异常写入，确保跨城市比较不被单日偏差所干扰。

-- 按城市、日期、状态聚合
SELECT
  city_id,
  city_name,
  date(created_at) AS day,
  status,
  COUNT(*) AS cnt
FROM events
GROUP BY city_id, city_name, date(created_at), status
ORDER BY city_id, day, status;

2.2 动态字段与透视表达式

如果状态集合是固定的，可以采用显式透视；若状态集合是动态的，推荐将聚合结果转换为动态字段（如 JSON），以便前端在变化的状态集合下仍能正确展示。

以下示例展示两种常见做法：静态透视与动态 JSON 透视。静态透视在状态集合稳定时性能佳；动态 JSON 在状态频繁变动时更具灵活性。

-- 静态透视：按日按城市统计固定状态
SELECT city_id, day,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) AS in_progress,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed
FROM (
  SELECT city_id, date(created_at) AS day, status
  FROM events
) s
GROUP BY city_id, day
ORDER BY city_id, day;

-- 动态透视：把不同状态动态聚合成一个 JSON 对象
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;

3. 适用的数据库实现差异与性能优化

不同数据库在语法、函数及执行计划优化点上存在差异。理解这些差异有助于在跨城市统计场景中获得稳定高效的查询结果。

同时，索引设计与 执行计划分析是提升大规模跨城市统计性能的关键。

3.1 不同数据库的语法差异

PostgreSQL 对 JSON/B 的原生支持很强，适合处理动态字段；MySQL 从 5.7 版本起也提供了成熟的 JSON 函数；SQL Server 可以通过 PIVOT/UNPIVOT 或 JSON 函数实现类似效果。

在实际项目中，建议将核心聚合逻辑沉淀为视图或物化视图，便于跨城市查询的复用与缓存。

-- PostgreSQL 示例：使用 jsonb 进行动态状态统计
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;

-- MySQL 示例：通过 CASE 进行静态透视
SELECT city_id, day,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) AS in_progress,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed
FROM (
  SELECT city_id, DATE(created_at) AS day, status
  FROM events
) AS t
GROUP BY city_id, day
ORDER BY city_id, day;

-- SQL Server 示例：使用 PIVOT 实现静态透视
SELECT city_id, day, [pending], [in_progress], [completed]
FROM (
  SELECT city_id, CAST(created_at AS date) AS day, status
  FROM events
) AS src
PIVOT (
  COUNT(*) FOR status IN ([pending], [in_progress], [completed])
) AS p
ORDER BY city_id, day;

-- 动态透视（跨数据库通用思路）：使用聚合 + JSON
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;

3.2 索引设计与执行计划优化

为了提升跨城市动态统计的查询性能，建议在以下字段上建立组合索引：city_id、date(created_at)、以及 status。

同时，定期检查执行计划，确保聚合阶段的扫描范围尽量小，必要时考虑分区表或拆分表来提升并发和缓存命中率。

CREATE INDEX idx_events_city_date_status ON events (city_id, date(created_at), status);

4. 动态维度与前端展示对接

从数据库取回的跨城市日度状态数据通常需要转成前端可用的结构，如 JSON 或表格。跨城市对比应以直观的日历视图和热力图等形式呈现，提升可读性与决策效率。

为了实现快速迭代，可以将统计结果直接输出为 JSON，供前端可视化组件直接消费。

4.1 将结果导出为 JSON 或 CSV

JSON 形式便于前端直接消费，CSV 形式便于离线分析或导入到 BI 工具。下面给出常用导出方式的示例。

-- 将每日跨城市状态统计导出为 JSON（示例：PostgreSQL）
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;

# Python 示例：将查询结果转换为 JSON 并输出到文件
import json
import psycopg2

conn = psycopg2.connect(host='db', dbname='ops', user='u', password='p')
cur = conn.cursor()
cur.execute("""
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;
""")
rows = cur.fetchall()
result = [
  {'city_id': r[0], 'day': r[1].isoformat(), 'status_counts': r[2]}
  for r in rows
]
with open('city_cross_status.json', 'w') as f:
  json.dump(result, f, ensure_ascii=False, indent=2, default=str)

5. 实战案例：跨城市订单状态动态统计

在实际电商或配送场景中，跨城市的状态动态统计用于实时监控订单量、异常比率及时序趋势。下面通过一个完整的实战案例，演示从数据源到可视化的一整套流程。

5.1 场景描述

场景涉及多城市的订单流转，覆盖 pending、in_progress、completed、以及 failed 等状态。需要在同一天实现不同城市的状态并列统计，并能追溯日环比变化。

为确保可扩展性，统计过程尽量采用只读的聚合查询或物化视图，避免对实时写入路径造成额外压力。

5.2 完整 SQL 脚本合集

下列脚本集合了聚合、透视与日环比等关键步骤，可直接执行，亦可按需求分拆成独立模块。

-- 5.2.1 按城市、日期、状态聚合
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
-- 5.2.2 动态状态统计：JSON
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;

-- 5.2.3 日环比（对比相邻日期的总量变化）
WITH daily AS (
  SELECT city_id, date(created_at) AS day, SUM(CASE WHEN status IN ('pending','in_progress','completed','failed') THEN 1 ELSE 0 END) AS total
  FROM events
  GROUP BY city_id, date(created_at)
)
SELECT city_id, day,
       total,
       total - LAG(total) OVER (PARTITION BY city_id ORDER BY day) AS day_delta
FROM daily
ORDER BY city_id, day;

-- 5.2.4 将结果导出到 CSV（示例，具体工具可替换）
COPY (
  WITH daily AS (
    SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
    FROM events
    GROUP BY city_id, date(created_at), status
  )
  SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
  FROM daily
  GROUP BY city_id, day
  ORDER BY city_id, day
) TO '/tmp/city_cross_status.csv' WITH CSV HEADER;

# 5.2.5 实战脚本：从数据库导出、写入缓存并驱动前端更新
import psycopg2
import json
import requests

conn = psycopg2.connect(...)
cur = conn.cursor()
cur.execute("""
WITH daily AS (
  SELECT city_id, date(created_at) AS day, status, COUNT(*) AS cnt
  FROM events
  GROUP BY city_id, date(created_at), status
)
SELECT city_id, day, jsonb_object_agg(status, cnt) AS status_counts
FROM daily
GROUP BY city_id, day
ORDER BY city_id, day;
""")
rows = cur.fetchall()
payload = [
  {'city_id': r[0], 'day': r[1].isoformat(), 'status_counts': r[2]}
  for r in rows
]
# 推送到前端服务
requests.post('https://dashboard.example.com/api/cross-city-status', json=payload)