Re: extraer parte decimal

Wed, Jun 22, 2011 at 06:12:16PM -0400, Alvaro Herrera escribió:

>Excerpts from Juan's message of mié jun 22 14:59:12 -0400 2011:
>> Perdon
>>
>> el lenguaje usado es 'plpgsql'
>> lo que pasa es q estoy haciendo algo usando random para seleccionar
>> un grupo de registros.
>
>me parece que vas por mal camino. Lee esto
>
>http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/

Efectivamente, hay una solución muy sencilla (como la que
comenté yo) pero que es conocido sólo aplica a tablas pequeñas, como el
link que que envía Álvaro demuestra. No hay soluciones inmediatas para
tablas grandes, como el post (y muchos otros al respecto) comentan, o
que requieren añadir columnas, depender de PKs numéricas o columnas
seriales que no tengan "muchos huecos". Ninguna aparenta, por tanto, una
buena solución.

Así que, volviendo a pensar sobre el tema, se me ha ocurrido
una solución que podría funcionar bien :) La idea básica es generar N
números aleatorios al principio y luego usar cursores y desplazamientos
para extraer dichos registros. Así que he escrito una pequeña función
para probarlo:

CREATE FUNCTION random_rows(table_name text, n_rnd_rows int, n_rows bigint) RETURNS SETOF RECORD AS $$
DECLARE
curs refcursor;
ret record;
rnd_row_pos int[];
BEGIN
FOR i IN 1..n_rnd_rows LOOP
rnd_row_pos[i] = floor(random() * n_rows);
END LOOP;

-- Sort the array
SELECT INTO rnd_row_pos array_agg(i) FROM (SELECT * FROM unnest(rnd_row_pos) i ORDER BY i) AS i;

OPEN curs FOR EXECUTE 'SELECT * FROM ' || table_name; -- ORDER is not relevant

MOVE ABSOLUTE rnd_row_pos[1] - 1 FROM curs;
FETCH NEXT FROM curs INTO ret;
RETURN NEXT ret;

FOR i IN 2..n_rnd_rows LOOP
MOVE RELATIVE rnd_row_pos[i] - rnd_row_pos[i - 1] - 1 FROM curs;
FETCH NEXT FROM curs INTO ret;
RETURN NEXT ret;
END LOOP;

CLOSE curs;

RETURN;
END;
$$ LANGUAGE plpgsql;

Esta función lee los registros de origen "en el orden que están
en disco". Sin embargo, esto no es un problema porque luego se
seleccionan aleatoriamente los registros a obtener.

A nivel de demo, probando con una tabla llamada 'random' que
tiene 10 millones de filas generadas con generate_series:

- Solución trivial (sólo tablas pequeñas):

aht=> EXPLAIN ANALYZE SELECT * FROM random ORDER BY random() LIMIT 20;
[...]
Total runtime: 7349.974 ms

- Con la función que comento, pero obteniendo el número de filas con COUNT(*),
que requiere un seqscan, ya supone un ahorro importante de tiempo (y
creciente con el tamaño de la tabla y muy significativamente si work_mem
limitara):

aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, (SELECT COUNT(*) FROM random)) AS (i integer);
[...]
Total runtime: 5472.431 ms

- Si en lugar de hacer COUNT(*) sabemos el número de registros o queremos
restringir el número de filas random a los primeros N registros, la
técnica que comento rinde fantásticamente:

aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, 10000000) AS (i integer);
[...]
Total runtime: 843.520 ms

- Otra opción sería fiarnos de las estadísticas de postgres y usarlas para
estimar el número de registros:

aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, (SELECT n_live_tup FROM pg_stat_all_tables WHERE relname = 'random')) AS (i integer);
[...]
Total runtime: 856.390 ms

- Por razones de eficiencia, los resultados de esta función salen
"ordenados". A su vez se pueden reordenar aleatoriamente si se quiere
sin mucho costo adicional:

aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, 10000000) AS (i integer) ORDER BY random() LIMIT 20;
[...]
Total runtime: 863.488 ms

Y parece funcionar:

aht=> SELECT * FROM random_rows('random', 20, 10000000) AS (i integer) ORDER BY random() LIMIT 20;
i
---------
2078060
4894086
4437251
3700397
642530
7685856
2297124
5127436
2764292
376513
6676704
791899
671257
1029904
2942200
1237436
9265684
1302897
7479438
3685012
(20 filas)

¿Qué os parece el método? ¿Se os ocurre algún punto flaco que
pueda tener?

Saludos,

Álvaro

--

Álvaro Hernández Tortosa

-----------
NOSYS
Networked Open SYStems