SQL 优化 - 从历史表中获取两个不同日期的值

Question

不知道从哪里开始...但基本上我有一个报告表、一个账户表和一个账户历史表。账户历史表将有零条或多条记录，其中每条记录都是账户注销标志改变后的状态。还有其他事情正在发生，但基本上我希望返回帐户详细信息数据，帐户状态取消位在开始日期和结束日期作为不同的列。

执行此操作的最佳方法是什么？

我在下面有以下工作查询

(想法)我是否应该对历史表进行单独的联接，每个日期 1 个？

我想我可以在三个单独的查询中完成(获取开始快照、结束快照、正常报告查询并连接到每个快照)

还有别的吗？

预期输出:

AccountID, OtherData, StartDateCancelled, EndDateCancelled

测试表:

DECLARE @Report TABLE (ReportID INT, StartDate DATETIME, EndDate DATETIME)
DECLARE @ReportAccountDetail TABLE( ReportID INT, Accountid INT, Cancelled BIT )
DECLARE @AccountHistory TABLE( AccountID INT, ModifiedDate DATETIME, Cancelled BIT )

INSERT INTO @Report
SELECT 1,'1/1/2011', '2/1/2011'
--
INSERT INTO @ReportAccountDetail
SELECT 1 AS ReportID, 1 AS AccountID, 0 AS Cancelled
UNION
SELECT 1,2,0
UNION
SELECT 1,3,1
UNION
SELECT 1,4,1
--
INSERT INTO @AccountHistory
SELECT 2 AS CustomerID, '1/2/2010' AS ModifiedDate, 1 AS Cancelled
UNION--
SELECT 3, '2/1/2011', 1
UNION--
SELECT 4, '1/1/2010', 1
UNION
SELECT 4, '2/1/2010', 0
UNION
SELECT 4, '2/1/2011', 1

当前查询:

SELECT Accountid, OtherData,
 MAX(CASE WHEN BeginRank = 1 THEN CASE WHEN BeginHistoryExists = 1 THEN HistoryCancelled ELSE DefaultCancel END ELSE NULL END ) AS StartDateCancelled,
 MAX(CASE WHEN EndRank = 1 THEN CASE WHEN EndHistoryExists = 1 THEN HistoryCancelled ELSE DefaultCancel END ELSE NULL END ) AS EndDateCancelled
FROM
(
SELECT c.Accountid,
'OtherData' AS OtherData,
--lots of other data
ROW_NUMBER() OVER (PARTITION BY c.AccountID ORDER BY
    CASE WHEN ch.ModifiedDate <= Report.StartDate THEN 1 ELSE 0 END DESC, ch.ModifiedDate desc) AS BeginRank,
CASE WHEN ch.ModifiedDate <= Report.StartDate THEN 1 ELSE 0 END AS BeginHistoryExists,
ROW_NUMBER() OVER ( PARTITION BY c.AccountID ORDER BY
    CASE WHEN ch.ModifiedDate <= Report.EndDate THEN 1 ELSE 0 END DESC, ch.ModifiedDate desc) AS EndRank,
CASE WHEN ch.ModifiedDate <= Report.EndDate THEN 1 ELSE 0 END AS EndHistoryExists,
    CAST( ch.Cancelled AS INT) AS HistoryCancelled,
    0 AS DefaultCancel
FROM
@Report AS Report
INNER JOIN @ReportAccountDetail AS C ON Report.ReportID = C.ReportID
--Others joins related for data to return
LEFT JOIN @AccountHistory AS CH ON CH.AccountID = C.AccountID
WHERE Report.ReportID = 1
) AS x
GROUP BY AccountID, OtherData

欢迎输入有关编写堆栈溢出问题的信息。谢谢!

Answer 1

ROW_NUMBER() 经常让我感到惊讶并且超出我的预期。然而，在这种情况下，我很想只使用相关的子查询。至少，我会针对替代方案测试它们。

注意:我还会使用真实的表、真实的索引和大量真实的假数据。 (如果值得发布这个问题，我假设值得对此进行实际测试。)

SELECT
  [Report].ReportID,
  [Account].AccountID,
  [Account].OtherData,
  ISNULL((SELECT TOP 1 Cancelled FROM AccountHistory WHERE AccountID = [Account].AccountID AND ModifiedDate <= [Report].StartDate ORDER BY ModifiedDate DESC), 0) AS StartDateCancelled,
  ISNULL((SELECT TOP 1 Cancelled FROM AccountHistory WHERE AccountID = [Account].AccountID AND ModifiedDate <= [Report].EndDate   ORDER BY ModifiedDate DESC), 0) AS EndDateCancelled
FROM
  Report                 AS [Report]
LEFT JOIN
  ReportAccountDetail    AS [Account]
    ON [Account].ReportID = [Report].ReportID
ORDER BY
  [Report].ReportID,
  [Account].AccountID

注意:无论出于何种原因，我发现 TOP 1 和 ORDER BY 比 MAX() 更快。​​

就您建议的答案而言，我会稍微修改它以仅使用 ISNULL 而不是尝试使 Exists 列起作用。

在完成所有计算之后，我还会加入“其他数据”，而不是在最内层的查询中，以避免必须按所有“其他数据”进行分组。

WITH
  HistoricData AS
(
  SELECT
    Report.ReportID,
    c.Accountid,
    c.OtherData,
    ROW_NUMBER() OVER (PARTITION BY c.ReportID, c.AccountID ORDER BY CASE WHEN ch.ModifiedDate <= Report.StartDate THEN 1 ELSE 0 END DESC, ch.ModifiedDate DESC) AS BeginRank,
    ROW_NUMBER() OVER (PARTITION BY c.ReportID, c.AccountID ORDER BY ch.ModifiedDate DESC) AS EndRank,
    CH.Cancelled
  FROM
    @Report AS Report
  INNER JOIN
    @ReportAccountDetail AS C
      ON Report.ReportID = C.ReportID
  LEFT JOIN
    @AccountHistory AS CH
      ON  CH.AccountID     = C.AccountID
      AND CH.ModifiedDate <= Report.EndDate
)
,
  FlattenedData AS
(
  SELECT
    ReportID,
    Accountid,
    OtherData,
    ISNULL(MAX(CASE WHEN BeginRank = 1 THEN Cancelled END), 0) AS StartDateCancelled,
    ISNULL(MAX(CASE WHEN EndRank   = 1 THEN Cancelled END), 0) AS EndDateCancelled
  FROM
    [HistoricData]
  GROUP BY
    ReportID,
    AccountID,
    OtherData
)
SELECT
  *
FROM
  [FlattenedData]
LEFT JOIN
  [OtherData]
    ON Whatever = YouLike
WHERE
  [FlattenedData].ReportID = 1

最终可能的版本...

WITH
  ReportStartHistory AS
(
  SELECT
    *
  FROM
  (
    SELECT
      [Report].ReportID,
      ROW_NUMBER() OVER (PARTITION BY [Report].ReportID, [History].AccountID ORDER BY [History].ModifiedDate) AS SequenceID,
      [History].*
    FROM
      Report                 AS [Report]
    INNER JOIN
      AccountHistory         AS [History]
        ON [History].ModifiedDate <= [Report].StartDate
  )
    AS [data]
  WHERE
    SequenceID = 1
)
,
  ReportEndHistory AS
(
  SELECT
    *
  FROM
  (
    SELECT
      [Report].ReportID,
      ROW_NUMBER() OVER (PARTITION BY [Report].ReportID, [History].AccountID ORDER BY [History].ModifiedDate) AS SequenceID,
      [History].*
    FROM
      Report                 AS [Report]
    INNER JOIN
      AccountHistory         AS [History]
        ON [History].ModifiedDate <= [Report].EndDate
  )
    AS [data]
  WHERE
    SequenceID = 1
)
SELECT
  [Report].ReportID,
  [Account].*,
  ISNULL([ReportStartHistory].Cancelled, 0) AS StartDateCancelled,
  ISNULL([ReportEndHistory].Cancelled,   0) AS EndDateCancelled
FROM
  Report                     AS [Report]
INNER JOIN
  Account                    AS [Account]
LEFT JOIN
  [ReportStartHistory]
    ON  [ReportStartHistory].ReportID  = [Report].ReportID
    AND [ReportStartHistory].AccountID = [Account].AccountID
LEFT JOIN
  [ReportEndHistory]
    ON  [ReportEndHistory].ReportID    = [Report].ReportID
    AND [ReportEndHistory].AccountID   = [Account].AccountID