Re: time series query - Mailing list pgsql-general

In case anyone is interested, I was able to solve this, more or less.
Here's my new "Latest value" query:

  select obj_id, val_type_id, (max(row(observation_date, val))).val
  from measurements
  group by obj_id, val_type_id

It was only necessary to define a new (date, numeric) type. Below is the
code. The performance is actually slower than using a JOIN between the
table and its  GROUP-BY  version. I guess for performance, I should code
the functions in C, but at the moment, the value for me is that it
simplifies a lot of my 12-way join queries!

create type dtval as (
  dt date,
  val numeric
);

create  function dtval_smaller(dtval, dtval) returns dtval as $$
  select case when $1.dt < $2.dt then $1 else $2 end
$$ language sql;

create aggregate min (
  sfunc = dtval_smaller,
  basetype = dtval,
  stype = dtval
);

create  function dtval_larger(dtval, dtval) returns dtval as $$
  select case when $1.dt > $2.dt then $1 else $2 end
$$ language sql;

create aggregate max (
  sfunc = dtval_larger,
  basetype = dtval,
  stype = dtval
);



Jaime Silvela wrote:
> The problem I'm trying to solve is pretty standard. I have a table
> that records measurements of different types at different times.
>
> CREATE TABLE measurements (
>  obj_id int4,
>  val_type_id int4 references lu_val_type(val_type_id),
>  val numeric,
>  observation_date date
> );
>
> I want a query as simple and fast as possible to return the latest
> observation of each type for each object.
> I sent a message to this list a while ago, and the suggestion I found
> to be the best compromise of clarity and speed was:
> a) create an index on (obj_id, val_type_id, observation_date)
> b) the "obvious" query becomes fast thanks to the index.
>    select ms.*
>    from (
>        select obj_id, val_type_id, max(observation_date) as
> observation_date
>        from measurements
>       group by obj_id, val_type_id
>    ) ms_last
>    join measurements ms using (obj_id, val_type_id, observation_date);
>
> It still bugged me a bit that this requires a JOIN, especially since
> in a procedural language, it would have been so easy to return the
> value associated with the max(observation_date).
> I think I've found a pretty good alternative. This at the moment works
> if we keep track of time with an integer, rather than a date, but it
> would be readily extensible.
>
> The idea is to in fact, associate the value with the
> max(observation_date) like so:
> select obj_id, val_type_id, max(array[observation_date, val])
> group by obj_id, val_type_id;
>
> There are two caveats:
> a) array requires elements to be of the same type, so observation_type
> must be kept as "time from"
> b) a row constructor would be ideal here, but there is now max
> function for rowtypes.
>
> If I did have a max() function for row types, it would be clean to do
> this:
> select obj_id, val_type_id, max(row(observation_date, val))
> group by obj_id, val_type_id;
>
> Now, it seems that since rowtype comparison is built in, it should be
> pretty easy to build a max() aggregate for it. Has anybody done this?
> I'd have looked at the code for max(anyarray) but I don't know how to
> access it. Can someone point me in the right direction?
>
> Also, has someone thought about this before? I'm wondering if there
> will be a speed gain coming from this.
>
> Thank you,
> Jaime
>
>
> ***********************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
>
> Bear Stearns does not provide tax, legal or accounting advice.  You
> should consult your own tax, legal and accounting advisors before
> engaging in any transaction. In order for Bear Stearns to comply with
> Internal Revenue Service Circular 230 (if applicable), you are notified
> that any discussion of U.S. federal tax issues contained or referred to
> herein is not intended or written to be used, and cannot be used, for
> the purpose of:  (A) avoiding penalties that may be imposed under the
> Internal Revenue Code; nor (B) promoting, marketing or recommending to
> another party any transaction or matter addressed herein.
> ***********************************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************