I am analysing whether distance to closest vehicle has an impact on probability to call. For that I have real data, and I would like to generate "fake" data by randomising results n times. In the fake randomised set there should be no correlation by definition. Then I compare the real to the fake data as my benchmark.
Problem:
I am looking for advice on the way to do this in Postgres 9.3. Currently I am trying to use a function that gets as a parameter the number of randomised columns n.
The input small_panel table is derived from real observations and has lots with lotid, a column called = 1 if the lot called and NULL if did not, and a column distance with various values. I want to generate n columns (called_1, called_2, ... ,called_n) with randomised lots lotid selected but for each time period dt having the same number of calls.
For example, in the table there are 2 calls with timestamp dt = 2009-06-05 22:30:00 so I would like to still have 2 calls with that timestamp but randomise which lots placed the calls in each fake column.
I tried 2 approaches: without and with an inner loop, none work as of now... I would greatly appreciate your input.
Data to try:
The toy table here is small (17 rows) but the ones I have to deal with are large (e.g. 3 million rows) so if it is too slow I might need to optimise later.
The original input table small_panel looks like this (I have put a csv and a pgsql file to create the table here):
select * from small_panel order by dt;
gid | id | lotid | called | distance | dt
-------+------+--------+--------+----------+---------------------
600463 | 967 | 5356 | | 62.8718 | 2009-06-05 17:00:00
601525 | 2029 | 230414 | | 530.352 | 2009-06-05 17:00:00
601437 | 1941 | 225857 | | 148.408 | 2009-06-05 17:00:00
627524 | 1329 | 10437 | | 6.9032 | 2009-06-05 18:50:00
627806 | 1613 | 11879 | | 796.495 | 2009-06-05 18:50:00
627323 | 1128 | 8079 | | 378.922 | 2009-06-05 18:50:00
628550 | 2358 | 299598 | 1 | 216.991 | 2009-06-05 18:50:00
626342 | 159 | 2772 | 1 | 41.5125 | 2009-06-05 18:50:00
628418 | 2226 | 283406 | | 1140.35 | 2009-06-05 18:50:00
626302 | 106 | 2497 | | 166.264 | 2009-06-05 18:50:00
628276 | 2082 | 230576 | | 135.663 | 2009-06-05 18:50:00
681500 | 1921 | 225791 | | 147.339 | 2009-06-05 22:30:00
680059 | 484 | 3562 | 1 | 154.411 | 2009-06-05 22:30:00
681258 | 1682 | 15842 | | 630.824 | 2009-06-05 22:30:00
681332 | 1755 | 18450 | | 176.173 | 2009-06-05 22:30:00
681605 | 2025 | 230328 | 1 | 92.0443 | 2009-06-05 22:30:00
679923 | 349 | 3257 | | 72.05 | 2009-06-05 22:30:00
(17 rows)
Solution - try1 :
I tried the following function (without inner loop, almost working but wrong results):
DROP FUNCTION ib_randomise_calls(int);
CREATE OR REPLACE FUNCTION ib_randomise_calls ( n INT DEFAULT 3 )
RETURNS TEXT AS
$func$
DECLARE mytext TEXT DEFAULT 'IB_000_panel';
DECLARE r record;
BEGIN
DROP TABLE IF EXISTS panel_000;
CREATE TABLE panel_000 AS ( SELECT * FROM small_panel );
ALTER TABLE panel_000 ADD COLUMN calledz INT;
FOR i IN 1..n LOOP
RAISE NOTICE 'i: %', i;
EXECUTE format( $x1$ ALTER TABLE panel_000 ADD COLUMN called_%1$s INT; $x1$, i);
DROP TABLE IF EXISTS panel_000_times;
CREATE TABLE panel_000_times AS ( SELECT distinct on (dt) * FROM panel_000 where called is not null );
DROP TABLE IF EXISTS panel_000_lots;
CREATE TABLE panel_000_lots AS ( SELECT distinct lotID FROM panel_000 order by 1);
UPDATE panel_000 a SET calledZ = 1
from panel_000_times b , panel_000_lots c
where a.dt = b.dt
AND a.lotid IN (
select lotID from panel_000_lots
order by random() limit ( select count(*) from panel_000 where called=1 and dt=a.dt)
)
;
END LOOP ;
RETURN mytext;
END;
$func$ LANGUAGE plpgsql;
select ib_randomise_calls(4);
Which produces the columns but populates only one calledz and even that one with wrong results (different number of calls in each timestamp):
select dt, sum(called), sum(calledz) from panel_000 group by 1 order by 1;
dt | sum | sum
---------------------+-----+-----
2009-06-05 17:00:00 | |
2009-06-05 18:50:00 | 2 | 4
2009-06-05 22:30:00 | 2 | 2
And the resulting table panel_000 I got here is:
select * from panel_000 order by dt;
gid | id | lotid | called | distance | dt | calledz | called_1 | called_2 | called_3 | called_4
--------+------+--------+--------+----------+---------------------+---------+----------+----------+----------+----------
601525 | 2029 | 230414 | | 530.352 | 2009-06-05 17:00:00 | | | | |
601437 | 1941 | 225857 | | 148.408 | 2009-06-05 17:00:00 | | | | |
600463 | 967 | 5356 | | 62.8718 | 2009-06-05 17:00:00 | | | | |
627323 | 1128 | 8079 | | 378.922 | 2009-06-05 18:50:00 | | | | |
626302 | 106 | 2497 | | 166.264 | 2009-06-05 18:50:00 | | | | |
626342 | 159 | 2772 | 1 | 41.5125 | 2009-06-05 18:50:00 | 1 | | | |
627524 | 1329 | 10437 | | 6.9032 | 2009-06-05 18:50:00 | | | | |
627806 | 1613 | 11879 | | 796.495 | 2009-06-05 18:50:00 | | | | |
628550 | 2358 | 299598 | 1 | 216.991 | 2009-06-05 18:50:00 | 1 | | | |
628418 | 2226 | 283406 | | 1140.35 | 2009-06-05 18:50:00 | 1 | | | |
628276 | 2082 | 230576 | | 135.663 | 2009-06-05 18:50:00 | 1 | | | |
681258 | 1682 | 15842 | | 630.824 | 2009-06-05 22:30:00 | 1 | | | |
680059 | 484 | 3562 | 1 | 154.411 | 2009-06-05 22:30:00 | | | | |
681500 | 1921 | 225791 | | 147.339 | 2009-06-05 22:30:00 | 1 | | | |
681332 | 1755 | 18450 | | 176.173 | 2009-06-05 22:30:00 | | | | |
681605 | 2025 | 230328 | 1 | 92.0443 | 2009-06-05 22:30:00 | | | | |
679923 | 349 | 3257 | | 72.05 | 2009-06-05 22:30:00 | | | | |
(17 rows)
Solution - try2:
I tried this function (like previous but with another loop inside the loop with parametrised column names) which gives me errors and wouldn't work:
DROP FUNCTION ib_randomise_calls(int);
CREATE OR REPLACE FUNCTION ib_randomise_calls ( n INT DEFAULT 3 )
RETURNS TEXT AS
$func$
DECLARE mytext TEXT DEFAULT 'IB_000_panel';
DECLARE r record;
BEGIN
DROP TABLE IF EXISTS panel_000;
CREATE TABLE panel_000 AS ( SELECT * FROM small_panel );
FOR i IN 1..n LOOP
RAISE NOTICE 'i: %', i;
EXECUTE format( $x1$ ALTER TABLE panel_000 ADD COLUMN called_%1$s INT; $x1$, i);
DROP TABLE IF EXISTS panel_000_lots;
CREATE TABLE panel_000_lots AS ( SELECT distinct lotID FROM panel_000 order by 1);
FOR r IN
SELECT * FROM (SELECT distinct on (dt) * FROM panel_000 where called is not null) AS foo
LOOP
mytext:= 'dt: '||r.dt; RAISE NOTICE '%', mytext;
EXECUTE format( $x2$
IF r.dt=panel_000.dt THEN UPDATE panel_000 SET called_%1$s =
CASE WHEN lotID in (
select lotID from panel_000_lots order by random()
limit ( select count(*) from panel_000 where called=1 )
) THEN 1 ELSE NULL END
END IF;
$x2$, i);
END LOOP;
END LOOP ;
RETURN mytext;
END;
$func$ LANGUAGE plpgsql;
select ib_randomise_calls(4);
Errors output:
select ib_randomise_calls(4);
NOTICE: i: 1
NOTICE: dt: 2009-06-05 18:50:00
ERROR: syntax error at or near "IF"
LINE 2: IF r.dt=panel_000.dt THEN UPDATE panel_000 SET c...
^
Aucun commentaire:
Enregistrer un commentaire