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.

Particionamiento por retardo/adelanto y opciones de ordenamiento en Clickhouse.

11 Jul, 2023 Programación

Tengo una tabla que se ve así:

Para cada fila, quiero calcular el valor sumatorio bruto precedente en la fecha de evento para el mismo trans_type, canal y país. El nuevo fragmento de datos para:

channel="Afiliado", país="CA" y trans_type="conversión"

debería verse así:

event_date   trans_type  channel   country gross_sum  prev
'2021-12-02' conversion  Afiliado  CA      120        100
'2021-12-01' conversion  Afiliado  CA      100        900
'2021-11-30' conversion  Afiliado  CA      900        800

Es decir, quiero ejecutar la función de desplazamiento estándar, que actualmente no está disponible para mí:

select lag(gross_sum) over(partition by event_date, trans_type, channel, country order by event_date desc) from table

Necesito una función de agregación, por ejemplo, una función de array, ¡pero no una función de vecino! Las funciones de vecino o similares que no son de agregación no manejan bien las variables de agrupación. Necesito una agregación para que en el futuro pueda eliminar las columnas trans_type, channel y country de la tabla y ver la sumatoria (sum) de gross_sum y sus valores precedentes para cada fecha.

¡Agradezco tu ayuda!

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para calcular el valor anterior de gross_sum para cada fila basado en el mismo trans_type, canal y país, podemos utilizar una función de array y luego seleccionar el último elemento del array usando el índice -1. Aquí está la consulta que logra esto:

    SELECT 
  event_date,
  trans_type,
  channel,
  country,
  gross_sum,
  arrayReduce('concat', groupArray(gross_sum) ORDER BY event_date DESC)[2] AS prev
FROM my_table
WHERE channel = 'Afiliado' AND country = 'CA' AND trans_type = 'conversión'
GROUP BY event_date, trans_type, channel, country, gross_sum
ORDER BY event_date DESC;

    La función arrayReduce concatena todos los valores de gross_sum que tienen la misma event_date, trans_type, canal y país en orden descendente según la event_date. Luego seleccionamos el segundo último valor (-1 índice) del array concatenado como el valor anterior de gross_sum.

    La consulta agrupa los datos por event_date, trans_type, channel, country y los valores de gross_sum para preservar filas únicas. Podemos utilizar esta consulta para eliminar las columnas trans_type, channel y country de la tabla y aún así obtener la suma de los valores de gross_sum para cada fecha junto con sus valores anteriores.

    ¡Espero que esto ayude! Avísame si tienes alguna pregunta.

Comments are closed.