CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008t-sql
roastedpotato
roastedpotato

Reputation: 17

write sql query to calculate product

Here is a table with three columns: user_id, product_id, rating,

This is the table:

user_id    product_id    rating
u0         p0            3
u0         p1            2
u1         p0            1
u1         p1            4
u2         p0            2
u2         p1            3

if two different customers both rated the same two products, then calculate the dot product of their ratings as follows: u0 and u1 both rated product p0 and p1, the result should be: 3 * 1 + 2 * 4 = 11

and the result table:

u0  u1  3*1+2*4=11 
u0  u2  3*2+2*3=12 
u1  u2  1*2+4*3=14

How can I write a query to get it?

Upvotes: 0

Views: 2205

Answers (3)

gotqn
gotqn

Reputation: 43636

T-SQL/SQL Server

DECLARE @DataSource TABLE
(
    [user_id] VARCHAR(2)
   ,[product_id] VARCHAR(2)
   ,[rating] DECIMAL(9,2)
);


INSERT INTO @DataSource ([user_id], [product_id], [rating])
VALUES ('u0', 'p0', 3)
      ,('u0', 'p1', 2)
      ,('u1', 'p0', 1)
      ,('u1', 'p1', 4);

WITH DataSource AS
(
    SELECT [product_id]
          ,EXP(SUM(LOG([rating]))) AS [value]
    FROM @DataSource
    GROUP BY [product_id]
)
SELECT SUM([value])
FROM DataSource;

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

The first two columns of the desired result make no sense - what if there were multiple users?

So, ignoring that bit...

SELECT ROUND(SUM(subtotal)) total 
  FROM
     ( SELECT product_id
     , EXP(SUM(LOG(rating))) subtotal
  FROM my_table
 GROUP 
   BY product_id
  ) n;

Sqlfiddle of same: http://sqlfiddle.com/#!9/3c9dac/3

Oh, this is almost an exact duplicate of the earlier answer. As it was posted first, if you’re minded to accept this, accept that instead.

Upvotes: 1

E. Villiger
E. Villiger

Reputation: 916

MySQL Solution:

SELECT SUM(m.product_rating)
FROM (
    SELECT EXP(SUM(LOG(COALESCE(rating,1))) AS product_rating
    FROM table
    GROUP BY product_id
    /*this query multiplies the ratings by product*/
) AS m
GROUP BY NULL

Edit:

If you want only certain products, add

WHERE product_id IN('p0', 'p1', ...)

in the inner query. If you want only certain users, add

WHERE user_id IN('u0', 'u1', ...)

in the outer query.

Upvotes: 0

Related Questions