3 PostgreSQL

Tenemos en PostgreSQL dos bases de datos, identificadas por el sufijo de su nombre, una OLTP y otra OLAP.

En cada base de datos vamos a realizar las mismas operaciones:

  1. Obtenemos un informe inicial focalizado en las ventas: Su título comienza por “Ventas”.

  2. Partiendo siempre de ese informe inicial, obtenemos 3 informes que hemos incluido en un apartado titulado como se indica a continuación:

    1. Roll-Up: obtenemos menos nivel de detalle de las ventas considerando uno de los conceptos que las describen.
    2. Drill-Down: obtenemos más nivel de detalle de las ventas según uno de los conceptos que las describen.
    3. Slice&Dice: no cambiamos el nivel de detalle pero filtramos las ventas por uno de los conceptos descriptores.

  3. Para cada informe indicamos:

    • Un título descriptivo.
    • El nivel de detalle al que está definido considerando los conceptos que describen las ventas:
      • Cuándo (fecha u hora de las ventas),
      • Dónde (lugar donde se producen las ventas) y
      • Qué (artículos vendidos).

Obtenemos los mismos informes en las dos bases de datos.

A continuación se muestra, para cada base de datos, una representación de su esquema y un ejemplo de las operaciones descritas.

3.1 Base de datos OLTP

Base de datos OLTP.

Figura 3.1: Base de datos OLTP.

3.1.1 Informe de partida

  • Ventas por mes y provincia durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Provincia
    • Qué: Todo
SELECT 
    TO_CHAR(TO_DATE(t.fecha, 'YYYY-MM-DD'), 'YYYY-MM') AS "Mes",
    ti.provincia AS "Provincia",
    SUM(lt.cantidad) AS "Cantidad", 
    SUM(lt.cantidad * lt.pvp) AS "Importe"
FROM 
    linea_ticket lt
JOIN 
    ticket t ON lt.ticket = t.numero_ticket
JOIN 
    tienda ti ON t.tienda = ti.codigo
WHERE 
    EXTRACT(YEAR FROM TO_DATE(t.fecha, 'YYYY-MM-DD')) = 2024
GROUP BY 
    "Mes", ti.provincia
ORDER BY 
    "Mes", ti.provincia;

3.1.2 Roll-Up

  • Ventas por mes durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Todo
    • Qué: Todo
SELECT 
    TO_CHAR(TO_DATE(t.fecha, 'YYYY-MM-DD'), 'YYYY-MM') AS "Mes",
    SUM(lt.cantidad) AS "Cantidad", 
    SUM(lt.cantidad * lt.pvp) AS "Importe"
FROM 
    linea_ticket lt
JOIN 
    ticket t ON lt.ticket = t.numero_ticket
WHERE 
    EXTRACT(YEAR FROM TO_DATE(t.fecha, 'YYYY-MM-DD')) = 2024
GROUP BY 
    "Mes"
ORDER BY 
    "Mes";

3.1.3 Drill-Down

  • Ventas por mes y municipio durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Municipio
    • Qué: Todo
SELECT 
    TO_CHAR(TO_DATE(t.fecha, 'YYYY-MM-DD'), 'YYYY-MM') AS "Mes",
    ti.provincia AS "Provincia",
    ti.municipio AS "Municipio",
    SUM(lt.cantidad) AS "Cantidad", 
    SUM(lt.cantidad * lt.pvp) AS "Importe"
FROM 
    linea_ticket lt
JOIN 
    ticket t ON lt.ticket = t.numero_ticket
JOIN 
    tienda ti ON t.tienda = ti.codigo
WHERE 
    EXTRACT(YEAR FROM TO_DATE(t.fecha, 'YYYY-MM-DD')) = 2024
GROUP BY 
    "Mes", ti.provincia, ti.municipio
ORDER BY 
    "Mes", ti.provincia, ti.municipio;

3.1.4 Slice&Dice

  • Ventas por mes y provincia durante 2024 de productos fabricados en Andalucía.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Provincia
    • Qué: Todo
SELECT 
    TO_CHAR(TO_DATE(t.fecha, 'YYYY-MM-DD'), 'YYYY-MM') AS "Mes",
    ti.provincia AS "Provincia",
    SUM(lt.cantidad) AS "Cantidad", 
    SUM(lt.cantidad * lt.pvp) AS "Importe"
FROM 
    linea_ticket lt
JOIN 
    ticket t ON lt.ticket = t.numero_ticket
JOIN 
    tienda ti ON t.tienda = ti.codigo
JOIN 
    producto_almacen pa ON lt.producto = pa.codigo_barras
WHERE 
    EXTRACT(YEAR FROM TO_DATE(t.fecha, 'YYYY-MM-DD')) = 2024
    AND pa.provincia IN ('Almería', 'Cádiz', 'Córdoba', 'Granada', 'Huelva', 'Jaén', 'Málaga', 'Sevilla')
GROUP BY 
    "Mes", ti.provincia
ORDER BY 
    "Mes", ti.provincia;

3.2 Base de datos OLAP

Base de datos OLAP.

Figura 3.2: Base de datos OLAP.

3.2.1 Informe de partida

  • Ventas por mes y provincia durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Provincia
    • Qué: Todo
SELECT 
    dc.anio_mes AS "Mes", 
    dd.provincia AS "Provincia", 
    SUM(fv.cantidad) AS "Cantidad", 
    SUM(fv.pvp * fv.cantidad) AS "Importe"
FROM 
    fact_ventas_dia fv
JOIN 
    dim_cuando dc ON fv.dim_cuando_key = dc.dim_cuando_key
JOIN 
    dim_donde dd ON fv.dim_donde_key = dd.dim_donde_key
WHERE
    dc.anio = '2024'
GROUP BY 
    dc.anio_mes, dd.provincia
ORDER BY 
    dc.anio_mes, dd.provincia;

3.2.2 Roll-Up

  • Ventas por mes durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Todo
    • Qué: Todo
SELECT 
    dc.anio_mes AS "Mes", 
    SUM(fv.cantidad) AS "Cantidad", 
    SUM(fv.pvp * fv.cantidad) AS "Importe"
FROM 
    fact_ventas_dia fv
JOIN 
    dim_cuando dc ON fv.dim_cuando_key = dc.dim_cuando_key
WHERE
    dc.anio = '2024'
GROUP BY 
    dc.anio_mes
ORDER BY 
    dc.anio_mes;

3.2.3 Drill-Down

  • Ventas por mes y municipio durante 2024.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Municipio
    • Qué: Todo
SELECT 
    dc.anio_mes AS "Mes", 
    dd.provincia AS "Provincia", 
    dd.municipio AS "Municipio", 
    SUM(fv.cantidad) AS "Cantidad", 
    SUM(fv.pvp * fv.cantidad) AS "Importe"
FROM 
    fact_ventas_dia fv
JOIN 
    dim_cuando dc ON fv.dim_cuando_key = dc.dim_cuando_key
JOIN 
    dim_donde dd ON fv.dim_donde_key = dd.dim_donde_key
WHERE
    dc.anio = '2024'
GROUP BY 
    dc.anio_mes, dd.provincia, dd.municipio
ORDER BY 
    dc.anio_mes, dd.provincia, dd.municipio;

3.2.4 Slice&Dice

  • Ventas por mes y provincia durante 2024 de productos fabricados en Andalucía.

  • Niveles de detalle:

    • Cuándo: Mes
    • Dónde: Provincia
    • Qué: Todo
SELECT 
    dc.anio_mes AS "Mes", 
    dd.provincia AS "Provincia", 
    SUM(fv.cantidad) AS "Cantidad", 
    SUM(fv.pvp * fv.cantidad) AS "Importe"
FROM 
    fact_ventas_dia fv
JOIN 
    dim_cuando dc ON fv.dim_cuando_key = dc.dim_cuando_key
JOIN 
    dim_donde dd ON fv.dim_donde_key = dd.dim_donde_key
JOIN 
    dim_que dq ON fv.dim_que_key = dq.dim_que_key
WHERE
    dc.anio = '2024' 
    AND dq.fabricante_c_aut = 'Andalucía'
GROUP BY 
    dc.anio_mes, dd.provincia
ORDER BY 
    dc.anio_mes, dd.provincia;

3.3 Diferencias OLTP y OLAP

Observa las diferencias que hay entre las consultas desarrolladas para obtener los informes a partir de la base de datos OLTP respecto a la base de datos OLAP. Presta especial atención a las tablas que intervienen en las operaciones de JOIN y a los atributos disponibles en las bases de datos.

Ejercicios

Ejercicio: Informes SQL

Todos los apartados valen igual.

  1. Informe Inicial y Operaciones:
    • Define un informe inicial original focalizado en ventas, que no sea una ligera modificación del ejemplo propuesto (la originalidad determinará la calificación de todos los apartados de este ejercicio):
      • Define su título: “Ventas…”
      • Define sus niveles de detalle.
    • Partiendo siempre de ese informe inicial, define 3 informes, indicando para cada uno su título y niveles de detalle, según las operaciones siguientes, con el significado que se ha explicado para cada una al inicio de este documento:
      1. Roll-Up
      2. Drill-Down
      3. Slice&Dice
  2. Base de datos OLTP:
    • Obtén los 4 informes definidos en el primer punto a partir de la base de datos OLTP mediante SQL.
  3. Base de datos OLAP:
    • Obtén los 4 informes definidos en el primer punto a partir de la base de datos OLAP mediante SQL.

Documentación a entregar:

  • Genera un documento en formato PDF con un apartado o subapartado para cada punto anterior, es decir, con la estructura siguiente:

    1. Informe Inicial y Operaciones
      1. Informe Inicial
      2. Roll-Up
      3. Drill-Down
      4. Slice&Dice
    2. Base de datos OLTP
      1. Informe Inicial
      2. Roll-Up
      3. Drill-Down
      4. Slice&Dice
    3. Base de datos OLAP
      1. Informe Inicial
      2. Roll-Up
      3. Drill-Down
      4. Slice&Dice

  • No es necesario incluir las figuras de los esquemas de las bases de datos en el documento.

  • En los apartados sobre bases de datos OLTP y OLAP:

    • Incluye las sentencias SQL que implementan los informes y comprueba que se pueden copiar del documento al portapapeles y, al pegarlas en el entorno de consulta PostgreSQL, se ejecutan correctamente.

    • Para cada consulta, incluye una captura de pantalla completa de la aplicación de consulta (similar a la de la figura 3.3) donde se muestre:

      • La consulta completa.
      • Parte del resultado.
Ejemplo de consulta.

Figura 3.3: Ejemplo de consulta.