CODEWITHSUNDEEP
mysqlmariadb
Paolo_Mulder
Paolo_Mulder

Reputation: 1289

Sum column , on a distinct count when a certain value is met

CREATE TABLE `vote_days` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `t_id` int(11) NOT NULL COMMENT 'title id',
 `r_id` int(11) NOT NULL COMMENT 'release id',
 `l_id` int(11) NOT NULL COMMENT 'language id',
 `e_id` int(11) DEFAULT NULL COMMENT 'episode_id',
 `unix_day` int(11) NOT NULL,
 `votes` mediumint(8) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
)

16000~ rows example , year spread. https://www.db-fiddle.com/f/amvnHkVnFS3YBFYUJNRcbx/0

This table is the amount of (user) votes for each day (in unix timestamp format).

We got Movie and Tv titles. (t_id).

TV titles have an e_id ( episode id) ,on movies the e_id is NULL.

It got different formats ( releases) and different languages (l_id).

Question : I want to get the sum of all votes for each movie titles , but I want to split the votes for TV shows by the amount of episodes. In certain time-frames ordered by the total amount of votes ( Most popular).

Is there a more efficient (performance wise) way to do this ?

SELECT
t1.*,
(SUM(t1.votes) / (CASE WHEN t1.e_id IS NULL THEN 1 ELSE COUNT(DISTINCT(e_id)) END) ) as total_votes

FROM `vote_days` t1
  GROUP BY t_id
ORDER BY total_votes  DESC

Upvotes: 1

Views: 35

Answers (1)

The Impaler
The Impaler

Reputation: 48875

In general, I think the query is good for MySQL 5.7. I don't see a way of improving it unless you jump into MySQL 8.x and you want to use CTEs.

The big change I would add is to create an index that could theoretically improve performance. Only the execution plan will tell, however:

create index ix1 on `vote_days` (t_id, e_id, votes);

I would improve the syntax a little bit (minor chnges to clear out some confusions) and I would rephrease it as:

SELECT
  *,
  (  
    SUM(votes) / 
    CASE WHEN max(e_id) IS NULL THEN 1 ELSE COUNT(DISTINCT e_id) END
  ) as total_votes
FROM `vote_days`
GROUP BY t_id
ORDER BY total_votes DESC

Upvotes: 1

Related Questions