¿Cómo obtener el primer y último evento relacionado sin tener que hace

¿Cómo obtener el primer y último evento relacionado sin tener que hacer subconsultas adicionales?

Tengo dos tablas: Customers y CustomerEvents. Cada cliente puede tener 0 o más eventos.

Estoy trabajando en un informe que se supone que debe verse así:

CustomerName     | FirstEventDate | FirstEventMessage   | LastEventDate | LastEventText
---------------------------------------------------------------------------------------
Customers 'R' US | 2018-01-01     | Customer registered | 2018-04-06    | Customer Quit

La consulta real va a ser mucho más grande que eso, pero esta es la esencia general.

Si solo necesitara las fechas, podría hacerlo fácilmente usando agregados:

SELECT 
    c.Name AS ContactName
    , MAX(e.DateTime) AS FirstEventDate
    , '???' AS FirstEventMessage
    , MIN(e.DateTime) AS FirstEventDate
    , '???' AS FirstEventMessage
FROM Contacts c
    LEFT JOIN CustomerEvents e ON e.ContactId = c.Id
GROUP BY c.Name

Desafortunadamente, eso deja fuera los mensajes correspondientes.

Me las arreglé para resolverlo usando una subconsulta para cada mensaje, pero eso se vuelve muy costoso, así que me preguntaba si hay otro enfoque que sugerirías. La consulta completa es más compleja que el ejemplo, tendrá uniones en diferentes tipos de eventos, cada uno mostrando la fecha y el mensaje del primer y último evento.

Mostrar la mejor respuesta

Con SQL-Server 2012, la ventana funciona Se introdujeron FIRST_VALUE() y LAST_VALUE().

Antes de SQL Server 2012, hay un truco complicado que puede usar que consiste en concatenar los valores deseados como BINARY, aplicarles el agregado y luego desempaquetar el resultado. No es bonito, pero es un pase único garantizado.

@Shnugo que funcionó! Si escribe una respuesta basada en eso, me aseguraré de que obtenga todos los puntos de Internet imaginarios.

@PetterBrodin Esto podría ser importante: agregue el c.Id a su GROUP BY... ¿Qué pasa si dos contactos tienen el mismo nombre?

Gracias por el aviso. La consulta real es mucho más compleja y se trata tanto de ID como de nombre, simplemente la elegí como mi única columna al simplificar el ejemplo.

Pruebe este enfoque CTE, ajuste las columnas a la cláusula SELECT según sus necesidades.

;WITH CTE
AS(
    SELECT 
        *, 
        ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY e.DateTime) AS RowAsc,
        ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY e.DateTime DESC) AS RowDesc
    FROM Contacts c
        LEFT JOIN CustomerEvents e ON e.ContactId = c.Id    
)
SELECT 
    c1.*
    , c2.*
FROM CTE c1
INNER JOIN CTE c2 ON c1.Name = c2.Name
    AND c1.RowAsc = 1 
    AND c2.RowDesc = 1

Pruebe el siguiente enfoque

SELECT 
    c.Name AS CustomerName
    , MAX(e.DateTime) AS LastEventDate
    , B.Message AS FirstEventMessage
    , MIN(e.DateTime) AS FirstEventDate
    , D.Message AS FirstEventMessage
FROM #Customers c
    LEFT JOIN #CustomerEvents e ON e.CustomerId = c.Id
    LEFT JOIN 
    (
    SELECT A.CustomerId, A.Message
    FROM
        (
        SELECT 
            CustomerId, Message, Row_Number()over(Partition By CustomerId order by DATETIME DESC) as No
        FROM
            #CustomerEvents 
        ) A
    WHERE
        A.No = 1
    ) B ON B.CustomerId = C.Id
    LEFT JOIN 
    (
    SELECT A.CustomerId, A.Message
    FROM
        (
        SELECT 
            CustomerId, Message, Row_Number()over(Partition By CustomerId order by DATETIME) as No
        FROM
            #CustomerEvents 
        ) A
    WHERE
        A.No = 1
    ) d ON d.CustomerId = C.Id
GROUP BY 
    c.Name, B.Message, D.Message

Como me pediste, para crear una respuesta a partir de mi comentario, aquí tienes:

Con la versión 2012, Microsoft introdujo algunas funciones nuevas de ventanas. Útiles para usted son FIRST_VALUE() y LAST_VALUE(). Ambos necesitan una cláusula OVER() para especificar el orden de clasificación y, si es necesario, una regla de partición.

Esto debería funcionar (pero no conozco sus tablas y datos):

SELECT 
    c.Name AS ContactName
    , MIN(e.DateTime) AS FirstEventDate
    , FIRST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS FirstEventMessage
    , MAX(e.DateTime) AS LastEventDate
    , LAST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS LastventMessage
FROM Contacts c
    LEFT JOIN CustomerEvents e ON e.ContactId = c.Id
GROUP BY c.Id,c.Name;

Pero tenga cuidado: si su e.DateTime no es único (por Contact), obtendrá un "primer" valor aleatorio...

Consejos

  • Asegúrese de tener índices en DateTime y ContactId y
  • agregue el c.Id a su GROUP BY

Una alternativa era reemplazar LEFT JOIN CustomerEvents con una subconsulta correlacionada ejecutada por filas. Esto tiene la ventaja de que puede estar seguro de que ambos (y más) valores se toman de la misma fila.

OUTER APPLY (SELECT TOP 1 e.[DateTime],e.[EventMessage]  
             FROM CustomerEvents AS e
             WHERE e.ContactId=c.Id
             ORDER BY e.EventMessage ASC) AS FirstEvent
--same with DESC
OUTER APPLY (SELECT TOP 1 e.[DateTime],e.[EventMessage]  
             FROM CustomerEvents AS e
             WHERE e.ContactId=c.Id
             ORDER BY e.EventMessage DESC) AS LastEvent

Luego use las columnas en su consulta como

 FirstEvent.DateTime AS FirstDateTime
,FirstEvent.EventMessage AS FirstMessage
,LastEvent.DateTime AS LastDateTime
,LastEvent.EventMessage AS LastMessage