I've got a database with customers which have a listing of products.
Each user has a listing of products. A Product can fit in with many customers (same itemID, different userID).
Now I wish to give weights towards the products dynamically (calculated at runtime, not something constant that may be saved within the database) after which Choose the customers using the greatest weights based on their Products.
Can One provide the weight towards the products within an SQL query, after which perform the calculation ?
I am attempting to avoid looping with the database and calculating the users' weights.
itemId = 1 = 15 itemID = 2 = 10
UserID1 total weight = 25
UserID2 total weight = 10
Produce a temporary table of weights, together with your dynamic values:
CREATE TEMPORARY TABLE weights(item_id integer, weight integer); CREATE TABLE INSERT INTO weights VALUES (1, 10); -- Inserting weights INSERT 0 1 INSERT INTO weights VALUES (2, 1); -- Inserting weights INSERT 0 1 ...
Produce a view to map products to weights:
CREATE TEMPORARY VIEW items_weights AS SELECT il.itemID, il.UserID, w.weight FROM ItemsList il JOIN weights w ON il.itemID=w.item_id;
Choose the greatest-weighted customers:
SELECT UserId, COUNT(weight) FROM items_weights GROUP BY UserId ORDER BY COUNT(weight) DESC;