PostgreSQL 按周统计数据

一个需求，要求统计最近6个周的数据，并且按周分组。

显示结果如图所示

echarts.png

本来打算用java先获取这几个周再处理，发现有的年份java和PostgreSQL获取的周不一样，估计是某年的第一个周跨年，有的把该周作为第一周，有的作为去年的最后一周导致的，所以只好用数据库去处理。

本来想弄个表存日期，如：周、年等各种日期，然后用这个表与业务表关联，但需要提前插入数据，万一时间长了忘了就完蛋了，当然可以用定时任务等方法处理。

后来用了PostgreSQL的一个函数generate_series，先获取周，然后用周与业务数据关联，这样就可以取到每周的数据，不会遗漏无数据的周，然后按周排个序即可。

-- 获取第一个日期的周数，然后递增1，直到与最后一个日期的周数相同
select
    generate_series(
        extract (week from timestamp '2016-07-18')::integer,  -- 起始周
        extract (week from timestamp '2016-09-04')::integer,  -- 结束周
        1) as week -- 递增step

本来这样能满足也许需求，突然想跨年的情况，然后把日期调整为2015-12-24和2016-01-08，结果没记录了，因为15年的周很大，而16年的周很小，取序列的函数直接罢工...

然后修改为利用时间数据递增以解决该问题（依然使用周与业务数据关联，year只是用来排序的，如果时间范围超过1年，还是需要关联year的）：

-- 使用日期格式按周递增，然后分别获取其年和周，用于后续的排序
select 
    extract (year from date_series.date) as year,
    extract (week from date_series.date) as week
from (
      select generate_series(
          timestamp '2015-12-24', 
          timestamp '2016-01-08',  '1 weeks') as date
) date_series

---

generate_series的时间递增包含：（前面的数字可以修改）

• 1 seconds
• 1 minutes
• 1 hours
• 1 days
• 1 weeks
• 1 months
• 1 years

generate_series还有不少用法，后续有需求时再研究下。