SQL:固定事件与多个其他事件之间的最小时间差

Question

我知道这个标题可能看起来很复杂，所以我会尽我所能简单地解释它。想象一条生产线，其中产品是构建机器的输出，稍后在某些机器中使用(其中产品现在是输入)

让我们想象一下在DT1生产的MaterialX的寿命，然后在不同的机器上在DT3、DT3、DT4消耗:

Machine       IN           OUT             Datetime
--------------------------------------------------
PROD          ***        MaterialX            DT1
M1         MaterialX         ***              DT2
M2         MaterialX         ***              DT3
M3         MaterialX         ***              DT4

对于不同的材料也是如此

Machine       IN           OUT             Datetime
--------------------------------------------------
PROD          ***        MaterialY            DT5
M1         MaterialY         ***              DT6
M2         MaterialY         ***              DT7
M5         MaterialY         ***              DT8

注:生产是唯一的，而产品可以是一台或多台机器的输入。

我想显示的输出是生产和机器中第一个条目之间的时间:

Material      Production     First Use
----------------------------------------
MaterialX        DT1            DT2
MaterialY        DT5            DT6

你知道怎么做吗?

我试过了:

1)创建一个显示所有产品的视图(WHERE OUT如"Material%")。

2)创建一个显示所有机器使用的视图(WHERE IN like "Material%")。

3)在prod.out=use上连接所有这两个视图。输入并尝试获取最小的DateTime增量。

仍然在这个想法上工作，但我有一些问题，不喜欢为那个用例创建2个视图。欢迎任何更好的想法或方法

Answer 1

您可以使用两个表的选择，首先是生成的材料，然后是它们的使用。在材料上加入它们，输出它们的创建时间以及创建后的最小时间:

SELECT PROD.Material, PROD.Datetime, MIN(USE.Datetime)
FROM
(
    SELECT OUT as MATERIAL, Datetime FROM your_table WHERE machine='PROD'
) AS PROD
JOIN
(
    SELECT IN as MATERIAL, Datetime FROM your_table WHERE machine<>'PROD'
) AS USE
ON USE.Material = PROD.Material
GROUP BY a.Material, a.Datetime

Answer 2

这样怎么样:

select x.out, x.datetime, min(p1.datetime)
from prod p1
inner join 
(
-- get the materials produced with their production time
select p2.out, p2.datetime 
from prod p2
where p2.out is not null
) x on p1.in = x.out
group by x.out, x.datetime