SQL Server: Group similar sales together

Question

I'm trying to do some reporting in SQL Server. Here's the basic table setup:

Order (ID, DateCreated, Status)

Product(ID, Name, Price)

Order_Product_Mapping(OrderID, ProductID, Quantity, Price, DateOrdered)

Here I want to create a report to group product with similar amount of sales over a time period like this:

Sales over 1 month:

1. Coca, Pepsi, Tiger: $20000 average(coca:$21000, pepsi: $19000, tiger: $20000)
2. Bread, Meat: $10000 avg (bread:$11000, meat: $9000)

Note that the text in () is just to clarify, not need in the report). User define the varying between sales that can consider similar. Example sales with varying lower than 5% are consider similar and should be group together. The time period is also user defined.

I can calculate total sale over a period but has no ideas on how to group them together by sales varying. I'm using SQL Server 2012. Any help is appreciated.

Sorry, my English is not very good :)

UPDATE: *I figured out about what I atually need ;)*

For an known array of numbers like: 1,2,3,50,52,100,102,105

I need to group them into groups which have at least 3 number and the difference between any two items in group is smaller than 10.

For the above array, output should be:

[1,2,3]

[100,102,105]

=> the algorithm take 3 params: the array, minimum items to form a group and maximum difference between 2 items.

How can I implement this in C#?

Answer 1

I can't believe I did it~~~

-- this threshold is the key in this query
-- it means that 
-- if the difference between two values are less than the threshold
-- these two values are belong to one group
-- in your case, I think it is 200
DECLARE @th int
SET @th = 200

-- very simple, calculate total price for a time range
;WITH totals AS ( 
  SELECT p.name AS col, sum(o.price * op.quantity) AS val
  FROM order_product_mapping op
  JOIN [order] o ON o.id = op.orderid
  JOIN product p ON p.id = op.productid
  WHERE dateordered > '2013-03-01' AND dateordered < '2013-04-01'
  GROUP BY p.name
),
-- give a row number for each row
cte_rn AS ( -- 
  SELECT col, val, row_number()over(ORDER BY val DESC) rn
  FROM totals
),
-- show starts now,
-- firstly, we make each row knows the row before it 
cte_last_rn AS (
  SELECT col, val, CASE WHEN rn = 1 THEN 1 ELSE rn - 1 END lrn
  FROM cte_rn
),
-- then we join current to the row before it, and calculate 
-- the difference between the total price of current row and that of previous row
-- if the the difference is more than the threshold we make it '1', otherwise '0'
cte_range AS (
  SELECT
    c1.col, c1.val,
    CASE
      WHEN c2.val - c1.val <= @th THEN 0
      ELSE 1
    END AS range,
    rn
  FROM cte_last_rn c1
  JOIN cte_rn c2 ON lrn = rn
),
-- even tricker here,
-- now, we join last cte to itself, and for each row
-- sum all the values (0, 1 that calculated previously) of rows before current row
cte_rank AS (
  SELECT c1.col, c1.val, sum(c2.range) rank
  FROM cte_range c1
  JOIN cte_range c2 ON c1.rn >= c2.rn
  GROUP BY c1.col, c1.val
)
-- now we have properly grouped theres total prices, and we can group on it's rank 
SELECT 
  avg(c1.val) AVG,
  (
    SELECT c2.col + ', ' AS 'data()'
    FROM cte_rank c2
    WHERE c2.rank = c1.rank
    ORDER BY c2.val desc
    FOR xml path('')
  ) product,
  (
    SELECT cast(c2.val AS nvarchar(MAX)) + ', ' AS 'data()'
    FROM cte_rank c2
    WHERE c2.rank = c1.rank
    ORDER BY c2.desc
    FOR xml path('')
  ) price
FROM cte_rank c1
GROUP BY c1.rank
HAVING count(1) > 2

The result will look like:

AVG     PRODUCT     PRICE
28      A, B, C     30, 29, 27
12      D, E, F     15, 12, 10
3       G, H, I     4, 3, 2

for understanding how I did concatenate, please read this: Concatenate many rows into a single text string?

Answer 2

By the way, if you just want c#:

var maxDifference = 10;
var minItems = 3;     

// I just assume your list is not ordered, so order it first
var array = (new List<int> {3, 2, 50, 1, 51, 100, 105, 102}).OrderBy(a => a);

var result = new List<List<int>>();
var group = new List<int>();
var lastNum = array.First();
var totalDiff = 0;
foreach (var n in array)
{
    totalDiff += n - lastNum;

    // if distance of current number and first number in current group
    // is less than the threshold, add into current group
    if (totalDiff <= maxDifference)
    {
        group.Add(n); 
        lastNum = n;
        continue;
    }

    // if current group has 3 items or more, add to final result
    if (group.Count >= minItems)
        result.Add(group);

    // start new group
    group = new List<int>() { n };
    lastNum = n;
    totalDiff = 0;   
}

// forgot the last group...
if (group.Count >= minItems)
    Result.Add(group);

the key here is, the array need to be ordered, so that you do not need to jump around or store values to calculate distances

Answer 3

This query should produce what you expect, it displays products sales for every months for which you have orders :

SELECT CONVERT(CHAR(4), OP.DateOrdered, 100) + CONVERT(CHAR(4), OP.DateOrdered, 120) As Month , 
Product.Name , 
AVG( OP.Quantity * OP.Price ) As Turnover
FROM Order_Product_Mapping OP
INNER JOIN Product ON Product.ID = OP.ProductID
GROUP BY  CONVERT(CHAR(4), OP.DateOrdered, 100) + CONVERT(CHAR(4), OP.DateOrdered, 120) ,
          Product.Name

Not tested, but if you provide sample data I could work on it

Answer 4

Look like I made things more complicate than it should be. Here is what should solve the problem:

-Run a query to get sales for each product.

-Run K-mean or some similar algorithms.