상위 9개로 정렬된 카운트(count_id)를 기준으로 상위 9개 및 기타를 가져온 다음 다른 카운트를 'club' 범주로 묶는 방법

상위 9개로 정렬된 카운트(count_id)를 기준으로 상위 9개 및 기타를 가져온 다음 다른 카운트를 'club' 범주로 묶는 방법

다음 쿼리는 모든 것을 실행합니다.

SELECT count(entity_id), cim.institute_id, cfi.name_ FROM ci_certification_students cis INNER JOIN ci_certification_master cim ON cis.certificate_id = cim.certificate_id
INNER JOIN ci_finalized_institute cfi ON cfi.institute_id=cim.institute_id
WHERE cis.status_id=4 AND cim.institute_id is not null group by cim.institute_id order by count(entity_id) desc limit 100;

다음을 시도했습니다.

SELECT seqnum,institute_id, (CASE WHEN seqnum > 9 THEN 'others' ELSE name_ END) as name_, sum(total_certi) FROM 
(select count(cis.entity_id) as total_certi, cim.institute_id, cfi.name_ as certiName, ROW_NUMBER() OVER (ORDER BY COUNT(cis.entity_id) desc) as seqnum
FROM certificationdb.ci_certification_students cis 
INNER JOIN ci_certification_master cim ON cis.certificate_id = cim.certificate_id
INNER JOIN certificationdb.ci_finalized_institute cfi ON cim.institute_id=cfi.institute_id
WHERE cis.status_id=4 AND cim.institute_id is not null group by cim.institute_id ORDER BY 
count(cis.entity_id)desc)c GROUP BY (CASE WHEN seqnum> 9 THEN 'others' ELSE name_ END)
ORDER BY seqnum;

어떤 도움이라도 주시면 감사하겠습니다...

사용하다row_number()그리고 두 가지 수준의 집계:

SELECT (CASE WHEN seqnum < 10 THEN institute_id END) as institute_id,
       (CASE WHEN seqnum < 10 THEN name_ ELSE 'OTHERS' END) as name,
       SUM(cnt) as cnt
FROM (SELECT cim.institute_id, cfi.name_, COUNT(*) as cnt,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM ci_certification_students cis INNER JOIN 
           ci_certification_master cim
           ON cis.certificate_id = cim.certificate_id INNER JOIN
           ci_finalized_institute cfi ON 
           cfi.institute_id = cim.institute_id
      WHERE cis.status_id = 4 AND cim.institute_id is not null
      GROUP BY cim.institute_id, cfi.name_
     ) c
GROUP BY institute_id, name
ORDER BY MIN(seqnum);

언급URL : https://stackoverflow.com/questions/60043750/how-to-fetch-top-9-and-others-based-on-countentity-id-sorted-highest-top-9-and