R (15) Admin (12) programming (11) Rant (6) personal (6) parallelism (4) HPC (3) git (3) linux (3) rstudio (3) spectrum (3) C++ (2) Modeling (2) Rcpp (2) SQL (2) amazon (2) cloud (2) frequency (2) math (2) performance (2) plotting (2) postgresql (2) DNS (1) Egypt (1) Future (1) Knoxville (1) LVM (1) Music (1) Politics (1) Python (1) RAID (1) Reproducible Research (1) animation (1) audio (1) aws (1) data (1) economics (1) graphing (1) hardware (1)

15 June 2009

Obfuscated SQL

I've finally learned to create views early and often in postgres... They're like variables (with lazy evaluation). Only different. A lot different, in ways that make as much sense to me as conjugating weird tenses in french. Here i've used multiple views stacked atop each other. It's not the fastest way, but for hundreds of thousands of records, it's fast enough. I have multiple loggers in different wells. Every well has its own datum. Every file belongs to a logger, which in turn belongs to a well, and requires the correct datum. I've had to join and union several different subqueries and views. It's a god-aweful mess, but i think i finally got it.
--here's the magic: v_sourcedat
--matches source files to datums
create or replace view v_sourcedat as
        select  unit_id||well_num as well, datum_m,, source
                from v_loggerdat wd,
                ( SELECT, min(tmp."time")::date AS start,
                        tmp.source FROM tmp_gwdata tmp
                        GROUP BY tmp.source,
                where =
                and gw.source not in (select source from v_sourcetowell)
        union select unit_id||well_num as well, datum_m,, source
                from v_loggerdat wd,
                (select * from v_sourcetowell)
                where gwo.well = wd.unit_id||well_num
        order by well;

-- using v_sourcedat, as above
-- the final data
create or replace view v_gwdata_4hr as
        SELECT datum.well,, gw."time",
                gw.dtw::numeric(10,4) - datum.datum_m::numeric(10,4) AS dtw,
                gw.dtwsd, gw.temp, gw.tempsd
        FROM v_sourcedat datum
        JOIN gwdata_4hr gw using (source)
        WHERE gw.temp <> 3::numeric
        AND gw.dtw > (-0.5) AND gw.dtw < 4.5
        AND gw."time" < '2010-01-01 00:00:00'::timestamp without time zone
        ORDER BY datum.well,, gw."time";

No comments:

Post a Comment