SQL:改进在给定时间之前查询n列的最新值

Question

我有一个巨大的表格，其中包含来自工业机器的监控数据。该表有30个所谓的参数列，其中典型的查询每次选择2-7列。像图和状态表这样的东西是根据结果计算的。大多数计算都要求，在给定时间框架(预数据)之前的参数的最新非空值也是已知的，并且在给定的时间框架(主数据)中的值也是已知的。

目前，我对每个参数执行的预数据使用一个查询。这并不像我想要的那样高效，因为它将n查询引入DB，其中n是计算所需的参数数量。是否可以使用单个查询获取预数据(当前查询的示例请参见下面)？

数据前查询示例(也为fuel_cons、pwr_on和idle_counter执行)：

SELECT
    alert_f335
FROM
    signal_value SIGV
    ,machine M /*Using old join since this is actually a Hibernate query*/
WHERE
    M.serialNumber = 'R451902'
    AND SIGV.machineId = M.id
    AND SIGV.time = (
        SELECT
            MAX(TMP.time)
        FROM
            signal_value TMP
        WHERE
            TMP.machineId = M.id
            AND TMP.time < 1370044800000 /*2013-06-01 00:00:00*/
            AND TMP.alert_f335 IS NOT NULL
    )
ORDER BY
    SIGV.time ASC

主数据查询示例(这是可以的)：

SELECT
    alert_f335
    ,fuel_cons
    ,pwr_on
    ,idle_counter
FROM
    machine M
INNER JOIN
    signal_value SIGV
    ON M.id = SIGV.machineId
WHERE
    M.serialNumber = 'R451902'
    AND SIGV.time >= 1370044800000 /*2013-06-01 00:00:00*/
    AND SIGV.time <= 1371340799000 /*2013-06-15 23:59:59*/
    AND (alert_f335 IS NOT NULL OR fuel_cons IS NOT NULL OR pwr_on IS NOT NULL OR idle_counter IS NOT NULL)
ORDER BY
    SIGV.time ASC

机器表有两个有趣的列: id & serialNumber。除了参数列之外，signal_value还有两个有趣的列: machineId，time (millis)。

Answer 1

这取决于结果从何而来。让我们来看看您在预数据查询中所做的事情。您基本上是在寻找一个行，其中alert_f335不是null (加上一些其他过滤器)。让我们调用该行A，现在让我们考虑下一篇专栏，fuel_cons。如果该行的正确行总是A行，那么它很简单，只需将它们全部插入查询的select部分即可。但是你已经经历了这么多的麻烦来发布这个问题，所以我假设他们不是同一排的。

因此，在某种程度上，有人需要执行多个查询。您可以像现在这样从应用程序代码中发送不同的查询，或者要求Server运行多个查询。您可以将这些查询隐藏为子查询，但最终结果是SQL Server几乎需要运行这4次。当您成功地将它们组合在一起时，您将看到执行计划有循环，或者只是平铺出多个分支，可能是多个分支。因此，合并它们不会得到太多好处。

这么说，你可以得到一点好处。这4个查询在很大程度上将共享相同的过滤器，因此您可以告诉Server，它们是相同的查询，因此它至少会重用它在表假脱机或索引中的位置，或者它决定做的任何事情。如果你有好的指数，那就无关紧要了。我会坚持任何更容易写的东西。

至于实际合并它们，我将使用CTE创建这个临时子表，然后使用4个子查询将它们全部放在同一行中：

with M as (
    select * from machine where id = 'R451902'
), TMP as (
    select * from signal_value 
    join M on M.id = signal_value.machineID
    where signal_value.time < 1370044800000
)
select 
    (select top 1 alert_f335 from TMP where alert_f335 is not null order by TMP.time DESC)
    ,(select top 1 fuel_cons from TMP where fuel_cons is not null order by TMP.time DESC)
    ,(select top 1 pwr_on  from TMP where pwr_on  is not null order by TMP.time DESC)
    ,(select top 1 idle_counter from TMP where idle_counter is not null order by TMP.time DESC)