es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Salida de jerarquía de Postgres

02 Aug, 2023 Programación

Estoy teniendo dificultades para obtener la salida correcta utilizando una consulta jerárquica.

Tengo una tabla que carga diariamente todos los productos y su precio. Con el tiempo, estos pueden ser cancelados y activados nuevamente.

Creo que con Oracle podríamos usar Connect By.

WITH RECURSIVE cte AS (
SELECT MIN(event_date) event_date, item_code, sum(price::numeric)/1024/1024 price, 1 AS level
FROM rdpidevdat.raid_r_cbs_offer_accttype_map
WHERE product_type='cars' AND item_code IN ('Renault')
GROUP BY item_code
UNION ALL
SELECT e.event_date, e.item_code, e.price, cte.level + 1
FROM (
  SELECT event_date, item_code, sum(price::numeric)/1024/1024 price
  FROM rdpidevdat.raid_r_cbs_offer_accttype_map
  WHERE product_type='cars' AND item_code IN ('9859')
  GROUP BY event_date, item_code
) e
JOIN cte ON e.event_date = cte.event_date AND e.item_code = cte.item_code
)
SELECT *
FROM cte
WHERE item_code IN ('Renault');

¿Cómo puedo generar una salida que muestre el rango de cada producto a lo largo del tiempo?

Si tenemos los siguientes datos:

EVENT_DATE ITEM_COD PRICE
20210910 Renault 2500
20210915 Renault 2500
20210920 Renault 2600
20211020 Renault 2900
20220101 Renault 2500

La salida esperada sería:

FROM_EVENT_DATE TO_EVENT_DATE ITEM_COD PRICE
20210910 20210915 Renault 2500
20210915 20210920 Renault 2600
20210920 20211020 Renault 2900
20211020 20220101 Renault 2500

¡Gracias de antemano y saludos!

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para obtener el resultado deseado, puedes modificar la consulta CTE recursiva de la siguiente manera:

    CON RECURSIVE cte AS (
  SELECT 
    event_date as from_event_date, 
    item_code, 
    price, 
    ROW_NUMBER() OVER (ORDER BY event_date) AS rn
  FROM 
    rdpidevdat.raid_r_cbs_offer_accttype_map 
  WHERE 
    product_type = 'cars' 
    AND item_code = 'Renault' 
  UNION ALL
  SELECT 
    e.event_date as from_event_date, 
    e.item_code, 
    e.price, 
    cte.rn + 1 AS rn
  FROM (
    SELECT 
      event_date, 
      item_code, 
      price, 
      ROW_NUMBER() OVER (PARTITION BY item_code ORDER BY event_date) AS rn
    FROM 
      rdpidevdat.raid_r_cbs_offer_accttype_map 
    WHERE 
      product_type = 'cars' 
      AND item_code = 'Renault' 
  ) e 
  JOIN cte ON e.item_code = cte.item_code AND e.rn = cte.rn + 1
)
SELECT 
  cte.from_event_date, 
  COALESCE((
    SELECT 
      MIN(event_date) 
    FROM 
      cte c2 
    WHERE 
      c2.rn > cte.rn 
      AND c2.price <> cte.price
      AND c2.item_code = cte.item_code
  ), (
    SELECT 
      MAX(event_date) 
    FROM 
      rdpidevdat.raid_r_cbs_offer_accttype_map 
    WHERE 
      item_code = cte.item_code 
      AND event_date >= cte.from_event_date
  )) AS to_event_date,
  cte.item_code, 
  cte.price
FROM 
  cte
ORDER BY 
  cte.from_event_date

    Explicación:

    • La consulta CTE selecciona las fechas de evento, códigos de artículo y precios de la tabla rdpidevdat.raid_r_cbs_offer_accttype_map y asigna un número de fila a cada fila utilizando la función de ventana ROW_NUMBER().
    • La parte recursiva de la consulta CTE une la CTE consigo misma utilizando los números de fila para crear una secuencia de filas que representan los cambios en el precio a lo largo del tiempo para cada código de artículo.
    • La consulta externa selecciona la fecha de inicio (from_event_date), la fecha de fin (to_event_date), el código de artículo y el precio para cada secuencia de filas.
    • La función COALESCE() se utiliza para determinar la fecha de fin de cada secuencia de filas. Primero busca la próxima fila donde el precio cambia, y si no encuentra una, utiliza la fecha de evento máxima para el código de artículo de la tabla original.

    Ten en cuenta que la consulta asume que no hay brechas en la secuencia de fechas de evento para cada código de artículo. Si hay brechas, el resultado puede ser incorrecto.

Comments are closed.