CODEWITHSUNDEEP
t-sqlgrouping
Mint
Mint

Reputation: 37

T-sql: How to create price range dynamically based on category?

This is for ecommerce site. Currently, when customer click on a category, application will find the maximum price and minimum price from products within this category, divide the difference by 5(only 5 price ranges needed), then group the products according to defined price ranges.

The problem is when 90% of products are around $50, but few products are around $3000, then most products will be grouped in the first range, and subsequent ranges only shows few items. This will defeat the purpose of having price ranges.

Ideal situation is, application display price ranges according to products population, like:

but min and max value of each price range is vary by category.

Can this be done in 1 store procedure (SQL Express 2008)? Any ideas?

Thanks in advanced.

Upvotes: 1

Views: 2476

Answers (3)

josephj1989
josephj1989

Reputation: 9709

You can use the ntile windowing function to group the product into the price ranges as required. One approach is to break the products into 20 buckets based on price(each bucket with 5%). Then then buckets 1-2 will be first 10%,3-7 next 25% and so on.

See below a sample SQL based on AVDW database products table

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

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Mixing TOP PERCENT and previously found ranges would yield the five price ranges.

SQL Statement

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

Test data

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)

Upvotes: 1

A G
A G

Reputation: 22597

One solution is to generate price ranges dynamically. This is how you can do it:

Lets say you have 100 products.

  • Sort all the products in ascending order.
  • 10% products in first price range - Pick top 10 products. Price range = 1st - 10th product.
  • Subsequent 25% in 2nd price range - Pick Next 25 products. Price Range 11th - 35th Product.
  • Subsequent 25% in 3nd price range - Pick Next 25 products. Price Range 36th - 60th Product.

And so on.

Upvotes: 0

Related Questions