sql – ECommerce Storefront网站：以编程方式发现类似产品

事实上,当你想到它,大多数店面数据库已经有很多相似性数据可用.在我的情况下,产品可能是：

>映射到制造商(又名品牌),
>映射到一个或多个类别,和
>映射到一个或多个标签(又称关键字).

通过计算产品和所有其他产品之间的共享属性数量,您可以计算一个“相似度分数”,用于将其他产品与客户正在查看的产品进行比较.这是我的初始原型实现：

;WITH ProductsRelatedByTags (ProductId,NumberOfRelations)
AS
(
    SELECT  t2.ProductId,COUNT(t2.TagId)
    FROM    ProductTagMappings AS t1 INNER JOIN
                ProductTagMappings AS t2 ON t1.TagId = t2.TagId AND t2.ProductId != t1.ProductId
    WHERE   t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
    GROUP BY t2.ProductId
),ProductsRelatedByCategories (ProductId,COUNT(t2.CategoryId)
    FROM    ProductCategoryMappings AS t1 INNER JOIN
                ProductCategoryMappings AS t2 ON t1.CategoryId = t2.CategoryId AND t2.ProductId != t1.ProductId
    WHERE   t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
    GROUP BY t2.ProductId
)
SELECT  prbt.ProductId AS ProductId,IsNull(prbt.NumberOfRelations,0) AS TagsInCommon,IsNull(prbc.NumberOfRelations,0) AS CategoriesInCommon,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId THEN 1 ELSE 0 END as SameManufacturer,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId 
            THEN IsNull(prbt.NumberOfRelations,0) + IsNull(prbc.NumberOfRelations,0) + 1
            ELSE IsNull(prbt.NumberOfRelations,0)
        END as SimilarityScore
FROM    Products AS SourceProduct,Products AS SimilarProduct INNER JOIN
        ProductsRelatedByTags prbt ON prbt.ProductId = SimilarProduct.Id FULL OUTER JOIN
        ProductsRelatedByCategories prbc ON prbt.ProductId = prbc.ProductId
WHERE SourceProduct.Id = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'

其结果是这样的数据：

ProductId                            TagsInCommon CategoriesInCommon SameManufacturer SimilarityScore
------------------------------------ ------------ ------------------ ---------------- ---------------
6416C19D-BA4F-4AE6-AB75-A25A0138B3A5 1            0                  0                1
77B2ECC0-E2EB-4C1B-A1E1-A25A0138BA19 1            0                  0                1
2D83276E-40CC-44D0-9DDF-A25A0138BE14 2            1                  1                4
E036BFE0-BBB5-450C-858C-A25A0138C21C 3            0                  0                3

我不是一个SQL性能大师,所以我有以下问题给你SQL大师：

>在这种用例中,common-table-expressions(CTE)是否适合/最适合？ (他们确实看起来更容易阅读/跟随SQL).在上面提供的模型中有没有办法保存加入？

和

>这将是索引视图(持久性)的好候选者,还是增加源数据更改的成本？在这种情况下,我将使这个存储过程更新任何给定产品的PhysicalProductMappings表.

解决方法

> CTE与派生表是语法糖.在性能上没有差别.使用它们的唯一好处是可以重用它们,而不是再次复制/粘贴/输出派生表.但是,在这种情况下,您不会重复使用它,因此取决于您.
>索引视图：请记住,视图上的索引像表上的索引一样不起作用.图像它就像为您的特定查询/视图创建的另一个表,并存储在磁盘上以便更快的检索.底层数据发生变化时,必须更新这些索引.是的,这可以造成巨大的资源影响.一般来说,我宁愿看到有人编写一个使用基表上的索引的查询,如果需要更多的索引用于特定的目的,那么在具有多个表的视图上详细而不是整体地查看.这更容易维护,并且更容易弄清楚您的CRUD为什么需要比预期的更长时间.索引视图没有任何错误.但是,由于对更新/插入/删除的表的复杂性,因此在这样的应用程序数据库模型上添加这一点非常小心.索引视图的大多数更合适的用途是在报告数据仓库中.无论如何,不??要在视图中放置索引,而不了解它将对CRUD(创建,读取,更新和删除)操作的表做什么.而在CRM或应用程序支持类型的数据库中,除非有静态需求,否则我将远离他们,绝对不会影响性能.

阅读这篇文章：http://technet.microsoft.com/en-us/library/ms187864(v=sql.105).aspx

注意大约3/4的页面,它谈到在哪里不使用一个,我认为你的情况适合你不应该使用的情况下的4/5.

关于保存联接…记住FULL OUTER加入是效率最差的罪犯之一.在我看来,这是唯一的原因,是因为你不是在你的CTE中包括制造商.您可以将其包含在您的CTE中,然后在最终查询中将cat / tag的匹配数量合并在一起,以获取您的分数.这样你只有两个左外连接(每个CTE一个),然后将两个计数合并在一起并由同一制造商(案例陈述),productId等组合.
> Finaly …我会考虑把所有这些放在一个去规范化的表中,或者甚至是一个预先计算的立方体.让我们考虑一下你的要求：
一个.产品的相关分数是否需要生存？如果是,为什么？当新产品被添加/删除时,这不是关键任务.任何人说这需要活着,可能不是真的意思.
湾检索速度.我可以使用临时表重写您的查询,确保索引正确等等,并在存储过程中提出相当快的查询.但是,我仍然聚合数据库中的数据,以便在每次页面加载时都显示在我的商店前面.如果数据被预先计算并存储在产品的单独表格中,并且每个产品的分数和产品ID被索引,则检索将非常快.您可以每隔一段时间,每小时/任何时间中继和重新载入ETL表,而不用担心维护每次重建的索引.当然,如果您的店面是24/7/365,您需要编写一些数据库端代码来担心版本控制,以便您的应用程序不必等待数据库在重新计算的中间.

另外,如果没有其他的话,请确保您至少在Web /应用程序服务器上缓存此信息.有一件事是肯定的,如果你去上面的解决方案,那么你需要在你的网站上建立一些东西,所以它不等待数据返回并缓存它.

希望有所帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!