Última fila por cliente: empates y planes eficientes

La última orden de cada cliente parece resolverse con MAX. MAX obtiene la fecha máxima, pero no una fila completa única cuando hay empates. Un join posterior puede devolver varias órdenes. Calcular MAX independientemente para otras columnas puede formar valores que nunca pertenecieron a la misma orden.

Definir la regla exacta

El ejemplo ordena OccurredAt y luego OrderId de forma descendente. El segundo criterio único rompe empates y selecciona una fila cuando existen órdenes. Una identidad mayor solo desempata; no demuestra mayor antigüedad de negocio ni un commit posterior.

CREATE TABLE #Customers(CustomerId int PRIMARY KEY);
INSERT #Customers VALUES(1),(2),(3);
CREATE TABLE #Orders(OrderId int PRIMARY KEY, CustomerId int NOT NULL,
 OccurredAt datetime2(0) NOT NULL, Amount decimal(10,2) NOT NULL);
INSERT #Orders VALUES(11,1,'20230101',10),(12,1,'20230101',20),
(13,2,'20230102',30);
CREATE INDEX IX_Latest ON #Orders(CustomerId,OccurredAt DESC,OrderId DESC)
INCLUDE(Amount);

;WITH ranked AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS rn FROM #Orders)
SELECT c.CustomerId,r.OrderId,r.OccurredAt,r.Amount
FROM #Customers AS c LEFT JOIN ranked AS r
ON r.CustomerId=c.CustomerId AND r.rn=1
ORDER BY c.CustomerId;

El cliente 1 tiene dos órdenes simultáneas, por lo que gana OrderId 12. El cliente 2 tiene una y el 3 ninguna. LEFT JOIN conserva al cliente 3 con campos NULL. rn = 1 debe permanecer en la condición de join; moverlo a WHERE eliminaría al cliente sin coincidencia.

Si necesita todas las órdenes empatadas en la fecha más reciente, utilice RANK o DENSE_RANK solo por fecha y acepte varias filas. Añadir OrderId eliminaría ese empate de negocio. Defina primero si quiere un representante o todos los eventos igualmente recientes.

Comparar estrategias de acceso

ROW_NUMBER resulta útil para conjuntos amplios porque permite procesar muchas órdenes juntas. OUTER APPLY expresa una búsqueda por cliente que puede detenerse al encontrar la primera fila con un índice apropiado.

SELECT c.CustomerId,o.OrderId,o.OccurredAt,o.Amount
FROM #Customers AS c
OUTER APPLY
(SELECT TOP(1) OrderId,OccurredAt,Amount FROM #Orders AS o
 WHERE o.CustomerId=c.CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS o
ORDER BY c.CustomerId;

Ninguna formulación es universalmente superior. Pocos clientes pueden favorecer búsquedas dirigidas; casi todos pueden favorecer un recorrido conjunto. El optimizador transforma planes. Examine ejecución real, lecturas lógicas y estimaciones en lugar de deducir el acceso del texto SQL.

El índice comienza por CustomerId y sigue el orden descendente solicitado, incluyendo Amount para cubrir la consulta. Puede evitar ordenación o búsquedas adicionales. Más columnas también incrementan almacenamiento y coste de escritura. Sin índice adecuado, APPLY puede repetir recorridos grandes para cada cliente.

Validar diferencias de significado

La última orden exitosa no equivale a la última orden si es exitosa. Filtrar éxito antes del ranking encuentra el éxito más reciente. Clasificar todo y filtrar después puede no devolver nada cuando el evento más reciente falló. Ambas interpretaciones son posibles, pero responden preguntas distintas.

Un informe a una fecha necesita el límite dentro de los candidatos. Aplique OccurredAt menor que la frontera exclusiva antes de seleccionar ganador. Si varias instrucciones deben compartir la misma vista, establezca aislamiento apropiado: lecturas separadas pueden observar cambios intermedios.

Pruebe clientes sin órdenes, fechas empatadas, un cliente enorme, muchos pequeños y ambas reglas de estado. Compruebe cantidad de clientes y que cada Amount pertenezca al OrderId devuelto. Compare planes usando parámetros y semántica equivalentes. Añada también un evento exactamente en la frontera temporal para verificar su exclusión. Una consulta más rápida que escoge otra orden no optimiza el contrato original.

Para eventos tardíos, determine además si manda la hora del evento o la hora de recepción. Guarde ambas cuando esa diferencia sea importante. De lo contrario, una importación retrasada puede modificar inesperadamente el último resultado.

Referencias técnicas: Microsoft Learn: ROW_NUMBER · Microsoft Learn: TOP.