MySQL SELECT t.* FROM row_table t INNER JOIN ( SELECT class, GROUP_CONCAT(student ORDER BY score DESC) grouped_student FROM row_table GROUP BY class) group_max ON t.class = group_max.class AND FIND_IN_SET(student, grouped_student) BETWEEN 1 AND 2;
MySQL SELECT t.* FROM row_table t INNER JOIN ( SELECT class, GROUP_CONCAT(student ORDER BY score DESC) grouped_student FROM row_table GROUP BY class) group_max ON t.class = group_max.class AND FIND_IN_SET(student, grouped_student) BETWEEN 1 AND 2; GROUP_CONCATͰ Ϋϥε͝ͱʹιʔτͯ͠
MySQL SELECT t.* FROM row_table t INNER JOIN ( SELECT class, GROUP_CONCAT(student ORDER BY score DESC) grouped_student FROM row_table GROUP BY class) group_max ON t.class = group_max.class AND FIND_IN_SET(student, grouped_student) BETWEEN 1 AND 2; FIND_IN_SETͰ ઌ಄͚ͩऔΓग़͢ GROUP_CONCATͰ Ϋϥε͝ͱʹιʔτͯ͠
Presto Hive PostgreSQL SELECT * FROM ( SELECT *, rank() over (partition by class order by score desc) as rank FROM row_table ) t WHERE rank <= 2 rank()ͰΫϥεͰͷ ॱҐΛ͚ͭͯ
Presto Hive PostgreSQL SELECT * FROM ( SELECT *, rank() over (partition by class order by score desc) as rank FROM row_table ) t WHERE rank <= 2 rank()ͰΫϥεͰͷ ॱҐΛ͚ͭͯ 2ҐҎ্ͷਓ͚ͩൈ͖ग़͢
SELECT each_top_k( 2, class, score, student, class ) as (rank, score, student, class) FROM (SELECT * FROM raw_table DISTRIBUTE BY class SORT BY class) t Hivemall
SELECT each_top_k( 2, class, score, student, class ) as (rank, score, student, class) FROM (SELECT * FROM raw_table DISTRIBUTE BY class SORT BY class) t Hivemall Ϋϥε͝ͱʹϊʔυΛ ࢄ͠ιʔτ
SELECT each_top_k( 2, class, score, student, class ) as (rank, score, student, class) FROM (SELECT * FROM raw_table DISTRIBUTE BY class SORT BY class) t Hivemall Ϋϥε͝ͱʹϊʔυΛ ࢄ͠ιʔτ each_top_kͰ্ҐͷΈநग़
kaky0922
sajikix
aki_iinuma
is_ryo
smt7174
kei_s
knsg16
masasuzu
pospome
cybozuinsideout
murachiakira
kazuhitotakahashi
coconala_engineer
ufuk
jennybc
lynnandtonic
3n
dotmariusz
jrom
lemiorhan
lara
myddelton
palkan
keathley
stephaniewalter