SQL在两个时间点之间选择一个价格

Question

给定一个datetime价格变化的时间序列和另一个不同的N个事件的时间序列，如何在不运行N个查询的情况下查询每个事件发生时的最后一个已知价格？本质上，我需要编写某种与最近已知价格相匹配的CROSS-JOIN BETWEEN查询。

例如，价格变化历史：

Changed At (time) | Price (money)
                1 |    10
                5 |    20
               10 |    30
               20 |    40

事件：

Event Time | Nearest Matched Price (from above)
         0 | n/a
         3 | 10
         6 | 20
        10 | 30
        15 | 30

现实世界中的用例是，我用成本和价格跟踪了股票变动的时间序列，但忽略了在发票线旁边存储成本。我的股票变动通常发生在发票开票前。

我有一个类似的日历报表查询，它跨日历日/周/月报告销售，但只起作用，因为交叉连接条件(> now && < next-day)只返回一行。请原谅这个吵闹的问题(会把它清理干净)：

SELECT [t3].[FirstDateOfWeek] AS [Date], [t3].[value] AS [Total], [t3].[value2] AS [Count]
FROM (
    SELECT SUM([t0].[Total]) AS [value], COUNT(*) AS [value2], [t2].[FirstDateOfWeek]
    FROM [dbo].[vw_Invoices] AS [t0]
    LEFT OUTER JOIN [dbo].[Cases] AS [t1] ON ([t1].[CaseId]) = [t0].[CaseId]
    CROSS JOIN [dbo].[Calendar] AS [t2]
    WHERE ([t0].[Date] >= [t2].[CalendarDate]) AND ([t0].[Date] < [t2].[NextDayDateTime]) AND ([t0].[Date] >= @p0) AND ([t0].[Date] < @p1) AND (NOT ([t0].[IsVoided] = 1))
    GROUP BY [t2].[FirstDateOfWeek]
    ) AS [t3]
ORDER BY [t3].[FirstDateOfWeek]  

在SQL中，这甚至是可能的，可能是“运行总计”查询按提取的时间分组的吗？

Answer 1

快速和肮脏，没有在一个活的实例上进行测试。

对于每一行事件数据，我们需要来自Price的一行--最近发生的，但在事件的时间戳之前。TSQL支持top 1 .. order by符号。通过在SELECT列表中嵌入作为子查询的Price查找，它将在事件中每一行执行一次。根据事件的值预测价格将确保返回最近的值。就像这样：

select
    e.event_id,
    e.event_time,
    ( select top 1     -- return one value
        p.price
      from Prices as p
      where p.price_time <= e.event_time -- ensure the price change happened at or before the event
      order by p.price_time desc  -- top ensure "top 1" picks the price with the gretest i.e. most recent, timestamp
    ) as price
from Events as e;

子查询将在事件中每一行运行一次，因此对于非常大的集合，性能可能会受到损害。确保Price.price_time上有索引。

Answer 2

如果将价格数据与UNION查询中的事件数据组合在一起，那么问题就归结为找到最后一个非空值。Itzik写到了这个问题，这里：

给定一个表T1，其中包含一个名为id的键列和一个名为col1的NULLable值列，则根据id顺序返回最后一个非空col1值。

回到您的问题，下面是我为处理示例数据所做的数据准备(为了简单起见，我使用INT列，但是您应该能够轻松地切换到存储日期时间的列)：

CREATE TABLE #X_PRICE_CHANGE (CHANGED_TIME INT, PRICE INT);

INSERT INTO #X_PRICE_CHANGE
VALUES (1, 10), (5, 20), (10, 30), (20, 40);

CREATE TABLE #X_EVENT (EVENT_TIME INT);

INSERT INTO #X_EVENT
VALUES (0), (3), (6), (10), (15);

下面是解决窗口函数问题的一种方法：

SELECT 
  CHANGED_TIME AS EVENT_TIME
, CURRENT_PRICE
FROM
(
    SELECT
      CHANGED_TIME
    , SRC
    , MAX(PRICE) OVER (PARTITION BY CHANGED_TIME_OF_LAST_PRICE) CURRENT_PRICE
    FROM 
    (
        SELECT 
          CHANGED_TIME
        , PRICE
        , SRC
        , MAX(CASE WHEN SRC = 'PRICE' THEN CHANGED_TIME ELSE NULL END) 
            OVER (ORDER BY CHANGED_TIME ASC, SRC DESC 
            ) CHANGED_TIME_OF_LAST_PRICE
        FROM
        (
            SELECT 
            CHANGED_TIME, PRICE, 'PRICE' AS SRC
            FROM #X_PRICE_CHANGE

            UNION ALL

            SELECT 
            EVENT_TIME, NULL, 'EVENT' AS SRC
            FROM #X_EVENT
        ) t
    ) tt
) ttt
WHERE ttt.SRC = 'EVENT';

让我们一步一步地看一遍代码。t派生表只是将价格和事件数据与UNION ALL结合在一起。这里没有什么令人兴奋的事情：

╔══════════════╦═══════╦═══════╗
║ CHANGED_TIME ║ PRICE ║  SRC  ║
╠══════════════╬═══════╬═══════╣
║            1 ║ 10    ║ PRICE ║
║            5 ║ 20    ║ PRICE ║
║           10 ║ 30    ║ PRICE ║
║           20 ║ 40    ║ PRICE ║
║            0 ║ NULL  ║ EVENT ║
║            3 ║ NULL  ║ EVENT ║
║            6 ║ NULL  ║ EVENT ║
║           10 ║ NULL  ║ EVENT ║
║           15 ║ NULL  ║ EVENT ║
╚══════════════╩═══════╩═══════╝

tt派生表将MAX窗口函数应用于t。窗口函数的目的是为每个包含最新价格的“事件”行查找changed_time。

╔══════════════╦═══════╦═══════╦════════════════════════════╗
║ CHANGED_TIME ║ PRICE ║  SRC  ║ CHANGED_TIME_OF_LAST_PRICE ║
╠══════════════╬═══════╬═══════╬════════════════════════════╣
║            0 ║ NULL  ║ EVENT ║ NULL                       ║
║            1 ║ 10    ║ PRICE ║ 1                          ║
║            3 ║ NULL  ║ EVENT ║ 1                          ║
║            5 ║ 20    ║ PRICE ║ 5                          ║
║            6 ║ NULL  ║ EVENT ║ 5                          ║
║           10 ║ 30    ║ PRICE ║ 10                         ║
║           10 ║ NULL  ║ EVENT ║ 10                         ║
║           15 ║ NULL  ║ EVENT ║ 10                         ║
║           20 ║ 40    ║ PRICE ║ 20                         ║
╚══════════════╩═══════╩═══════╩════════════════════════════╝

考虑CHANGED_TIME为15的行。CHANGED_TIME_OF_LAST_PRICE的值为10，因此，如果我们可以返回到CHANGED_TIME_OF_LAST_PRICE = 10的“价格”行的价格值，那么对于CHANGED_TIME为15的行，我们将得到正确的价格。这就是第三个派生表ttt中发生的情况：

╔══════════════╦═══════╦═══════════════╗
║ CHANGED_TIME ║  SRC  ║ CURRENT_PRICE ║
╠══════════════╬═══════╬═══════════════╣
║            0 ║ EVENT ║ NULL          ║
║            1 ║ PRICE ║ 10            ║
║            3 ║ EVENT ║ 10            ║
║            5 ║ PRICE ║ 20            ║
║            6 ║ EVENT ║ 20            ║
║           10 ║ PRICE ║ 30            ║
║           10 ║ EVENT ║ 30            ║
║           15 ║ EVENT ║ 30            ║
║           20 ║ PRICE ║ 40            ║
╚══════════════╩═══════╩═══════════════╝

MAX()窗口函数只会在每个分区中找到一个非空值。这里使用MAX()有效地将PRICE值从“价格”行抹黑到所有对CHANGED_TIME_OF_LAST_PRICE具有相同值的“事件”行。

最后，我们从结果中删除了具有“价格”来源的不必要的行。我们需要这些行从窗口函数中获得正确的结果，但我们不希望它们出现在最终的结果集中。以下是过滤后的ttt的结果：

╔════════════╦═══════════════╗
║ EVENT_TIME ║ CURRENT_PRICE ║
╠════════════╬═══════════════╣
║          0 ║ NULL          ║
║          3 ║ 10            ║
║          6 ║ 20            ║
║         10 ║ 30            ║
║         15 ║ 30            ║
╚════════════╩═══════════════╝

从性能的角度来看，需要注意的一点是，如果您的时间列在每个表中都是唯一的，那么您可以使用行使行或范围子句可能是改善性能。

如果您想变得非常漂亮，可以通过将数据转换为二进制来消除其中一个窗口函数。为此，您可能需要调整代码以使用非INT列。这里有一个实现：

SELECT
  CHANGED_TIME
, CURRENT_PRICE
FROM
(
    SELECT 
      CHANGED_TIME
    , PRICE
    , SRC
    , CAST(SUBSTRING(MAX(binval) OVER (ORDER BY CHANGED_TIME ASC, SRC DESC 
        ), 5, 4) AS INT) CURRENT_PRICE
    FROM
    (
        SELECT 
        CHANGED_TIME, PRICE, 'PRICE' AS SRC
        , CAST(CHANGED_TIME AS BINARY(4)) + CAST(PRICE AS BINARY(4)) AS binval
        FROM #X_PRICE_CHANGE

        UNION ALL

        SELECT 
        EVENT_TIME, NULL, 'EVENT' AS SRC
        , CAST(EVENT_TIME AS BINARY(4)) + CAST(NULL AS BINARY(4)) AS binval
        FROM #X_EVENT
    ) t
) tt
WHERE tt.SRC = 'EVENT';

如果您想了解更多关于sqlmag技术的知识，我建议您阅读sqlmag文章。