Crono BI

# Filtros con subconsulta 💥💥

miércoles, 5 de enero de 2022
Ver en GitHub

La actualización 21.52 de Crono Analysis permite hace filtros con subconsultas. Esto permite resolver una necesidad avanzada pero bastante común.

Esta funcionalidad permite hacer una primera consulta y luego una segunda consulta a partir del resultado de la primera.

image

# Ejemplo 1

Necesitamos saber la ventas totales de las últimas 12 facturas de cada cliente. En este ejemplo no es posible filtrar por meses, pues la fecha de facturación es distinta en cada cliente.

Para resolver esta necesidad se han de hacer dos consultas:

  • Primero calcularemos las últimas doce facturas (con la función TOP 12)
  • La segunda consulta simplemente mostrará las ventas por cliente, pero filtrando las facturas de la primera consulta.

image

Haciendo clic en el filtro, se abre el formulario que muestra la subconsulta:

image

# Ejemplo 2

En retail se habla de "venta pico" para referirse a la semana de más venta de cada modelo en cada tienda. Esta información puede ser útil para programar el stock mínimo de cada artículo en cada tienda. ¿Qué capacidad necesita tener el almacén de cada tienda?

Para resolver esta consulta es necesario hacer dos consultas:

  • Primero se debe calcular la semana pico de cada modelo/tienda (con la función TOP 1 o con un "filtro final")
  • La segunda consulta simplemente será las ventas por tiendas, pero filtrando solo las semanas pico...

Este tipo de consultas avanzadas ya se puede hacer con los filtros de subconsulta:

image

El resultado es una consulta compleja:

WITH
subquery1 AS (
  SELECT
    Tienda,
    Semana,
    [Título],
    Unidades
  FROM (
      SELECT
        Tienda,
        Semana,
        [Título],
        Unidades,
        ROW_NUMBER() OVER (PARTITION BY Tienda,[Título] ORDER BY Unidades DESC) rownumber
      FROM (
          SELECT
            LB_TIENDAS.NOMBRE AS Tienda,
            LB_TIEMPO.SEMANA AS Semana,
            LB_LIBROS.TITULO AS [Título],
            sum(LB_VENTAS.UNIDADES) AS Unidades
          FROM dbo.LB_VENTAS LB_VENTAS
          INNER JOIN dbo.LB_TIENDAS LB_TIENDAS ON (LB_VENTAS.ID_TIENDA=LB_TIENDAS.ID_TIENDA)
          INNER JOIN dbo.LB_TIEMPO LB_TIEMPO ON (LB_VENTAS.FECHA=LB_TIEMPO.FECHA)
          INNER JOIN dbo.LB_LIBROS LB_LIBROS ON (LB_VENTAS.ID_LIBRO=LB_LIBROS.ID_LIBRO)
          WHERE CAST(LB_TIEMPO.ANYO AS varchar(4))='2012'
          GROUP BY
            LB_TIENDAS.NOMBRE,
            LB_TIEMPO.SEMANA,
            LB_LIBROS.TITULO
        ) allRows
    ) allRowsNumbered
  WHERE rownumber=1
)
SELECT
  LB_TIENDAS.NOMBRE AS Tienda,
  sum(LB_VENTAS.UNIDADES) AS Unidades
FROM dbo.LB_VENTAS LB_VENTAS
INNER JOIN dbo.LB_TIENDAS LB_TIENDAS ON (LB_VENTAS.ID_TIENDA=LB_TIENDAS.ID_TIENDA)
INNER JOIN dbo.LB_TIEMPO LB_TIEMPO ON (LB_VENTAS.FECHA=LB_TIEMPO.FECHA)
INNER JOIN dbo.LB_LIBROS LB_LIBROS ON (LB_VENTAS.ID_LIBRO=LB_LIBROS.ID_LIBRO)
WHERE
  EXISTS (SELECT 1 FROM subquery1 WHERE LB_TIENDAS.NOMBRE=subquery1.Tienda AND LB_TIEMPO.SEMANA=subquery1.Semana AND LB_LIBROS.TITULO=subquery1.[Título])
  AND CAST(LB_TIEMPO.ANYO AS varchar(4))='2012'
GROUP BY LB_TIENDAS.NOMBRE

El resultado responde exactamente a la necesidad del usuario:

image

No intentes hacer esto con otros productos 😊