显示表中不存在的行的逗号分隔值
显示表中不存在的行的逗号分隔值
提问于 2018-05-30 03:25:03
我有下面的数据集，看起来像这样。
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'回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-30 05:02:45
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被保留为JOINed，这意味着对于那些以前不存在的行，现在有了空值。然后在string_agg中将空值转换为空字符串，从而得到您想要的结果。
编辑
我通过OP在注释中对每个请求进行了更改，以便它只返回具有原始数据集中存在的时间戳的行。
编辑2个
我根据OP的另一个请求将其更改为仅使用数据集中的data_ids。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50591553
复制相关文章
相似问题