在三个星期的时间内获得总数

Question

我有一张很大的(比方说)用户表，他们访问各种网页。该表的一个例子可以是：

schema.page_views

      date|  user_id|         webpage|
----------+---------+----------------+
01/05/2012|aaaaaaaaa|www.example1.com|
01/06/2012|aaaaaaaaa|www.example2.com|
01/06/2012|ababababa|www.example1.com|
                     ...
01/05/2013|aaaaaaaaa|www.example2.com|
01/05/2013|ababababa|www.example1.com|
03/05/2013|aaaaaaaaa|www.example2.com|

此外，我还有一个日历查找表：

schema.weeks

week_number| begin_date|   end_date|year|
-----------+-----------+-----------+----+
         18| 2012-04-23| 2012-04-30|2012|
         17| 2012-04-15| 2012-04-22|2012|
         16| 2012-04-08| 2012-04-14|2012|

我最终想从这两张表格中获得三个星期的页面浏览量。更具体地说，如果我今天发出这个查询，我想要的内容如下：

         webpage|    1-3|    4-6|    7-9|  10-12|   
----------------+-------+-------+-------+-------+
www.example1.com| 124875| 175682| 168542| 159780|
www.example2.com|    three week period numbers  |

其中“1-3”指的是最近的三周，“4-6”指的是四到六周前，等等。

到目前为止，我已经获得了每个网站和周的每周总页面浏览量如下：

-- join to get begin and end_dates
with t1 as (
select a.*, b.* from schema.page_views a
join schema.weeks b
on a.date between b.begin_date and b.end_date),

-- now aggregate per week
weekly_vol as (
select webpage, year, week_number, begin_date, end_date,
count(*) volume
from t1
group by 1, 2, 3, 4, 5)

select * from weekly_vol limit 1;

举个例子，

         webpage| year| week_number| begin_date|   end_date| volume|
----------------+-----+------------+-----------+-----------+-------+
www.example1.com| 2012|          18| 2012-04-23| 2012-04-30|  41871|

有了这样一张桌子，我怎样才能到达我想要的桌子呢？我曾经想过用类似于on a.week_number between b.week_number and b.week_number + 2的东西来加入它，但是我不认为这个想法会在过去的12周包括新的一年开始的时候起作用。

有没有人做过类似的事情，如果是，你是如何处理这个问题的？关于采取不同办法的建议也将得到同样的赞赏。

Answer 1

你的问题有点不清楚。周数是在一年之内还是在所有时间内？您想要数据中相对于上周的金额还是相对于当前日期的金额？

在任何情况下，下面的操作至少与您想要的类似。关键是外部查询中的条件聚合：

with t1 as (
      select pv.*, w.*,
             max(pv.week_number) over (partition by webpage, year) as last_week_number
      from schema.page_views pv join
           schema.weeks w
           on pv.date between w.begin_date and w.end_date
     ),
     weekly_vol as (
      select webpage, year, week_number, begin_date, end_date,
             count(*) as volume
      from t1
      group by webpage, year, week_number, begin_date, end_date
    )
select webpage, year,
       sum(case when last_week_number - week_number < 3
                then volume else 0
           end) as week_1_3,
       sum(case when last_week_number - week_number >= 3 and  last_week_number - week_number < 6
               then volume else 0
           end) as week_4_6
from weekly_vol 
group by webpage, year;