T-sql：如何根据类别动态创建价格范围？

Question

这是针对电子商务网站的。目前，当客户点击某个类别时，应用程序会查找该类别内产品的最高价格和最低价格，将差值除以 5（仅需要 5 个价格范围），然后根据定义的价格范围对产品进行分组。

问题是，当 90% 的产品在 50 美元左右，但很少有产品在 3000 美元左右时，那么大多数产品将被分组在第一个范围中，而后续范围只显示很少的商品。这将违背设定价格范围的目的。

理想的情况是，应用程序根据产品群体显示价格范围，例如：

• 第一个价格范围中的前 10% 产品
• 第二个价格范围中的后续 25
• % 第三个价格范围中的
• 后续 25% 第四个价格范围中
• 的最后 15%价格范围

，但每个价格范围的最小值和最大值因类别而异。

这可以在 1 个存储过程中完成吗 (SQL Express 2008)？有什么想法吗？

提前致谢。

Answer 1

将最高百分比与之前找到的范围混合将产生五个价格范围。

SQL语句

DECLARE @p1 INTEGER
DECLARE @p2 INTEGER
DECLARE @p3 INTEGER
DECLARE @p4 INTEGER
DECLARE @p5 INTEGER
DECLARE @p6 INTEGER
DECLARE @p7 INTEGER
DECLARE @p8 INTEGER
DECLARE @p9 INTEGER
DECLARE @p10 INTEGER


/* Price category 1 */
SELECT  @p1 = MIN(Price), @p2 = MAX(Price) FROM (SELECT TOP 10 PERCENT Price FROM @Products ORDER BY Price) c

/* Price category 2 */
SELECT  @p3 = MIN(Price), @p4 = MAX(Price) FROM (SELECT TOP 25 PERCENT Price FROM @Products WHERE Price > @p2 ORDER BY Price) c                  

/* Price category 3 */
SELECT  @p5 = MIN(Price), @p6 = MAX(Price) FROM (SELECT TOP 25 PERCENT Price FROM @Products WHERE Price > @p4 ORDER BY Price) c                  

/* Price category 4 */
SELECT  @p7 = MIN(Price), @p8 = MAX(Price) FROM (SELECT TOP 25 PERCENT Price FROM @Products WHERE Price > @p6 ORDER BY Price) c                          

/* Price category 5 */
SELECT  @p9 = MIN(Price), @p10 = MAX(Price) FROM @Products WHERE Price > @p8

SELECT 'Category 1', [Low] = @p1, [High] = @p2
UNION ALL SELECT 'Category 2', @p3, @p4
UNION ALL SELECT 'Category 3',  @p5, @p6
UNION ALL SELECT 'Category 4',  @p7, @p8
UNION ALL SELECT 'Category 5',  @p9, @p10

测试数据

DECLARE @Products TABLE (Price INTEGER)

INSERT INTO @Products 
          SELECT (50)
UNION ALL SELECT (51)
UNION ALL SELECT (52)
UNION ALL SELECT (53)
UNION ALL SELECT (54)
UNION ALL SELECT (55)
UNION ALL SELECT (56)
UNION ALL SELECT (57)
UNION ALL SELECT (58)
UNION ALL SELECT (59)
UNION ALL SELECT (60)

Answer 2

一种解决方案是动态生成价格范围。您可以这样做：

假设您有 100 种产品。

• 按升序对所有产品进行排序。
• 第一个价格范围内的 10% 产品 - 选择前 10 名产品。价格范围 = 第 1 - 10 个产品。
• 第二个价格范围内的后续 25% - 选择接下来 25 个产品。价格范围第 11 - 35 个产品。
• 第三个价格范围内的后续 25% - 选择接下来 25 个产品。价格范围第 36 - 60 个产品。

等等。

Answer 3

您可以使用ntile窗口功能根据需要将产品分组到价格范围内。一种方法是根据价格将产品分成20类（每类5%）。然后桶 1-2 将是第一个 10%，3-7 接下来是 25%，依此类推。

请参阅下面基于 AVDW 数据库产品表的 SQL 示例

select englishproductname,listprice,
 case  
 when nt between 1 and 2 then 1
 when nt between 3 and 7 then 2
 when nt between 8 and 12 then 3
 when nt between 13 and 17 then 4
 when nt between 18 and 20 then 5
  end as range
  from(
select englishproductname,listprice,
  ntile(20) over(order by listprice) as nt
from dbo.dimproduct) as prd