Funciones SQL para Ciencia de Datos: Consultas y Reportes Prácticos

Clase 15 de 34 • Curso de PostgreSQL Aplicado a Ciencia de Datos

Contenido del curso

Introdución a las Bases de Datos

Ciencia de Datos

Particularidades de PostgreSQL

Casos Prácticos

Platzi movies dashboard

Siguientes pasos

Tomar examen

Resumen

¿Cómo maximizar el uso de funciones en la ciencia de datos?

Uno de los aspectos más fascinantes de la ciencia de datos es su capacidad para entrelazar funciones y estructuras para realizar análisis complejos. Las bases de datos y los lenguajes como PL/pgSQL permiten una flexibilidad impresionante en la manipulación de datos. Vamos a explorar cómo las funciones pueden ser utilizadas en escenarios del mundo real, particularmente cuando se trata de resumir, reportar y visualizar datos de manera efectiva.

¿Cuál es el rol de las funciones en el análisis diario?

En el mundo del análisis de datos, los científicos frecuentemente necesitan:

Resumir grandes cantidades de datos.
Generar reportes claros y concisos.
Calcular totales, promedios y métricas relevantes.
Organizar datos de forma que sean fácilmente visualizables y aprovechables para la toma de decisiones.

Estas tareas se logran eficazmente mediante el uso de funciones bien diseñadas que operan dentro de las bases de datos.

¿Cómo se declaran y utilizan variables en una función de PL/pgSQL?

Es esencial entender cómo declarar y utilizar variables en funciones para realizar cálculos y almacenar resultados. Aquí te muestro un ejemplo práctico:

CREATE OR REPLACE FUNCTION estadisticas_peliculas()
RETURNS void AS $$
DECLARE
    total_rating_r AS REAL := 0.0;
    total_duracion_mayor_100 AS REAL := 0.0;
    total_publicadas_2006 AS REAL := 0.0;
    promedio_duracion AS REAL := 0.0;
    promedio_precio_renta AS REAL := 0.0;
BEGIN
    -- Calculando el total de películas con clasificación 'R'
    total_rating_r := (SELECT COUNT(*) FROM peliculas WHERE clasificacion = 'R');
    
    -- Calculando el total de películas con una duración mayor a 100 minutos
    total_duracion_mayor_100 := (SELECT COUNT(*) FROM peliculas WHERE duracion > 100);
    
    -- Calculando el total de películas publicadas en el año 2006
    total_publicadas_2006 := (SELECT COUNT(*) FROM peliculas WHERE ano_publicacion = 2006);
    
    -- Calculando el promedio de duración de las películas
    promedio_duracion := (SELECT AVG(duracion) FROM peliculas);
    
    -- Calculando el promedio del precio de renta
    promedio_precio_renta := (SELECT AVG(precio_renta) FROM peliculas);
    
    -- Insertando los datos calculados en una tabla de estadísticas
    TRUNCATE TABLE peliculas_estadisticas;
    INSERT INTO peliculas_estadisticas (tipo_estadistica, total)
    VALUES
    ('Películas con clasificación R', total_rating_r),
    ('Duración > 100 minutos', total_duracion_mayor_100),
    ('Publicadas en 2006', total_publicadas_2006),
    ('Promedio de duración en minutos', promedio_duracion),
    ('Promedio del precio de renta', promedio_precio_renta);
END; $$ LANGUAGE plpgsql;

Las variables aquí se utilizan para almacenar resultados intermedios de conteos y promedios, permitiendo que estos resultados sean fácilmente manipulables o reportables más adelante.

¿Qué beneficios aporta el almacenamiento de resultados en tablas?

El almacenamiento de los resultados procesados en tablas tiene múltiples ventajas que simplifican el manejo de datos:

Centralización de Datos: Permite acceder a estadísticas clave desde una ubicación central en la base de datos.
Facilidad en Actualizaciones: Optimiza el proceso de actualización de estadísticas, importante en reportes continuos.
Eficiencia Operativa: Al reducir la carga computacional de realizar cálculos repetidos innecesariamente.

Las funciones no sólo deben calcular y almacenar, sino también integrarse con trigger que aseguren la actualización en tiempo real según nuevos data entry, garantizando así análisis siempre actualizados. ¿Eres capaz de aplicar estos principios revolucionarios a tus datos? Cuéntame tus ideas sobre cómo implementarías estas prácticas en tus propios proyectos o ámbitos laborales. La ciencia de datos tiene un mundo de posibilidades que esperan por ser exploradas.

Comentarios

Melanie Daniela Ventura Jimenez

student•

Esquema de declaración de variables.

María José Medina

student•

buen aporte! Gracias

Oscar Ortiz Valle

student•

:= no es símbolo de igual, sino de asignación, es el valor que le estas dando a esa variable para inicializarla

José Rodrigo Arana Hi

student•

El profesor mencionó que se puede hacer un trigger en la tabla 'peliculas' y actualizar las estadísticas cada vez que inserten una nueva película.

Intenté hacerlo y creo que lo logré, los pasos que hice para hacerlo fueron los siguientes:

La función actual lo dropeé y lo cree de nuevo, puesto que lo que debe retornar es un 'trigger', y dentro de la función debe retornar un NEW:

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE-- ANTES DE BEGIN, DEBEMOS DECLARAR NUESTRAS VARIABLES
    total_rated_r REAL := 0.0; -- := sirve para asignar valor
    total_larger_than_100 REAL := 0.0; -- REAL permite operar con decimales
    total_published_2006 REAL := 0.0;
    duration_avg REAL := 0.0;
    rental_price_avg REAL := 0.0;
BEGIN
    total_rated_r := count(*) from peliculas WHERE clasificacion = 'R';
    total_larger_than_100 := count(*) FROM peliculas WHERE duracion > 100;
    total_published_2006 := count(*) FROM peliculas WHERE anio_publicacion = 2006;
    duration_avg := avg(duracion) FROM peliculas;
    rental_price_avg := avg(precio_renta) FROM peliculas;
    
    TRUNCATE table peliculas_estadisticas;
    
    INSERT INTO peliculas_estadisticas(tipo_estadistica,total) VALUES 
        ('Peliculas con clasificacion R', total_rated_r),
        ('Peliculas de mas de 100 minutos', total_larger_than_100),
        ('Peliculas publicadas en 2006',total_published_2006),
        ('Promedio de duracion en minutos', duration_avg),
        ('Precio promedio de renta', rental_price_avg);
    
    RETURN NEW;
END
$$;

En este momento, si corres el SELECT movies_stats(); te dirá que solo puede ser llamado como un trigger. Se crea ahora el trigger y lo corres:

CREATE TRIGGER new_stats
AFTER INSERT
ON peliculas
FOR each row
execute procedure movies_stats();

Ahora falta solo insertar un row nuevo:

insert into peliculas (titulo, descripción, anio_publicacion, lenguaje_id,precio_renta, duracion, textocompleto) values 
('Una pruebita', 'muy interesante', 2006, 1, 99, 20, 'texto de relleno');

Revisa la tabla peliculas_estadisticas y veras el cambio:

SELECT * FROM public.peliculas_estadisticas
ORDER BY tipo_estadistica ASC

Posiblemente haya otra manera, favor de compartirlos y lo analizamos entre todos.

Gabriel López

student•

Genial, muchas gracias por el aporte.!

Matias Alexander Ibarra Trujillo

student•

pandas hace lo mismo

Luis Quiroz Prada

student•

Si, la diferencia creo que esta en los datos, en postgrest los datos están guardados en un archivo sql que es de base de datos que utilizan muchas empresas, mientras que panda se utiliza mas que todo en archivos csv que el que utilizan muchos PhD para guardar sus datos. No he visto que panda trabaje con archivos sql.

Elias Ojeda Medina

student•

Sí, pero recuerda que son herramientas, acorde al caso puedes usar una u otra dependiendo de la complejidad o la que pueda resolver mejor la problematica, si son datos operativos en el que el motor de datos puede servir la aplicación y esto a su vez ya está configurado, puede que sea más útil generar una solución de este tipo.

Depende del caso, por ello es bueno saber que alternativas existen

Camilo Duque

student•

Se me ocurre un dashboard para una tienda online, donde cada venta ejecutada dispare un trigger que actualice una función de clientes la cual alimenta algunos indicadores de ventas, cantidad de clientes y productos mas vendidos.

Julián Cárdenas

student•

El trigger es pura automatización :D

Leandro Tenjo

student•

Podría hacerse al final de cada día, para sobrecargar con operaciones en cada venta individual.

JAVIER SANTIAGO SALGADO

student•

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS VOID 
LANGUAGE plpgsql
AS $$
DECLARE
	total_rated_r REAL := 0.0;
	total_larger_thank_100 REAL := 0.0;
	total_published_2006 REAL := 0.0;
	average_duracion REAL := 0.0;
	average_rental_price REAL := 0.0;
BEGIN
	total_rated_r := COUNT(*) FROM peliculas WHERE clasificacion = 'R';
	total_larger_thank_100 := COUNT(*) FROM peliculas WHERE duracion > 100;
	total_published_2006 := COUNT(*) FROM peliculas WHERE anio_publicacion = 2006;
	average_duracion := AVG(duracion) FROM peliculas;
	average_rental_price := AVC(precio_renta) FROM peliculas;
	
	TRUNCATE TABLE peliculas_estadisticas;
	
	INSERT INTO peliculas_estadisticas (tipo_estadisticas, total)
	VALUES
		('Peliculas con clasificacion R', total_rate_r),
		('Peliculas de mas de 100 minutos', total_larger_than_100),
		('Peliculas publicadas en 2006', total_published_2006),
		('Promedio de duracion en minutos', average_duracion),
		('Precio promedio de renta', average_rental_price);
END
$$;

SELECT movies_stats();

SELECT * FROM peliculas_estadisticas;

Antonio Demarco Bonino

student•

En mi trabajo lo uso constantemente para chequear bugs de la base de datos y saber el estado de los mismos. Como herramienta es maravillosa.

Ronny Jair Mora Rojas

student•

¿Usas qué exactamente? ¿Cómo encuentras los bugs? Me pareció interesante, gracias de antemano por responder

Miguel Angel Paz Gonzalez

student•

Idea: El hacer un mapa actualizado del COVID 19 por municipio en México, con las estadisticas del CINVESTAV. Para tener mayor detalle de los contagios y muertes en México

José Javier Sanahuja

student•

Tomando el control con las funciones de PostgreSQL: Cerrando la brecha con la funcionalidad de los ORM

Revelando la disparidad: comprendiendo la brecha entre el controlador directo y la funcionalidad ORM

Cuando se trata de elegir las tecnologías para desarrollar un backend y manipular datos en una base de datos como PostgreSQL, los desarrolladores a menudo se enfrentan a la elección entre usar el controlador oficial o una capa de abstracción como un ORM, en este caso, Prisma. Ambos enfoques tienen sus ventajas y desventajas, pero se ha argumentado que los ORM son superiores debido a una miríada de características diseñadas para las operaciones comunes CRUD que se realizan típicamente en una API REST. En este artículo, exploraremos cómo las funciones en PostgreSQL nos permiten cerrar la brecha entre usar el controlador oficial directamente y las funcionalidades proporcionadas por un ORM como Prisma. Nos enfocaremos específicamente en las operaciones de inserción de datos, examinando cómo las funciones pueden proporcionar resultados similares a los obtenidos al usar un ORM, incluida la capacidad de devolver objetos creados y mucho más.

import 'dotenv/config';
import { Client } from 'pg';

const pg = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  database: process.env.DB_NAME,
});

pg.connect().then(() => {
  console.log('db connected');
  pg.query(
    `INSERT INTO reservations (_date, hora, res_number, res_name, room, meal_plan, pax_number, cost, observations)
      VALUES
        ('2023-07-27', '21:00', 001, 'Jhon Doe', 'P01', 'SC', 2, 50.00, 'Sin observaciones')`,
  ).then((result) => {
    console.log(result);
    pg.end().then(() => {
      console.log('disconnected from db');
    });
  });
});

/*
db connected
Result {
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  ... más metadatos
}
disconnected from db
*/

En este ejemplo ejecutado en Node.js, se puede ver que no hay mucha información útil sobre el resultado de esa operación y demasiados metadatos.

Ahora, comparemos eso con una inserción realizada en Prisma.

prisma.pokemons.create({
    data:{
      id: 1100,
      name: 'new_pokemon'
    }
  }).then((result: any) => {
    console.log(result);
  })

/*
{ id: 1100, name: 'new_pokemon' }
*/

Con Prisma, podemos deshacernos de los metadatos y extraer el objeto insertado en la base de datos.

Intentemos emular ese tipo de resultado.

Introducción a las funciones en PostgreSQL

Vayamos directamente a un ejemplo de código simple de una función en Postgres.

CREATE FUNCTION sum(num1 numeric, num2 numeric) -- declarar la función y los parámetros
RETURNS numeric -- tipo de dato devuelto
AS $$
BEGIN -- inicio del código
  RETURN num1 + num2; -- resultado
END; -- fin del código
$$ LANGUAGE plpgsql; -- fin de la función

SELECT sum(2, 3); -- devuelve 5

Podría haber más secciones en una declaración de función, como la captura de excepciones o la declaración de variables. Dejaremos eso para más adelante.

Pero ahora vamos a adentrarnos en otro ejemplo. Un poco más elaborado.

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  pax INTEGER,
  capacity INTEGER,
  do_res DATE, -- fecha de la reserva
  to_res TIME -- hora de la reserva
);

-- llenando la base de datos con datos incorrectos que generarán excepciones en nuestra función
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(0, 0, '2022-01-01', '10:00:00');

/* Úsalo para jugar con la función si es necesario
-- llenando la base de datos con datos correctos
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(2, 4, '2022-01-01', '10:00:00'),
(3, 6, '2022-01-02', '11:00:00'),
(4, 8, '2022-01-03', '12:00:00'),
(5, 10, '2022-01-04', '13:00:00'),
(6, 12, '2022-01-05', '14:00:00'),
(7, 14, '2022-01-06', '15:00:00'),
(8, 16, '2022-01-07', '16:00:00'),
(9, 18, '2022-01-08', '17:00:00'),
(10, 20, '2022-01-09', '18:00:00');
*/

Intentemos crear una función que pueda calcular el porcentaje de ocupación.

SELECT SUM(pax)*100 / SUM(capacity) AS ocupation FROM reservations;

Esta es una consulta normal, pero ahora generemos la función.

Sé que podríamos usar NULLIF para evitar excepciones, pero pronto quiero lanzar esta excepción.

CREATE OR REPLACE FUNCTION porcentaje_ocupacion(fecha_i DATE, fecha_f DATE)
RETURNS FLOAT AS $$
DECLARE -- declarando variables
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentaje FLOAT;
BEGIN
  SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
  porcentaje := total_pax * 100.0 / total_capacity;
  RETURN porcentaje;
END;
$$ LANGUAGE plpgsql;

Ahora hemos creado una función más compleja, pero aún no es suficiente.

Es muy común y una buena práctica dejar que los servidores manejen cualquier tipo de errores. Si ocurre un error dentro de la base de datos, se escalará hasta el servidor lanzando una excepción. Si llenas el servidor solo con los datos incorrectos restantes y tratas de ejecutar la función, esto es lo que obtendrás.

SELECT porcentaje_ocupacion('2022-01-01', '2022-12-31');
-- ERROR:  división por cero
-- CONTEXT:  función PL/pgSQL porcentaje_ocupation(date,date) línea 8 en la asignación

No sugiero que deberíamos crear una forma diferente de manejar estas excepciones, pero podríamos hacerlo si queremos. En la próxima parte manejaremos excepciones.

##Mejorando las capacidades de la función con respuestas personalizadas y gestión de excepciones

Y ahora es el momento de presentarte algunas otras características que podemos utilizar en PostgreSQL, como un bloque try/catch dentro de PostgreSQL y algunos datos del sistema que podemos obtener dentro de las funciones.

CREATE OR REPLACE FUNCTION porcentage_ocupation2(fecha_i DATE, fecha_f DATE)
RETURNS JSON AS $$ -- devolver un JSON es una excelente opción para retornar resultados personalizados
DECLARE
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
  stack text; -- obtendremos la pila de errores aquí
  result JSON; -- construiremos el resultado aquí
BEGIN -- comienza la función
  BEGIN -- este segundo BEGIN funciona como el try en un bloque try/catch
    SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
    porcentage := total_pax * 100.0 / total_capacity;
    result := json_build_object('isError', FALSE, 'result', porcentage);
  EXCEPTION -- y aquí está el catch en el bloque try/catch
    WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT; -- cómo obtener la pila de errores en PostgreSQL
      result := json_build_object('isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE, 'stack', stack); -- SQLERRM, SQLSTATE son variables del sistema proporcionadas por PostgreSQL
  END; -- fin del bloque try/catch
  RETURN result; -- devolver la respuesta
END;
$$ LANGUAGE plpgsql;

Y con esta segunda función creada, veamos el resultado cuando terminamos dividiendo por 0.

SELECT porcentage_ocupation2('2022-01-01','2022-12-31');
/*                                                                      
porcentage_ocupation2                                                
----------------------
 {"isError" : true, "message" : "division by zero", "errorCode" : "22012", "stack" : "PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment"}
*/

Como dije antes, no defenderé si esta es una buena práctica o no, simplemente diré que podríamos manejar las excepciones que ocurren dentro de la ejecución del código. Será decisión del gerente de proyecto o de los desarrolladores senior si esto es útil o no.

Pasemos al controlador en Node.js, porque hemos alcanzado los requisitos previos para lograr nuestro objetivo.

pg.connect().then(() => {
  console.log('db connected');
  pg.query(`SELECT porcentage_ocupation2('2022-01-01','2022-12-31' as result)`).then(
    (result) => {
      console.log(result.rows[0].result); // buscando el resultado directamente en el punto donde debería estar
      pg.end().then(() => {
        console.log('disconnected from db');
      });
    },
  );
});
/*
db connected
{
  isError: true,
  message: 'division by zero',
  errorCode: '22012',
  stack: 'PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment'
}
disconnected from db
*/

Estamos aprovechando el hecho de que al devolver un JSON desde la función de PostgreSQL, se devolverá 1 y solo 1 fila. Es por eso que siempre podemos buscar el resultado en ese lugar del objeto. Esta es exactamente la característica que queríamos construir. Ahora avancemos para implementarla en las operaciones de CRUD, ya que es más significativo que hacerlo en una consulta SELECT.

Convirtiendo conceptos en código: Escribiendo la lógica de un INSERT en funciones de PostgreSQL

Estamos a punto de terminar, así que no perdamos más tiempo.

CREATE OR REPLACE FUNCTION create_reservation(
  _pax INTEGER,
  _capacity INTEGER,
  _do_res DATE,
  _to_res TIME
) RETURNS JSON AS $$
DECLARE
  inserted_reservation reservations; -- aquí recuperaremos el objeto insertado
  response JSON;
  stack_info TEXT;
BEGIN
  BEGIN
    IF _do_res < CURRENT_DATE THEN
      response := json_build_object(
        'isError', FALSE, -- podría considerarse un error (un error de solicitud incorrecta)
        'message', 'Solicitud incorrecta: No se insertó ningún registro - No puedes crear una reserva en el pasado',
        'rowsAffected', 0,
        'result', NULL
      );
    ELSE
      BEGIN
        INSERT INTO reservations (
          pax,
          capacity,
          do_res,
          to_res
        ) VALUES (
          _pax,
          _capacity,
          _do_res,
          _to_res
        )
        RETURNING * INTO inserted_reservation; -- recuperar el objeto insertado

        IF inserted_reservation IS NULL THEN
          response := json_build_object(
            'isError', TRUE, 'message', 'No se insertó ningún registro',
            'rowsAffected', 0
            );
        ELSE
          response := json_build_object(
            'isError', FALSE, 'result', inserted_reservation, 'rowsAffected', 1
            );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          GET STACKED DIAGNOSTICS stack_info = PG_EXCEPTION_CONTEXT;
          response := json_build_object(
            'isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE,
            'stack', stack_info
            );
      END;
    END IF;

    RETURN response;
  END;
END;
$$ LANGUAGE plpgsql;

SELECT create_reservation(
  2, 4, '2023-07-27', '19:00'
) AS result;

Ten en cuenta que la función está agregando una "restricción" a create_reservation, no permitiendo crear una reserva antes de la fecha actual (CURRENT_DATE). Este es un control adicional que podemos agregar utilizando funciones.

Con esto, hemos llegado al final de esta publicación.

En conclusión, al aprovechar el poder de las funciones en PostgreSQL, hemos reducido la brecha entre el ORM y la programación directa de la base de datos. A través de nuestra exploración, hemos obtenido una comprensión más profunda de cómo aprovechar las funciones de manera efectiva, lo que nos permite personalizar las respuestas, manejar excepciones y agregar más control en nuestra base de datos.

Si bien trabajar directamente con la base de datos puede requerir un esfuerzo adicional, ofrece un control incomparable sobre el proceso de manipulación de datos. Las funciones permiten a los desarrolladores tener un control detallado y aprovechar todo el potencial de PostgreSQL.

El momento en que las funciones de PostgreSQL realmente superan a los ORMs es cuando el servidor se enfrenta al desafío de ejecutar consultas complejas o proporcionar inteligencia empresarial. Es en estos escenarios donde se hace evidente el verdadero poder de la programación de funciones en PostgreSQL. Las funciones ofrecen un nivel de flexibilidad y control que va más allá de las capacidades de los ORMs, permitiendo a los desarrolladores crear consultas intrincadas y optimizadas adaptadas a requisitos comerciales específicos.

Ricardo Gomez

student•

Analizando esta base de datos podrías hacer análisis en tiempo real utilizando funciones y triggers, para luego capturarlos en un dashboard

Análisis de inventarios Análisis de cuales son los actores mas vistos y en que categoría de películas Cuales so la ciudades mas representativas y sus paises, mostrándonos sus ventas

Jhony Pacheco

student•

por que al descargar el csv se pierde la escritura de la palabra, sabiendo que en el editor se ingreso bien

Isay Humberto Lucas Hernandez

student•

Por el tipo de codificación, intenta cambiarla a UTF-8 al momento de abrir el archivo con tu editor de texto.

Jair calderon flores

student•

¿El REAL para que sirve?

Samuel Bustos

student•

Define el tipo de variable

Natalia Carolina Gutierrez Ulloa

student•

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS VOID 
LANGUAGE plpgsql
AS $$
DECLARE
	total_rated_r REAL := 0.0;
	total_larger_than_100 REAL := 0.0;
	total_published_2006 REAL := 0.0;
	average_duracion REAL := 0.0;
	average_rental_price REAL := 0.0;
BEGIN
	total_rated_r := COUNT(*) FROM peliculas WHERE clasificacion = 'R';
	total_larger_than_100 := COUNT(*) FROM peliculas WHERE duracion > 100;
	total_published_2006 := COUNT(*) FROM peliculas WHERE anio_publicacion = 2006;
	average_duracion := AVG(duracion) FROM peliculas;
	average_rental_price := AVG(precio_renta) FROM peliculas;
	
	TRUNCATE TABLE peliculas_estadisticas;
	
	INSERT INTO peliculas_estadisticas (tipo_estadistica, total)
	VALUES
		('Peliculas con clasificacion R', total_rated_r),
		('Peliculas de mas de 100 minutos', total_larger_than_100),
		('Peliculas publicadas en 2006', total_published_2006),
		('Promedio de duracion en minutos', average_duracion),
		('Precio promedio de renta', average_rental_price);
END
$$;

SELECT movies_stats();

SELECT * FROM peliculas_estadisticas;

Fernando Robles

student•

¿Cuándo vale la pena mantener una tabla de estadísticas en lugar de ejecutar el query que genera el cálculo?

Vicente Fernandez

student•

Pienso que la tabla de estadística (con un trigger para mantenerla actualizada) te hará mas fácil el acceso a esos datos, y no tener que estar generando queries a cada rato.

David Rueda

student•

Es útil cuando se tiene una base de datos muy grande y se requiera consultar varias tablas. Entre menos tiempo se demore procesando y retornando datos mejor será el sentimiento del usuario hacia el sistema de información.

Robert Castro

student•

para mi caso la cantidad de vehiculos que tienen siniestros viales (choques) llevar ese record de que marca, que año, el genero que mas accidentes tienen

FELIX DAVID CORDOVA GARCIA

student•

Esta clase si es super util :3

Jordan Sobrino Santos

student•

En un tienda por descuento sería útil tener por ejemplo el top 10 de sku's con mayor rotación, la categoría y linea de productos con mayor facturación, el ticket promedio de los clientes según rango de edades, los días y el horario de mayor facturación entre otros indicadores claves que podrían interesar al negocio.

Victor Rolo Montañez Quiroz

student•

Se me ocurre presentar las ventas diarias y los movimientos de almacén que se consideren necesarios, para evaluar el stock de los productos.

Sergio Javier Lopez Olivera

student•

Realizando un trigger para evitar peliculas de categoría 'M'

David J. Monroy P.

student•

El Truncate Table seria en otras palabras sobreescribir una tabla?

Isay Humberto Lucas Hernandez

student•

El truncate se refiere a "truncar": Eliminar todos los registros. A diferencia del Delete, el truncate no valida records uno por uno ni las posibles constraints que la tabla pueda tener (Delete en cascada, etc).

Jesús Rafael Hernández Montero

student•

¿Se puede agregar argumentos a las funciones? Digamos que quiero las mismas estadisticas pero no de la tabla actualizada sino por meses o por años sin que estos valores sean fijos en la función.

Cesar Augusto Herrera Rosales

student•

Hola Jesús, claro que sí! Según la documentación, los argumentos son especificados entre paréntesis al costado del nombre de la función:

CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) ...

Por ejemplo: CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer LANGUAGE plpgsql AS $$ BEGIN RETURN i + 1; END; $$;

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE-- ANTES DE BEGIN, DEBEMOS DECLARAR NUESTRAS VARIABLES
    total_rated_r REAL := 0.0; -- := sirve para asignar valor
    total_larger_than_100 REAL := 0.0; -- REAL permite operar con decimales
    total_published_2006 REAL := 0.0;
    duration_avg REAL := 0.0;
    rental_price_avg REAL := 0.0;
BEGIN
    total_rated_r := count(*) from peliculas WHERE clasificacion = 'R';
    total_larger_than_100 := count(*) FROM peliculas WHERE duracion > 100;
    total_published_2006 := count(*) FROM peliculas WHERE anio_publicacion = 2006;
    duration_avg := avg(duracion) FROM peliculas;
    rental_price_avg := avg(precio_renta) FROM peliculas;
    
    TRUNCATE table peliculas_estadisticas;
    
    INSERT INTO peliculas_estadisticas(tipo_estadistica,total) VALUES 
        ('Peliculas con clasificacion R', total_rated_r),
        ('Peliculas de mas de 100 minutos', total_larger_than_100),
        ('Peliculas publicadas en 2006',total_published_2006),
        ('Promedio de duracion en minutos', duration_avg),
        ('Precio promedio de renta', rental_price_avg);
    
    RETURN NEW;
END
$$;

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS VOID 
LANGUAGE plpgsql
AS $$
DECLARE
	total_rated_r REAL := 0.0;
	total_larger_thank_100 REAL := 0.0;
	total_published_2006 REAL := 0.0;
	average_duracion REAL := 0.0;
	average_rental_price REAL := 0.0;
BEGIN
	total_rated_r := COUNT(*) FROM peliculas WHERE clasificacion = 'R';
	total_larger_thank_100 := COUNT(*) FROM peliculas WHERE duracion > 100;
	total_published_2006 := COUNT(*) FROM peliculas WHERE anio_publicacion = 2006;
	average_duracion := AVG(duracion) FROM peliculas;
	average_rental_price := AVC(precio_renta) FROM peliculas;
	
	TRUNCATE TABLE peliculas_estadisticas;
	
	INSERT INTO peliculas_estadisticas (tipo_estadisticas, total)
	VALUES
		('Peliculas con clasificacion R', total_rate_r),
		('Peliculas de mas de 100 minutos', total_larger_than_100),
		('Peliculas publicadas en 2006', total_published_2006),
		('Promedio de duracion en minutos', average_duracion),
		('Precio promedio de renta', average_rental_price);
END
$$;

SELECT movies_stats();

SELECT * FROM peliculas_estadisticas;

import 'dotenv/config';
import { Client } from 'pg';

const pg = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  database: process.env.DB_NAME,
});

pg.connect().then(() => {
  console.log('db connected');
  pg.query(
    `INSERT INTO reservations (_date, hora, res_number, res_name, room, meal_plan, pax_number, cost, observations)
      VALUES
        ('2023-07-27', '21:00', 001, 'Jhon Doe', 'P01', 'SC', 2, 50.00, 'Sin observaciones')`,
  ).then((result) => {
    console.log(result);
    pg.end().then(() => {
      console.log('disconnected from db');
    });
  });
});

/*
db connected
Result {
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  ... más metadatos
}
disconnected from db
*/

CREATE FUNCTION sum(num1 numeric, num2 numeric) -- declarar la función y los parámetros
RETURNS numeric -- tipo de dato devuelto
AS $$
BEGIN -- inicio del código
  RETURN num1 + num2; -- resultado
END; -- fin del código
$$ LANGUAGE plpgsql; -- fin de la función

SELECT sum(2, 3); -- devuelve 5

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  pax INTEGER,
  capacity INTEGER,
  do_res DATE, -- fecha de la reserva
  to_res TIME -- hora de la reserva
);

-- llenando la base de datos con datos incorrectos que generarán excepciones en nuestra función
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(0, 0, '2022-01-01', '10:00:00');

/* Úsalo para jugar con la función si es necesario
-- llenando la base de datos con datos correctos
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(2, 4, '2022-01-01', '10:00:00'),
(3, 6, '2022-01-02', '11:00:00'),
(4, 8, '2022-01-03', '12:00:00'),
(5, 10, '2022-01-04', '13:00:00'),
(6, 12, '2022-01-05', '14:00:00'),
(7, 14, '2022-01-06', '15:00:00'),
(8, 16, '2022-01-07', '16:00:00'),
(9, 18, '2022-01-08', '17:00:00'),
(10, 20, '2022-01-09', '18:00:00');
*/

CREATE OR REPLACE FUNCTION porcentaje_ocupacion(fecha_i DATE, fecha_f DATE)
RETURNS FLOAT AS $$
DECLARE -- declarando variables
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentaje FLOAT;
BEGIN
  SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
  porcentaje := total_pax * 100.0 / total_capacity;
  RETURN porcentaje;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION porcentage_ocupation2(fecha_i DATE, fecha_f DATE)
RETURNS JSON AS $$ -- devolver un JSON es una excelente opción para retornar resultados personalizados
DECLARE
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
  stack text; -- obtendremos la pila de errores aquí
  result JSON; -- construiremos el resultado aquí
BEGIN -- comienza la función
  BEGIN -- este segundo BEGIN funciona como el try en un bloque try/catch
    SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
    porcentage := total_pax * 100.0 / total_capacity;
    result := json_build_object('isError', FALSE, 'result', porcentage);
  EXCEPTION -- y aquí está el catch en el bloque try/catch
    WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT; -- cómo obtener la pila de errores en PostgreSQL
      result := json_build_object('isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE, 'stack', stack); -- SQLERRM, SQLSTATE son variables del sistema proporcionadas por PostgreSQL
  END; -- fin del bloque try/catch
  RETURN result; -- devolver la respuesta
END;
$$ LANGUAGE plpgsql;

SELECT porcentage_ocupation2('2022-01-01','2022-12-31');
/*                                                                      
porcentage_ocupation2                                                
----------------------
 {"isError" : true, "message" : "division by zero", "errorCode" : "22012", "stack" : "PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment"}
*/

pg.connect().then(() => {
  console.log('db connected');
  pg.query(`SELECT porcentage_ocupation2('2022-01-01','2022-12-31' as result)`).then(
    (result) => {
      console.log(result.rows[0].result); // buscando el resultado directamente en el punto donde debería estar
      pg.end().then(() => {
        console.log('disconnected from db');
      });
    },
  );
});
/*
db connected
{
  isError: true,
  message: 'division by zero',
  errorCode: '22012',
  stack: 'PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment'
}
disconnected from db
*/

CREATE OR REPLACE FUNCTION create_reservation(
  _pax INTEGER,
  _capacity INTEGER,
  _do_res DATE,
  _to_res TIME
) RETURNS JSON AS $$
DECLARE
  inserted_reservation reservations; -- aquí recuperaremos el objeto insertado
  response JSON;
  stack_info TEXT;
BEGIN
  BEGIN
    IF _do_res < CURRENT_DATE THEN
      response := json_build_object(
        'isError', FALSE, -- podría considerarse un error (un error de solicitud incorrecta)
        'message', 'Solicitud incorrecta: No se insertó ningún registro - No puedes crear una reserva en el pasado',
        'rowsAffected', 0,
        'result', NULL
      );
    ELSE
      BEGIN
        INSERT INTO reservations (
          pax,
          capacity,
          do_res,
          to_res
        ) VALUES (
          _pax,
          _capacity,
          _do_res,
          _to_res
        )
        RETURNING * INTO inserted_reservation; -- recuperar el objeto insertado

        IF inserted_reservation IS NULL THEN
          response := json_build_object(
            'isError', TRUE, 'message', 'No se insertó ningún registro',
            'rowsAffected', 0
            );
        ELSE
          response := json_build_object(
            'isError', FALSE, 'result', inserted_reservation, 'rowsAffected', 1
            );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          GET STACKED DIAGNOSTICS stack_info = PG_EXCEPTION_CONTEXT;
          response := json_build_object(
            'isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE,
            'stack', stack_info
            );
      END;
    END IF;

    RETURN response;
  END;
END;
$$ LANGUAGE plpgsql;

SELECT create_reservation(
  2, 4, '2023-07-27', '19:00'
) AS result;

CREATE OR REPLACE FUNCTION movies_stats()
RETURNS VOID 
LANGUAGE plpgsql
AS $$
DECLARE
	total_rated_r REAL := 0.0;
	total_larger_than_100 REAL := 0.0;
	total_published_2006 REAL := 0.0;
	average_duracion REAL := 0.0;
	average_rental_price REAL := 0.0;
BEGIN
	total_rated_r := COUNT(*) FROM peliculas WHERE clasificacion = 'R';
	total_larger_than_100 := COUNT(*) FROM peliculas WHERE duracion > 100;
	total_published_2006 := COUNT(*) FROM peliculas WHERE anio_publicacion = 2006;
	average_duracion := AVG(duracion) FROM peliculas;
	average_rental_price := AVG(precio_renta) FROM peliculas;
	
	TRUNCATE TABLE peliculas_estadisticas;
	
	INSERT INTO peliculas_estadisticas (tipo_estadistica, total)
	VALUES
		('Peliculas con clasificacion R', total_rated_r),
		('Peliculas de mas de 100 minutos', total_larger_than_100),
		('Peliculas publicadas en 2006', total_published_2006),
		('Promedio de duracion en minutos', average_duracion),
		('Precio promedio de renta', average_rental_price);
END
$$;

SELECT movies_stats();

SELECT * FROM peliculas_estadisticas;

Funciones SQL para Ciencia de Datos: Consultas y Reportes Prácticos

Introdución a las Bases de Datos

Aplicación de PostgreSQL en Ciencia de Datos

Importación de Bases de Datos en PgAdmin 4

Historia y Evolución de las Bases de Datos Relacionales

Fundamentos de Bases de Datos Relacionales para Científicos de Datos

Conceptos Fundamentales de Bases de Datos Relacionales

Sentencias SQL: Select, Where, Group By y Order By

Ciencia de Datos

Rol y Funciones del Científico de Datos en Empresas

Responsabilidades del Científico de Datos en la Toma de Decisiones

Roles y Perfiles en Equipos de Ciencia de Datos

Diferencias y similitudes entre Machine Learning y Data Science

Particularidades de PostgreSQL

Diferencias entre PostgreSQL y otros gestores de bases de datos

Fundamentos de la Programación en Python

Procedimientos y Funciones en PostgreSQL: Creación y Uso

Creación y uso de funciones y triggers en bases de datos SQL