Ejercicios con la BD Hubway¶

Base de datos Hubway¶

Esta es una base de datos con datos reales que encontré en este (buenísimo) tutorial sobre SQLite. Son 130 MB en dos tablas:

stations . Con datos de estaciones de alquiler de bicicletas
trips. Con datos de viajes de personas que usan una bicicleta. Tiene más de un millón (sí un millón) de registros.

El diseño no es perfecto, pero es muy sencillo e ideal para ejercicios prácticos. Y así entiendes porque no te llega con una hoja de cálculo cuando hay muchos registros.

Descargar BD Hubway (SQLite)

La versión en SQLite, ideal para practicar, la puedes descargar aquí hubway.db

Ojo que son 130 MB.

Descargar Exportación SQL (para importar)

Si quieres realizar algún tipo de importación a otro SGBDR puedes descargar la exportación, en SQL: hubway.sql

Ojo que son 190 MB. Este serían el código de referencia para imporar al SGBDR que quieras.

Descargar BD Hubway (Postgres)

Más abajo te propongo hacer una importación en Postgres, a partir de la exportación en sql. Es un ejercicio muy interesante y completo. Pero si si te bloqueas o no tienes tiempo para dedicarle, puedes importarla en Posgres directamente (vía pg_dump): Hubway DB en Postgres

En este último caso tienes que crear la BD, por ejemplo hubway_pg y definir el usuario propietario (será con el que haces la exportación). Yo he usado el usuario cesareo (aparece en la exportación), puedes crear ese usuario o simplemente modificar el propietario (OWNER) al usuario que tú tengas. Quizá lo más fácil es sustituir cesareo por postgres (siempre existirá un usuario administrador que se llame postgres en tu instalación) pero así eres consciente el tema de usuarios y permisos.

Ojo que una cosa es el usario con el que estás trabajando en la BD, supongamos postgres, y otro el propietario de la BD (supongamos pepe). Si todo lo haces con el usuario postgres, quizá te sea más fácil, pero es importante que sepas qué estás haciendo.

Problemas en los datos¶

Esta es una base de datos real que ha estado en producción. Tiene varios problemas que hacen difícil, e interesante, su importación a un SGBDR con un diseño más restrictivo:

Valores incoherentes: sin valores (nulos) o con datos que no son coherentes, por ejemplo se repiten estaciones (con ids diferentes)
Lentitud: son muchos registros y la importación puede ser muy lenta
Falta de un modelo de datos único, porque depende de diferentes suposiciones
Falta de normalización: hay información que puede generar problemas de redundancia
Necesitaría un mejor diseño del modelo de datos, por ejemplo usando PK y FK pero, al ser tantos registros, dificulta la inserción de datos
Y más que te van a ir surgiendo, a medida que realizas la importación

Tratando de resolver esas dificultades, aprendes un móntón de diseño físico de un modelo de datos y la definición de restricciones y reglas de integridad. Si no lo haces desde un principio, surgirán problemas a posteriori.

Importación desde SQLite a Postgres¶

Necesitas crear una base de datos (por ejemplo hubway_pg), donde irás cargando las instrucciones SQL. Y después, necesitas ir insertando los datos, tienes varias opciones:

Usando psql, con la opción –file
Usando el metacomando \i
Usando el comando sql-copy

En concreto, los pasos serían:

Modelo de Datos. Archivo SQL en Modelo de Datos Postgres
Datos, ya editados y listos para importar (después de mucho editar texto):
1. Datos Depurados tabla stations: Datos Stations
2. Datos Depurados tabla trips: Datos Trips ). Este archivo es grande, puedes dejar sólo algunos registros para que puedas trabajar mejor. Ojo que lleva mucho tiempo la importación (Postgres te avisará si hay algún problema)
Una vez completada la importación, comprueba que esté todo bien. Por ejemplo, te conectas a la BD en Postgres y puedes repasar Ejercicios para usar SQL

Creando Funciones¶

al_norte. Necesito conocer qué estación está más al norte (mayor latitud)
dime_nombre. Necesito obtener el nombre de la estación identificada con i
en_minutos. Necesito saber la duración de un viaje en minutos
duracion_viaje. Necesito saber la duración de un viaje, en minutos o en horas, según el parámetro sea M o H
informe_viaje. Necesito saber un informe de un viaje, usando su id, que me diga: «Fue de tal a tal estación, con la bicicleta tal y tardó tantos minutos»

Creando Procedimientos¶

actualizar_precision. Resulta que tengo valores de los campos lat y lng, de la tabla stations, con diferentes decimales. Al insertar estos valores, la precisión puede cambiar. Necesito un procedimiento, que quiero ejecutar periódicamente, para actualizar la precisión a numeric(8,6), es decir con dos números enteros y 6 decimales.
actualizar_bicis(codigo). Quiero cambiar todos los valores de las bicis que están a null, con un valor que yo quiero escoger. Por ejemplo 000000.
corregir_codigo_bicis(codigo_antes, codigo_nuevo). Resulta que quiero cambiar el código que puse antes (porque ya no tengo nulos), por uno actualizado. Por ejemplo corregir_codigo(“000000”,”B00000”);
depurar_estaciones(). Quiero actualizar la tabla trips periódicamente, para que, si una de las estaciones (start_station o end_station) es null, supongo que salió (o llegó) a la misma estación. Además debe borrar viajes, donde no haya valores en las dos estaciones.
añade_estacion(archivo). Necesito añadir a la tabla stations, nuevas estaciones de alquiler. El sistema me deja los datos en un archivo de texto separado por tabuladores, del que conozco el nombre y necesito crear un procedimiento almacenado que inserte los datos desde un archivo csv estándar.

Usando PL/pgSQL¶

codigo_estacion (id, separador). Devuelve una cadena de texto con el nombre y la ciudad de una estación concreta (id), separadas por el caracter separador)
informe_texto_estaciones (ciudad). Haz un pequeño informe de texto donde analices la tabla estaciones para esa ciudad (municipality)). Devolverá la ciudad y la cantidad entre paréntesis. Por ejemplo Boston (92).
actualiza_viaje(viaje, factor). Actualizar la duración para un viaje concreto. Es decir la nueva duración será la que tiene multiplicada por factor. Devolverá el número de viajes realizado desde la misma estación de origen
contar_viajes_mes(mes, referencia). Devuelve un entero con los viajes que se hicieron en un mes concreto (siendo 1 - enero y 12 - diciembre). Lanzará también un aviso en consola (RAISE NOTICE) clasificándolo en pocos / normal / muchos. El umbral de decisión será de 0,25 el valor de referencia (para pocos), 0,5 para normal y 0,75 muchos
borra_y_guarda(id). Crea una función que borre el viaje con ese id. Además guardará en un log (tabla viajes_borrados) un registro del tipo «El usuario $U borró el viaje $V», además el timestamp del borrado

Solución

/* Funciones básicas para conocer la sintaxis */

-- Función sencilla donde puedes ver donde
-- se declaran las variables y como se pueden asignar datos
-- También puedes ver el valor que se devuelve (equivalente a select (n1 * n2);).

create or replace function probando_plpgsql() returns integer
AS $$
DECLARE
n1 integer := 3;
n2 integer := 4;
BEGIN
  return (n1 * n2);
END;
$$ LANGUAGE plpgsql;

-- Aquí puedes ver el uso de los argumentos
-- Realmente el bloque DECLARE aquí sobraría, podrías
-- nombrar los argumentos en la declaración
-- Es para que vieras el uso del ALIAS

create or replace function calcula(integer, integer, integer) returns integer
AS $$
DECLARE
n1 ALIAS FOR $1;
n2 ALIAS FOR $2;
n3 ALIAS FOR $3;
BEGIN
  return (n1 + n2) * n3;
END;
$$ LANGUAGE plpgsql;


-- Función sencilla de control

create or replace function decide(t boolean) RETURNS varchar(2)
AS $$
BEGIN
IF t THEN
  RETURN 'SÍ';
ELSE
  RETURN 'NO';
END IF;

END;
$$ LANGUAGE plpgsql;

-- Esta función te muestra como crear un bucle simple
-- y también manejar variables dentro del programa (el índice del bucle)

create or replace function repite(n integer, texto varchar(255)) returns integer
AS $$
DECLARE
i integer := 1;
BEGIN
  WHILE (i<=n) LOOP
    RAISE NOTICE '%: %', i,texto;
    i := i + 1;
  END LOOP;
  return n;
END;
$$ LANGUAGE plpgsql;


-- Función sencilla de control
-- Lo podía resolver también con un CASE .. WHEN
-- Pero para ver la sintaxis del IF .. THEN .. ELSE

create or replace function calculadora(op1 integer, op2 integer, o varchar(128)) RETURNS integer
AS $$
BEGIN
IF o = 'multiplica' THEN
  RETURN (op1 * op2) ;
END IF;

IF o = 'suma' THEN
  RETURN (op1 + op2) ;
ELSE
  RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

/* Funciones sobre la BD Hubway*/

-- Simplemente usando SELECT .. INTO para asignar el resultado

create or replace function codigo_estacion(integer,varchar(1)) returns varchar(255)
AS $$
DECLARE
valor varchar(255);
BEGIN
  select concat(station,$2,municipality) into valor from stations where id = $1;
  return valor;
END;
$$ language plpgsql ;

-- Usa una consulta un poco más compleja, también añadí
-- una forma de usar el argumento cualificado con el nombre de la función (podría usar $1)

create or replace function informe_texto_estaciones(ciudad varchar(255)) RETURNS varchar(100)
AS $$
DECLARE
valor varchar(255);
BEGIN
  select (municipality || ' (' || count(id) || ')') into valor from stations where municipality = informe_texto_estaciones.ciudad group by municipality;
  return valor;
END;
$$ language plpgsql ;

-- Se pueden ejecutar operaciones y luego devolver un valor que no tiene que ver
-- También se pueden incluir instrucciones SQL más complejas, y sin usar una variable

create or replace function actualiza_viaje(viaje integer, factor integer) RETURNS integer
AS $$
BEGIN
  UPDATE trips SET duration = duration * factor WHERE id = $1;
  return count(*) from trips where start_station = (select start_station from trips where id = $1);
END;
$$ language plpgsql ;

-- Una función un poco más enrevesada por el uso de funciones de fecha
-- y el uso de varios if (quería hacerlo con CASE pero no lo lograba)

create or replace function contar_viajes_mes(mes integer, referencia integer) RETURNS integer
AS $$
DECLARE

viajes integer;

BEGIN
  viajes := count(id) from trips where extract(month from start_date) = mes;
IF  (viajes <= referencia * 0.25)
THEN  RAISE NOTICE 'Pocos Viajes';
END IF;

IF (viajes between (referencia * 0.25) AND (referencia * 0.75) )
THEN RAISE NOTICE 'Lo normal';
END IF;

IF (viajes >= referencia * 0.75)
THEN RAISE NOTICE 'Muchos Viajes';
END IF;

return viajes;
END;
$$ language plpgsql ;

-- Necesitas crear previamente la tabla create table viajes_borrados(fecha timestamp, informe varchar(255));
-- Podrías no hacerlo, e incluirlo en el código (pero implica controlar ese posible error usando excepciones)
-- Las excepciones no las dimos, que es muy poco tiempo para tantas cosas (pero si tienes curiosidad, no es difícil añadirlo)

create or replace function borra_y_guarda(integer) RETURNS integer
AS $$
BEGIN
  INSERT INTO viajes_borrados(fecha,informe) values (current_timestamp, concat('El usuario ', current_user, ' borró el viaje ', $1));
  DELETE from trips where id = $1;
  return 0;
END;
$$ language plpgsql ;

Creando Disparadores¶

Crea los siguientes disparadores:

act_viaje_t. Cada vez que se borre un viaje, lo guarda en la tabla viajes borrados con el texto «El usuario TAL borró el viaje TAL».
check_letra_t. Al insertar una estación, que se asegure que el municipio (municipality) esté todo en minúsculas
act_duracion_t. Al insertar un viaje, comprobará que al menos la duración es de 10 segundos. Si no lo es, lo actualizará para que sea ese valor
num_viajes_estacion_t. Después de insertar un viaje, comprobará que no existen más de 100 viajes desde esa estación de origen. Si ocurriera, dejará un aviso (vía RAISE)
check_municipio_t. Cuando se actualice el municipio de una estación, garantizará que el sea de un municipio que esté previamente en la tabla. En el caso de que no exista ese municipio previamente no se aceptarán municipios nuevos. Para ello lanzarás un error con RAISE EXCEPTION «No existe este municipio en la tabla»

Solución

-- Se crea la función trigger
-- Se usa la varieable especial OLD
-- NEW y OLD guardan información del registro, según la acción DML que está sucediendo

CREATE OR REPLACE FUNCTION guarda_viaje_t() RETURNS trigger AS
$$
BEGIN
  INSERT INTO viajes_borrados(fecha,informe) values (current_timestamp, concat('El usuario ', current_user, ' borró el viaje ', OLD.id));
  RETURN OLD;
END;
$$
LANGUAGE plpgsql;

-- Cada vez que se borra un registro
-- ejecuta esa función

CREATE TRIGGER act_viaje_t
BEFORE DELETE ON trips
FOR EACH ROW
EXECUTE FUNCTION guarda_viaje_t();

-- Simplemente se asegura que se guarde en minúsculas
-- La lógica sirve si quieres realizar algo cualquier
-- tipo de procesado de texto sobre el valor (p.ej)
-- que guarde en formato Capital, la primera siempre en mayúsculas

CREATE OR REPLACE FUNCTION check_minusculas_t() RETURNS trigger AS
$$
BEGIN
  NEW.municipality := lower(NEW.municipality);
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER check_minusculas
BEFORE INSERT ON stations
FOR EACH ROW
EXECUTE FUNCTION check_minusculas_t();

-- Control sencillo para que el mínimo de duración sea 10

CREATE OR REPLACE FUNCTION check_duracion_t() RETURNS trigger AS
$$
BEGIN
  IF NEW.duration < 10
  THEN NEW.duration :=10;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER check_duracion
BEFORE INSERT ON trips
FOR EACH ROW
EXECUTE FUNCTION check_duracion_t();

-- Si hay más de 100 viajes desde
-- esta estación, notificar el aviso

CREATE OR REPLACE FUNCTION viajes_estacion_t() RETURNS trigger AS
$$
DECLARE

viajes_estacion integer;

BEGIN
  viajes_estacion := count(*) from trips where start_station = NEW.start_station;
  IF viajes_estacion >= 100
  THEN RAISE NOTICE 'Desde esta estacion han salido % viajes',viajes_estacion;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER num_viajes_estacion_t
AFTER INSERT ON trips
FOR EACH ROW
EXECUTE FUNCTION viajes_estacion_t();

-- Se usa UPDATE en el trigger
-- Se define una restricción extra
-- Una forma de abortar la instrucción es con RAISE EXCEPTION
-- Genera un error y no permite que continue

CREATE OR REPLACE FUNCTION esta_en_tabla_t() RETURNS trigger AS
$$
BEGIN
IF (NEW.municipality  IN (select distinct municipality from stations))
        THEN
        RAISE NOTICE 'Sí existe';
        ELSE
        RAISE EXCEPTION 'No existe este municipio en la tabla';
        END IF;
        RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER check_municipio_t
BEFORE UPDATE ON stations
FOR EACH ROW
EXECUTE FUNCTION esta_en_tabla_t();