日付範囲で集計しデータ件数を取得・集計する

日付範囲にあるデータ件数を取得・集計する

モデル

• 集計要素

• start_date/end_date：開始日、終了日をもつ

• 分類するための属性

• item_type：タイプ
• genre_cd:種別

最終的な目標 表のイメージ

日付を縦軸に、その日付が開始日・終了日に含まれているレコード件数
締め切りとその件数のようなイメージ

締め切り| 件数
-------+-------
7/1 |10件
7/2 |4件
7/3 |1件
7/4 |0件

--　１）テーブル作成

create table item(
    item_no serial primary key,
    item_type integer not null,
    genre_cd integer not null,
    start_date date,
    end_date date,
    start_time time
);

サンプル投入

insert into item (item_type,genre_cd,start_date,end_date) values
(1,100,'2019-07-01','2019-07-03'),
(1,100,'2019-07-01','2019-07-02'),
(1,100,'2019-07-02','2019-07-02'),
(1,100,'2019-07-03','2019-07-03'),
(1,100,'2019-07-05','2019-07-05'),
(1,102,'2019-07-11','2019-07-12'),
(1,102,'2019-07-12','2019-07-12'),
(1,102,'2019-07-14','2019-07-14'),
(1,102,'2019-07-14','2019-07-15'),
(1,102,'2019-07-15','2019-07-16')
;

縦軸になる日付

• 範囲＝最小値/最大値から求める。
• GENERATE_SERIESが便利

SELECT GENERATE_SERIES(
      (select cast(min(start_date) as date) from item as X) 
    , (select cast(max(end_date) as date) from item as Y)
    ,'1 day'
);

小計・集計 表示

select
    CZ.axis
    ,count(CZ.item_no)

from
    (
    SELECT
        TO_DATE(TO_CHAR(X.axis, 'YYYY/MM/DD'),'YYYY/MM/DD') as axis
        ,CX.item_no
        ,CX.item_type
        ,CX.genre_cd
        ,CX.start_date
        ,CX.end_date
    from
        ( SELECT GENERATE_SERIES(
              (select cast(min(start_date) as date) from item  as X) 
            , (select cast(max(end_date) as date) from item  as Y)
            ,'1 day'
        )as axis
    ) as X
    LEFT JOIN
        item as CX
    ON
        (CX.start_date<= X.axis and CX.end_date>=X.axis)
    where
        item_no <> 0
    order by
        X.axis
    ) as CZ
group by
    CZ.axis
order by
    CZ.axis
;

結果

axis    | count

------------+-------
2019-07-01 | 2
2019-07-02 | 3
2019-07-03 | 2
2019-07-05 | 1
2019-07-11 | 1
2019-07-12 | 2
2019-07-14 | 2
2019-07-15 | 2
2019-07-16 | 1
(9 行)