From Wiki-UX.info

MDB101 Taller 02
Jump to: navigation, search

MDB101 Taller 02

Contents

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 retornadasTiempo (segundos)
111.84
25046 2.08
31252.50
400.22 sec
511.75
614.26
711.86
812.73
91784147.86
101784147.64
115019374.81
1336076128.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

This page was last modified on 20 October 2016, at 02:58. This page has been accessed 962 times.