复古蓝牙收音机改造：用现代模块赋予古董设备新生命
2026/6/2 13:01:57
Doris Array类型实战：用一张表搞定路口安全指标的复杂存储与查询
在智慧交通系统的设计中，路口安全指标管理一直是个令人头疼的问题。传统的做法是为每个路口进口（东、南、西、北）创建单独的字段或表，但这会导致表结构臃肿、查询复杂。而Doris的Array类型为我们提供了一种优雅的解决方案——将多个进口的关联指标打包存储在一个字段中，既能保持数据结构整洁，又能实现高效查询。
1. 为什么选择Array类型存储路口数据
路口安全指标通常需要记录四个进口方向的数据，传统关系型数据库的处理方式不外乎两种：
- 宽表模式：为每个指标创建四个字段（如
north_pedestrian_rate、south_pedestrian_rate等）- 问题：字段数量爆炸，新增指标需要修改表结构
- 关联表模式：创建单独的
intersection_approach表- 问题：需要频繁联表查询，性能较差
相比之下，Array类型提供了第三种选择——将每个进口的多个指标打包成一个数组元素。例如：
-- 传统宽表设计（16个字段） CREATE TABLE intersection_wide ( intersection_id INT, north_pedestrian_rate FLOAT, north_illegal_rate FLOAT, north_conflict_count INT, south_pedestrian_rate FLOAT, -- 省略其他12个字段... ); -- Array优化设计（5个字段） CREATE TABLE intersection_array ( intersection_id INT, approach_data ARRAY<VARCHAR(100)> -- 每个元素存储"进口方向-指标1-指标2-指标3" );这种设计的优势显而易见：
- 存储效率：单条记录包含所有进口数据
- 扩展灵活：新增指标只需调整数组元素格式，无需修改表结构
- 查询便利：内置数组函数可直接分析特定进口数据
2. 实战表设计与数据插入
让我们看一个完整的路口安全指标表设计案例。该表需要记录：
- 路口整体指标（如安全系数、相位清空率）
- 各进口方向的三项指标：
- 行人过街时间保障率
- 行人闯红灯违法率
- 交通冲突次数
2.1 建表语句
CREATE TABLE intersection_safety_metrics ( `timestamp` DATETIME NOT NULL COMMENT '统计时间', `region_id` INT NOT NULL COMMENT '区域ID', `intersection_id` INT NOT NULL COMMENT '路口ID', `period_type` TINYINT COMMENT '时段类型：1-早高峰,2-平峰,3-晚高峰,4-夜间', `overall_safety_score` FLOAT COMMENT '整体安全评分', `phase_clearance_rate` FLOAT COMMENT '相位清空率', `approach_metrics` ARRAY<VARCHAR(100)> COMMENT '进口指标数组' ) DUPLICATE KEY(`timestamp`, `region_id`, `intersection_id`) DISTRIBUTED BY HASH(`intersection_id`) BUCKETS 8 PROPERTIES ("replication_num" = "3");2.2 数据插入示例
实际插入数据时，我们需要将各进口指标拼接成特定格式的字符串数组：
// Java示例：构建approach_metrics数组 String northData = String.join("-", "NB", String.valueOf(northPedestrianRate), String.valueOf(northIllegalRate), String.valueOf(northConflictCount)); String southData = String.join("-", "SB", String.valueOf(southPedestrianRate), String.valueOf(southIllegalRate), String.valueOf(southConflictCount)); // 同理构建eastData和westData... String[] approaches = {northData, southData, eastData, westData}; String arrayLiteral = "['" + String.join("','", approaches) + "']"; // 最终SQL插入语句 String sql = String.format( "INSERT INTO intersection_safety_metrics VALUES ('%s', %d, %d, %d, %.2f, %.2f, %s)", timestamp, regionId, intersectionId, periodType, safetyScore, clearanceRate, arrayLiteral);提示：实际项目中建议使用PreparedStatement防止SQL注入，此处为展示数组构造简化处理
3. 核心查询场景与数组函数应用
Array类型的真正价值在于查询时的灵活处理。Doris提供了丰富的数组函数来满足各种分析需求。
3.1 基础数组操作
获取特定进口数据（如北进口）：
SELECT intersection_id, element_at(approach_metrics, 1) AS north_data FROM intersection_safety_metrics WHERE period_type = 1;统计各路口进口数量：
SELECT intersection_id, array_size(approach_metrics) AS approach_count FROM intersection_safety_metrics;3.2 高级分析：展开数组进行明细统计
有时我们需要将数组"炸开"（explode）为多行进行更细粒度的分析：
SELECT t.intersection_id, explode_result.approach, explode_result.pedestrian_rate, explode_result.illegal_rate FROM intersection_safety_metrics t, LATERAL ( SELECT split_part(approach, '-', 1) AS approach, split_part(approach, '-', 2)::FLOAT AS pedestrian_rate, split_part(approach, '-', 3)::FLOAT AS illegal_rate FROM UNNEST(t.approach_metrics) AS tmp(approach) ) AS explode_result WHERE period_type = 1;这个查询会为每个路口的每个进口生成一行记录，便于后续的聚合分析。
3.3 性能优化技巧
对于频繁查询的数组元素，可以考虑使用物化视图预计算：
CREATE MATERIALIZED VIEW mv_approach_stats DISTRIBUTED BY HASH(intersection_id) REFRESH ASYNC AS SELECT intersection_id, period_type, array_avg( array_map( x -> split_part(x, '-', 2)::FLOAT, approach_metrics ) ) AS avg_pedestrian_rate FROM intersection_safety_metrics GROUP BY intersection_id, period_type;4. 真实业务场景解决方案
让我们看几个典型的业务需求如何通过Array类型优雅解决。
4.1 需求一：找出早高峰行人违法率最高的进口
WITH exploded_data AS ( SELECT t.intersection_id, split_part(approach, '-', 1) AS approach, split_part(approach, '-', 3)::FLOAT AS illegal_rate FROM intersection_safety_metrics t, UNNEST(t.approach_metrics) AS tmp(approach) WHERE period_type = 1 -- 早高峰 ) SELECT intersection_id, approach, illegal_rate FROM exploded_data ORDER BY illegal_rate DESC LIMIT 10;4.2 需求二：计算各区域平峰期的平均交通冲突次数
SELECT region_id, array_sum( array_map( x -> split_part(x, '-', 4)::INT, approach_metrics ) ) / array_size(approach_metrics) AS avg_conflicts FROM intersection_safety_metrics WHERE period_type = 2 -- 平峰 GROUP BY region_id;4.3 需求三：监控安全指标异常波动
-- 对比今日与上周同期的指标变化 WITH current_data AS ( SELECT intersection_id, approach_metrics AS current_metrics FROM intersection_safety_metrics WHERE timestamp = CURRENT_DATE() ), historical_data AS ( SELECT intersection_id, approach_metrics AS hist_metrics FROM intersection_safety_metrics WHERE timestamp = DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) ) SELECT c.intersection_id, array_size( array_filter( array_zip( array_map(x -> split_part(x, '-', 2)::FLOAT, c.current_metrics), array_map(x -> split_part(x, '-', 2)::FLOAT, h.hist_metrics) ), pair -> abs(pair.1 - pair.2) > 0.2 -- 变化超过20% ) ) AS abnormal_count FROM current_data c JOIN historical_data h ON c.intersection_id = h.intersection_id;5. 设计模式进阶：嵌套数组与JSON组合
对于更复杂的场景，我们可以结合Array与JSON类型实现多级嵌套存储：
CREATE TABLE intersection_advanced_metrics ( intersection_id INT, metrics ARRAY<JSON> COMMENT '各进口指标的JSON数组' ); -- 示例数据 INSERT INTO intersection_advanced_metrics VALUES (1001, [ '{"approach": "NB", "pedestrian": {"rate": 0.85, "illegal": 0.12}, "conflicts": 3}', '{"approach": "SB", "pedestrian": {"rate": 0.78, "illegal": 0.15}, "conflicts": 5}' ]);查询时结合JSON函数：
SELECT intersection_id, json_extract( element_at(metrics, 1), '$.pedestrian.rate' )::FLOAT AS north_ped_rate FROM intersection_advanced_metrics;这种设计适合指标结构经常变化的场景，但查询性能会略低于固定格式的Array设计。