sql - Sqlite 中每个类别的前 n 个计数

Question

我有一张看起来像的 table :

user    books
a       aa
a       ab
a       ab
a       ac
a       ac
a       ac
b       aa
b       aa 
b       aa
b       ac
c       aa
c       aa
c       ab
c       ab
c       ab

我想要一个聚合字段，其中包含每个用户的唯一书籍计数 - 我想按降序显示其中的前 2 个，这意味着:

user    book    count  
a       ac      3
a       ab      2 
b       aa      3
b       ac      1
c       ab      3  
c       aa      2

我正在使用sqlite。

在 postgres 中，我会让你过度分区，但我认为 sqllite 中没有等价物。
有什么建议吗？

Answer 1

这对 SQLite 来说是一个真正的痛苦，因为它既没有变量也没有窗口函数。一种方法是相关子查询:

with ub as (
      select user, book, count(*) as cnt
      from t
      group by user, book
     )
select ub.*
from ub
where ub.book in (select ub2.book
                  from ub ub2
                  where ub2.user = ub.user
                  order by cnt desc
                  limit 2
                 );

注意:如果有平局，则任意选择其中两个。