每周总记录数 [英] Total Number of Records per Week

问题描述

我有一个Postgres 9.1数据库.我正在尝试生成每周(在给定日期范围内)的记录数，并将其与上一年进行比较.

我有以下代码用于生成系列:

 选择generate_series('2013-01-01'，'2013-01-31'，'7 day':: interval)作为系列 

但是，我不确定如何将计数的记录加入生成的日期.

因此，以以下记录为例:

  Pt_ID考试日期====== =========1 2012年1月2日2 2012年1月2日3 2012年1月8日4 2012年1月8日1 2013-01-022 2013-01-023 2013-01-034 2013年1月4日1 2013-01-082 2013年1月10日3 2013年1月15日4 2013年1月24日 

我想让记录返回为:

 系列thisyr lastyr=====================2013-01-01 4 22013-01-08 3 22013-01-15 1 02013-01-22 1 02013-01-29 0 0 

不确定在子搜索中如何引用日期范围.感谢您的协助.

解决方案

使用交叉连接应该可以，我将在下面粘贴SQL Fiddle的markdown输出.在2013-01-08系列中，您的示例输出似乎不正确:thisyr应该为2，而不是3.尽管这可能不是最好的方法，但我对Postgresql的知识尚有待改进.

SQL提琴

PostgreSQL 9.2.4模式设置:

  CREATE TABLE表1("Pt_ID" varchar(6)，"exam_date"日期)；插入表1("Pt_ID"，考试日期")价值('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'); 

查询1 :

 选择系列，总和(案件考试日期系列与系列之间+'6天'::间隔然后1否则0结尾)作为thisyr，总和(案件当audit_date +'1 year':: interval系列与系列之间+'6天'::间隔然后1其他0结尾)作为lastyr来自表1作为系列交叉连接generate_series('2013-01-01'，'2013-01-31'，'7 day':: interval)作为系列按系列分组按系列订购 

结果 :

  |系列|蒂尔|LASTYR || -------------------------------- | -------- || --------||2013年1月1日00:00:00 + 0000 |4 |2 ||2013年1月8日00:00:00 + 0000 |2 |2 ||2013年1月15日00:00:00 + 0000 |1 |0 ||2013年1月22日00:00:00 + 0000 |1 |0 ||2013年1月29日00:00:00 + 0000 |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.

解决方案

Using across joinshould work, I'm just going to paste the markdown output from SQL Fiddle below. It would seem that your sample output is incorrect for series 2013-01-08: the thisyr should be 2, not 3. This might not be the best way to do this though, my Postgresql knowledge leaves a lot to be desired.

SQL Fiddle

PostgreSQL 9.2.4 Schema Setup:

CREATE TABLE Table1
    ("Pt_ID" varchar(6), "exam_date" date);

INSERT INTO Table1
    ("Pt_ID", "exam_date")
VALUES
    ('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');

Query 1:

select 
  series, 
  sum (
    case 
      when exam_date 
        between series and series + '6 day'::interval
      then 1 
      else 0 
    end
  ) as thisyr,
  sum (
    case 
      when exam_date + '1 year'::interval 
        between series and series + '6 day'::interval
      then 1 else 0 
    end
  ) as lastyr

from table1
cross join generate_series('2013-01-01', '2013-01-31', '7 day'::interval) as series
group by series
order by series

Results:

|                         SERIES | THISYR | LASTYR |
|--------------------------------|--------|--------|
| January, 01 2013 00:00:00+0000 |      4 |      2 |
| January, 08 2013 00:00:00+0000 |      2 |      2 |
| January, 15 2013 00:00:00+0000 |      1 |      0 |
| January, 22 2013 00:00:00+0000 |      1 |      0 |
| January, 29 2013 00:00:00+0000 |      0 |      0 |

这篇关于每周总记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！