时间戳

首页 > 解决方案 > Sum 列，在满足某个值时进行不同的计数

mysql - Sum 列，在满足某个值时进行不同的计数

问题描述

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~ 行示例，年份分布。 https://www.db-fiddle.com/f/amvnHkVnFS3YBFYUJNRcbx/0

此表是（用户）每天的投票数量（以 unix 时间戳格式）。

我们有电影和电视节目。(t_id)。

电视标题有一个 e_id (episode id)，在电影上 e_id 为 NULL。

它有不同的格式（版本）和不同的语言（l_id）。

问题： 我想获得每部电影标题的所有票数的总和，但我想按剧集的数量划分电视节目的票数。在按总票数排序的特定时间范围内（最受欢迎）。

有没有更有效（性能方面）的方法来做到这一点？

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

标签： mysqlmariadb

解决方案

一般来说，我认为该查询适用于 MySQL 5.7。除非您跳入 MySQL 8.x 并且想要使用 CTE，否则我看不到改进它的方法。

我要添加的最大变化是创建一个理论上可以提高性能的索引。然而，只有执行计划会告诉我们：

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

我会稍微改进一下语法（小改动以消除一些混淆），我会重新表述为：

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

推荐阅读