SQL查询挑战

Question

因此，这是另一个“向X编写查询”挑战。

我正在监视许多联网的自动售货机。每台机器都有许多零件，例如钞票接收器，硬币系统，打印机等。

机器零件的问题记录在表中，我们称其为“故障”，它看起来像这样(忽略了不相关的字段):

machineid           partid         start_time            end_time
---------           ------         ----------------      ----------------
       1                2          2009-10-05 09:00      NULL
       1                3          2009-10-05 08:00      2009-10-05 10:00
       2                2          2009-09-30 12:00      2009-09-30 14:00
       3                4          2009-09-28 13:00      2009-09-28 15:00
       3                2          2009-09-28 12:00      2009-09-28 14:00

如果问题当前仍在进行，则end_date为NULL。

我需要一个查询，该查询显示整个机器停机的时间段，并且该时间段可以解释重叠的范围，将它们分解为单个记录。因此，对于上面的示例数据，它将产生:

machineid          start_time            end_time
---------          ----------------      ----------------
       1           2009-10-05 08:00      NULL
       2           2009-09-30 12:00      2009-09-30 14:00
       3           2009-09-28 12:00      2009-09-28 15:00

逐行编写过程代码并不难，但是一个不错的声明性SQL查询将更有用，更优雅。似乎应该有可能，尽管我还是不能完全到达那儿。

SQL方言是Oracle。如果有帮助，可以使用分析功能。

谢谢!

Answer 1

使用分析，您可以构建一个查询来对数据进行一次传递(对于大型数据集，这将是最有效的):

SELECT machineid, MIN(start_time), MAX(end_time)
  FROM (SELECT machineid, start_time, end_time, 
               SUM(gap) over(PARTITION BY machineid 
                             ORDER BY start_time) contiguous_faults
           FROM (SELECT machineid, start_time, 
                        coalesce(end_time, DATE '9999-12-31') end_time,
                         CASE
                            WHEN start_time > MAX(coalesce(end_time, 
                                                           DATE '9999-12-31'))
                                              over(PARTITION BY machineid 
                                                   ORDER BY start_time 
                                                   ROWS BETWEEN UNBOUNDED PRECEDING
                                                            AND 1 preceding)
                            THEN 1
                         END gap
                    FROM faults))
 GROUP BY machineid, contiguous_faults
 ORDER BY 1, 2

该查询从确定某行是否与之前开始的任何行连续开始。然后，我们将连续的行分组。