Re: window function to sort times series data?

On Wed, Mar 24, 2010 at 05:29:46PM +0100, Andreas Kretschmer wrote:
> A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> 
> Well, and now i'm using 8.4 windowing-functions:
> 
> test=*# select * from price order by price_id, d;
>  price_id | price |     d
> ----------+-------+------------
>         1 |    10 | 2010-03-12
>         1 |    11 | 2010-03-19
>         1 |    12 | 2010-03-26
>         1 |    13 | 2010-04-02
>         1 |    14 | 2010-04-09
>         1 |    15 | 2010-04-16
>         1 |    16 | 2010-04-23
>         1 |    17 | 2010-04-30
>         2 |    20 | 2010-03-12
>         2 |    21 | 2010-03-19
>         2 |    22 | 2010-03-26
>         2 |    23 | 2010-04-02
> (12 Zeilen)
> 
> -- now i'm searching for 2010-03-20:
> 
> Zeit: 0,319 ms
> test=*# select price_id, sum(case when d < '2010-03-20'::date then price
> else 0 end) as price_old, sum(case when d > '2010-03-20'::date then
> price else 0 end) as price_new, max(case when d < '2010-03-20'::date
> then d else null end) as date_old, max(case when d > '2010-03-20'::date
> then d else null end) as date_new from (select price_id, price, d,
> lag(d) over(partition by price_id order by d), lead(d) over(partition by
> price_id order by d) from price) foo where '2010-03-20'::date between
> lag and lead group by price_id;
>  price_id | price_old | price_new |  date_old  |  date_new
> ----------+-----------+-----------+------------+------------
>         1 |        11 |        12 | 2010-03-19 | 2010-03-26
>         2 |        21 |        22 | 2010-03-19 | 2010-03-26
> (2 Zeilen)

Nice use of lag() and lead() functions. 

In my db id_price is a serial so it's easy to use in an aggregate to
determine the latest.

I also looked at window functions and did the following:

select p3.price as first_price,   p4.price as second_price   from (select            first_value(max(p.id_price)) over
was first_id_price,            nth_value(max(p.id_price),2) over w as second_id_price,            p.created_on >
'2010-03-20'as is_new_price           from price p           group by p.created_on > '2010-03-20'           window w as
(orderby p.created_on > '2010-03-20'           desc rows between unbounded preceding and unbounded following)    ) as t
  join price p3 on (t.first_id_price=p3.id_price)    left join price p4 on (t.second_id_price=p4.id_price)    where
t.is_new_priceis true
 

test=# \efirst_price | second_price 
-------------+--------------         17 |           11
(1 row)

Is there some potential optimizations or flaws?




Here is the test database:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: price; Type: TABLE; Schema: public; Owner: ldm; Tablespace: 
--

CREATE TABLE price (   id_price integer NOT NULL,   price integer,   created_on timestamp without time zone
);


ALTER TABLE public.price OWNER TO ldm;

--
-- Name: price_id_price_seq; Type: SEQUENCE; Schema: public; Owner: ldm
--

CREATE SEQUENCE price_id_price_seq   START WITH 1   INCREMENT BY 1   NO MAXVALUE   NO MINVALUE   CACHE 1;


ALTER TABLE public.price_id_price_seq OWNER TO ldm;

--
-- Name: price_id_price_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ldm
--

ALTER SEQUENCE price_id_price_seq OWNED BY price.id_price;


--
-- Name: price_id_price_seq; Type: SEQUENCE SET; Schema: public; Owner: ldm
--

SELECT pg_catalog.setval('price_id_price_seq', 8, true);


--
-- Name: id_price; Type: DEFAULT; Schema: public; Owner: ldm
--

ALTER TABLE price ALTER COLUMN id_price SET DEFAULT nextval('price_id_price_seq'::regclass);


--
-- Data for Name: price; Type: TABLE DATA; Schema: public; Owner: ldm
--

COPY price (id_price, price, created_on) FROM stdin;
1    10    2010-03-12 00:00:00
2    11    2010-03-19 00:00:00
3    12    2010-03-26 00:00:00
4    13    2010-04-02 00:00:00
5    14    2010-04-09 00:00:00
6    15    2010-04-16 00:00:00
7    16    2010-04-23 00:00:00
8    17    2010-04-30 00:00:00
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--