我有下面的数据集,看起来像这样。
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'
发布于 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;
编辑
我通过OP在注释中对每个请求进行了更改,以便它只返回具有原始数据集中存在的时间戳的行。
编辑2个
我根据OP的另一个请求将其更改为仅使用数据集中的data_ids。
https://stackoverflow.com/questions/50591553
复制相似问题