SQL Server: Groupez les ventes similaires set

J'essaie de faire des rapports dans SQL Server. Voici la configuration de base de la table:

Commande (ID, DateCréée, Statut)

Produit (identifiant, nom, prix)

Order_Product_Mapping (OrderID, ProductID, Quantité, Prix, DateOrdered)

Ici, je veux créer un rapport pour regrouper le produit avec un volume de ventes similaire sur une période comme celle-ci:

Ventes sur 1 mois:

  1. Coca, Pepsi, Tiger: 20000 $ en moyenne (coca: 21000 $, pepsi: 19000 $, tigre: 20000 $)
  2. Pain, viande: 10000 $ avg (pain: 11000 $, viande: 9000 $)

Notez que le text de () est juste pour clarifier, pas besoin dans le rapport). L'user définit la variation entre les ventes qui peuvent être similaires. Des exemples de ventes avec des taux inférieurs à 5% sont considérés comme similaires et devraient être groupés. La période est également définie par l'user.

Je peux calculer la vente totale sur une période, mais je n'ai aucune idée sur la façon de les regrouper en fonction des ventes. J'utilise SQL Server 2012. Toute aide est appréciée.

Désolé mon anglais n'est pas très bon 🙂

MISE À JOUR: * J'ai compris ce dont j'avais besoin;) *

Pour un tableau connu de nombres comme: 1,2,3,50,52,100,102,105

J'ai besoin de les regrouper en groupes qui ont au less 3 numéros et la différence entre deux éléments du groupe est inférieure à 10.

Pour le tableau ci-dessus, la sortie devrait être:

[1,2,3]

[100,102,105]

=> l'algorithm prend 3 params: le tableau, les items minimum pour former un groupe et la différence maximum entre 2 items.

Comment puis-je implémenter ceci en C #?

Au fait, si vous voulez juste 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); 

la key ici est, le tableau doit être commandé, de sorte que vous n'avez pas besoin de sauter ou de stocker des valeurs pour calculer les distances

Je ne peux pas croire que je l'ai fait ~~~

 -- 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 sortingcker 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 

Le résultat ressemblera à:

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

Pour comprendre comment j'ai concaténé, veuillez lire ceci: Concaténer plusieurs lignes en une seule string de text?

Cette requête devrait produire ce que vous attendez, elle affiche les ventes de produits pour chaque mois pour lequel vous avez des commands:

 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 

Non testé, mais si vous fournissez des exemples de données, je pourrais y travailler

On dirait que j'ai rendu les choses plus compliquées qu'elles ne devraient l'être. Voici ce qui devrait résoudre le problème:

-Lancer une requête pour get des ventes pour chaque produit.

-Run K-mean ou certains algorithms similaires.