我不确定如何将多个表中的数据聚合到一个表中--有人能帮我吗?
我有大量的“历史数据”表,每个表有2列: Timestamp (bigint,unix毫秒)和Value (双精度)。
我尝试从多个表中收集数据,并为每个表计算每天的最大值。
到目前为止,我的查询如下:
SELECT DATE(FROM_UNIXTIME(Timestamp/1000)) AS Date,
CASE WHEN Source = 'Meter 1' THEN MAX(Value) ELSE '' END AS 'Meter 1',
CASE WHEN Source = 'Meter 2' THEN MAX(Value) ELSE '' END AS 'Meter 2',
CASE WHEN Source = 'Meter 3' THEN MAX(Value) ELSE '' END AS 'Meter 3'
FROM
(
SELECT 'Meter 1' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 1>
UNION ALL
SELECT 'Meter 2' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 2>
UNION ALL
SELECT 'Meter 3' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 3>
) AS data
GROUP BY Source, DATE(FROM_UNIXTIME(Timestamp/1000));如果我仅按日期分组(FROM_UNIXTIME(Timestamp/1000)),我只会看到计量器1的结果。通过也按来源分组,我会看到所有结果,但不会汇总日期,例如:
Date Meter 1 Meter 2 Meter 3
2019-12-11 26
2019-12-12 27
2019-12-13 28
2019-12-14 29
2019-12-15 30
2019-12-16 31
2019-12-17 31
2019-12-11 18
2019-12-12 18
2019-12-13 18
2019-12-14 22
2019-12-15 22
2019-12-16 22
2019-12-17 22
2019-12-11 3747
2019-12-12 3784
2019-12-13 3819
2019-12-14 3847
2019-12-15 3875
2019-12-16 3908
2019-12-17 918我已经读了一些相关的问题,但我想不通。我肯定我错过了一些简单的东西。
非常提前谢谢你。
发布于 2019-12-19 04:13:35
您还没有提供测试数据,但是让我们尝试一下
SELECT DATE(FROM_UNIXTIME(Timestamp/1000)) AS Date,
MAX(if(Source = 'Meter 1', Value, 0)) AS 'Meter 1',
MAX(if(Source = 'Meter 2', Value, 0)) AS 'Meter 2',
MAX(if(Source = 'Meter 3', Value, 0)) AS 'Meter 3'
FROM
(
SELECT 'Meter 1' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 1>
UNION ALL
SELECT 'Meter 2' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 2>
UNION ALL
SELECT 'Meter 3' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
FROM <table name 3>
) AS data
GROUP BY DATE(FROM_UNIXTIME(Timestamp/1000));发布于 2019-12-19 17:35:51
@Alexey非常感谢,您的建议非常有效。看起来,使用IF而不是CASE是可行的:
Date Meter 1 Meter 2 Meter 3
2019-12-11 26 18 3747
2019-12-12 27 18 3784
2019-12-13 28 18 3819
2019-12-14 29 22 3847
2019-12-15 30 22 3875
2019-12-16 31 22 3908
2019-12-17 31 22 3918https://stackoverflow.com/questions/59394323
复制相似问题