sql - 编写此相关查询的最佳方式是什么？ (根据优先级选择可能包含 NULL 的价格)

Question

假设您有一个值(value)组合，其中包含 4 种需要定价的 Assets 。 SourceID 指示应给予 PriceSource 的优先级以及应检索最低的 SourceID。如果 EODPrice 表中没有可用的价格，则应检索 AverageBookCost(位于不同的表中)。我正在使用 SQL Server 2005。

例如，假设 EODPrice 表具有以下数据(缺少“TEST004”):

SourceID    Date                   Ticker     Price
0           2011-08-02 00:00:00    TEST001    104.50
1           2011-08-01 00:00:00    TEST001    100.00
1           2011-08-02 00:00:00    TEST001    105.00
1           2011-08-04 00:00:00    TEST001    115.00
2           2011-08-03 00:00:00    TEST001    109.38
2           2011-08-04 00:00:00    TEST001    114.24
1           2011-08-01 00:00:00    TEST002    9.99
1           2011-08-02 00:00:00    TEST002    9.89
1           2011-08-03 00:00:00    TEST002    9.79
1           2011-08-04 00:00:00    TEST002    9.69
0           2011-08-03 00:00:00    TEST003    0.42
2           2011-08-01 00:00:00    TEST003    0.33
2           2011-08-02 00:00:00    TEST003    0.38
2           2011-08-03 00:00:00    TEST003    0.28
2           2011-08-04 00:00:00    TEST003    0.45

假设我们想要构建一个 Select 语句，在其中检索以下 Assets (“TEST001”、“TEST002”、“TEST003”、“TEST004”)的 EODPrice。请注意，“TEST004”是一种刚刚上市的新 Assets ，在 EODPrice 表或市场中尚无可用价格。

此外，让我们假设任何日期的所有代码都有一个来自 RunningTotal 表的非 NULL AverageBookCost 字段。 (即 SELECT AverageBookCost FROM RunningTotal WHERE Ticker = 'TEST004' AND Date = '2011-08-03' 将返回值 0.15 比方说)。

如何构建最高效的“相关”或“合并/ISNULL”查询:

SELECT Ticker, SourceID, Price
   ???
   WHERE [Date] = '2011-08-03' 
   AND [Ticker] IN ('TEST001','TEST002', 'TEST003' and 'TEST004') 

这将返回下表:(注意“TEST004”价格是 AverageBookCost 而不是在 EODPrice 表中，然后将 SourceID 设置为 NULL 以指示价格来自 RunningTotal 表:

Ticker   SourceID   Price
TEST1    2          109.38
TEST2    1          9.79
TEST3    0          0.42
TEST4    NULL       0.15

非常感谢，伯蒂。

Answer 1

我很可能把事情复杂化了，但这可能会让你入门。

简单来说，推理是这样的

• 在子选择中，SELECT 给定日期的所有代码及其最低的 SourceID。
• JOIN 此子选择的结果返回原始表。此步骤允许检索具有给定日期和最低 SourceID 的每个代码的价格。
• FULL OUTER JOIN 以前的结果与平均图书成本。此步骤向每一行添加该代码的平均价格，并为没有从 Pricetable 返回记录的代码添加行。
• 从这些结果中选择 价格(如果有)，否则从附加的列中选择平均图书成本。

SQL语句

SELECT  [Ticker] = ISNULL(pt.Ticker, pt_avg.Ticker)
        , [SourceID] = pt.SourceID
        , [Price] = ISNULL(pt.Price, pt_avg.AverageBookCost)
FROM    EODPriceTable pt 
        INNER JOIN (
          SELECT  SourceID = MIN(SourceID), Ticker, Date
          FROM    EODPriceTable
          WHERE   Date = '2011-08-03 00:00:00'
          GROUP BY
                  Ticker, Date
         ) pt_min ON  pt_min.SourceID = pt.SourceID 
                      AND pt_min.Ticker = pt.Ticker
                      AND pt_min.Date = pt.Date
         FULL OUTER JOIN (
            SELECT  Ticker, AverageBookCost
            FROM    RunningTable
         ) pt_avg ON pt_avg.Ticker = pt.Ticker
WHERE  ISNULL(pt.Ticker, pt_avg.Ticker) IN ('TEST001', 'TEST002', 'TEST003', 'TEST004')

测试脚本

;WITH EODPriceTable (SourceID, Date, Ticker, Price) AS (
  SELECT 0, '2011-08-02 00:00:00', 'TEST001', 104.50
  UNION ALL SELECT 1, '2011-08-01 00:00:00', 'TEST001', 100.00
  UNION ALL SELECT 1, '2011-08-02 00:00:00', 'TEST001', 105.00
  UNION ALL SELECT 1, '2011-08-04 00:00:00', 'TEST001', 115.00
  UNION ALL SELECT 2, '2011-08-03 00:00:00', 'TEST001', 109.38
  UNION ALL SELECT 2, '2011-08-04 00:00:00', 'TEST001', 114.24
  UNION ALL SELECT 1, '2011-08-01 00:00:00', 'TEST002', 9.99
  UNION ALL SELECT 1, '2011-08-02 00:00:00', 'TEST002', 9.89
  UNION ALL SELECT 1, '2011-08-03 00:00:00', 'TEST002', 9.79
  UNION ALL SELECT 1, '2011-08-04 00:00:00', 'TEST002', 9.69
  UNION ALL SELECT 0, '2011-08-03 00:00:00', 'TEST003', 0.42
  UNION ALL SELECT 2, '2011-08-01 00:00:00', 'TEST003', 0.33
  UNION ALL SELECT 2, '2011-08-02 00:00:00', 'TEST003', 0.38
  UNION ALL SELECT 2, '2011-08-03 00:00:00', 'TEST003', 0.28
  UNION ALL SELECT 2, '2011-08-04 00:00:00', 'TEST003', 0.45
)
, RunningTable (Ticker, AverageBookCost) AS (
  SELECT 'TEST004', 0.15
  UNION ALL SELECT 'TEST003', 0.09
)
SELECT  [Ticker] = ISNULL(pt.Ticker, pt_avg.Ticker)
        , [SourceID] = pt.SourceID
        , [Price] = ISNULL(pt.Price, pt_avg.AverageBookCost)
FROM    EODPriceTable pt 
        INNER JOIN (
          SELECT  SourceID = MIN(SourceID), Ticker, Date
          FROM    EODPriceTable
          WHERE   Date = '2011-08-03 00:00:00'
          GROUP BY
                  Ticker, Date
         ) pt_min ON  pt_min.SourceID = pt.SourceID 
                      AND pt_min.Ticker = pt.Ticker
                      AND pt_min.Date = pt.Date
         FULL OUTER JOIN (
            SELECT  Ticker, AverageBookCost
            FROM    RunningTable
         ) pt_avg ON pt_avg.Ticker = pt.Ticker
WHERE  ISNULL(pt.Ticker, pt_avg.Ticker) IN ('TEST001', 'TEST002', 'TEST003', 'TEST004')