代码之家 › 专栏 › 技术社区 › Ted Mosby

将多行中的一些子字符串转换为新表

oracle sql

Ted Mosby · 技术社区 · 7 年前

我想从“主题”行转换这个输出…

SMARTBASE/N0184/1/MOISTURE/value
SMARTBASE/N0184/1/MOISTURE/unit
SMARTBASE/N0184/1/MOISTURE/timestamp
SMARTBASE/N0184/1/CONDUCTIVITY/value
SMARTBASE/N0184/1/CONDUCTIVITY/unit
SMARTBASE/N0184/1/CONDUCTIVITY/timestamp

到一个新的表格,如:

第一行:主题(行)的值,第二行:值的值(行)(MQTT主题的值)

但那是一个500+以上的传感器…smartbase并不总是smartbase,所以regexp…不是个好主意…最后,应将其保存为视图。

这是可能的吗?我不知道如何实现它…或者如何开始。要转换表中的一行,我可以使用pivot函数,但其余的,我不知道。

我的主要问题是:如何访问主题的各个值?

1 回复 | 直到 7 年前

MT0 7 年前

使用 REGEXP_SUBSTR 以获取 topic 列,然后使用 PIVOT :

Oracle设置 :

CREATE TABLE table_name ( topic, value ) AS 
SELECT 'SMARTBASE/N0184/1/MOISTURE/value',         '0.414' FROM DUAL UNION ALL
SELECT 'SMARTBASE/N0184/1/MOISTURE/unit',          'Raw VWC' FROM DUAL UNION ALL
SELECT 'SMARTBASE/N0184/1/MOISTURE/timestamp',     '2019-01-01T00:00:00.000' FROM DUAL UNION ALL
SELECT 'SMARTBASE/N0184/1/CONDUCTIVITY/value',     '0.529' FROM DUAL UNION ALL
SELECT 'SMARTBASE/N0184/1/CONDUCTIVITY/unit',      'mS/cm' FROM DUAL UNION ALL
SELECT 'SMARTBASE/N0184/1/CONDUCTIVITY/timestamp', '2019-01-01T00:00:00.000' FROM DUAL;

查询 :

SELECT SENSOR_TYPE,
       SENSOR,
       TO_NUMBER( moisture_value ) AS moisture_value,
       moisture_unit,
       TO_TIMESTAMP( moisture_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF3' ) AS moisture_timestamp,
       TO_NUMBER( conductivity_value ) AS conductivity_value,
       conductivity_unit,
       TO_TIMESTAMP( conductivity_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF3' ) AS conductivity_timestamp
FROM   (
  SELECT REGEXP_SUBSTR( topic, '[^/]+', 1, 1 ) AS sensor_type,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 2 ) AS sensor,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 4 ) AS measurement_name,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 5 ) AS measurement_metadata_type,
         value
  FROM   table_name
)
PIVOT(
  MAX( value )
  FOR ( measurement_name, measurement_metadata_type )
  IN  (
    ( 'MOISTURE', 'value'     ) AS MOISTURE_value,
    ( 'MOISTURE', 'unit'      ) AS MOISTURE_unit,
    ( 'MOISTURE', 'timestamp' ) AS MOISTURE_timestamp,
    ( 'CONDUCTIVITY', 'value'     ) AS CONDUCTIVITY_value,
    ( 'CONDUCTIVITY', 'unit'      ) AS CONDUCTIVITY_unit,
    ( 'CONDUCTIVITY', 'timestamp' ) AS CONDUCTIVITY_timestamp
  )
)

产量 :

SENSOR_TYPE | SENSOR | MOISTURE_VALUE | MOISTURE_UNIT | MOISTURE_TIMESTAMP              | CONDUCTIVITY_VALUE | CONDUCTIVITY_UNIT | CONDUCTIVITY_TIMESTAMP         
:---------- | :----- | -------------: | :------------ | :------------------------------ | -----------------: | :---------------- | :------------------------------
SMARTBASE   | N0184  |           .414 | Raw VWC       | 01-JAN-19 12.00.00.000000000 AM |               .529 | mS/cm             | 01-JAN-19 12.00.00.000000000 AM

小提琴 here