SQL top N序列用于时间序列数据

Question

我正试图建立一个sql查询，将给我的前N系列。为了简化，我们可以说top N将意味着第一天的最大计数()(或者整个系列的计数()更好)。

为例:我有X列，但只有两列要进行汇总:"颜色"one_answers"日期"。"颜色"列有多个100个变体，但我只想要前10个。对于这10种"颜色"中的每一种，我想要有所有日期的计数(*)

如果没有排名，我的查询将是这样的:

"select count(*), color, date from table group by color, date"

如果我输入"order by + limit"，它只会选择最上面的段，而不是最上面的级数。

任何想法?

感谢

PS;如果这改变了什么，我将使用hive/impala

EX输入:

color  | date 
red    | 2016-01-01
red    | 2016-01-01
red    | 2016-01-01
red    | 2016-01-01
black  | 2016-01-01
black  | 2016-01-01
black  | 2016-01-01
red    | 2016-01-02
red    | 2016-01-02
black  | 2016-01-02
black  | 2016-01-02
black  | 2016-01-02
blue   | 2016-01-03
blue   | 2016-01-03
blue   | 2016-01-03
blue   | 2016-01-03
black  | 2016-01-03
red    | 2016-01-03

在这种情况下，如果我只选择"top 2 series"，即使蓝色在第三天是最大的，我也只想选择黑色和红色，因为它们是最大的"整体"。蓝色应该完全从结果中过滤掉

所需输出

color  | date       | count
red    | 2016-01-01 |   4
black  | 2016-01-01 |   3
red    | 2016-01-02 |   2
black  | 2016-01-02 |   3
red    | 2016-01-03 |   1
black  | 2016-01-03 |   1

Answer 1

这可以使用cte和window函数来实现。

with temp as (
SELECT color ,date, COUNT(1) OVER (PARTITION BY color ,date ) as cont
from table)
select *  from temp order by cont

注释:用表名更新表。