Seleccionar filas aleatorias en PostgreSQL

Usa ORDER BY RANDOM() LIMIT n cuando quieras hasta un número fijo de filas ordenadas aleatoriamente de una tabla de PostgreSQL. Por ejemplo, SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2; devuelve como máximo dos filas de pets (o menos si la tabla tiene menos filas). Para una muestra porcentual aproximada cuyo tamaño puede variar, usa WHERE RANDOM() < probability en su lugar. Estos patrones utilizan la función pseudoaleatoria de PostgreSQL para el muestreo ordinario; no son adecuados para elecciones criptográficas.

Si quieres copiar la instrucción básica de un pequeño ejemplo ejecutable, este fragmento de Python imprime el SQL que debes ejecutar en tu sesión de PostgreSQL. No se conecta a una base de datos ni ejecuta la consulta.

Ejecuta este ejemplo:

query = "SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;"
print(query)

SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;

Seleccionar filas aleatorias con RANDOM()

RANDOM() devuelve un valor pseudoaleatorio de tipo double precision desde 0 inclusive hasta 1 exclusive. Una condición como SELECT id, name FROM pets WHERE RANDOM() < 0.05; considera cada fila de forma independiente, reteniendo cada una con aproximadamente un 5 % de probabilidad. El número de resultados varía: puede ser menor o mayor que un número objetivo, incluso cero filas, especialmente en una tabla pequeña. Cada llamada proporciona un valor para la fila que se está evaluando; no se garantiza que esos valores sean únicos.

Para un número acotado de filas, usa SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;. PostgreSQL asigna claves de ordenación y devuelve las dos primeras filas en ese orden, si están disponibles. Es cómodo y sencillo, pero por lo general requiere evaluar las filas candidatas y ordenarlas. No es automáticamente más rápido que el filtrado por probabilidad, y puede resultar costoso en una tabla grande. Añade las columnas que necesites en la lista de selección en lugar de * cuando eso haga que el resultado sea más fácil de usar.

Seleccionar filas aleatorias de una tabla grande

Para una relación grande, elige entre la consulta de ordenación simple y una muestra en función de la cantidad y el tipo de aleatoriedad que necesite tu tarea. TABLESAMPLE BERNOULLI (5) solicita una muestra aproximada del 5 % considerando las filas individualmente. TABLESAMPLE SYSTEM (5) muestrea bloques de la tabla, por lo que las filas cercanas entre sí en el disco pueden aparecer juntas en el resultado. Ambos porcentajes son aproximados, y una tabla pequeña puede devolver sustancialmente menos o más filas de las esperadas. TABLESAMPLE se ejecuta antes de una condición WHERE posterior, por lo que los filtros pueden reducir aún más la muestra.

Por ejemplo, SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5); proporciona una muestra de tamaño variable; no promete exactamente cinco filas ni un cinco por ciento en cada ejecución. SYSTEM puede reducir el trabajo necesario para seleccionar una muestra en tablas grandes, pero el muestreo por bloques puede ser menos representativo cuando el orden de almacenamiento sigue una agrupación significativa. Compara los resultados con tus datos y requisitos reales en lugar de suponer que un método es universalmente mejor.

Para que el muestreo sea repetible en una configuración de consulta concreta, añade REPEATABLE (42), como en SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5) REPEATABLE (42);. La semilla puede reproducir una muestra cuando la tabla y las condiciones pertinentes siguen siendo adecuadas y no han cambiado; no es una promesa de que los resultados permanezcan idénticos tras cambios en la tabla o en todos los entornos.

pg_class.reltuples de PostgreSQL puede proporcionar un recuento estimado de filas después de que ANALYZE haya recopilado estadísticas. Por ejemplo, SELECT reltuples FROM pg_class WHERE oid = 'public.pets'::regclass; busca la estimación para esa relación. Puede estar desactualizada o ser aproximada, así que no la uses como un recuento exacto. COUNT(*) proporciona el recuento real para la instantánea de la consulta, pero puede necesitar inspeccionar muchas filas; evita afirmaciones fijas sobre tiempos porque el coste depende de los datos y del sistema.

Seleccionar una fila aleatoria con OFFSET

Si conoces el recuento actual de filas y puedes aceptar el coste de saltar filas, un desplazamiento aleatorio puede seleccionar una fila por posición: SELECT id, name FROM pets ORDER BY id OFFSET floor(random() * row_count) LIMIT 1;. Sustituye row_count por un recuento obtenido para el mismo contexto de consulta. Sin un ORDER BY, las posiciones de las filas no están especificadas; con un orden, las inserciones o eliminaciones simultáneas pueden cambiar qué fila ocupa una posición. Contar y saltar también puede ser costoso, por lo que este patrón no es intrínsecamente una búsqueda eficiente de una sola fila. Si la tabla no tiene filas, la consulta no devuelve ninguna; si su contenido cambia entre el recuento y la selección, un desplazamiento basado en el recuento anterior puede dejar de describir el conjunto actual.

Usar una estimación de filas para seleccionar una fila aleatoria

Una estimación puede ayudar a elegir un umbral para una clave, pero por sí misma no muestrea una fila. La conversión ::regclass de la consulta de estadísticas identifica public.pets para que PostgreSQL pueda encontrar su entrada en pg_class. Un patrón de umbral como WHERE id >= floor(random() * estimated_count) ORDER BY id LIMIT 1 solo tiene sentido cuando id tiene valores numéricos adecuados y un índice apropiado. Las claves dispersas o no contiguas pueden dejar huecos, hacer que el umbral no coincida con ninguna fila o hacer que algunas filas sean más probables que otras. Los rangos de claves que no empiezan cerca de cero también incumplen los supuestos del patrón. Valida la distribución de las claves y gestiona la ausencia de coincidencias antes de confiar en este enfoque. Para una consulta de propósito general, ORDER BY RANDOM() LIMIT 1 es más fácil de razonar, aunque puede suponer más trabajo en una tabla grande.