開始日のあるidを現在まで日別に集計したい。

Question

前提・実現したいこと

開始日と終了日がそれぞれ記録されている端末の紐付けられているid数を日別で集計をしたいのですが、
下記クエリは動作するものの、実際の数と合わず困っています。

例えば、
10/17に開始,10/19に開始,10/22の3端末があるとしたら、

10/17 1
10/18 1
10/19 2
10/20 2
10/21 2
10/22 3

のように結果が出される状態の物を想定しています。
クエリの間違いを指摘いただければ幸いです。よろしくお願いします。

テーブル(device_histories)構成

id:履歴id
started_at:紐付け開始日(2020年10月19日以降に紐付け開始されたものが今回取りたいです）
ended_at:終了日(中身は全て空白です。つまり今日の日付まで取り外しがなく紐付けられている状態になります)
device_type:デバイスの種類（今回はタイプ2で限定したいです）


サンプルデータ
id               started_at          ended_at   device_type
679000           2020-10-15 12:13               2
342000           2020-10-15 21:35               2
275000           2020-10-19 10:10               2
689000           2020-10-30 22:50               2
990000           2020-11-01 08:50               2

該当のソースコード

select
    CZ.axis
    ,count(CZ.id)
from
    (
    SELECT
        TO_DATE(TO_CHAR(X.axis, 'YYYY-MM-DD'),'YYYY-MM-DD') as axis
        ,CX.id
        ,CX.started_at
        ,CX.ended_at
    from
        ( SELECT GENERATE_SERIES(
              (select cast(min(started_at+ interval '9 hour') as date) from device_histories  as X) 
            , (select cast(max(now()) as date) from device_histories  as Y)
            ,'1 day'
        )as axis
    ) as X
    LEFT JOIN
        device_histories  as CX
    ON
        (CX.started_at<= X.axis and now()>=X.axis)
    where
        id <> 0
    AND    
        device_type = 2
    and
        (started_at+ interval '9 hour') >= '2020-10-19'
    order by
        X.axis
    ) as CZ
group by
    CZ.axis
order by
    CZ.axis
;

Comments

[m.ts10806]

テーブル定義をご提示ください。
¥d テーブル名

[sou10]

こちらになります
>テーブル(device_histories)構成

[m.ts10806]

型やPK、Nullまであるのが定義です。
¥d テーブル名
の実行結果をご提示ください

[sazi]

SQLではdevice_histories以外のテーブルも登場していますし、それぞれのサンプルデータもない状態では、希望の結果だけ出されても、回答できません。

[sou10]

サンプルデータ追加とクエリの誤り（参照するテーブルは1つ）を修正いたしました。

[sazi]

> 下記クエリは動作するものの
エラーになります。

[sazi]

それから、interval '9 hour'についてですが、１日の終わりは15:00までで、15:00以降は翌日という事でいいですか？

[m.ts10806]

¥d テーブル名
の実行結果をご提示ください

Answer 1

クエリの間違いを指摘いただければ幸いです。

到底動作しないので、何かの間違いだと思いますが検証は出来ません。
ただ、日付に直したものと結合していないのが原因な気がします。

取り敢えず組み替えてみました。
抽出の範囲の条件は、generate_seriesで範囲を作成する際に与えています。

select x.axis, count(cx.id)
from (
        select start_date + generate_series(0, '2020-11-02'::date - start_date) as axis
        from (
                select min(started_at + '9 hours'::interval)::date start_date
                from device_histories
        ) as xx
     ) as x
     left join device_histories as cx
     on    x.axis between (cx.started_at + '9 hour'::interval)::date 
       and coalesce((cx.endet_at + '9 hour'::interval)::date, x.axis)
       and cx.device_type=2
group by x.axis
order by x.axis

Comments

[sou10]

日付に直したものと結合したら無事に数も合いました。初歩的な事と質問の仕方が悪く申し訳ありませんでした。
有難うございました。

[sazi]

質問のSQLではended_atの考慮が無いので、結合部位分だけの対処にしているなら、考慮が必要です。