如何按日期范围连接记录

Question

我需要将一张表中的报废记录与指示在机器上同时运行的材料的记录相匹配。我有一张有废料计数的桌子和一张有记录的桌子，只要机器上的材料变了，我就会记录下来。

我有一个工作中的查询，我将在下面包含一个简化版本，但是当应用到一个大型数据集时，它会非常慢。我想尝试Oracle的分析功能之一，以使它更快，但我不知道如何。我尝试了FIRST_VALUE和ROW_NUMBER的几种不同的形式，但是我无法正确地理解它们。寻求任何建议。

如果你想要更多的细节，请告诉我。

以下是表的简化版本：

废料读数表(~4100万行)

• 机器
• ScrapReasonCode
• ScrapQuantity
• ReportTime

材料数(~3m行)

• 机器
• MaterialNumber
• MEASUREMENT_TIMESTAMP

  SELECT Scrap.Machine,
       Scrap.MaterialNumber,
       Scrap.ScrapReasonCode,
       Scrap.ScrapQuantity,
       Scrap.ReportTime
  FROM Scrap, Materials
  WHERE Scrap.Machine = Materials.Machine
       AND Materials.MEASUREMENT_TIMESTAMP =
               (SELECT MAX (M2.MEASUREMENT_TIMESTAMP)
                FROM Materials M2
                WHERE M2.Materials.Machine = Scrap.Machine
                       AND M2.MEASUREMENT_TIMESTAMP <= Scrap.ReportTime)

Answer 1

我想这就是你想要做的。您可以使用FIRST_VALUE窗口函数。

SELECT DISTINCT
       s.Machine,
       s.MaterialNumber,
       s.ScrapReasonCode,
       s.ScrapQuantity,
       s.ReportTime,
       FIRST_VALUE(m.MEASUREMENT_TIMESTAMP) OVER(PARTITION BY s.Machine ORDER BY m.MEASUREMENT_TIMESTAMP DESC)
  --or you can use the `MAX` window function too. 
  --MAX(m.MEASUREMENT_TIMESTAMP) OVER(PARTITION BY s.Machine)
FROM Scrap s
JOIN Materials m
WHERE s.Machine = m.Machine AND m.MEASUREMENT_TIMESTAMP <= s.ReportTime

Answer 2

我可能误解了您的要求，但我认为以下查询在使用ROW_NUMBER实现时应该有效

SELECT q.* 
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Scrap.Machine ORDER BY Materials.MEASUREMENT_TIMESTAMP DESC) AS RNO 
       Scrap.MaterialNumber,
       Scrap.ScrapReasonCode,
       Scrap.ScrapQuantity,
       Scrap.ReportTime
  FROM Scrap, Materials
 WHERE     Scrap.Machine = Materials.Machine
       AND Materials.MEASUREMENT_TIMESTAMP <= Scrap.ReportTime
) q 
WHERE q.RNO = 1

编辑:如果您需要在报废ReportTime之前(而不是在-或之前)使用度量时间戳，您只需在上面的查询中将<=符号更改为<符号即可。