Home>

I would like to aggregate the number of associated ids of terminals on which the start date and end date are recorded on a daily basis.
The query below works, but I'm having trouble with the actual number.

For example
If there are 3 terminals, start on 10/17, start on 10/19, and 10/22

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

It is assumed that the result will be obtained as shown in.
I would appreciate it if you could point out the mistake in the query. Thank you.

Table (device_histories) configuration
id: history id
started_at: Linking start date (I want to take the one that started linking after October 19, 2020)
ended_at: End date (all contents are blank, that is, it will be tied without removal until today's date)
device_type: Device type (I want to limit it to type 2 this time)

Sample data
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
Corresponding source code
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
;
  • Answer # 1

    I would appreciate it if you could point out the mistake in the query.

    It doesn't work at all, so I think it's something wrong, but I can't verify it.
    However, I feel that the cause is that it is not combined with the dated one.

    I tried to rearrange it for the time being.
    The condition of the extraction range is given when creating the range with 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