Re: Query help
От | Adrian Klaver |
---|---|
Тема | Re: Query help |
Дата | |
Msg-id | 97b3bcff-8e3a-00a7-09bc-c6f82a420196@aklaver.com обсуждение исходный текст |
Ответ на | Query help (Chuck Martin <clmartin@theombudsman.com>) |
Ответы |
Re: Query help
(Chuck Martin <clmartin@theombudsman.com>)
|
Список | pgsql-general |
On 1/26/19 3:04 PM, Chuck Martin wrote: > I'm having trouble formulating a query. This is a simplified version of > the tables: > > ombcase > ------------ > case_pkey integer, primary key > casename varchar > insdatetime timestamp w/o time zone > status_fkey integer, foreign key > > status > -------- > status_pkey integer, primary key > statusid varchar > > statuschange > -------- > statuschange_pkey integer, primary key > insdatetime timestamp w/o time zone > ombcase_fkey integer, foreign key > oldstatus_fkey integer, foreign key > newstatus_fkey integer, foreign key > active integer, not nullable > > The idea should be obvious, but to explain, insdatetime is set when a > new record is created in any table. All records in ombcase have a > foreign key to status that can't be null. When status changes, a record > is created in statuschange recording the old and new status keys, and > the time (etc). > > The goal is to find records in ombcase that have not had a status change > in xx days. If the status has not changed, there will be no statuschange > record. > > This query returns the age of each ombcase and the last statuschange > record, but only if there is a statuschange record: > > --Finds the age and last status change for open cases, but not age of > cases with no status change > > SELECT casename, age(ombcase.insdatetime) AS caseage, > age(laststatuschange.created_at) AS statusage > > FROM > > (SELECT > > case_fkey, MAX(insdatetime) AS created_at > > FROM > > statuschange > > GROUP BY > > case_fkey) AS laststatuschange > > INNER JOIN > > ombcase > > ON > > laststatuschange.case_fkey = case_pkey > > RIGHT JOIN status > > ON status_fkey = status_pkey > > WHERE lower(statusid) NOT LIKE ('closed%') > > AND case_pkey <> 0 > > > I want to use coalesce > (age(ombcase.insdatetime),age(statuschange.insdatetime) ) to return the > time that a case has been in a status—or without a status change. But > first I have to find the cases with no statuschange record. I was able > to do that, too, using this query: Outline form: 1) If a record is in ombcase it has a status('in a status') by definition. From query below you are not looking for just records in ombcase, but those that have a statusid other then 'closed%' in status table. 2) For the criteria in 1) you want to find the age of the last statuschange. To me that leads to something like: SELECT case_pkey FROM ombcase AS JOIN status ON ombcase.case_pkey = status.status_fkey LEFT JOIN statuschange ON -- Or statuschange.ombcase_fkey. Not clear from above. statuschange.case_fkey = ombcase.status_pkey GROUP BY ombcase.pkey HAVING status.LOWER(statusid) NOT LIKE ('closed%') AND max(coalesce(statuschange.insdatetime, ombcase.insdatetime)) < 'some date' Obviously not tested. > > > --find cases in status too long > > SELECT casename, coalesce > (age(ombcase.insdatetime),age(statuschange.insdatetime) ) > > FROM ombcase > > LEFT JOIN statuschange > > ON case_fkey = case_pkey > > LEFT JOIN status > > ON status_fkey = status_pkey > > AND lower(statusid) NOT LIKE ('closed%') > > AND coalesce ( age(ombcase.insdatetime), age(statuschange.insdatetime) ) > > '2 months' > > > But this query will return all statuschange records for an ombcase > record that has multiple ones. > > > Any suggestions on how to combine the two ideas? > > > Chuck Martin > Avondale Software > -- > Chuck Martin > Avondale Software -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Does creating readOnly connections, when possible, free up resources in Postgres?