Devuelva el registro más reciente que fue anterior a la fila actual.

01 Aug, 2023 Programación 0

Tengo una tabla de mensajes. Cada mensaje tiene una marca de tiempo created_ts, un tipo (ya sea purchase o support), user_id y una clave primaria msg_id. Por ejemplo,

msg_id	user_id	created_ts	type
1	1	1	purchase
2	1	2	support
3	2	3	purchase
4	2	4	support
5	2	5	support

Me gustaría generar una tabla de mensajes de soporte, de modo que cada fila contenga los detalles del mensaje de soporte y también los detalles de la última compra (si existe), es decir, el último mensaje de tipo purchase que ocurrió antes de este mensaje de soporte. Por ejemplo,

support_msg_id	user_id	support_created_ts	type	last_purchase_msg_id	last_purchase_created_ts
2	1	2	support	1	1
4	2	4	support	3	3
5	2	5	support	3	3

He intentado la siguiente consulta:

select * from
(select msg_id as support_msg_id, user_id, created_ts as support_created_ts, type
  (select msg_id as last_purchase_msg_id
   from messages
   where type = 'purchase' and created_ts < support_created_ts
 order by created_ts desc
limit 1)

from messages
where type = 'support'
) as supports
inner join
(
 select msg_id as last_purchase_msg_id, created_ts as last_purchase_ts
   from messages
   where type = 'purchase'
) as purchases
on supports.last_purchase_msg_id = purchases.last_purchase_msg_id

Sin embargo, esto es demasiado lento.

davy.ai

August 1, 2023 at 9:42 am

Como experto en SQL especializado en Amazon Redshift, hay algunas formas de optimizar esta consulta en particular para obtener un mejor rendimiento. Aquí tienes algunas sugerencias:

Utiliza una función analítica en lugar de una subconsulta: En lugar de usar una subconsulta para encontrar el último mensaje de compra para cada mensaje de soporte, puedes usar una función analítica como LAG() para encontrar el mensaje de compra anterior dentro de los mensajes de cada usuario ordenados por marca de tiempo. Esto se puede hacer en un único escaneo de la tabla en lugar de potencialmente escanear la tabla múltiples veces para cada mensaje de soporte.
Utiliza una función de ventana para realizar la unión: Si utilizas una función analítica como se mencionó anteriormente, también puedes utilizar una función de ventana para unir el último mensaje de compra a cada mensaje de soporte utilizando el resultado de LAG() como clave de unión. Esto evita la necesidad de una unión interna separada de las compras.
Utiliza una vista materializada: Si la tabla de mensajes es muy grande y se consulta con frecuencia, puedes crear una vista materializada que precalcule las uniones y agregaciones necesarias para la tabla de mensajes de soporte. Esto puede acelerar significativamente las consultas que, de lo contrario, tendrían que calcular las mismas uniones repetidamente.
Divide la tabla en particiones: Si la tabla de mensajes tiene un gran número de filas y se divide por user_id, puedes aprovechar la poda de particiones para limitar la cantidad de datos escaneados por consulta. Esto puede ayudar a reducir la latencia de las consultas que solo necesitan acceder a un subconjunto de la tabla.

Aquí tienes un ejemplo de consulta que utiliza las primeras dos sugerencias mencionadas anteriormente:

SELECT
    msg_id AS support_msg_id,
    user_id,
    created_ts AS support_created_ts,
    type,
    last_purchase_msg_id,
    last_purchase_created_ts
FROM (
    SELECT
        *,
        LAG(msg_id) OVER (
            PARTITION BY user_id
            ORDER BY created_ts DESC
            ) AS last_purchase_msg_id,
        LAG(created_ts) OVER (
            PARTITION BY user_id
            ORDER BY created_ts DESC
            ) AS last_purchase_created_ts
    FROM messages
    WHERE type = 'support'
) AS supports
WHERE supports.last_purchase_msg_id IS NOT NULL;

Utilizando LAG() con una función de ventana, la consulta puede encontrar el mensaje anterior de tipo purchase para cada mensaje de soporte dentro de los mensajes de cada usuario. Luego, la consulta externa filtra las filas donde no hay un mensaje de compra anterior.

Esta consulta asume que user_id es una clave de partición estable para la tabla de mensajes, lo que significa que los mensajes de cada usuario se almacenan juntos en la misma partición. Esto permite que la consulta se beneficie de la poda de particiones para limitar la cantidad de datos escaneados por cada función de ventana.

Answer

davy.ai

August 1, 2023 at 9:42 am
Como experto en SQL especializado en Amazon Redshift, hay algunas formas de optimizar esta consulta en particular para obtener un mejor rendimiento. Aquí tienes algunas sugerencias:
1. Utiliza una función analítica en lugar de una subconsulta: En lugar de usar una subconsulta para encontrar el último mensaje de compra para cada mensaje de soporte, puedes usar una función analítica como LAG() para encontrar el mensaje de compra anterior dentro de los mensajes de cada usuario ordenados por marca de tiempo. Esto se puede hacer en un único escaneo de la tabla en lugar de potencialmente escanear la tabla múltiples veces para cada mensaje de soporte.
2. Utiliza una función de ventana para realizar la unión: Si utilizas una función analítica como se mencionó anteriormente, también puedes utilizar una función de ventana para unir el último mensaje de compra a cada mensaje de soporte utilizando el resultado de LAG() como clave de unión. Esto evita la necesidad de una unión interna separada de las compras.
3. Utiliza una vista materializada: Si la tabla de mensajes es muy grande y se consulta con frecuencia, puedes crear una vista materializada que precalcule las uniones y agregaciones necesarias para la tabla de mensajes de soporte. Esto puede acelerar significativamente las consultas que, de lo contrario, tendrían que calcular las mismas uniones repetidamente.
4. Divide la tabla en particiones: Si la tabla de mensajes tiene un gran número de filas y se divide por user_id, puedes aprovechar la poda de particiones para limitar la cantidad de datos escaneados por consulta. Esto puede ayudar a reducir la latencia de las consultas que solo necesitan acceder a un subconjunto de la tabla.
Aquí tienes un ejemplo de consulta que utiliza las primeras dos sugerencias mencionadas anteriormente:
```
SELECT
    msg_id AS support_msg_id,
    user_id,
    created_ts AS support_created_ts,
    type,
    last_purchase_msg_id,
    last_purchase_created_ts
FROM (
    SELECT
        *,
        LAG(msg_id) OVER (
            PARTITION BY user_id
            ORDER BY created_ts DESC
            ) AS last_purchase_msg_id,
        LAG(created_ts) OVER (
            PARTITION BY user_id
            ORDER BY created_ts DESC
            ) AS last_purchase_created_ts
    FROM messages
    WHERE type = 'support'
) AS supports
WHERE supports.last_purchase_msg_id IS NOT NULL;
```
Utilizando LAG() con una función de ventana, la consulta puede encontrar el mensaje anterior de tipo purchase para cada mensaje de soporte dentro de los mensajes de cada usuario. Luego, la consulta externa filtra las filas donde no hay un mensaje de compra anterior.

Esta consulta asume que user_id es una clave de partición estable para la tabla de mensajes, lo que significa que los mensajes de cada usuario se almacenan juntos en la misma partición. Esto permite que la consulta se beneficie de la poda de particiones para limitar la cantidad de datos escaneados por cada función de ventana.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Devuelva el registro más reciente que fue anterior a la fila actual.

Answer

davy.ai