查询 SQL
更新时间：2026-08-06
本附录列出性能测试报告使用的完整查询 SQL。查询时间范围、数据规模、实测 QPS 和查询耗时以性能测试报告正文为准。
SQL 中的参数含义如下：
| 参数 | 说明 |
|---|---|
:start |
查询时间范围的开始时间 |
:end |
查询时间范围的结束时间 |
:bucket |
趋势查询的时间分组粒度 |
:sampled_columns |
从数据表中选择的代表性 Field |
:vin |
车辆标识 |
:fleet_id |
车队标识 |
:device_id |
设备标识 |
:gateway_id |
网关标识 |
:host_id |
主机或服务实例标识 |
:cluster_id |
业务集群标识 |
:region |
大区标识 |
24 小时趋势查询的 :bucket 为 5 分钟，7 天趋势查询的 :bucket 为 35 分钟，两种时间范围均输出约 288 个时间点。
车联网
车联网最新状态
查询一辆车在最近 1 小时内的最新状态，返回最新 1 行。
SQL
1SELECT time, vin, latitude, longitude, speed_kph, battery_soc_pct
2FROM vehicle_telemetry
3WHERE vin = :vin AND time >= :start AND time < :end
4ORDER BY time DESC LIMIT 1车联网明细
查询一辆车在最近 1 小时内的最近 20 行数据。:sampled_columns 从已存在的 Field 中选取至多 16 列。
SQL
1SELECT :sampled_columns FROM vehicle_telemetry
2WHERE vin = :vin AND time >= :start AND time < :end
3ORDER BY time DESC LIMIT 20车联网胎压汇总
查询一辆车在 1 小时或 24 小时内的最低轮胎压力。
SQL
1SELECT min(tire_fl_pressure_kpa) AS min_fl, min(tire_fr_pressure_kpa) AS min_fr,
2 min(tire_rl_pressure_kpa) AS min_rl, min(tire_rr_pressure_kpa) AS min_rr
3FROM vehicle_telemetry
4WHERE vin = :vin AND time >= :start AND time < :end车联网单车电量趋势
查询一辆车在 24 小时或 7 天内的平均电量和平均功率趋势。
SQL
1SELECT date_bin(INTERVAL :bucket, time) AS bucket,
2 avg(battery_soc_pct) AS avg_soc, avg(battery_power_kw) AS avg_power
3FROM vehicle_telemetry
4WHERE vin = :vin AND time >= :start AND time < :end
5GROUP BY bucket ORDER BY bucket车联网车队功率趋势
查询一个车队约 100 辆车在 24 小时或 7 天内的平均功率和最大充电功率趋势。
SQL
1SELECT date_bin(INTERVAL :bucket, time) AS bucket,
2 avg(battery_power_kw) AS avg_power, max(charging_power_kw) AS max_charging_power
3FROM vehicle_telemetry
4WHERE fleet_id = :fleet_id AND time >= :start AND time < :end
5GROUP BY bucket ORDER BY bucket车联网大范围扫描聚合
查询一个大区约六分之一的车辆在 24 小时或 7 天内的新增故障数和平均活动故障码数量。
SQL
1SELECT bucket, sum(vehicle_fault_delta) AS new_faults, avg(vehicle_avg_dtc) AS avg_active_dtc
2FROM (
3 SELECT date_bin(INTERVAL :bucket, time) AS bucket, vin,
4 max(fault_count_total) - min(fault_count_total) AS vehicle_fault_delta,
5 avg(dtc_active_count) AS vehicle_avg_dtc
6 FROM vehicle_telemetry
7 WHERE region = :region AND time >= :start AND time < :end
8 GROUP BY bucket, vin
9)
10GROUP BY bucket ORDER BY bucket物联网
物联网最新状态
查询一台设备在最近 6 小时内的最新状态，返回最新 1 行。
SQL
1SELECT time, device_id, temperature_c, battery_pct, online, gateway_connected
2FROM iot_device_telemetry
3WHERE device_id = :device_id AND time >= :start AND time < :end
4ORDER BY time DESC LIMIT 1物联网明细
查询一台设备在最近 6 小时内的最近 20 行数据。:sampled_columns 从已存在的 Field 中选取至多 12 列。
SQL
1SELECT :sampled_columns FROM iot_device_telemetry
2WHERE device_id = :device_id AND time >= :start AND time < :end
3ORDER BY time DESC LIMIT 20物联网告警汇总
查询一台设备在 6 小时或 24 小时内的新增告警数量、告警上报次数和低电量上报次数。
SQL
1SELECT max(alarm_count_total) - min(alarm_count_total) AS new_alarms,
2 count(alarm_active) AS alarm_reports,
3 count(low_battery) AS low_battery_reports
4FROM iot_device_telemetry
5WHERE device_id = :device_id AND time >= :start AND time < :end物联网单设备趋势
查询一台设备在 24 小时或 7 天内的温度、电量和上行时延趋势。
SQL
1SELECT date_bin(INTERVAL :bucket, time) AS bucket,
2 avg(temperature_c) AS avg_temp, min(battery_pct) AS min_battery,
3 max(uplink_latency_ms) AS max_uplink_latency
4FROM iot_device_telemetry
5WHERE device_id = :device_id AND time >= :start AND time < :end
6GROUP BY bucket ORDER BY bucket物联网网关质量趋势
查询一个网关约 100 台设备在 24 小时或 7 天内的信号强度、丢包率和队列深度趋势。
SQL
1SELECT date_bin(INTERVAL :bucket, time) AS bucket,
2 avg(rssi_dbm) AS avg_rssi, avg(packet_loss_pct) AS avg_packet_loss,
3 max(queue_depth) AS max_queue_depth
4FROM iot_device_telemetry
5WHERE gateway_id = :gateway_id AND time >= :start AND time < :end
6GROUP BY bucket ORDER BY bucket物联网大范围扫描聚合
查询一个大区约六分之一的设备在 24 小时或 7 天内的新增告警数和新增错误数。
SQL
1SELECT bucket, sum(device_alarm_delta) AS new_alarms, sum(device_error_delta) AS new_errors
2FROM (
3 SELECT date_bin(INTERVAL :bucket, time) AS bucket, device_id,
4 max(alarm_count_total) - min(alarm_count_total) AS device_alarm_delta,
5 max(error_count_total) - min(error_count_total) AS device_error_delta
6 FROM iot_device_telemetry
7 WHERE region = :region AND time >= :start AND time < :end
8 GROUP BY bucket, device_id
9)
10GROUP BY bucket ORDER BY bucket服务器监控
服务器监控最新健康状态
查询一个实例在最近 1 小时内的最新健康状态，返回最新 1 行。
SQL
1SELECT time, host_id, request_rate, error_rate, latency_p95_ms, healthy
2FROM devops_service
3WHERE host_id = :host_id AND time >= :start AND time < :end
4ORDER BY time DESC LIMIT 1服务器监控明细
查询一个实例在最近 1 小时内的最近 20 行服务数据。:sampled_columns 从已存在的 Field 中选取至多 8 列。
SQL
1SELECT :sampled_columns FROM devops_service
2WHERE host_id = :host_id AND time >= :start AND time < :end
3ORDER BY time DESC LIMIT 20服务器监控内存汇总
查询一个实例在 1 小时或 24 小时内的内存使用峰值、交换分区使用峰值和新增内存溢出次数。
SQL
1SELECT max(mem_used_pct) AS peak_mem_used, max(swap_used_pct) AS peak_swap_used,
2 max(oom_kills_total) - min(oom_kills_total) AS new_oom_kills
3FROM devops_mem
4WHERE host_id = :host_id AND time >= :start AND time < :end服务器监控主机 CPU 趋势
查询一个实例在 24 小时或 7 天内的 CPU 使用率、负载和 IO 等待趋势。
SQL
1SELECT date_bin(INTERVAL :bucket, time) AS bucket,
2 avg(usage_user_pct) AS avg_user, avg(usage_system_pct) AS avg_system,
3 max(load1) AS max_load1, max(iowait_pct) AS max_iowait
4FROM devops_cpu
5WHERE host_id = :host_id AND time >= :start AND time < :end
6GROUP BY bucket ORDER BY bucket服务器监控集群磁盘趋势
查询一个业务集群约 100 个实例在 24 小时或 7 天内的平均磁盘使用率、最大 IO 时延和新增磁盘错误数。
SQL
1SELECT bucket, avg(host_avg_used) AS avg_used, max(host_max_latency) AS max_latency,
2 sum(host_disk_error_delta) AS new_disk_errors
3FROM (
4 SELECT date_bin(INTERVAL :bucket, time) AS bucket, host_id,
5 avg(disk_used_pct) AS host_avg_used, max(io_latency_ms) AS host_max_latency,
6 max(disk_error_count) - min(disk_error_count) AS host_disk_error_delta
7 FROM devops_disk
8 WHERE cluster_id = :cluster_id AND time >= :start AND time < :end
9 GROUP BY bucket, host_id
10)
11GROUP BY bucket ORDER BY bucket服务器监控大范围扫描聚合
查询一个大区约五分之一的实例在 24 小时或 7 天内的新增 HTTP 5xx 错误数和新增重启次数。
SQL
1SELECT bucket, sum(host_http_5xx_delta) AS new_http_5xx, sum(host_restart_delta) AS new_restarts
2FROM (
3 SELECT date_bin(INTERVAL :bucket, time) AS bucket, host_id,
4 max(http_5xx_total) - min(http_5xx_total) AS host_http_5xx_delta,
5 max(restart_count_total) - min(restart_count_total) AS host_restart_delta
6 FROM devops_service
7 WHERE region = :region AND time >= :start AND time < :end
8 GROUP BY bucket, host_id
9)
10GROUP BY bucket ORDER BY bucket评价此篇文章