【逗号分隔数据转时间序列】

逗号分隔数据转时间序列

有时候，时间序列数据比较多的时候，需要将其存储为逗号分割的数据，用来节省时间空间，但是，又需要转化为正确的格式，提交给用户，以下sql可以实现此功能。

sql语句详情

select * from (
WITH RECURSIVE TimeSeries AS (
    SELECT
        tm::TIMESTAMP AS tm,
				scprod_id,
				unitcd,
				ndcd,
				step,
				tm_end,
        1 AS idx,
        string_to_array(q, ',') AS parts
    FROM
        (select t1.tm,
				t1.scprod_id,
				t1.unitcd,
				t1.ndcd,
				t1.step,
				q,
				t3.end_time as tm_end
				from 
(select  * from resource_calc_scene_node_result_plus where scprod_id=1759) t1
LEFT JOIN
(select * from resource_calc_scene_production where scprod_id=1759) t2
ON
t1.scprod_id=t2.scprod_id
LEFT JOIN
resource_calc_scene t3
ON
t2.calcscene_id=t3.calcscene_id
				) td
    UNION ALL
    SELECT
        (tm + interval '1 minute' * step)::TIMESTAMP AS tm,
				scprod_id,
				unitcd,
				ndcd,
				step,
				tm_end,
        idx + 1 AS idx,
        parts
    FROM
        TimeSeries
    WHERE
        (tm + interval '1 minute' * step)::TIMESTAMP <= tm_end
)

SELECT
    tm,
		scprod_id,
		unitcd,
		ndcd,
		step,
		tm_end,
    parts[idx] AS q
FROM
    TimeSeries
ORDER BY
    scprod_id,unitcd,ndcd,tm
)tb