最近接触到比较多的频率、计数的结果生成问题，比较典型的问题就是统计用户在过去某段时间内产生记录的条数，例如有五个用户A、B、C、D、E，过去五天分别产生了5、3、3、2、1条记录。那么频率计数产生的结果便是A-5，B-3，C-3，D-2，E-1。而在实际情况中，经常需要对用户信息进行统计和聚合计算才能得到上述结果，本文就来详细分析如何在这类统计和聚合计算中运用现有函数达到预期效果。
假设有已知数据源表包含了所有用户记录信息，包含了时间字段，单条记录所应涵盖的内容，以及用户的身份认证信息(如ID)，此阶段可用简单的group by加count聚合函数完成用户计数的统计：(样本代码如下)
insert into/overwrite table middle_table1
select count(*) as user_count
from source_table
group by user_id需要注意的是，这里除了被group的维度user_id，以及聚合计数的项外，其他维度的项如也需筛选入输出表中，则需对此维度进行聚合计数，类似max(),min(),avg(),sum()等，在保证原有信息价值尽量保留的前提下，选择合适的函数聚合，如date_modified，对应行的修改时间，可取最近的时间点作为参考，则可用max(date_modified)。若不实施对输出维度的聚合，则会出现bug导致脚本无法运行。(在最开始使用ODPS SQL或类似SQL时作者经常卡在这一块，在网上也找不到不聚合情况下的筛选维度解决方案，这里特此提出，望大家避免在此浪费时间)
统计计数完成后，对频率进行聚合计数，则到了下一阶段。对频率聚合的前提是，用户信息已经不重要，只要求得到出现次数与相应计数，结合之前提到的ABCDE例子，频率聚合得到的(次数，计数)结果是3-2，5-1，2-1，1-1，后三者其实为并列关系。出现三次的ID有两个，出现五次的ID有一个，以此类推。
在这一块聚合时会涉及到额外的点，如何让结果看起来直白明了，符合需求者统计分析的要求呢？这里我们便可以引入rank函数的概念，对所有聚合计数进行排名，这样结果更清晰明了，而且rank函数的作用也不止于此，之后会再提到。样本代码如下：
select user_count
,rank() over(order by user_count desc) as rank_num
,count(*) as freq_count
from middle_table1这里需要介绍的是，可以用到的rank函数有两种，rank和dense_rank，rank是泛用的排名，12345以此类推，遇到并列时，试做同排名而后排名往后顺延，如之前的例子，3-1-2，5-2-1，2-2-1，1-2-1，第一位为user_count,第二位为排名计数，第三位为频率计数，这里若0次user count被允许，则它会以0-5-0的形式出现。而dense_rank与rank的区别是，并列之后的排名计数，不会往后顺延那么多位，如上述例子，dense_rank与rank的区别会体现在：0-5-0会输出为0-3-0，可酌情或根据自己喜好来选择相应函数。
在进行完频率计数后，多个时间范围的输出表可形成，如过去三十天，过去一周，过去两周，过去一天，过去三天等，也可是其他维度的范围。在这，如果输出成多个表并不比单个表方便或者便于阅览的话，则会用到联结的句法，而这里会涉及到一些结果输出展示的问题。
例如，结果要求我们把过去三十天，过去一周和过去三天的输出结果整合到一张表中，我们可以用简单的join，把三张表联结到一起，联结的维度最初可能为当天的日子，但是，这样直观明了的做法可能会导致一些问题。在联结后，会出现不同表频率计数不一的情况，过去三十天可能有几百行频率计数，而过去三天可能只有十几条，而这样会导致过去三天的数据到十几条之后开始重复，甚至在第一遍重复之前，每一行的记录都被重复，这就是结果显示的问题。
要解决这一问题，则需对各个表内有的统一维度进行联结，而这里，rank_num作为之前生成的结果，可以起到很好的联结效果。
这里，为了避免信息的流失，不建议用join语句，而改用join的其他类型，如left outer join，right outer join，假设开发者对过去30天的数据频率计数一定超过7天，7天一定超过3天有信息，可用left outer join，但是也可能会出现频率计数超过，但恰巧都一样的情况，如ABCDE都出现了10000次，每个用户的频率计数结果都归属于一条10000-1-5，那么此时，最稳妥的选择应是full outer join，不管之前的表还是之后的表行数更多，都可以保留信息输出完整结果。样本示例如下：
select t1.user_count as user_count_30
,t1.rank_num as rank_num_30
,t1.freq_count as freq_count_30
,t2.user_count as user_count_7
,t2.rank_num as rank_num_7
,t2.freq_count as freq_count_7
,t3.user_count as user_count_3
,t3.rank_num as rank_num_3
,t3.freq_count as freq_count_3
from
(select user_count
,rank() over(order by user_count desc) as rank_num
,count(*) as freq_count
from middle_table1
where date_modified between (30_days_ago) and (today)
) t1
full outer join
(select user_count
,rank() over(order by user_count desc) as rank_num
,count(*) as freq_count
from middle_table1
where date_modified between (7_days_ago) and (today)
) t2 on t1.rank_num = t2.rank_num
full outer join
(select user_count
,rank() over(order by user_count desc) as rank_num
,count(*) as freq_count
from middle_table1
where date_modified between (3_days_ago) and (today)
) t3 on t1.rank_num = t3.rank_num
;
