[Doris/函数] Doris 之数据查询

1 概述: Doris 之数据查询

2 JSON 函数篇

GET_JSON_STRING 函数 : JSON 字段提取为String

  • 推荐文献
  • GET_JSON_STRING 是 Apache Doris 提供的一个内置函数

用于从 JSON 文本中提取指定路径的字段值，并将其转换为字符串类型。
如果路径不存在或无法转换为字符串，则返回 NULL。

示例: 提取简单键值

  • 从 JSON 中获取键 "k1" 的值：
SELECT 
	GET_JSON_STRING('{"k0": {"k01": ["hello", "world"] }, "k1":"v1", "k2":"v2"}', "$.k1") as r1 -- 第1层的字段
    -- , GET_JSON_STRING('{"k0": {"k01": ["hello", "world"] }, "k1":"v1", "k2":"v2"}', "$.k0.k01") as r2 -- 第2层的字段(嵌套字段)
    -- , GET_JSON_STRING('{"k0": {"k01": ["hello", "world"] }, "k1":"v1", "k2":"v2"}', "$.k0.k01[1]") as r2 -- 第2层的字段(嵌套字段)

-- 返回结果:
r1|r2               |r2   |
--+-----------------+-----+
v1|["hello","world"]|world|

示例: 提取数组中的元素

  • 从键 "my.key" 的数组中获取第二个元素：
SELECT GET_JSON_STRING('{"k1":"v1", "my.key":["e1", "e2", "e3"]}', '$."my.key"[1]');
-- 返回结果: e2

示例: 嵌套路径提取

  • 从嵌套路径中提取第一个数组元素：
SELECT GET_JSON_STRING('{"k1.key":{"k2":["v1", "v2"]}}', '$."k1.key".k2[0]');
-- 返回结果: v1

示例: 提取数组中所有匹配的值

  • 从数组中提取所有键为 "k1" 的值：
SELECT GET_JSON_STRING('[{"k1":"v1"}, {"k2":"v2"}, {"k1":"v3"}, {"k1":"v4"}]', "$.k1");

-- 返回结果: ["v1","v3","v4"]

注意事项

  • 路径格式：<json_path> 必须以 $ 开头，使用 . 分隔层级，数组索引用 [ ] 表示。
  • 特殊字符：路径中包含 . 时需用双引号包裹，例如 $."my.key".
  • 返回值：如果路径不存在或格式错误，返回 NULL。
  • 此函数适用于解析 JSON 数据，但对于复杂操作，推荐使用 jsonb 类型和相关函数以提高性能。

GET_JSON_INT 函数 : JSON 字段提取为Int型

  • 参考/类比: get_json_string 函数

GET_JSON_BOOL 函数 : JSON 字段提取为Bool型

  • 参考/类比: get_json_string 函数

3 时间函数篇

CASE 时间字符串 转 毫秒级时间戳

  • 时间字符串 转 毫秒级时间戳: UNIX_TIMESTAMP(timeString)
  • 时区转换函数: CONVERT_TZ( {timeString}, '{fromTimeZone，如:Asia/Shanghai}', '{fromTimeZone，如:UTC}')
select
   *
	-- , ROUND(Clutch_Workstate, 0) as Clutch_Workstate_Int
	, LEAD(Clutch_Workstate, 1, null) OVER (PARTITION BY device_id ORDER BY collect_time ASC) AS Clutch_Workstate_NextState -- 下一时刻的信号状态 | LEAD 函数: 比较同一结果集中当前行与后续行的数据
from (
  select
    device_id
    , report_ts as report_time -- 上报时间
    , `time` -- 采集时间
    -- 针对 `time` 格式为 `2026-01-20T02:54:01.421Z` (UTC+0时区) 时，转毫秒级时间戳:
    -- , UNIX_TIMESTAMP(SUBSTRING_INDEX(REGEXP_REPLACE(`time`, '[TZ]', ' '), '.', 1), 'yyyy-MM-dd HH:mm:ss') * 1000 + CAST(LPAD(SUBSTRING_INDEX(REGEXP_REPLACE( `time`, '[TZ]', ' '), '.', -1), 3, '0') AS INT) AS collect_time
    -- 针对 `time` 格式为 `2025-12-20 02:39:19` (东八区) 时，转毫秒级时间戳:
    , UNIX_TIMESTAMP( CONVERT_TZ( `time`, 'Asia/Shanghai', 'Asia/Shanghai') ) * 1000 as collect_time

    , CAST( SG_IC_Mileage AS DECIMAL(10, 2) ) as IC_Mileage
    , CAST( SG_Clutch_Workstate AS DECIMAL(10, 2) ) as Clutch_Workstate
  from ods_device_signals_ri
) x1

Z 案例实践

CASE 降采样查询

  • 关键点: DATE_TRUNC(...) 函数 + AVG/MAX/MIN/...(...)函数
-- 若 eventTime 已是 Asia/Shanghai 时间
-- https://doris.apache.org/zh-CN/docs/dev/sql-manual/sql-functions/scalar-functions/date-time-functions/from-second

set time_zone = 'Asia/Shanghai'; -- 'UTC' / 设置会话级时区 , 将影响 from_second 等函数

SELECT
    device_id,
    DATE_TRUNC( samplingDatetime , 'minute')                  AS sampling_segment, -- year / month / week / hour / minute / second / 
    AVG(  XxxPackCnt )                                        AS XxxPackCntAvg,
    MAX( XxxPackCnt )                                         AS XxxPackCntMax
FROM (
	select 
		*
		, from_second(sampling_time/1000) as samplingDatetime
        , get_json_int(Xxx_info, '$.XxxPackCnt') as XxxPackCnt
		, get_json_string(Xxx_info, '$.XxxPackInfo') as XxxPackInfo
		, get_json_string(Xxx_info, '$.XxxPackInfo[0].XxxSoh') as firstXxxPackXxxSoh -- 首个电池包的Soh
		, get_json_string(Xxx_info, '$.XxxPackInfo[0].XxxPackTempMax') as firstXxxPackTempMax -- 首个电池包的最大温度
	from bdp_rhr.dwd_device_status_signal_ri
	where 1=1
	  and device_id = 'DEVICE-0001'
	order by data_version desc
	limit 1000
) x
-- WHERE sampling_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY device_id, sampling_segment
ORDER BY device_id, sampling_segment;

device_id     |sampling_segment   |XxxPackCntAvg|XxxPackCntMax|
--------------+-------------------+-------------+-------------+
DEVICE-0001|2025-11-07 18:22:00|          1.0|            1|
DEVICE-0001|2025-11-07 18:23:00|          1.5|            1|
DEVICE-0001|2025-11-07 18:24:00|          1.0|            1|
DEVICE-0001|2025-11-07 18:25:00|          1.0|            1|
DEVICE-0001|2025-11-07 18:26:00|          1.9|            1|
DEVICE-0001|2025-11-07 18:27:00|          1.0|            1|

Y 推荐文献

  • Apache Doris

X 参考文献

posted @ 2025-10-13 15:35  千千寰宇  阅读(240)  评论(0)    收藏  举报