Sentencias SQL y Postgresql

En este mes vamos a realizar unos pequeños ejemplos del uso de sentencias SQL en Postgresql para visualizar información proveniente de una tabla de datos. Para lo cual usaremos una tabla que tengan los departamentos del Perú (llamada PERU_DEPARTAMENTOS) y que está conformada por las siguientes columnas:

gid: que es la columna numérica que incrementa su valor al añadir un nuevo registro.

nombre: es el nombre del departamento. Columna de tipo character varying(50).

num_prov: número de provincias por departamento. Columna de tipo smallint.

num_dist: número de distritos por departamento. Columna de tipo smallint.

región_nat: región natural en que cada departamento está ubicado. Columna de tipo character varying(50).

Poblac: número de habitantes de cada departamento. Columna de tipo double precision.

The_geom: columna de tipo geometry donde están los polígonos que representan cada departamento. Estos polígonos están en coordenadas geográficas.

Tenemos por lo tanto siete columnas y 25 registros en esta tabla.

1. Para ver todas las columnas y registros de la tabla PERU_DEPARTAMENTOS y ordenados de menor a mayor usamos:

select * from "PERU_DEPARTAMENTOS" order by nombre

Si queremos ordenarlos de mayor por la columna nombre a menor usamos:

select * from "PERU_DEPARTAMENTOS" order by nombre DESC

Esto es lo mismo que hacer:

select gid, nombre, num_prov, num_dist, región_nat, poblac, the_geom from "PERU_DEPARTAMENTOS" order by nombre DESC

Donde el asterisco (*) reemplaza a TODOS los nombres de las columnas.

DESC sirve para ordenar descendentemente una columna. ASC se usa para hacerlo ascendentemente. Cuando se usa order by por defecto se usa el ASC:

select * from "PERU_DEPARTAMENTOS" order by nombre

es lo mismo que usar

select * from "PERU_DEPARTAMENTOS" order by nombre ASC

2.  Si queremos agrupar los departamentos por región natural (región_nat), calcular la población por dichas regiones y ordenar el resultado por población de mayor a menor, usamos:

select distinct(region_nat), sum(poblac) as poblacion from "PERU_DEPARTAMENTOS" group by region_nat order by poblacion DESC

cabe resaltar que se puede cambiar el nombre de una columna en la consulta usando el as:

Select nombre_columna_original as nombre_columna_nueva…

distinct sirve para eliminar los registros con igual valor, sum es para sumar los registros de una columna numérica. Si se usa distinct en una columna se debe usar group by para la misma columna.

3. Si queremos tener los nombres, la región natural, la población y el área en km2 de cada departamento, usamos:

select
nombre, region_nat, poblac, area(st_transform(the_geom,32718)) * 0.000001 as
area_km2 from "PERU_DEPARTAMENTOS"

Para hallar el área en km2, usamos la columna the_geom, dicha columna están en coordenadas geográficas y se usa la función st_transform(columna_geometria,proyección a proyectar) y luego a este resultado se calcula el área con la función area(), y finalmente se multiplica por  0.000001 para pasar de m2 a km2 para después renombrar el resultado como area_km2.

4.Para calcular la densidad de cada departamento, usamos:

select a.nombre, a.poblac, a.area_km2 ,a.poblac / a.area_km2 as densidad
from
(select nombre, poblac, area(st_transform(the_geom,32718)) * 0.000001 as area_km2 from "PERU_DEPARTAMENTOS")a
order by nombre

donde se observa una consulta anidada formada por dos subconsultas. Si se separaran por colores tenemos:

select a.nombre, a.poblac, a.area_km2 ,a.poblac / a.area_km2 as densidad

from

(select nombre, poblac, area(st_transform(the_geom,32718)) * 0.000001 as area_km2 from "PERU_DEPARTAMENTOS")a

order by nombre

Lo señalado en rojo es una consulta, cuyo resultado lo usamos como fuente para obtener el nombre, población y área (en km2). Necesariamente esta consulta anidada debe tener un nombre y se le ha llamado arbitrariamente como a. Luego en la consulta superior (marcado en negro) se hace el cálculo de la densidad poblacional (a.poblac/a.area_km2). Para llamar los campos de la consulta llamada a, se escribe: a.nombre_columna, donde nombre_columna es una columna que está en a.

5. Si se quiere ahora la densidad poblacional por región natural, usamos:

select a.region_nat, a.poblacion, a.area_km2 ,a.poblacion / a.area_km2 as densidad
from
(select distinct(region_nat) as region_nat, sum(poblac) as poblacion, sum(area(st_transform(the_geom,32718)) * 0.000001) as area_km2  from "PERU_DEPARTAMENTOS" group by region_nat) a

Es otra consulta anidada que al separarla en colores tenemos:

select a.region_nat, a.poblacion, a.area_km2 ,a.poblacion / a.area_km2 as densidad

from

(select distinct(region_nat) as region_nat, sum(poblac) as poblacion, sum(area(st_transform(the_geom,32718)) * 0.000001) as area_km2  from "PERU_DEPARTAMENTOS" group by region_nat) a

Lo señalado en rojo es parecido en el ejemplo número 2, solo que se ha agregado el área y a esta misma consulta se la ha llamado arbitrariamente como a.  Luego a la consulta a sirve para obtener la densidad como en el ejemplo 4.

Esperando que sea de ayuda este post. Sera hasta el otro mes. Saludos.