产品比较数据库模式

Question

我们希望在我们的购物网站中存储两个或多个产品之间的比较数据。

我找到了以下链接，但当我们尝试时，我们遇到了一些性能问题

SQL Group By / Count: Count Same Values Across Multiple Columns?

我们如何存储我们的数据？对于比较历史数据，我们的表模式应该是什么?我如何显示比较最多的产品？查询内容是什么？

Answer 1

这个问题相当宽泛，有点含糊，我的回答可能包含了你已经做过的考虑。

为了进行比较逻辑，您需要三件事:产品、该产品的评级和一些对产品进行逻辑分组以进行比较的方法。具有适当索引的粒度通常是最好的。

下面是我要创建的表/索引的示例：

Create  Table Product (ProductID Int, ProductName Varchar(256), etc)
Create  Table Groups (GroupID Int, GroupName Varchar(256), etc)
Create  Table ProductGroup (ProductID Int, GroupID Int)
Create  Table RatingType (RatingID Int, RatingName Varchar(64))
Create  Table ProductRatings (ProductID Int, RatingID Int, RatingValue Varchar(32))

Create  Clustered Index ix_Product_pID On Product (ProductID)
Create  Nonclustered Index ix_Product_pID_pName On Product (ProductID, ProductName)
Create  Clustered Index ix_Groups_gID On Groups (GroupID)
Create  Nonclustered Index ix_ProductGroup_gID_pID On ProductGroup (GroupID, ProductID)
Create  Clustered Index ix_RatingType_rID On RatingType (RatingID)
Create  Nonclustered Index ix_ProductRatings_pID_rID On ProductRatings (ProductID, RatingID)

将集群索引放在产品、组和RatingType上是有意义的，因为它们的ID列将是它们的标识列，因此您不必担心物理顺序，因为它们将是连续的。另一方面，ProductGroup和ProductRatings可以在任何时间以任何顺序接收任何记录，因此在它们上放置聚集索引将减慢插入速度并导致更快的碎片。

要在链接的示例中执行查询中完成的逻辑，您可以执行如下操作：

Create  Proc GetProductComparisonRatings (@productID Int, @ratingID Int)
As

Declare @columns Nvarchar(Max), 
        @SQL Nvarchar(Max);

;With   DistinctCols As
(
        Select  Distinct p.productName
        From    ProductGroup pg1
        Join    ProductGroup pg2
                On  pg1.groupID = pg2.groupID
        Join    Product p
                On  pg2.productID = p.productID
        Where   pg1.productID = @productID      
)
Select  @columns = Coalesce(@columns + ',','') + '[' + productName + ']'
From    DistinctCols
Order   By  Case
            When    p.productID = @productID Then 0
            Else    1
            End;        -- This will put your product that was the basis of the comparison as the leftmost column

Select  @SQL = ';With   baseRecords As
                (
                        Select  pg2.productID, 
                                pr.ratingValue, 
                                Count(1) As cnt
                        From    ProductGroup pg1
                        Join    ProductGroup pg2
                                On  pg1.groupID = pg2.groupID
                        Join    ProductRatings pr
                                On  pg2.productID = pr.productID
                                And pr.ratingID = ' + @ratingID + '
                        Where   pg1.productID = ' + @productID + '
                        Group   By  pg2.productID, 
                                    pr.ratingValue;
                )
                Select  ratingValue, ' + @columns + '
                From    baseRecords br
                Join    product p
                        On  br.productID = p.productID
                Pivot   (Sum(cnt) For p.productName In (' + @columns + ')) pivot';

Exec    sp_executeSQL @SQL;

这使其更进一步，并将评级比较限制为特定的ratingType；一些示例可能是(持久性、大小、整体)。如果你只有一个ratingType，它仍然可以正常工作。您可以随心所欲地编辑或更改它。