代码之家 › 专栏 › 技术社区 › RononDex

合并由联合查询选择的2行

union mysql

RononDex · 技术社区 · 11 年前

好吧,我遇到了一个非常奇怪的问题。

我正试图从mysql数据库加载一些传感器数据。表结构如下:

+-----------------+---------------------+--------------+
+ PK [bigint(20)] +  timed [bigint(20)] + NO2 [double] +
+-----------------+---------------------+--------------+

杀伤概率 是主键
计时是从1970年1月1日起存储为毫秒的时间戳(linux时间戳)
二氧化氮 NO2的测量浓度(单位:ppb,十亿分之一)

现在最难的部分来了。有两个相同的表具有与上述完全相同的模式。一个用于原始数据,一个用于验证数据。

在数据得到验证之前,通常需要大约1个月的时间。因此,当我显示传感器的实时数据时,我使用 UNION 。验证值应始终优先于原始值。此外,我只加载每日平均值。

这是我到目前为止创建的SQL查询:

SELECT  
    mergedData.rawValue,
    mergedData.validatedValue,
    CAST(IF(mergedData.validatedValue IS NULL, mergedData.rawValue, mergedData.validatedValue) AS DECIMAL(65, 2)) as sensorValue,
    timeValue,
    IsValid
FROM
(SELECT 
    Month(FROM_UNIXTIME(timed / 1000)) as months,
    Year(FROM_UNIXTIME(timed / 1000)) as years,
    DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
    HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
    MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
    avg(NO2) as rawValue,
    FROM_UNIXTIME(timed / 1000) as timeValue,
    IF(NO2 IS NOT NULL, 1, 0) as IsValid,
    NULL as validatedValue
FROM
    nabelnrt_pay
WHERE
    timed > 1360236120000
    AND timed < 1391772120000
GROUP BY years, months, days
UNION
SELECT 
    Month(FROM_UNIXTIME(timed / 1000)) as months,
    Year(FROM_UNIXTIME(timed / 1000)) as years,
    DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
    HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
    MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
    avg(NO2) as validatedValue,
    FROM_UNIXTIME(timed / 1000) as timeValue,
    IF(NO2 IS NOT NULL, 1, 0) as IsValid,
    NULL as rawValue
FROM
    nabelvalidated_pay
WHERE
    timed > 1360236120000
    AND timed < 1391772120000
GROUP BY years, months, days) as mergedData ORDER BY timeValue

这将导致以下结果: enter image description here

我知道的问题是,当两个表都包含一个条目时,我会从查询中得到两行。我想将它们合并到一行,所以结果中每个时间戳只有一行。出于某种原因,数据 mergedData.validatedValue 显示在 rawValue 而不是 validatedValue .

有人能告诉我如何将这两行合并为一行,以及为什么validatedData显示在错误的列中吗?

2 回复 | 直到 11 年前

chingeez 11 年前

在与联合使用的查询中,列的顺序必须相同。您的查询应该类似于:

SELECT max(mergedData.rawValue),
       max(mergedData.validatedValue),
       CAST(IF(max(mergedData.validatedValue) IS NULL, max(mergedData.rawValue),  
       max(mergedData.validatedValue)) AS DECIMAL(65, 2)) as sensorValue,
       max(timeValue),
       max(IsValid)  
FROM ((SELECT Month(FROM_UNIXTIME(timed / 1000)) as months,
          Year(FROM_UNIXTIME(timed / 1000)) as years,
          DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
          HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
          MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
          avg(NO2) as rawValue,
          FROM_UNIXTIME(timed / 1000) as timeValue,
          0 IsValid,
          NULL as validatedValue
  FROM nabelnrt_pay
  WHERE timed > 1360236120000 AND timed < 1391772120000
  GROUP BY years, months, days
 )
 UNION ALL
 (SELECT Month(FROM_UNIXTIME(timed / 1000)) as months,
         Year(FROM_UNIXTIME(timed / 1000)) as years,
         DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
         HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
         MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
         NULL as rawValue,
         FROM_UNIXTIME(timed / 1000) as timeValue,
         1 AS IsValid,
         avg(NO2) as validatedValue
  FROM nabelvalidated_pay
  WHERE timed > 1360236120000 AND timed < 1391772120000
  GROUP BY years, months, days
 )
) as mergedData
GROUP BY years, months, days
ORDER BY timeValue

更改后,您将获得两行已验证的读数和一行未验证的读数。您使用的最后一组将对这两行进行分组,以获得经过验证的读数。因为你在select中使用了聚合函数 max(合并数据.rawValue) ,你会得到想要的结果。

Gordon Linoff 11 年前

问题是你正在得到两者最终数据中的行。你需要再次聚合。此外,由于您希望在最终结果中包含这两个值,因此应该使用 union all 而不是 union . 联盟 删除重复项,这是不必要的,因为两个子查询之间的行不会相同:

SELECT max(mergedData.rawValue),
       max(mergedData.validatedValue),
       CAST(IF(mergedData.validatedValue IS NULL, mergedData.rawValue, mergedData.validatedValue) AS DECIMAL(65, 2)) as sensorValue,
       timeValue,
       IsValid
FROM ((SELECT Month(FROM_UNIXTIME(timed / 1000)) as months,
              Year(FROM_UNIXTIME(timed / 1000)) as years,
              DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
              HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
              MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
              avg(NO2) as rawValue,
              FROM_UNIXTIME(timed / 1000) as timeValue,
              IF(NO2 IS NOT NULL, 1, 0) as IsValid,
              NULL as validatedValue
      FROM nabelnrt_pay
      WHERE timed > 1360236120000 AND timed < 1391772120000
      GROUP BY years, months, days
     )
     UNION ALL
     (SELECT Month(FROM_UNIXTIME(timed / 1000)) as months,
             Year(FROM_UNIXTIME(timed / 1000)) as years,
             DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
             HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
             MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
             avg(NO2) as validatedValue,
             FROM_UNIXTIME(timed / 1000) as timeValue,
             IF(NO2 IS NOT NULL, 1, 0) as IsValid,
             NULL as rawValue
      FROM nabelvalidated_pay
      WHERE timed > 1360236120000 AND timed < 1391772120000
      GROUP BY years, months, days
     )
    ) as mergedData
GROUP BY years, months, days
ORDER BY timeValue