sql-server - T-SQL 每天滚动十二个月的性能

Question

我检查过类似的问题，但没有一个对我有效。最有用的是 http://forums.asp.net/t/1170815.aspx/1 ，但性能使我的查询运行了几个小时。

我有 150 万条基于 4 年的产品销售记录(约 10,000 件产品)。我想要一个包含日期、产品和连续 12 个月销售额的表格。

此查询(来自上面的链接)有效，并显示了我想要的内容，但性能使其无用:

select day_key, product_key, price, (select sum(price) as R12 from #ORDER_TURNOVER as tb1 where tb1.day_key <= a.day_key and tb1.day_key > dateadd(mm, -12, a.day_key) and tb1.product_key = a.product_key) as RSum into #hejsan
from #ORDER_TURNOVER as a

我尝试了对所有记录的滚动求和游标函数，速度快如闪电，但我无法获得仅对过去 365 天的销售额求和的查询。

非常感谢有关如何解决此问题的任何想法。谢谢。

Answer 1

我会稍微更改您的设置。

首先，有一个表格列出所有感兴趣的产品 key ...

CREATE TABLE product (
  product_key    INT NOT NULL,
  price          INT,
  some_fact_data VARCHAR(MAX),
  what_ever_else SOMEDATATYPE,
  PRIMARY KEY CLUSTERED (product_key)
)

然后，我会有一个日历表，其中包含您可能需要报告的每个日期...

CREATE TABLE calendar (
  date             SMALLDATETIME,
  is_bank_holdiday INT,
  what_ever_else   SOMEDATATYPE,
  PRIMARY KEY CLUSTERED (date)
)

最后，我会确保您的数据表在所有相关字段上都有一个覆盖索引...

CREATE INDEX IX_product_day ON #ORDER_TURNOVER (product_key, day_key)

这将允许以下查询...

SELECT
  product.product_key,
  product.price,
  calendar.date,
  SUM(price)    AS RSum
FROM
  product
CROSS JOIN
  calendar
INNER JOIN
  #ORDER_TURNOVER AS data
    ON  data.product_key = product.product_key
    AND data.day_key    >  dateadd(mm, -12, calendar.date)
    AND data.day_key    <= calendare.date
GROUP BY
  product.product_key,
  product.price,
  calendar.date

通过以这种方式执行所有操作，每个产品/calendar_date 组合将与数据表中的一组记录相关，这些记录都是彼此连续的。这将使查找要聚合的数据的操作对于优化器来说变得非常非常简单。

[需要单个索引，特别是顺序(产品、日期)。]

如果你有相反的索引，它实际上更难......

示例数据:

 product | date                   date | product
---------+-------------    ------------+---------
    A    |  01/01/2012      01/01/2012 |    A
    A    |  02/01/2012      01/01/2012 |    B
    A    |  03/01/2012      02/01/2012 |    A
    B    |  01/01/2012      02/01/2012 |    B
    B    |  02/01/2012      03/01/2012 |    A
    B    |  03/01/2012      03/01/2012 |    B

在左边 oyu 只获取 365 天 block 中彼此相邻的所有记录。

在聚合之前，您可以在右侧搜索每条记录。搜索相对简单，但你做了 365 个。比左边的版本多得多。