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.
If there are 3 terminals, start on 10/17, start on 10/19, and 10/22
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.
Corresponding source code
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
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
- postgresql - i want to aggregate the data recorded every 1 minute every 10 minutes
- postgresql - aggregate rows with matching values in sql
- postgresql - i want to aggregate weekly time series in sql
- postgresql does not start on mac
- i can't start postgresql
- i want to start the postgresql server in windows subsystem for linux
- i want to start postgresql with wsl
- cannot start postgresql
- postgresql - cannot start postgre server with wsl
- cannot start postgresql
- postgresql - postgres console does not start
- postgresql - postgredb does not start with docker-compose up -d
- PostgreSQL pg_ctl start startup timeout instance analysis
- postgresql doesn't start
- heroku:spring boot2:project does not start in postgresql
- postgresql cannot start
- systemctl start postgresqlservice cannot start postgresql
- start postgresql
- postgresql - i want to output the start time and end time from the table where the date and time are registered
- python : POSTGRESQL queries caching
- java : How to make overwrite lines in PostgreSQL?
- java : Why does @Orderby generate a mistake?
- Automatic data filling in PostgreSQL
- mysql : Union does not display all columns
- Column "" does not exist when the PostgreSQL trigger is triggered
- sql : Calculation of each field with UPDATE table
- sql : Import data from Excel
- mysql : COUNT column and data output from this column
- sql : Help compile a complex request