postgresql sql：window函数应用

最近在进行数据中心的统计访问分析，发现postgresql中还有一类window函数，可以进行分类汇总分析。
在postgresql官方网站中，是这样解释的：

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

简言之，聚合函数返回的各个分组的结果（如group后的sum、average、count等），窗口函数则为每一行返回结果。

目前用到的两个功能：

1. 累计访问量统计
即统计每一天的累计访问量，如昨天访问量10人、今天访问量20人，则从昨天开始统计到今天的累计访问量是30。

select visit_date,sum(visit_count) over (order by visit_date) from mdvisit;

2. 统计每个数据的访问排序
即将所有数据每天的访问量进行统计后进行排序，如A数据访问量第一，则其rank为1。

select uuid,visit_date,rank() over (partition by uuid order by visit_count desc) from mdvisit;