On Wed, Mar 24, 2010 at 05:29:46PM +0100, Andreas Kretschmer wrote:
> A. Kretschmer <andreas.kretsch...@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 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
--
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
