MDB101 Taller 02

From Wiki-UX.info
Jump to: navigation, search

Instrucciones

1. Se utilizará la table creada en el MDB101 Taller 01. La table fue creada y los datos cargados de la siguiente manera: 

CREATE TABLE stage_registro (
	cedula			TEXT,
	numero_folio		TEXT,
	cedula_padre		TEXT,
	cedula_madre		TEXT,
	codigo_hospital		TEXT,
	hora_suceso		TEXT,
	fecha_suceso		TEXT,
	genero			TEXT,
	nacionalidad		TEXT,
	defuncion		TEXT,
	pais_del_padre		TEXT,
	pais_de_la_madre	TEXT,
	prov_canton_madre	TEXT,
	fecha_naturalizacion	TEXT,
	apellido1		TEXT,
	apellido2		TEXT,
	nombre			TEXT,
	nombre_padre		TEXT,
	nombre_madre		TEXT,
	lugar_nacimiento	TEXT
);

COPY stage_registro
FROM '[PATH]\REGISTROCIVIL_UTF8.txt'
DELIMITERS ';'

Genere las siguientes sentencias

Tiempo de Ejecución
# Sentencia # Tuplas retornadas Tiempo (segundos)
1 1 1.84
2 5046 2.08
3 125 2.50
4 0 0.22 sec
5 1 1.75
6 1 4.26
7 1 1.86
8 1 2.73
9 178414 7.86
10 178414 7.64
11 501937 4.81
13 360761 28.55

1. Liste la información complete de una persona, para ello usted indicará el número de cédula. 

SELECT * FROM stage_registro WHERE cedula = '109740825';
  • Tiempo de Ejecución: 1 rows fetched (1.84 sec)

2. Liste la información completa de las personas que tienen su mismo nombre. 

SELECT * FROM stage_registro WHERE nombre = 'ALEJANDRO';
  • Registros: 4278531
  • Tiempo de Ejecución: 5046 rows fetched (2.72 sec)

3. Liste la información completa de las personas que tengas sus mismos apellidos. 

SELECT * FROM stage_registro WHERE apellido1 = 'MARIN' AND apellido2 = 'BADILLA';
  • Tiempo de Ejecución: 125 rows fetched (3.71 sec)

4. Agregar 3 columnas a la tabla: provincia, canton, y distrito. 

ALTER TABLE stage_registro
      ADD COLUMN provincia TEXT,
      ADD COLUMN canton TEXT,
      ADD COLUMN DISTRITO TEXT;
  • Tiempo de Ejecución: Query OK, 0 rows affected (0.22 sec)

5. Contar cuantos registros tiene la tabla empleado la columna cedula. 

SELECT COUNT(cedula) FROM stage_registro;
  • La consulta retorna 4278531 registros a través de la columna cédula.
  • Tiempo de Ejecución: 1 rows fetched (1.75 sec)


6. Contar cuantos registros tiene la tabla empleado la columna cedula. 

SELECT COUNT(provincia) FROM stage_registro;
  • La consulta retorna 0 registros a través de la columna cédula. Todos los valores son NULL dado que la columna acaba de ser agregada, sin utilizar un valor default.
  • Tiempo de Ejecución: 1 rows fetched (4.26 sec)

7. Actualize los datos de provincia, canton y distrito donde usted nació. 

UPDATE stage_registro SET provincia='SAN JOSE', canton='CENTRAL', distrito='URUCA' WHERE cedula='109740825';
  • Tiempo de Ejecución: 1 rows fetched (1.86 sec)

8. Contar cuantos registros tiene la tabla empleando la columna provincia. 

SELECT COUNT(provincia) FROM stage_registro;


  • La consulta retorna 1 registro a través de la columna cédula. Solamente la fila correspondiente al número de cédula actualizado contiene un valar distinto de NULL.
  • Tiempo de Ejecución: 1 rows fetched (2.73 sec)

9. Listar las personas que se llaman exactamente igual y cuantas veces se repiten. Ordenado descendentemente por numero de apariciones. 

SELECT nombre, COUNT(cedula) AS repeticiones
FROM stage_registro
GROUP BY nombre
HAVING count(cedula) > 1
ORDER BY 2 DESC;
  • Tiempo de Ejecución: 178414 rows fetched (7.86 sec)

10. Listar las personas que se llaman exactamente igual y cuantas veces se repiten.. 

SELECT nombre, COUNT(cedula) AS repeticiones
FROM stage_registro
GROUP BY nombre
HAVING count(cedula) > 1;
  • Tiempo de Ejecución: 178414 rows fetched (7.64 sec)

11. Averiguar el tamaño en bytes que ocupa la tabla al momento. 

VACUUM stage_registro;
SELECT pg_size_pretty(pg_total_relation_size('stage_registro'));
  • La table ocupa 864 MB.
  • Tiempo de Ejecución: 1 rows fetched (0.20 sec)

12. Crear una tabla con los distritos, cantones y provincias de Costa Rica. Debe manejarse una opción de registro para las personas nacidas en el extranjero.

La lista fue basada en el trabajo previo recopilado en Provincias, Cantones y Distritos. 

CREATE TABLE provincia	(
	provincia_id	SMALLINT PRIMARY KEY,
	provincia_nombre	CHAR(15)
);

CREATE TABLE canton (
  canton_id	SMALLINT PRIMARY KEY,
  provincia_id SMALLINT NOT NULL,
  canton_nombre	VARCHAR(45) NOT NULL
);

CREATE TABLE distrito (
  distrito_id		INTEGER PRIMARY KEY,
  canton_id			SMALLINT NOT NULL,
  distrito_nombre	VARCHAR(45) NOT NULL
);

COPY provincia FROM 'C:\Users\Alejandro\Documents\Maestria\MDB-101\taller_02\provincias.txt' DELIMITERS ','
COPY canton FROM 'C:\Users\Alejandro\Documents\Maestria\MDB-101\taller_02\cantones.txt' DELIMITERS ','
COPY distrito FROM 'C:\Users\Alejandro\Documents\Maestria\MDB-101\taller_02\distritos.txt' DELIMITERS ','
  • Para manejar las personas nacidas en el extranjero, se utiliza el código -1 para provincia, -100 para canton y -10000 para distrito. El nombre es extranjero en cada uno de ellos.

13. Modifique la table registro civel para que las columnas provincia, canton y distrito almacenen un dato tipo integer que será el código de provincia, cantón y distrito respectivamente. 


UPDATE stage_registro SET provincia=NULL, canton=NULL, distrito=NULL WHERE cedula='109740825';

ALTER TABLE public.stage_registro
	DROP COLUMN provincia,
	DROP COLUMN canton,
	DROP COLUMN distrito;
  
ALTER TABLE stage_registro
	ADD COLUMN provincia SMALLINT,
	ADD COLUMN canton INTEGER,
	ADD COLUMN DISTRITO INTEGER;

14. Nuevo tamaño de la tabla es identico porque no hay datos en las columnas.

15. Basados en la información de la columna LUGAR_NACIMIENTO, realice las sentencias para relacionar la información del lugar de nacimiento con la tabla de Provincias, Cantones y Distritos.

Asociar distritos cantos y provincias

  • . Los elementos no pueden ser trasladados directamente, ya que los datos de esa columna aparecen con nombres planos, no con identificadores numericos. Si bien la provincia se suceptible a ser obtenida usando comandos CASE (solo 7), las subsiguientes cantones y distritos requiren sus propias reglas. Además, los nombres presentan problemas de normalización.

La siguiente es una extrategia general para producir una asociacion entre estos elementos usando las caraterísticas de full-text search de Postgresql.

Primera Etapa

  • Crear una tabla con los ID de provincial y el remanente de distrito_canton.
  1. El primer paso es extraer las provincias de lugar_nacimiento y eliminar del string la provincia ara que no forma parte de match the string de la segunda etapa
  2. Los resultados se almacenan en la tabla temporal provid_cant_prov_tmp
CREATE TABLE provid_cant_prov_tmp AS (
	SELECT
		TO_NUMBER(cedula, '999999999') AS cedula,
		lugar_nacimiento,
		TO_NUMBER(SPLIT_PART(provincia_id_remaining, ';', 1), '9') AS provincia_id,
		SPLIT_PART(provincia_id_remaining, ';', 2) AS canton_distrito_string
	FROM
	(
		SELECT
			cedula,
			lugar_nacimiento,
			CASE
				WHEN lugar_nacimiento ~* 'S(A)*(N)*\s+JOSE\s*$' THEN '1;' || REGEXP_REPLACE(lugar_nacimiento, 'S(A)*(N)*\s+JOSE\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'ALAJUELA\s*$' THEN '2;' || REGEXP_REPLACE(lugar_nacimiento, 'ALAJUELA\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'CARTAGO\s*$' THEN '3;' || REGEXP_REPLACE(lugar_nacimiento, 'CARTAGO\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'HEREDIA\s*$' THEN '4;' || REGEXP_REPLACE(lugar_nacimiento, 'HEREDIA\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'GUANACASTE\s*$' THEN '5;' || REGEXP_REPLACE(lugar_nacimiento, 'GUANACASTE\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'PUNTARENAS\s*$' THEN '6;' || REGEXP_REPLACE(lugar_nacimiento, 'PUNTARENAS\s*$', '', 'i')
				WHEN lugar_nacimiento ~* 'LIMON\s*$' THEN '7;' || REGEXP_REPLACE(lugar_nacimiento, 'LIMON\s*$', '', 'i')
				WHEN TO_NUMBER(SUBSTRING(cedula FROM 1 FOR 1), '9') BETWEEN 1 AND 7 THEN SUBSTRING(cedula from 1 for 1) || ';'
				ELSE '-9;' || lugar_nacimiento
			END AS provincia_id_remaining
		FROM stage_registro
	) AS tmp
);
  • Query OK, 4278531 rows affected (308.26 sec)

Segunda Etapa

  • Analizar la tabla provid_cant_prov_tmp y asociar los lexemas de distrinto y canton. Para este ejemplo empezaremos con el cantón de San José, del cual analizaremos los primero 100k registros.
SELECT COUNT(CEDULA) FROM provid_cant_prov_tmp
WHERE provincia_id = 1;
  • Count: 1704751, 1 rows fetched (2.11 sec)
-- CREATE a table with cedula, lugar_nacimiento y to_tsvector('spanish', lugar_nacimiento)
DROP TABLE ced_lugnac_tsv;
 
 
CREATE TABLE ced_lugnac_tsv AS (
	SELECT
		cedula,
		canton_distrito_string,
		to_tsvector('spanish', coalesce(canton_distrito_string,'')) AS canton_distrito_tsv
	FROM provid_cant_prov_tmp
	WHERE provincia_id = 1
);
  • Query OK, 1704751 rows affected (23.82 sec)
  • Crear una table de asociacion que contenga la provincia_id, canton_id, distrito_id, provincia_nombre, canton_nombre, distrito_nombre, to_tsquery('spanish',regexp_replace(TRIM(BOTH FROM distrito_nombre) || ' ' || CASE WHEN canton_nombre = 'SAN JOSE' THEN 'CENTRAL' ELSE TRIM(BOTH FROM canton_nombre) END,'\s+','&','g')) AS dist_can_tsq


DROP TABLE prov_cant_dist_tsq;
 
CREATE TABLE dist_can_tsq AS (
	SELECT
		  provincia.provincia_id,
		  canton.canton_id,
		  distrito.distrito_id,
		  provincia_nombre,
		  canton_nombre,
		  distrito_nombre,
		  to_tsquery('spanish',regexp_replace(TRIM(BOTH FROM distrito_nombre) || ' ' || CASE WHEN canton_nombre = 'SAN JOSE' THEN 'CENTRAL' ELSE TRIM(BOTH FROM canton_nombre) END,'\s+','&','g')) AS distrito_canton_tsq
	FROM provincia
	JOIN canton
		ON canton.provincia_id = provincia.provincia_id
	JOIN distrito
		ON distrito.canton_id = canton.canton_id
	WHERE provincia.provincia_id = 1
);

Tercera Etapa

  • Realizar la asociacion entre patrones (FULL TEXT SEARCH)
SELECT * FROM (
SELECT
      cedulas.cedula,
      cedulas.canton_distrito_tsv,
      patrones.provincia_id,
      patrones.canton_id,
      patrones.distrito_id,
      patrones.provincia_nombre,
      patrones.canton_nombre,
      patrones.distrito_nombre,
	  ts_rank('{1,1,0.5,0.5}', canton_distrito_tsv, distrito_canton_tsq) AS ranking
FROM ced_lugnac_tsv AS cedulas
JOIN (
	SELECT
		  provincia_id,
		  canton_id,
		  distrito_id,
		  provincia_nombre,
		  canton_nombre,
		  distrito_nombre,
		  distrito_canton_tsq
	FROM dist_can_tsq
) AS patrones
ON cedulas.canton_distrito_tsv @@ distrito_canton_tsq
) AS a
ORDER BY cedula, ranking DESC;
  • Query OK, 1397146 rows affected (105.22 sec)
  • Calcular los ranking máximos de cada cedula.
CREATE TABLE max_rank_tmp AS (
	SELECT cedula, MAX(ranking) AS max_rank
	FROM ranked_matches_tmp
	GROUP BY cedula
);
  • Obtener provincia, canton distrito basado en el máximo ranking.
CREATE TABLE san_jose_tmp AS (
	SELECT
		ranked_matches_tmp.cedula,
		canton_distrito_tsv,
		provincia_id,
		canton_id,
		distrito_id,
		provincia_nombre,
		canton_nombre,
		distrito_nombre,
		ranking
	FROM ranked_matches_tmp
	JOIN max_rank_tmp
		ON ranked_matches_tmp.cedula = max_rank_tmp.cedula
		AND ranked_matches_tmp.ranking = max_rank_tmp.max_rank
);
  • 1371086 rows fetched (15.62 sec)

Con esta información, lo único que resta es unir los codigos de provincia, canton y distrito almacenados en la table san_jose_tmp con los datos de la tabla de registro.

References

Autores

Retrieved from "http://wiki-ux.info/w/index.php?title=MDB101_Taller_02&oldid=17278"