From: | Rémi Cura <remi(dot)cura(at)gmail(dot)com> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: efficient way to do "fuzzy" join |
Date: | 2014-04-15 09:25:37 |
Message-ID: | CAJvUf_sW_3w1BSE1cFGvoEGyeMYFbVKtYDrZQMyk7HQ3yHhr7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
A little related bonus :
when doing the time-join,
the next step is to interpolate to have a more accurate estimation :
-------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS range_interpolate(nr anyrange,obs anyelement) ;
CREATE OR REPLACE FUNCTION range_interpolate(nr anyrange,obs
anyelement)
RETURNS TABLE(lower_weight NUMERIC,upper_weight NUMERIC)
AS $$
--(at)param a range
--(at)param an observation (value) of the same type as the range
--(at)return the weight to apply to lower bound and upper bound of
range to get the value.
--exceptions : -inf or +inf : weight of the bound is 0, the other
1.
--exceptions : range = a point : returns weight of 0.5 for each
bound (they are identical but the asociated data may not be)
SELECT
CASE WHEN upper(nr)=lower(nr) THEN ROW(0.5,0.5)
--WHEN obs=lower(nr) AND obs=upper(nr) THEN ARRAY[0.5,0.5]
--THEN (obs-lower(nr))/ra, (upper(nr)-obs)/ra
WHEN lower_inf(nr)=TRUE OR lower(nr) IS NULL THEN ROW(0,1)
WHEN upper_inf(nr)=TRUE OR upper(nr) IS NULL THEN ROW(1,0)
ELSE ROW(
(upper(nr)-obs)/(upper(nr)-lower(nr)),(obs-lower(nr))/(upper(nr)-lower(nr)))
END
--testing :
--SELECT * FROM range_interpolate(numrange(1,10) , round(10,2))
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
--------------------------------------------------------------
Cheers,
Rémi-C
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2014-04-15 10:09:51 | Re: streaming replication + wal shipping |
Previous Message | senthilnathan | 2014-04-15 08:28:06 | Re: streaming replication + wal shipping |