특정 값이 충족될 때 고유 카운트에 대한 합계 열

특정 값이 충족될 때 고유 카운트에 대한 합계 열

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

이 표는 (유닉스 타임스탬프 형식으로) 각 요일에 대한 (사용자) 투표의 양입니다.

우리는 영화와 TV 타이틀을 얻었습니다.(t_id).

TV 제목에는 e_id(에피소드 ID)가 있으며, 영화에서는 e_id가 NULL입니다.

다른 형식(릴리즈)과 다른 언어(l_id)를 가지고 있습니다.

질문 : 각 영화 제목의 총 투표수를 합산하고 싶은데 TV 프로그램의 투표수를 에피소드의 양으로 나누고 싶습니다.특정 시간대에서 총 투표량(가장 많이 사용됨)으로 정렬됩니다.

이를 위한 보다 효율적인 (성능 면에서) 방법이 있습니까?

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

일반적으로 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

언급URL : https://stackoverflow.com/questions/55248743/sum-column-on-a-distinct-count-when-a-certain-value-is-met