以下是我的数据:
date | word | count
01/01/2020 #abc 1
01/01/2020 #xyz 2
02/05/2020 #ghi 2
02/05/2020 #def 1
02/04/2020 #pqr 4
02/04/2020 #cde 3
01/01/2020 #lmn 1
这是我想要的结果:
date | word | count
01/01/2020 #xyz 2
02/04/2020 #pqr 4
02/05/2020 #ghi 2
所以基本上,我想要在每个特定日期都有最大计数的单词。有人能帮我解决这个问题吗?
将row_number
窗口函数与partition by and order by
子句一起使用,并仅从分区中选择最大计数!
SELECT date,word,count
FROM (
SELECT date,word,count,row_number() over (partition by date order by count desc) as rn
from <table_name>) sq
WHERE sq.rn = 1;