例数据中关键指标对应的SQL查询模板

以下是针对示例数据中关键指标对应的SQL查询模板，包含MySQL和PostgreSQL两种版本：

1. 订单处理系统指标查询

1.1 订单处理成功率

-- MySQL
SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') AS time_window,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_orders,
  (SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS success_rate
FROM orders
WHERE created_at BETWEEN [start_time] AND [end_time]
GROUP BY FLOOR(UNIX_TIMESTAMP(created_at)/300)  -- 5分钟窗口
ORDER BY time_window;

-- PostgreSQL
SELECT 
  date_trunc('minute', created_at) - INTERVAL '5 min' * (EXTRACT(MINUTE FROM created_at)::int / 5) AS time_window,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_orders,
  (SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)::float / COUNT(*)) * 100 AS success_rate
FROM orders
WHERE created_at BETWEEN [start_time] AND [end_time]
GROUP BY time_window
ORDER BY time_window;

1.2 支付延迟分析

-- 通用版
SELECT 
  payment_gateway,
  AVG(completed_at - created_at) AS avg_delay,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY completed_at - created_at) AS p95_delay
FROM payments
WHERE status = 'completed'
  AND created_at >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY payment_gateway;

2. 用户认证模块指标查询

2.1 认证成功率

-- 通用版
WITH auth_stats AS (
  SELECT
    date_trunc('hour', request_time) AS hour_window,
    COUNT(*) FILTER (WHERE status_code = 200) AS success_count,
    COUNT(*) AS total_requests
  FROM auth_logs
  WHERE request_time BETWEEN [start_time] AND [end_time]
  GROUP BY hour_window
)
SELECT
  hour_window,
  success_count,
  total_requests,
  (success_count::float / total_requests) * 100 AS success_rate
FROM auth_stats;

2.2 JWT验证延迟分析

-- PostgreSQL (JSONB支持)
SELECT
  endpoint,
  AVG(processing_time) AS avg_latency,
  MAX(processing_time) AS max_latency
FROM (
  SELECT
    request_path AS endpoint,
    (response_headers->>'x-processing-ms')::numeric AS processing_time
  FROM api_logs
  WHERE request_path LIKE '/auth/verify%'
    AND logged_at >= NOW() - INTERVAL '1 week'
) AS jwt_logs
GROUP BY endpoint;

3. 实时数据分析引擎指标查询

3.1 事件处理延迟

-- 时序数据库示例 (TimescaleDB)
SELECT
  time_bucket('30 seconds', event_time) AS bucket,
  system_id,
  AVG(processed_at - event_time) AS avg_latency,
  MAX(processed_at - event_time) AS max_latency
FROM events
WHERE event_time >= NOW() - INTERVAL '15 minutes'
GROUP BY bucket, system_id
ORDER BY bucket DESC;

3.2 资源利用率分析

-- 监控数据查询 (InfluxDB示例)
SELECT
  MEAN("cpu_usage") AS avg_cpu,
  MAX("memory_usage") AS peak_mem
FROM "host_metrics"
WHERE time > now() - 1h
GROUP BY time(1m), "host"

通用性能分析查询模板

-- 错误率趋势分析
SELECT
  date_trunc('hour', error_time) AS time_window,
  error_code,
  COUNT(*) AS error_count,
  (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM logs 
                        WHERE log_time BETWEEN [start] AND [end]) AS error_percent
FROM error_logs
WHERE error_time BETWEEN [start] AND [end]
GROUP BY time_window, error_code
ORDER BY error_count DESC;

-- 瓶颈组件分析
SELECT
  component,
  COUNT(*) AS failure_count,
  AVG(duration) AS avg_response_time,
  CORR(system_load, duration) AS load_correlation
FROM performance_logs
WHERE timestamp BETWEEN [start] AND [end]
GROUP BY component
HAVING COUNT(*) > 10
ORDER BY failure_count DESC
LIMIT 5;

使用建议：

1. 时间参数替换：

   • 将[start_time]/[end_time]替换为实际时间范围
   • 使用数据库特定时间函数（如NOW() - INTERVAL ‘1 day’）

2. 性能优化：

-- 创建覆盖索引示例
CREATE INDEX idx_orders_status_time ON orders(status, created_at);
CREATE INDEX idx_authlogs_time_status ON auth_logs(request_time, status_code);

3. 查询安全：

-- 使用参数化查询防止SQL注入
PREPARE get_metrics (timestamptz, timestamptz) AS
SELECT ... WHERE timestamp BETWEEN $1 AND $2;

这些SQL模板可根据具体业务需求进行以下扩展：

1. 增加异常处理：TRY_CAST/CASE WHEN处理数据异常
2. 添加层次聚合：WITH ROLLUP进行多维度聚合
3. 结合窗口函数：计算移动平均值等趋势指标
4. 集成机器学习：使用MADlib进行异常检测

实际使用时应根据数据库类型（OLTP/OLAP）选择合适的执行策略，建议：

• 业务数据库：使用预计算物化视图
• 分析数据库：使用列式存储+分区
• 实时分析：结合流处理引擎（如Flink SQL）