sql - postgres DB 中的 Concat 列数据

Question

我有一个查询显示这样的数据:-

t                    max_type   mean_type   min_type    std_type    sid
 2010-04-17 00:00:00 5.300       4.3372,     3.4000,     null        1      
 2010-04-17 00:00:00 5.3135      null        3.0365      0.3795      2

我想像这样显示数据:-

 t                      string_agg                 sid
'2010-04-17 00:00:00',  5.3000,4.3372,3.4000,        1
'2010-04-17 00:00:00',  5.3135,,3.0365,0.3795        2

我使用的查询:-

SELECT m.measurement_at AS t,MAX(CASE mc.type WHEN 'max' THEN VALUE END) 
AS max_type,MAX(CASE mc.type WHEN 'mean' THEN VALUE END) AS mean_type,
MAX(CASE mc.type WHEN 'min' THEN VALUE END) AS min_type,MAX(CASE mc.type WHEN 'std' THEN VALUE END)
AS std_type,mc.sensor_id AS sId 
FROM flow.measure m
INNER JOIN flow.measure_col mc 
ON mc.id=m.measure_col_id 
INNER JOIN flow.sensors s 
ON s.id=mc.sensor_id WHERE s.station_id=1 
GROUP BY t,sId ORDER BY t

Answer 1

这是简单的字符串连接:

select t,
       concat_ws(',', coalesce(max_type, ''), coalesce(mean_type, ''),
                 coalesce(min_type, ''), coalesce(std_type, '')
                ) as string_agg
       sid
from . . . ;

你确实需要注意类型。上面假设值实际上是字符串(尽管如果不是，它们很容易转换为字符串)。

您可以将查询构造为:

SELECT m.measurement_at AS t,
       concat_ws(',',
                 coalesce(MAX(CASE mc.type WHEN 'max' THEN VALUE END)::text, ''),
                 coalesce(MAX(CASE mc.type WHEN 'mean' THEN VALUE END)::text, ''),
                 coalesce(MAX(CASE mc.type WHEN 'min' THEN VALUE END)::text, ''), 
                 coalesce(MAX(CASE mc.type WHEN 'std' THEN VALUE END)::text, '')
                ) as string_agg,
        mc.sensor_id AS sId 
FROM flow.measure m INNER JOIN
     flow.measure_col mc 
     ON mc.id = m.measure_col_id INNER JOIN
     flow.sensors s 
     ON s.id = mc.sensor_id
WHERE s.station_id = 1
GROUP BY t, sId
ORDER BY t;