sqlite - SQL-选择按多个字段分组的前n个，按计数排序

Question

我正在尝试按多个属性分组时查找前n条记录。我相信它与this problem有关，但是我很难适应所描述的解决方案。

为了简化，我有一个带有列的表（did是device_id的缩写）：

id int
did int
dateVal dateTime

我试图找到行数最多的每一天的前n个device_id。

例如（忽略id和dateTime的时间部分），

did dateVal
1   2017-01-01
1   2017-01-01
1   2017-01-01
2   2017-01-01
3   2017-01-01
3   2017-01-01

1   2017-01-02
1   2017-01-02
2   2017-01-02
2   2017-01-02
2   2017-01-02
3   2017-01-02

找到前2个会产生...

1, 2017-01-01
3, 2017-01-01
2, 2017-01-02
1, 2017-01-02

我目前的幼稚做法只为我提供了所有日期中的前2名。

--Using SQLite
select date(dateVal) || did 
from data 
group by date(dateVal), did
order by count(*) desc 
limit 2

我正在使用串联运算符，以便以后可以提取行。

我正在使用SQLite，但任何常规的SQL解释将不胜感激。

Answer 1

与this question相似，定义一个CTE，该CTE计算所需组的所有设备计数，然后在WHERE ... IN子查询中使用它，该查询仅限于该日期的前2个设备：

WITH device_counts AS (
  SELECT did, date(dateval) AS dateval, COUNT(*) AS device_count
  FROM data
  GROUP BY did, date(dateval)
)
SELECT did, date(dateval) FROM device_counts DC_outer
WHERE did IN (
  SELECT did
  FROM device_counts DC_inner
  WHERE DC_inner.dateval = DC_outer.dateval
  GROUP BY did, date(dateval)
  ORDER BY DC_inner.device_count DESC LIMIT 2
)
ORDER BY date(dateval), did