pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInterpolation and extrapolation in SQL
2+ messages / 2 participants
[nested] [flat]
* Interpolation and extrapolation in SQL
@ 2004-09-13 15:03 David Garamond <lists@zara.6.isreserved.com>
0 siblings, 1 reply; 2+ messages in thread
From: David Garamond @ 2004-09-13 15:03 UTC (permalink / raw)
To: pgsql-sql
On my first try, interpolation and extrapolation turns out to be pretty
easy to do. In psql:
-- the "lookup" table
CREATE TABLE p (
x DOUBLE PRECISION NOT NULL UNIQUE,
y DOUBLE PRECISION NOT NULL
);
INSERT INTO p VALUES (1,1);
INSERT INTO p VALUES (2,5);
INSERT INTO p VALUES (5,14);
INSERT INTO p VALUES (10,21);
-- the table that contains our x values, the y values of which will be
-- looked up in the lookup table.
CREATE TABLE q (
x DOUBLE PRECISION NOT NULL
);
INSERT INTO q VALUES (0);
INSERT INTO q VALUES (1);
INSERT INTO q VALUES (2);
INSERT INTO q VALUES (3.5);
INSERT INTO q VALUES (5.5);
INSERT INTO q VALUES (10);
INSERT INTO q VALUES (11);
-- query A. only handles interpolation
\set x1 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)'
\set x2 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1)'
\set y1 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)'
\set y2 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1)'
SELECT
q.x,
CASE
WHEN :x1 = :x2 THEN :y1
ELSE (:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1))
END AS y
FROM q;
-- query B. also handles extrapolation, and the note column tells us
-- whether a certain y value is directly taken from p, or interpolated,
-- or extrapolated.
\set x0 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1
OFFSET 1)'
\set x3 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1
OFFSET 1)'
\set y0 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1
OFFSET 1)'
\set y3 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1
OFFSET 1)'
SELECT
q.x,
CASE
WHEN :x1 = :x2 THEN :y1
WHEN :x1 IS NULL THEN (:y2 + (q.x-:x2)/(:x3-:x2)*(:y3-:y2))
WHEN :x2 IS NULL THEN (:y0 + (q.x-:x0)/(:x1-:x0)*(:y1-:y0))
ELSE (:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1))
END AS y,
CASE
WHEN :x1 = :x2 THEN 'direct'
WHEN :x1 IS NULL THEN 'extrapolated to the left'
WHEN :x2 IS NULL THEN 'extrapolated to the right'
ELSE 'interpolated'
END AS note
FROM q;
-- C. to handle the case where x in p is not unique, replace the x0..x3
and y0..y3 template into:
\set x1 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <=
q.x ORDER BY p2.x DESC LIMIT 1)'
\set x2 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >=
q.x ORDER BY p2.x ASC LIMIT 1)'
\set y1 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON
p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1)'
\set y2 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON
p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1)'
\set x0 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <=
q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)'
\set x3 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >=
q.x ORDER BY p2.x ASC LIMIT 1 OFFSET 1)'
\set y0 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON
p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)'
\set y3 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON
p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1 OFFSET 1)'
Questions:
1) Is the above correct? (It gives me correct result, but I'm not 100% sure)
2) Does the optimizer cache the result of identical subqueries (e.g. :x1
or :x2, which is mentioned several times in the query)? If yes, how do I
know this?
3) Is there another (simpler, more elegant, more efficient) way to do
interpolation/extrapolation in SQL?
--
dave
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Interpolation and extrapolation in SQL
@ 2004-09-13 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Garamond <lists@zara.6.isreserved.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-09-13 15:16 UTC (permalink / raw)
To: David Garamond <lists@zara.6.isreserved.com>; +Cc: pgsql-sql
David Garamond <lists@zara.6.isreserved.com> writes:
> 2) Does the optimizer cache the result of identical subqueries (e.g. :x1
> or :x2, which is mentioned several times in the query)?
No.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-13 15:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-13 15:03 Interpolation and extrapolation in SQL David Garamond <lists@zara.6.isreserved.com>
2004-09-13 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox