agora inbox for pgsql-sql@postgresql.orghelp / color / mirror / Atom feed
matching against start/end times and diagnostic values (was: filtering based on table of start/end times) 2+ messages / 1 participants [nested] [flat]
* matching against start/end times and diagnostic values (was: filtering based on table of start/end times) @ 2014-11-12 21:49 Seb <spluque@gmail.com> 2014-11-14 17:04 ` Re: matching against start/end times and diagnostic values Seb <spluque@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Seb @ 2014-11-12 21:49 UTC (permalink / raw) To: pgsql-sql On Sun, 09 Nov 2014 12:43:22 -0600, Seb <spluque@gmail.com> wrote: > On Sat, 08 Nov 2014 11:26:53 +0100, > Tim Schumacher <tim@bandenkrieg.hacked.jp> wrote: [...] >> Something like this should work: >> SELECT * FROM voltage_series AS vs LEFT JOIN voltage_log vl ON >> vs.time BETWEEN vl.time_beg AND vl.time_end WHERE vl.id IS NULL >> This is untested, but I think it should work. > Thank you all for your suggestions. The above proved very fast with > the millions of records and several other joins involved. Sorry to come back with a related issue, which is proving troublesome. There's another log table, that looks just like voltage_log, but has an additional column with an integer indicating what problem occurred during the period: CREATE TABLE voltage_diagnostic_log ( record_id serial, time_beg timestamp without time zone NOT NULL, time_end timestamp without time zone NOT NULL, diagnostic integer, CONSTRAINT voltage_log_pkey PRIMARY KEY (record_id)); So that a view can be built for the voltage_series table where its columns can be adjusted based on the diagnostic integer (there are other columns besides voltage in the actual table), if the time stamp falls within a period of the voltage_diagnostic_log table. To illustrate, the view needs to be able to have field definitions such as (pseudo-code): CASE WHEN diagnostic=1 THEN voltage * 0.88 ELSE voltage END AS voltage_corrected, CASE WHEN diagnostic=2 THEN pressure - 2.5 ELSE pressure END AS pressure_corrected, The problem is that each record in voltage_series can have several matching records in voltage_diagnostic_log. Any insights welcome! -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: matching against start/end times and diagnostic values 2014-11-12 21:49 matching against start/end times and diagnostic values (was: filtering based on table of start/end times) Seb <spluque@gmail.com> @ 2014-11-14 17:04 ` Seb <spluque@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Seb @ 2014-11-14 17:04 UTC (permalink / raw) To: pgsql-sql On Wed, 12 Nov 2014 15:49:57 -0600, Seb <spluque@gmail.com> wrote: > Sorry to come back with a related issue, which is proving troublesome. > There's another log table, that looks just like voltage_log, but has > an additional column with an integer indicating what problem occurred > during the period: > CREATE TABLE voltage_diagnostic_log ( record_id serial, time_beg > timestamp without time zone NOT NULL, time_end timestamp without time > zone NOT NULL, diagnostic integer, CONSTRAINT voltage_log_pkey PRIMARY > KEY (record_id)); [...] For posterity's sake, the only solution I was able to find was to first create a view with a separate boolean column for each diagnostic value via crosstab(). From there it was possible to use a WITH subquery to remove rows with a particular diagnostic value (as suggested previously), and then have the main SELECT statement do a second left join to the crosstab view so that it could make use of the rest of the boolean columns as sources for CASE statements for each field. Cheers, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-11-14 17:04 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-11-12 21:49 matching against start/end times and diagnostic values (was: filtering based on table of start/end times) Seb <spluque@gmail.com> 2014-11-14 17:04 ` Re: matching against start/end times and diagnostic values Seb <spluque@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox