自连接、交叉连接和分组

Question

我从多个来源获得了随时间变化的温度样本表，我想找到所有来源在设定时间间隔内的最低、最高和平均温度。乍一看，这很容易做到，如下所示：

SELECT MIN(temp), MAX(temp), AVG(temp) FROM samples GROUP BY time;

但是，如果源不断进出，而不是在我想要的时间间隔内忽略丢失的源，事情就会变得更加复杂（到了我被难住的地步！）使用来源最后知道的缺失样本的温度。使用日期时间并在随时间分布不均匀的样本中构建间隔（例如每分钟）会使事情变得更加复杂。

我认为应该可以通过在示例表上进行自联接来创建我想要的结果，其中第一个表的时间大于或等于第二个表的时间，然后计算按以下方式分组的行的聚合值来源。然而，我对如何实际做到这一点感到困惑。

这是我的测试表：

+------+------+------+
| time   | source  | temp |
+------+------+------+
|    1 | a    |   20 | 
|    1 | b    |   18 | 
|    1 | c    |   23 | 
|    2 | b    |   21 | 
|    2 | c    |   20 | 
|    2 | a    |   18 | 
|    3 | a    |   16 | 
|    3 | c    |   13 | 
|    4 | c    |   15 | 
|    4 | a    |    4 | 
|    4 | b    |   31 | 
|    5 | b    |   10 | 
|    5 | c    |   16 | 
|    5 | a    |   22 | 
|    6 | a    |   18 | 
|    6 | b    |   17 | 
|    7 | a    |   20 | 
|    7 | b    |   19 | 
+------+------+------+
INSERT INTO samples (time, source, temp) VALUES (1, 'a', 20), (1, 'b', 18), (1, 'c', 23), (2, 'b', 21), (2, 'c', 20), (2, 'a', 18), (3, 'a', 16), (3, 'c', 13), (4, 'c', 15), (4, 'a', 4), (4, 'b', 31), (5, 'b', 10), (5, 'c', 16), (5, 'a', 22), (6, 'a', 18), (6, 'b', 17), (7, 'a', 20), (7, 'b', 19);

为了进行最小值、最大值和平均值计算，我想要一个如下所示的中间表：

+------+------+------+
| time   | source  | temp |
+------+------+------+
|    1 | a    |   20 | 
|    1 | b    |   18 | 
|    1 | c    |   23 | 
|    2 | b    |   21 | 
|    2 | c    |   20 | 
|    2 | a    |   18 | 
|    3 | a    |   16 | 
|    3 | b    |   21 | 
|    3 | c    |   13 | 
|    4 | c    |   15 | 
|    4 | a    |    4 | 
|    4 | b    |   31 | 
|    5 | b    |   10 | 
|    5 | c    |   16 | 
|    5 | a    |   22 | 
|    6 | a    |   18 | 
|    6 | b    |   17 | 
|    6 | c    |   16 | 
|    7 | a    |   20 | 
|    7 | b    |   19 | 
|    7 | c    |   16 | 
+------+------+------+

以下查询使我接近我想要的结果，但它采用源第一个结果的温度值，而不是给定时间间隔内的最新一个：

SELECT s.dt as sdt, s.mac, ss.temp, MAX(ss.dt) as maxdt FROM (SELECT DISTINCT dt FROM samples) AS s CROSS JOIN samples AS ss WHERE s.dt >= ss.dt GROUP BY sdt, mac HAVING maxdt <= s.dt ORDER BY sdt ASC, maxdt ASC;

+------+------+------+-------+
| sdt  | mac  | temp | maxdt |
+------+------+------+-------+
|    1 | a    |   20 |     1 | 
|    1 | c    |   23 |     1 | 
|    1 | b    |   18 |     1 | 
|    2 | a    |   20 |     2 | 
|    2 | c    |   23 |     2 | 
|    2 | b    |   18 |     2 | 
|    3 | b    |   18 |     2 | 
|    3 | a    |   20 |     3 | 
|    3 | c    |   23 |     3 | 
|    4 | a    |   20 |     4 | 
|    4 | c    |   23 |     4 | 
|    4 | b    |   18 |     4 | 
|    5 | a    |   20 |     5 | 
|    5 | c    |   23 |     5 | 
|    5 | b    |   18 |     5 | 
|    6 | c    |   23 |     5 | 
|    6 | a    |   20 |     6 | 
|    6 | b    |   18 |     6 | 
|    7 | c    |   23 |     5 | 
|    7 | b    |   18 |     7 | 
|    7 | a    |   20 |     7 | 
+------+------+------+-------+

更新： chadhoc（顺便说一句，名字很棒！）提供了一个很好的解决方案，不幸的是它在 MySQL 中不起作用，因为它不支持 他使用的是 FULL JOIN。幸运的是，我相信简单的 UNION 是一个有效的替代品：

-- Unify the original samples with the missing values that we've calculated
(
  SELECT time, source, temp
  FROM samples
)
UNION
( -- Pull all the time/source combinations that we are missing from the sample set, along with the temp
  -- from the last sampled interval for the same time/source combination if we do not have one
  SELECT  a.time, a.source, (SELECT t2.temp FROM samples AS t2 WHERE t2.time < a.time AND t2.source = a.source ORDER BY t2.time DESC LIMIT 1) AS temp
  FROM    
  ( -- All values we want to get should be a cross of time/temp
    SELECT t1.time, s1.source
    FROM
    (SELECT DISTINCT time FROM samples) AS t1
    CROSS JOIN
    (SELECT DISTINCT source FROM samples) AS s1
  ) AS a
  LEFT JOIN samples s
  ON a.time = s.time
  AND a.source = s.source
  WHERE s.source IS NULL
)
ORDER BY time, source;

更新 2：MySQL 为 chadhoc 代码提供了以下 EXPLAIN 输出：

+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | PRIMARY            | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 |                             | 
|  2 | UNION              | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |   21 |                             | 
|  2 | UNION              | s          | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where                 | 
|  4 | DERIVED            | <derived6> | ALL  | NULL          | NULL | NULL    | NULL |    3 |                             | 
|  4 | DERIVED            | <derived5> | ALL  | NULL          | NULL | NULL    | NULL |    7 |                             | 
|  6 | DERIVED            | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using temporary             | 
|  5 | DERIVED            | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using temporary             | 
|  3 | DEPENDENT SUBQUERY | t2         | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where; Using filesort | 
| NULL | UNION RESULT       | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort              | 
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------------------+

我能够让查尔斯的代码像这样工作：

SELECT T.time, S.source,
  COALESCE(
    D.temp,
    (
      SELECT temp FROM samples
      WHERE source = S.source AND time = (
        SELECT MAX(time)
        FROM samples
        WHERE
          source = S.source
          AND time < T.time
      )
    )
  ) AS temp
FROM (SELECT DISTINCT time FROM samples) AS T
CROSS JOIN (SELECT DISTINCT source FROM samples) AS S
  LEFT JOIN samples AS D
ON D.source = S.source AND D.time = T.time

它的解释是：

+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY            | <derived5> | ALL  | NULL          | NULL | NULL    | NULL |    3 |                 | 
|  1 | PRIMARY            | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |    7 |                 | 
|  1 | PRIMARY            | D          | ALL  | NULL          | NULL | NULL    | NULL |   18 |                 | 
|  5 | DERIVED            | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using temporary | 
|  4 | DERIVED            | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using temporary | 
|  2 | DEPENDENT SUBQUERY | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where     | 
|  3 | DEPENDENT SUBQUERY | temp       | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where     | 
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+

Answer 1

我认为使用 mySql 中的排名/窗口函数会获得更好的性能，但不幸的是我不知道这些以及 TSQL 实现。这是一个符合 ANSI 的解决方案，可以正常工作：

-- Full join across the sample set and anything missing from the sample set, pulling the missing temp first if we do not have one
select  coalesce(c1.[time], c2.[time]) as dt, coalesce(c1.source, c2.source) as source, coalesce(c2.temp, c1.temp) as temp
from    samples c1
full join ( -- Pull all the time/source combinations that we are missing from the sample set, along with the temp
            -- from the last sampled interval for the same time/source combination if we do not have one
            select  a.time, a.source,
                    (select top 1 t2.temp from samples t2 where t2.time < a.time and t2.source = a.source order by t2.time desc) as temp
            from    
                (   -- All values we want to get should be a cross of time/samples
                    select t1.[time], s1.source
                    from
                    (select distinct [time] from samples) as t1
                    cross join
                    (select distinct source from samples) as s1
                ) a
            left join samples s
            on  a.[time] = s.time
            and a.source = s.source
            where s.source is null
        ) c2
on c1.time = c2.time
and c1.source = c2.source
order by dt, source

Answer 2

我知道这看起来很复杂，但它的格式是为了解释自己......
它应该有效...希望您只有三个来源...如果您有任意数量的来源，则这将不起作用...在这种情况下，请参阅第二个查询...
编辑：删除第一次尝试

编辑：如果您提前不知道来源，则必须执行一些操作来创建“填充”缺失值的中间结果集。
像这样的东西：

第二次编辑：通过移动逻辑来检索每个源的最新临时读数从 Select 子句到 Join 条件，从而消除了对 Coalesce 的需要。

Select T.Time, Max(Temp) MaxTemp,
  Min(Temp) MinTemp, Avg(Temp) AvgTemp
From
  (Select T.TIme, S.Source, D.Temp
   From (Select Distinct Time From Samples) T
     Cross Join 
        (Select Distinct Source From Samples) S
     Left Join Samples D
        On D.Source = S.Source
           And D.Time = 
               (Select Max(Time)
                From Samples
                Where Source = S.Source
                   And Time <= T.Time)) Z
Group By T.Time