Dap Dap - 1 year ago 44
SQL Question

Select several max types for each datatype per distinct value in mysql

userid data_type, timespentaday
1 League of Legends 500
1 Hearthstone 1500
1 Hearthstone 1400
2 World of Warcraft 1200
1 Dota 2 100
2 Final Fantasy 500
1 Dota 2 700

Given this data. I would like to query the most time each user has spent on every.

Output desired:

User League Of Legends Hearthstone World of Warcraft Dota 2
1 500 1500 0 700
2 0 0 1200 0

Something along the lines of this is something I've tried

SELECT t1.* FROM user_info GROUP BY userid JOIN(
(SELECT max(timespentaday) where data_type='League of Legends'),
(SELECT max(timespentaday) where data_type='Hearhstone'),
(SELECT max(timespentaday) where data_type='Dota 2)'
FROM socialcount AS t2
) as t2
ON t1.userid = t2.userid

Answer Source

basically to do this you need the greatest n per group.. there is a good article on it but the gist is in mysql you have to use variables to even get close to this.. especially with doing a pivot on the table (a fake pivot since MySQL doesn't have native support for that).

SELECT userid,
    MAX(CASE WHEN data_type = "League of Legends" THEN timespentaday ELSE 0 END) as "League of Legends",
    MAX(CASE WHEN data_type = "Hearthstone" THEN timespentaday ELSE 0 END) as "Hearthstone",
    MAX(CASE WHEN data_type = "Dota 2" THEN timespentaday ELSE 0 END) as "Dota 2",
    MAX(CASE WHEN data_type = "World of Warcraft" THEN timespentaday ELSE 0 END) as "World of Warcraft",
    MAX(CASE WHEN data_type = "Final Fantasy" THEN timespentaday ELSE 0 END) as "Final Fantasy"
(   SELECT *, @A := if(@B = userid, if(@C = data_type, @A + 1, 1), 1) as count_to_use, @B := userid, @C := data_type
    (   SELECT userid, timespentaday, data_type
        FROM gamers
        CROSS JOIN(SELECT @A := 0, @B := 0, @C := '') temp
        ORDER BY userid ASC, data_type ASC, timespentaday DESC
    ) t
    HAVING count_to_use = 1
GROUP BY userid



MySQL DOCS is quite clear on warnings about using user defined variables:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.