di

Resumen

Uno de los conocimientos necesarios en entornos de data warehousing es el manejo de consultas SQL para análisis de datos. En este actividad se proponen una serie de ejercicios que permiten practicar con las extensiones OLAP de SQL, como ROLLUP, CUBE y GROUPING SETS, así como con funciones de ventana y la función GROUPING para identificar los valores de agrupación NULL en consultas OLAP.

Objetivos

  • Realizar consultas SQL con las extensiones OLAP de SQL.

  • Practicar con los operadores ROLLUP, CUBE y GROUPING SETS.

  • Crear consultas SQL con funciones de ventana.

  • Usar la función GROUPING para identificar los valores de agrupación NULL en consultas OLAP.

1. Descripción de la actividad

Realizar las consultas siguientes sobre la base de datos AdventureWorksDW2019 tomando como fecha de las ventas la fecha en que se efectuó el pedido (order date)

  1. Escribir una consulta SQL que devuelva el importe de ventas realizadas en Internet por mes y país del cliente

    • ¿Cuántas filas develve la consulta? 72

    • Importe de ventas en Julio en Estados Unidos 747463,912

    • Importe de ventas en Enero en Australia 567222,3744

  2. Escribir una consulta SQL que devuelva el importe de ventas realizadas en Internet por mes y país del cliente y que use ROLLUP para calcular además el importe de ventas por mes y el total general

    • ¿Cuántas filas develve la consulta? 85

    • Importe de ventas en Abril 1948432,2303

    • Importe de ventas total 29358677,2207

  3. Escribir una consulta SQL que devuelva el importe de ventas realizadas en Internet por país del cliente y mes, y que use ROLLUP para calcular además el importe de ventas por país y el total general

    • ¿Cuántas filas develve la consulta? 79

    • Importe de ventas en Alemania 2894312,3382

    • Importe de ventas total 29358677,2207

  4. Rescribir la consulta del ejercicio anterior con UNION ALL

    • ¿Cuántas filas develve la consulta? 79

    • Importe de ventas en Reino Unido 3391712,2109

    • Importe de ventas total 29358677,2207

  5. Escribir una consulta SQL que devuelva el importe de ventas realizadas en Internet por país del cliente y mes y que use CUBE para calcular además el importe de todos los subtotales y el total general

    • ¿Cuántas filas develve la consulta? 91

    • Importe de ventas en Canadá en el mes de Abril 84216,0113

    • Importe de ventas en Septiembre 2536756,6378

    • Importe de ventas en Francia 2644017,7143

    • Importe de ventas total 29358677,2207

  6. Escribir una consulta SQL que devuelva sólo los subtotales de ventas realizadas en Internet por país del cliente y mes, así como el el total general

    • ¿Cuántas filas develve la consulta? 19

    • Importe de ventas en Agosto 2689540,8765

    • Importe de ventas en Canadá 1977844,8621

  7. Rescribir la consulta del ejercicio anterior con UNION ALL

    • ¿Cuántas filas develve la consulta? 19

    • Importe de ventas en Australia 9061000,5844

    • Importe de ventas total 29358677,2207

  8. Escribir una consulta SQL que use una función de ventana para calcular las ventas por producto restringidas a clientes de nacionalidad francesa vendidos en Diciembre del 2013. La consulta debe mostrar el nombre del producto, nombre y apellidos del cliente, la fecha, importe de ventas e importe de ventas por producto. Ordenar los resultados por nombre de producto y apellidos del cliente. A partir de lo resultados obtenidos:

    • ¿Qué importe se ha vendido del producto AWC Logo Cap? 197,78

    • ¿Se ha vendido el producto AWC Logo Cap a Kyle Carter? No

    • ¿Cuánto gastó Sabrina Blanco en el producto AWC Logo Cap el 04 de diciembre de 2013? 8,99

  9. Crear una consulta SQL a partir de la consulta del Ejercicio 5 en la que se sustituyan los valores NULL de agrupación de país por Todos los países y los valores NULL de agrupación de mes por Todos los meses.

  10. A partir de la consulta anterior añadir filtros a grupos para responder a las preguntas siguientes:

    • ¿Qué valores hay en la columna país y mes para el valor de ventas 131235,7541? Francia / Abril

    • ¿Qué valores hay en la columna país y mes para el valor de ventas 3211714,9962? Todos los países / Diciembre

    • ¿Qué valores hay en la columna país y mes para el valor de ventas 2536756,6378? Todos los países / Septiembre

  11. Escribir una consulta SQL que devuelva el ranking de los 3 países que más han vendido en Internet por año

    • País que aparece repetido en el ranking Australia

    • Máximo importe vendido por mes en un país 5462078,86

    • ¿En qué mes año aparece Estados Unidos en el ranking? 2013

  12. Escribir una consulta SQL que devuelva el ranking de ventas realizadas en Internet por año y país

    • Posición que ocupó Francia en 2010 4

    • País con mayor cantidad de ventas en 2010 Australia

    • Importe de ventas del último clasificado en 2013 1085632,65

  13. Escribir una vista indexada denominada vistaDimProductoReducida que almacene una forma resumida de la dimensión Producto con la clave del producto, nombre, subcategoría y categoría del producto, todos ellos en español.

  14. Escribir una vista indexada denominada vistaVentasInternetAlemania que almacene la suma de ventas y costes realizadas en Alemania como país del territorio en el que se realizan las ventas. Las ventas y costes de la vista estarán agrupados por clave de producto, clave de fecha de pedido (orderDateKey) y clave de cliente. Además de mostrar los atributos por los que se agrupa, también se quiere mostrar las ventas y costes

  15. Escribir una consulta SQL que a partir de las vistas indexadas creadas en los ejercicios 13 y 14 obtenga el TOP 5 de ventas y costes por subcategoría y categoría.

    • ¿Cuál es la subcategoría con mayor importe en ventas? Bicicleta de carretera

    • ¿Qué importe en ventas tiene el resultado que aparece en tercera posición? 424370,52

    • ¿Cuántas veces aparece la categoría Bicicleta en el TOP 5 de ventas? 3