SQL Server - 根据值搜索特定列

Question

我得到了非常宽的每月索引表，如下所示：

index_id | ... | Mar2009 | Apr2009 | May2009 | ... | Feb2010 |
1        | ... | value1  | value2  | value3  | ... | value11 |

有 180 列，名称串联代表月份和年份（2009 年 3 月、2009 年 4 月……），大约有 5000 条记录，该表每月更新一次。

我还获得了第二个表，其中包含以下数据：（

index | some other data | index_id | saledate | saleprice | estimated price |
1234  | other data ...  |        1 | 03/05/09 |       100 | ??????????????? |

大约 100 万条记录），我需要根据索引提供完整记录，包括 estimated_price ，其计算方式为： saleprice * ( value1 / value11 ) value1 是因为销售日期 ID 是 2009 年 3 月，value11 是因为当前月份。

我有两个选项，两个选项都需要根据值访问特定列，第二个选项可以解决：

1. 动态计算（以及如何根据销售日期和当前数据访问索引表中的正确列) - 请注意数据表很大，索引也不小

2. 当更新索引表运行一个作业来计算估计价格时，将其放入数据表中，如何与上面相同的问题（如何访问基于索引表中的正确列销售日期）

第二个解决方案乍一看似乎更有效，但是我担心更新过程可能需要太长时间...我考虑使用一个临时表将宽索引表转换为长表，例如：

index_id | date_from_column | index_value |

这样可以更容易合并表，但是我需要在更新索引表后截断长表并运行 180 个插入，例如：

INSERT INTO long_table SELECT index_id, 'Mar2009' AS date_from_column, Mar2009 AS index_value FROMindexs_table

其中每个下一个 INSERT 将具有下一列名称表单索引

Answer 1

从根本上来说，您遇到的问题是您的设计没有标准化，结果是提取信息的查询更加困难。具体来说，第一个表的结构应如下所示：

Create Table MonthlyIndexes As
    (
    index_id int not null
    , date datetime not null
    , value ...
    , Primary Key ( index_id, date )
    , Constraint CK_MonthlyIndexes_Date Check ( DatePart(d, date) = 1 )
    )

index_id | date     | value
--------   --------   -------
1        | 20090301 | ...
1        | 20090401 | ...
...

现在，检索所需信息的查询变得更简单：

Select S.saleprice * ( MStart.value / MEnd.value )
From Sales As S
    Join MonthlyIndexes As MStart
        On MStart.date = DateAdd(d, -DatePart(d, S.saledate) + 1)
    Join MonthlyIndexes As MEnd
        On MEnd.date = DateAdd(d, -DatePart(d, CURRENT_TIMESTAMP) + 1)

除非重构架构，否则您可以像这样模拟正确的设计（假设 SQL Server 2005+）：

With Indexes As
    (
    Select index_id, Cast('20090301' As datetime) as date, Mar2009
    From MonthlyIndexes
    Union All
    Select index_id, Cast('20090401' As datetime) as date, Apr2009
    From MonthlyIndexes
    ....
    Union All
    Select index_id, Cast('20100201' As datetime) as date, Feb2010
    From MonthlyIndexes
    )
Select S.saleprice * ( MStart.value / MEnd.value )
From Sales As S
    Join MonthlyIndexes As MStart
        On MStart.date = DateAdd(d, -DatePart(d, S.saledate) + 1)
    Join MonthlyIndexes As MEnd
        On MEnd.date = DateAdd(d, -DatePart(d, CURRENT_TIMESTAMP) + 1)

如果每月索引表如果很大，您可以对规范化表进行周期更新并将其用于查询。

另一个解决方案是使用类似于我的 CTE 中的 Union All 查询的查询定期更新具有正确结构的数据的临时表。如果每月添加一次数据，您甚至可以添加一个计划存储过程来检查是否存在给定月份的列并附加该月的数据。这将涉及动态 SQL，我通常建议不要使用动态 SQL，但是对于维护解决方案来说，它可能会解决问题（直到您说服管理层更正架构）。