sql - 显示表中不存在的行的逗号分隔值

Question

我有下面的数据集，看起来像这样。

t               mean        max     min     std     data_id
4/14/2010 0:00  12.6941 12.6941 12.6941 12.6941          1
4/14/2010 0:00  12.3851 12.3851 12.3851 12.3851          2
4/14/2010 0:10  12.389  12.389  12.389  12.389           1
4/14/2010 0:10  12.1836 12.1836 12.1836 12.1836          2
4/14/2010 0:10  11.3887 11.3887 11.3887 11.3887          3

我想将数据转换成

t,str_agg
'2010-04-14 00:00:00','12.6941','12.6941','12.6941','12.6941','12.3851','12.3851','12.3851','12.3851',,,,
'2010-04-14 00:10:00','12.3890','12.3890','12.3890','12.3890','12.1836','12.1836','12.1836','12.1836','11.3887','11.3887','11.3887','11.3887'

因此，如果您查看结果，则 t=4/14/2010 0:00 没有 data_id 3 的数据，因此结果将没有以逗号分隔的值。

我想在 postgres 中得到这个结果。我尝试了以下查询:-

select t,string_agg(mean||','||max||','||min||','||std,',') within group(order by t)  
from table_name 
group by t  
order by t;

但这给了我以下结果:-

t,str_agg
'2010-04-14 00:00:00','12.6941','12.6941','12.6941','12.6941','12.3851','12.3851','12.3851','12.3851'
'2010-04-14 00:10:00','12.3890','12.3890','12.3890','12.3890','12.1836','12.1836','12.1836','12.1836','11.3887','11.3887','11.3887','11.3887'

Answer 1

WITH dataset AS (
    SELECT *
    FROM
        (
            VALUES
            ('2010-04-14T00:00'::TIMESTAMP, 1, 1, 1, 1, 1),
            ('2010-04-14T00:00'::TIMESTAMP, 2, 2, 2, 2, 2),
            ('2010-04-14T00:20'::TIMESTAMP, 3, 3, 3, 3, 1),
            ('2010-04-14T00:20'::TIMESTAMP, 4, 4, 4, 4, 2),
            ('2010-04-14T00:20'::TIMESTAMP, 5, 5, 5, 5, 3)
        ) AS data(t, mean, max, min, std, data_id)
),
timestamps AS (
    SELECT t FROM dataset GROUP BY t
),
data_id AS (
    SELECT data_id AS id FROM dataset GROUP BY data_id
),
dataset_full AS (
    SELECT
        coalesce(dataset.t, ts.t) AS t,
        mean,
        max,
        min,
        std,
        data_id
    FROM
--         generate_series(
--                 (SELECT min(t) FROM dataset),
--                 (SELECT max(t) FROM dataset),
--                 '10 minutes')
--             AS ts(t)
        timestamps AS ts
--        CROSS JOIN generate_series(
--                       (SELECT min(data_id) FROM dataset),
--                       (SELECT max(data_id) FROM dataset))
--            AS data_id(id)
        CROSS JOIN data_id
        LEFT JOIN dataset ON ts.t = dataset.t AND data_id.id = dataset.data_id
)
SELECT
    t,
    string_agg(concat(mean, ',', max, ',', min, ',', std), ',')
FROM dataset_full
GROUP BY t
ORDER BY t;

• 数据集 CTE(公用表表达式)就在您的表的位置。

• dataset_full 通过为 10m 间隔和 data_id 值的每个组合生成一行来添加所有缺失的行。 dataset 然后被左连接到它，这意味着那些以前不存在的行现在有 NULL 值。 NULL 值然后在 string_agg 中转换为空字符串，从而得到您想要的结果。

编辑
我根据 OP 在评论中的请求更改了它，以便它只返回原始数据集中存在的具有时间戳的行。

编辑 2
我根据 OP 的另一个请求将其更改为仅使用数据集中的 data_ids。