Cómo elegir registros al azar de una tabla con consultas SQL (para las principales bases de datos)

Por regla general, lo que esperamos del lenguaje SQL cuando lo utilizamos para consultar bases de datos son resultados predecibles, precisos y relevantes. No solemos asociar una consulta con datos imprecisos o incluso aleatorios ¿verdad? Sin embargo, poder elegir registros al azar desde una tabla de nuestra base de datos puede tener muchas aplicaciones prácticas y casos de uso como, por ejemplo:

• Aplicaciones de juegos y entretenimiento: las aplicaciones orientadas a juegos suelen requerir la generación de contenido aleatorio para brindar experiencias dinámicas o para seleccionar ciertos usuarios para un sorteo, por ejemplo.
• Recomendaciones aleatorias dentro de categorías: al seleccionar registros al azar de una tabla podemos mostrar usuarios, productos u otros elementos de manera impredecible (aunque los filtremos previamente por ciertos criterios para enfocarlos mejor). Por ejemplo, podemos recomendar diferentes productos al azar dentro de las mismas categorías que el producto que está viendo un usuario.
• Muestreo estadístico: en el análisis de datos, a menudo necesitamos extraer una muestra representativa de una población. Al elegir registros al azar, podemos obtener una vista previa de los datos y realizar análisis exploratorios. Esto puede resultar valioso en machine learning, estudios de mercado y análisis de tendencias.
• Pruebas de rendimiento: cuando se evalúa el rendimiento de una aplicación o de un sistema basado en una base de datos, es esencial poder simular un uso realista de los datos. Al seleccionar un subconjunto aleatorio de registros, podemos imitar el acceso no determinista a los datos y poner a prueba la eficiencia de nuestras consultas y algoritmos.

Por desgracia no hay una manera estándar y directa de realizar una selección aleatoria sobre un conjunto de datos y en cada sistema gestor de bases de datos relacionales (SGBDR) se debe hacer de una manera diferente. En este artículo veremos cómo podemos obtener registros aleatorios de una tabla en los principales SGBD del mercado, como SQL Server, MySQL, PostgreSQL u Oracle entre otros. Exploraremos técnicas como el uso de funciones específicas o el uso de ordenamiento, el muestreo y la limitación de resultados para poder lograr el objetivo.

Vamos a ello...

SQL Server

En SQL Server la consulta necesaria para seleccionar un único registro aleatorio desde una tabla es la siguiente:

SELECT TOP 1 campo FROM tabla ORDER BY NEWID()

El operador TOP 1 limita el resultado a una sola fila. Si quisiésemos obtener más, simplemente habría que poner aquí otro valor en lugar de un 1. La función NEWID() genera un identificador único para cada fila de la tabla. El operador ORDER BY ordena las filas por ese valor que, como es aleatorio, la consulta devuelve una fila al azar de la tabla, junto con el valor del campo especificado. Por supuesto, si quisiésemos más de un campo de la tabla simplemente habría que añadir sus nombres antes del FROM separados por comas: no está limitada a devolver un único campo.

Nota: en el resto de los ejemplos ocurre lo mismo: se devuelve un solo registro y un solo campo por simplicidad, pero se pueden obtener varios registros y los campos que deseemos.

Si se trata de una tabla muy muy grande, con cientos de miles o millones de registros podríamos utilizar el modificador TABLESAMPLE en lugar de esta técnica. Esta función devuelve una muestra aleatoria de una tabla y puede ser más rápida que usar NEWID() para tablas grandes. Su uso es bastante sencillo:

SELECT TOP 1 campo FROM tabla TABLESAMPLE (10 PERCENT);

Esto devolverá un registro aleatorio de la tabla, pero solo utilizando una muestra del 10% de la misma (ten en cuenta que no usará todos los registros de la tabla debido a ello, claro). Puedes ajustar el porcentaje para equilibrar el rendimiento y la aleatoriedad según sea necesario.

MySQL y MariaDB

En el caso del gestor Open Source de Oracle y su fork, este caso la consulta:

SELECT campo FROM tabla ORDER BY RAND() LIMIT 1

En este caso la técnica es idéntica a la de SQL Server, pero la sintaxis cambia debido al lenguaje SQL específico de MySQL. La función RAND() genera un valor aleatorio, a través del cual ordenamos los resultados como antes. Para limitar el número de registros devueltos se utiliza LIMIT en lugar de TOP. Por lo demás, el concepto es igual.

PostgreSQL y SQLite

PostgreSQL es uno de los sistemas de gestión de bases de datos relacionales más populares y utilizados en el mundo, tanto por empresas como por desarrolladores. Es Open Source y ofrece un gran rendimiento y multitud de características avanzadas que no tienen nada que envidiar a sistemas comerciales como Oracle o SQL Server.

En PostgreSQL podemos obtener registros al azar de una tabla utilizando otra consulta casi idéntica a la anterior:

SELECT campo FROM tabla ORDER BY RANDOM() LIMIT 1

En este caso la función que devuelve un valor aleatorio se llama RANDOM() y no RAND(), por eso cambia ligeramente la consulta. Pero es funcionalmente idéntica a las anteriores.

Esta misma consulta serviría para SQLite, la base de datos más utilizada del mundo, ya que es muy compatible con PostgreSQL.

Si la tabla es muy grande podemos usar una consulta como esta en su lugar:

SELECT campo
FROM tabla
OFFSET floor(random() * (
    SELECT COUNT(*)
    FROM tabla))
LIMIT 1

Esta consulta selecciona un decalaje aleatorio en función del número total de filas de la tabla y devuelve la fila en ese punto. Esta consulta es más rápida que la anterior porque solo lee la cantidad de filas necesarias para calcular el desplazamiento, en lugar de la tabla completa.

Nota: esta última consulta no funcionaría en SQLite.

Oracle

Oracle es uno de los SGBD más populares y potentes del mundo, utilizado por empresas, gobiernos e instituciones de todo tipo, debido a su fiabilidad, escalabilidad y rendimiento.

Sin embargo, en su variante del lenguaje SQL no es tan fácil lograr cosas como la que nos ocupa, ya que hace falta lanzar una consulta como esta:

SELECT campo FROM
    (SELECT campo FROM tabla
    ORDER BY dbms_random.value)
WHERE rownum = 1

Para elegir un registro aleatorio utiliza una subconsulta que ordena los valores de campo por un número aleatorio generado en este caso por la función dbms_random.value. Luego, la consulta principal usa la condición rownum = 1 para obtener solo el primer registro de la subconsulta, que será uno de los valores de campo ordenados al azar.

Hay que tener en cuenta que este enfoque puede no ser eficaz para tablas muy grandes, ya que requiere generar un orden aleatorio para toda la tabla antes de seleccionar un registro. En ese caso, puede ser mejor utilizar otros métodos como la cláusula SAMPLE o la función dbms_stats. gather_table_stats.

Para lo primero utilizaríamos una consulta similar a la siguiente:

SELECT campo FROM tabla 
SAMPLE (10) WHERE campo = 'nombre'

Se parece a la que usábamos en SQL Server pero un poco más complicada. Aplica un muestreo aleatorio del 10% de los registros de la tabla con SAMPLE, pero luego, filtra el resultado para quedarse solo con las filas donde el campo es igual a nombre.

Para lo segundo la consulta es todavía mucho más complicada (Oracle tiene merecida fama de no ser sencillo) y sería similar a esta:

DECLARE
  l_sql VARCHAR2(4000);
  l_cursor INTEGER;
  l_random NUMBER;
  l_record VARCHAR2(4000);
BEGIN
  l_sql := 'SELECT COUNT(*) FROM tabla';
  EXECUTE IMMEDIATE l_sql INTO l_random;
  l_random := DBMS_RANDOM.VALUE(1, l_random);
  l_sql := 'SELECT campo FROM tabla WHERE ROWNUM = :1';
  l_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(l_cursor, 1, l_random);
  DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_record, 4000);
  IF DBMS_SQL.EXECUTE(l_cursor) > 0 THEN
    DBMS_SQL.FETCH_ROWS(l_cursor);
    DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_record);
  END IF;
  DBMS_SQL.CLOSE_CURSOR(l_cursor);
  DBMS_OUTPUT.PUT_LINE(l_record);
END

¡Buff! Vamos a ver qué se hace aquí:

1. Declara variables: se declaran varias variables locales en el bloque PL/SQL, incluyendo l_sql (cadena de caracteres), l_cursor (número), l_random (número) y l_record (cadena de caracteres).

2. Construye la consulta dinámica: la variable l_sql se inicializa con una cadena que representa la consulta SQL estática: SELECT COUNT(*) FROM tabla. Esta consulta cuenta el número total de filas en la tabla tabla.

3. Ejecuta la consulta dinámica: el comando EXECUTE IMMEDIATE se utiliza para ejecutar la consulta SQL almacenada en la variable l_sql. El resultado de la consulta (el número total de filas en la tabla) se guarda en la variable l_random mediante la cláusula INTO.

4. Genera un número aleatorio: la función DBMS_RANDOM.VALUE se utiliza para generar un número aleatorio entre 1 y el valor de l_random (el número total de filas en la tabla). El resultado se asigna a la variable l_random.

5. Construye otra consulta dinámica: la variable l_sql se actualiza con otra cadena que representa una consulta SQL: SELECT campo FROM tabla WHERE ROWNUM = :1. Esta consulta selecciona un campo específico de la tabla 'tabla' donde el número de fila es igual al valor de l_random.

6. Prepara el cursor: se abre un cursor utilizando DBMS_SQL.OPEN_CURSOR.

7. Analiza la consulta: el método DBMS_SQL.PARSE se utiliza para analizar la consulta SQL almacenada en la variable l_sql. El cursor l_cursor y la bandera DBMS_SQL.NATIVE se pasan como argumentos.

8. Vincula la variable: el método DBMS_SQL.BIND_VARIABLE se utiliza para vincular el valor de l_random a la posición de enlace :1 en la consulta SQL. Esto permite que el valor de l_random se use en la consulta.

9. Define la columna de salida: el método DBMS_SQL.DEFINE_COLUMN se utiliza para definir la columna de salida del cursor. En este caso, la columna es l_record y tiene una capacidad de 4000 caracteres.

10. Ejecuta la consulta: el método DBMS_SQL.EXECUTE se utiliza para ejecutar la consulta SQL. Devuelve un valor mayor que cero si la ejecución tiene éxito.

11. Recupera las filas: si la ejecución de la consulta fue exitosa, el método DBMS_SQL.FETCH_ROWS se utiliza para recuperar las filas del cursor.

12. Obtiene el valor de la columna: el método DBMS_SQL.COLUMN_VALUE se utiliza para obtener el valor de la columna de la fila actual del cursor y almacenarlo en la variable l_record.

13. Cierra el cursor: el método DBMS_SQL.CLOSE_CURSOR se utiliza para cerrar el cursor.

14. Imprime el valor: el resultado final, almacenado en la variable l_record, se imprime utilizando DBMS_OUTPUT.PUT_LINE.

En resumen, esta consulta PL/SQL ejecuta dos consultas dinámicas. La primera consulta cuenta el número total de filas en una tabla y la segunda consulta selecciona un campo específico de la tabla en función de un número aleatorio generado. El valor seleccionado se imprime como resultado final.

¿Cómo te has quedado? 😱 Pues, con Oracle, es lo que hay... 😉

¡Espero que te resulte útil!

Fecha de publicación: 24 de mayo de 2023