Categoría: sqlite

En la sección anterior vimos como resumir datos a través de algunas funciones de SQL de agregación. Por lo general estas funciones se usan o van de la mano con la agrupación de datos. En esta sección veremos como agrupar datos y usar las funciones de agregación para mostrar resultados.

Cómo agrupar los resultados de una consulta SQL.

SQL permite agrupar los resultados de una consulta por una o varias columnas o campos. Para eso usamos la cláusula GROUP BY de la sentencia SQL SELECT.

Cláusula SQL GROUP BY.

La cláusula GROUP BY agrupa los datos por una determinada columna o columnas. Veamos un ejemplo, para ello, abre la base de datos con la que hemos venido trabajando y abre el archivo sql también. Escribe y ejecuta la siguiente sentencia:

En la sentencia anterior estamos agrupando los resultados por la columna id_marca, el resultado da los distintos valores de la columna id_marca. Es decir que esta agrupando por los distintos valores de la columna id_marca. Esta consulta no tiene mucho valor práctico, solo nos muestra los distintos valores que contiene id_marca y nos dice que hay 250 filas o valores distintos de id_marca. Apliquemos ahora las funciones de agregación que vimos en la sección anterior:

La consulta anterior nos muestra el id_marca, el número de productos que hay de cada id_marca contando los valores nulos de id_marca y por último, cuántos productos de cada id_marca hay, sin tomar en cuenta los valores nulos de id_marca. Es por eso la diferencia en la primera fila la cual tiene el valor nulo (null) en id_marca, COUNT(*) regresa 1 porque cuenta todas las filas que contienen nulo (null) en la columna id_marca , mientras que COUNT(id_marca) cuenta los valores de id_marca ignorando los de valor nulo (null) lo que da como resultado cero.

No sólo podemos usar el campo por el que estamos agrupando en las funciones de agregación, podemos usar cualquiera de los otros campos. Escribe y ejecuta el siguiente ejemplo:

En la sentencia anterior, seguimos agrupando las filas por la columna id_marca, sin embargo, estamos aplicando las funciones de agregación al campo o columna id_categoria. ¿Que significan los resultados? Bueno, en el caso del grupo de id_marca con valor 5, el grupo cuenta con 28 filas con id_categoria no nulas, la id_categoria con el valor máximo o más alto de ese grupo es 781, la id_categoria con el valor menor de ese grupo es 3 y la suma de los valores de id_categoria de ese grupo da 14,083. Y así sucesivamente para cada grupo diferente de id_marca.

Las funciones de agregación no se tienen que aplicar todas a una misma columna, pueden aplicarse a distintas columnas:

También es posible agrupar por más de un campo:

En la sentencia anterior estamos agrupando por dos columnas: id_marca e id_categoria. Según las filas resultantes, existen 995 combinaciones diferentes de id_marca-id_categoria, es decir 995 grupos diferentes. La tercera columna de los resultados nos muestra cuantas filas o registros, que en este caso representan productos, hay en cada grupo distinto de id_marca-id_categoria. Por ejemplo, decimos que hay 6 productos o filas que tienen en su columna id_marca igual el valor 5 y el calor 3 en su columna id_categoria y así sucesivamente.

En la próxima sección vamos a combinar las cláusulas ORDER BY y GROUP BY para mostrar resultados más presentables. ¡Hasta la próxima!

IR A LA PAGINA DEL CURSO.

SQL cuenta con funciones que permiten resumir en un sólo dato los valores de una columna de todas o algunas de las filas de una tabla. A estas funciones se les conoce como funciones de agregación (“aggregate functions” en inglés). Para hacer las siguientes prácticas, vamos a abrir y a usar la base de datos que hemos estado usando en este curso básico y nuestro archivo de scripts sql.

Funciones SQL de agregación.

Las funciones de agregación trabajan con un conjunto de valores de una columna y regresan un sólo valor. Casi todas ellas ignoran los valores NULOS (null) a excepción de una función que veremos enseguida. Cada motor de base de datos implementan sus propias funciones de agregación y su propia sintaxis, veremos las más comunes entre las distintos motores y que la implementa SQLite.

Función SQL de agregación COUNT()

Esta función cuanta o regresa el número de elementos que existen en un grupo de datos, de acuerdo a la sintaxis empleada, puede incluir los elemento con valor NULO (null) o no. Veamos estas dos diferentes sintaxis:

Sintaxis: COUNT(*) o COUNT([DISTINCT] expresión)

Los corchetes, en la sintaxis, se refiere a que la palabra que contiene es opcional, puede o no puede escribirse. Si se escribe la palabra reservada DISTINCT, la función COUNT() sólo contará los elementos con valores distintos, ignorará los valores duplicados.

Cuando se usa el asterisco (*) la función cuenta todas los filas aún cuando sus elementos tengan valor NULO (null). Cuando se usa una expresión, que por lo general es un nombre de una columna o campo o alias, sólo contará aquellos elementos que tengan valores distintos a NULO. Veamos un ejemplo:

El primer uso de la función COUNT(*) regresa un número que representa el total de filas de la tabla “productos” mientras que COUNT(id_marca) regresa el total de valores no nulos que tiene el campo id_marca. En este caso, los resultados son iguales, ambas funciones regresan el número 14,767.

Vamos a actualizar una fila de la tabla “productos” poniendo el valor nulo a la columna id_marca a la fila con el valor 2 en id_producto. Para eso ejecuta la siguiente sentencia:

Vuelve a ejecutar la sentencia COUNT() inicial:

Ahora los resultados de ambas funciones COUNT() son diferentes. ¿Por qué? Porque como dijimos antes, COUNT(*) cuanta el total de fila del grupo, en este caso de toda la tabla y, COUNT(id_marca) cuenta los valores no nulos del campo id_marca. Y ya existe un valor nulo en ese campo con ls sentencia UPDATE que ejecutamos anteriormente.

Si a la sentencia SELECT le agregamos un filtro o condición a través de la cláusula WHERE las funciones COUNT() sólo se limitarán a contar las filas o elementos de ese grupo. Veamos un ejemplo:

La sentencia anterior limita las filas a sólo aquellas que su marca sea igual a 34. Las funciones COUNT() se limita a contar solo las filas que cumplan con esa condición. en ese caso, el resultado de ambas funciones es 93, debido a que dentro de los productos con marca igual a 34 no hay valores nulos en su campo id_categoria.

Ahora incluiremos la palabra reservada DISTINCT dentro de la función COUNT(). Veamos el ejemplo:

En la segunda forma de la función COUNT() que incluye la palabra DISTINCT sólo cuenta los valores distintos de la columna id_marca que existen en la tabla “productos”. Quiere decir que de los 14,766 valores no nulos que contiene la columna id_marca sólo hay 249 valores distintos.

Funciones SQL de agregación AVG(), MAX(), MIN() y SUM().

Las funciones de agregación AVG() y SUM() sólo aceptan expresiones numéricas, MAX() y MIN() aceptan tanto expresiones de texto como numéricas.

Sintaxis: AVG(expresion-numérica), SUM(expresión-numérica), MAX(expresión), MIN(expresión)

La función AVG() obtiene el promedio de los valores de una columna. SUM(), MAX() y MIN() obtienen la suma, el valor máximo, y valor mínimo de los valores de una columna respectivamente. Estás funciones sólo tomarán en cuanta los valores no nulos. Al igual que la función COUNT(), estas funciones aplican sobre un grupo de filas o toda la tabla si la sentencia SELECT tiene o no tiene una condición o filtro. Veamos el siguiente ejemplo:

En el ejemplo anterior obtenemos el promedio, valor máximo, valor mínimo y la suma de los valores de la columna o campo id_categoria de todas las filas de la tabla “productos”, ya que no existe existe una condición en la sentencia SELECT que limite el número de filas. Como ejercicio escribe una sentencia SELECT que contenga una cláusula WHERE que limite las filas, por medio del campo id_marca.

En la próxima sección veremos como agrupar datos con SQL mediante una sentencia SQL. ¡Hasta la próxima!

IR A LA PAGINA DEL CURSO.

SQLite no tiene un tipo de dato fecha o tiempo, en su lugar el almacena las fecha en un formato de cadena determinado y las manipula a través de funciones.

Las cadenas de texto deben cumplir con los siguientes formatos de valor tiempo para ser tomados como fechas o tiempo por las funciones:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Puede consultar los valores de tiempo completos en la página oficial de SQLite.

Función SQL DATE().

Regresa la fecha en el formato YYYY-MM-DD.

Sintaxis: DATE(valor-tiempo, modificador, modificador, …)

Donde valor-tiempo es una cadena de texto con el formato arriba descrito. Y los modificadores pueden ser, entre otros:

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
localtime

Puede consultar los modificadores completos en la página oficial de SQLite. Veamos algunos ejemplos de el uso de la función date().

En el ejemplo anterior obtenemos primeramente en la primera columna la fecha actual del sistema (al tiempo de publicar esta entrada el 2 de septiembre de 2021) aplicando el modificador mostrado, y en los columnas posteriores hacemos operaciones de agregar y restar días a la fecha actual. Por último se muestra como agregar un mes y un año a la fecha actual.

Función SQL DATETIME().

Regresa la fecha y el tiempo de 24 horas en el formato YYYY-MM-DD HH:MM:SS.

Sintaxis: DATETIME(valor-tiempo, modificador, modificador, …)

Donde valor-tiempo es una cadena de texto con el formato arriba descrito. Los modificadores son los mismos descritos en en la función DATE().

Veamos el siguiente ejemplo:

En la primer columna la función DATETIME() obtiene la fecha y hora UTC (Tiempo Universal Coordinado) que corresponde a la hora en el meridiano de Greenwich. En la segunda columna la misma función agrega el modificador “localtime” lo que provoca que la fecha y hora correspondan a la zona geográfica en la que nos encontramos.

Podemos también usar los modificadores que utilizamos anteriormente en la función DATE(), pero además podemos usar los modificadores que suman o restan horas, minutos y segundos:

La primera columna muestra la fucnión DATETIME() obteniendo la fecha y hora actual, las siguientes columnas muestran la misma fecha y hora pero aumentada 1 hora, decrementada 30 minutos y aumentada 60 segundos, respectivamente.

Función JULIANDAY().

Regresa el día Juliano al que corresponde la fecha y tiempo dados.

Sintaxis: JULIANDAY(valor-tiempo, modificador, modificador, …)

Ésta función también acepta los mismos modificadores que la función DATE() y DATETIME(). Veamos un ejemplo:

En este ejemplo, estamos obteniendo el día juliano correspondiente al día en que se publicó esta entrada, que es el día 2 de Septiembre de 2021.

Obtener los días de diferencia entre dos fechas con SQLite.

Para obtener la diferencia de días entre dos fechas usamos la función JULIANDAY() de la siguiente forma:

En el ejemplo anterior estamos obteniendo los días transcurridos desde el 16 de Septiembre de 1810 hasta la fecha y hora que se escribió esta entrada que es el 2 de Septiembre de 2021.

En esta sección vimos las funciones de fecha y tiempo más usadas, existen otras las cuales pueden consultar en la documentación del sitio oficial de SQLite.

En la siguiente sección veremos como resumir (summarize en inglés) o acumular datos con SQL. ¡Hasta la próxima!

IR A LA PAGINA DEL CURSO.