Re: window function to sort times series data?

On Wed, Mar 24, 2010 at 05:29:46PM +0100, Andreas Kretschmer wrote:
> A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)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 w as 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 (order by 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_price is true

test=# \e
first_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
--