每周记录总数(Total Number of Records per Week)

 我有一个Postgres 9.1数据库。 我试图生成每周的记录数（对于给定的日期范围）并将其与上一年进行比较。 
 
 我有以下代码用于生成系列： 
 
select generate_series('2013-01-01', '2013-01-31', '7 day'::interval) as series
 
 但是，我不确定如何将计算的记录加入到生成的日期。 
 
 因此，使用以下记录作为示例： 
 
Pt_ID      exam_date
======     =========
1          2012-01-02
2          2012-01-02
3          2012-01-08
4          2012-01-08
1          2013-01-02
2          2013-01-02
3          2013-01-03
4          2013-01-04
1          2013-01-08
2          2013-01-10
3          2013-01-15
4          2013-01-24
 
 我希望将记录返回为： 
 
  series        thisyr      lastyr
===========     =====       =====
2013-01-01        4           2
2013-01-08        3           2
2013-01-15        1           0
2013-01-22        1           0
2013-01-29        0           0
 
 不确定如何在子搜索中引用日期范围。 谢谢你的帮助。 

I have a Postgres 9.1 database. I am trying to generate the number of records per week (for a given date range) and compare it to the previous year.
 
I have the following code used to generate the series:
 
select generate_series('2013-01-01', '2013-01-31', '7 day'::interval) as series
 
However, I am not sure how to join the counted records to the dates generated.
 
So, using the following records as an example:
 
Pt_ID      exam_date
======     =========
1          2012-01-02
2          2012-01-02
3          2012-01-08
4          2012-01-08
1          2013-01-02
2          2013-01-02
3          2013-01-03
4          2013-01-04
1          2013-01-08
2          2013-01-10
3          2013-01-15
4          2013-01-24
 
I wanted to have the records return as:
 
  series        thisyr      lastyr
===========     =====       =====
2013-01-01        4           2
2013-01-08        3           2
2013-01-15        1           0
2013-01-22        1           0
2013-01-29        0           0
 
Not sure how to reference the date range in the subsearch. Thanks for any assistance.

原文：https://stackoverflow.com/questions/26834062