SQL研究-非平衡树聚合

 

今天，我重新检查了一个困扰已久的问题，在经过仔细的考虑，算是找到了一个比较合理的解决办法。

 

问题是这样的，表StoreProductSales记录一系列的商场以及它们每天的营业额。商场与商场之间有从属关系，记录在Store_WithAcestor表里。当用户查询某个商场的对某个产品的销售额时，除了返回自己的营业额，还需要包含所有子商场的营业额。

 

示例结构和数据如下：

StoreProductSales

(

DayId     int,

StoreId  nvarchar(8),

ProductId nvarchar(8),

Sales int

)

Insert into StoreProductSales values(1,'BJ','Cloth',100)

Insert into StoreProductSales values(1,'BJ','Shoe',100)

Insert into StoreProductSales values(1,'BJ-HaiDian','Cloth',50)

 

表Store_WithAncestor存放有

Parent, Child

BJ, BJ

BJ, BJ-HaiDian

BJ_HaiDian, BJ_Haidian

 

用户输入某个Store的ID，开始DayId和结束DayId，输出该Store在这段时间里所有卖出的Product及其销售量。

 

最简单的方法是从Store_WithAncestor中选出需要store所有子store，如果用户选择BJ，那么就把BJ，BJ-HaiDian都选出来，然后把所有这些商场上成交的产品销量叠加起来；

 

这样做是很慢的。不妨简单的计算一下，如果某个商场有100个子商场，共计10000个商品，那么为了求出该商场的每个产品的日销量，需要做1百万次加法运算。同时应该考虑到数据库的内存扫描工作。假设数据按照store做聚集索引，那么对于每个商场，需要额外读取1百万的页面。

 

怎样才能使它快一点呢？

很容易想到的是如果我们能生成一个聚合表，在该表中存放的sales就是商场本身的销售量加上所有孩子的销售量。这样查询的时候，既不需要做online的计算，也不需要执行额外的IO操作。

 

但是这样做有一个问题。

那就是您不能保证每个商品在每个商场每天都有销量。

假设这样的情形。在某天，cloth在BJ_haidian销售了100，而在BJ 没有销售。那么在上述的聚合表里是不会出现BJ的记录的，因为它只存放自己和孩子的销售量之和。当用户查询那天BJ的销售额时，由于不查询它的孩子，cloth的销量是0。

 

显然这是一个错误。

 

当然如果原始表中有一条记录，BJ， cloth，0，那一切都很好。但是你不可能那么做。假如有1000个store，10000个商品，在表中每天加入1千万行只是为了占位置，看起来是不可理喻的。

 

那么还有没有别的办法呢？

我的方法是在源表的基础上加一个列withParent，它表明在那一天的销售记录中，对于本产品是否有父辈的商场也出售了。例如，在20090601这一天，BJ_haidian和bj都卖出了cloth，那么这一行的withParent对应为0，如果BJ没有卖出，则为1.

当用户查询BJ的销售记录时，我任然对它进行扩展，但我并不需要加载所有子节点对应的行，因为withParent就在索引里，我们只需要扫描索引节点就可以判断是否需要加载这一行。因此我们不但可以减少加载所需的IO操作，还减少了额外的加法操作。

 

实际证明，该方法使得执行时间缩短了十分之一。