Hi,
I'm trying to run a query to find the median value, organized by
date. However, when I run the query, it runs for about 4 hours, and
then quits with the following message:
> ERROR: could not write block 10447102 of temporary file: No space left on device
> HINT: Perhaps out of disk space?
My table has 512327 rows and is the following:
> Table "public.m_uop_times"
> Column | Type | Modifiers
> ----------------+-----------------------------+-----------
> objectid | integer |
> date_part | double precision |
> date_start | timestamp without time zone |
> date_processed | timestamp without time zone |
> gen_time | integer |
> Indexes:
> "m_uop_date_idx" btree (date_processed)
> "m_uop_epoch_idx" btree (date_part)
> "m_uop_gen_idx" btree (gen_time)
> "m_uop_objectid_idx" btree (objectid)
> "m_uop_start_idx" btree (date_start)
The date_part column is actually simply "EXTRACT (EPOCH FROM
date_start::date)" so that I could put an index on that date, and the
gen_time column is actually "date_processed-date_start" so that there
could be an index on that difference as well.
My median query is copied from
http://book.itzero.com/read/others/0602/OReilly.SQL.Cookbook.Dec.2005_html/0596009763/sqlckbk-CHP-7-SECT-10.html
Here it is:
> select date_start, avg(gen_time)
> from (
> select a.date_start::date, a.gen_time
> from m_uop_times a, m_uop_times b
> where a.date_part = b.date_part
> group by a.date_start::date, a.gen_time
> having sum(case when a.gen_time=b.gen_time then 1 else 0 end)
> >= abs(sum(sign(a.gen_time - b.gen_time)))
> ) as foo
> group by date_start;
Basically, I want to find the median gen_time for each day.
Would anyone know a better way to do this, or have suggestions on how
I can make this work without dying?
Any help appreciated!
--Richard
