sql - multiples - Obtener la fila superior 1 de cada grupo

transponer filas en columnas mysql

Question

Tengo una tabla en la que quiero obtener la última entrada para cada grupo. Aquí está la tabla:

Tabla DocumentStatusLogs

|ID| DocumentID | Status | DateCreated |
| 2| 1 | S1 | 7/29/2011 |
| 3| 1 | S2 | 7/30/2011 |
| 6| 1 | S1 | 8/02/2011 |
| 1| 2 | S1 | 7/28/2011 |
| 4| 2 | S2 | 7/30/2011 |
| 5| 2 | S3 | 8/01/2011 |
| 6| 3 | S1 | 8/02/2011 |

La tabla se agrupará por DocumentID y se ordenará por DateCreated en orden descendente. Para cada DocumentID , quiero obtener el estado más reciente.

Mi salida preferida:

| DocumentID | Status | DateCreated |
| 1 | S1 | 8/02/2011 |
| 2 | S3 | 8/01/2011 |
| 3 | S1 | 8/02/2011 |

• ¿Hay alguna función agregada para obtener solo la parte superior de cada grupo? Vea el pseudo-código GetOnlyTheTop continuación:

  SELECT
  DocumentID,
  GetOnlyTheTop(Status),
  GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC

• Si dicha función no existe, ¿hay alguna manera de lograr la salida que quiero?

• O, en primer lugar, ¿podría ser causado por una base de datos no normalizada? Estoy pensando, ya que lo que estoy buscando es solo una fila, ¿debería ese status también estar ubicado en la tabla principal?

Por favor, consulte la tabla de padres para más información:

Tabla de Documents actuales

| DocumentID | Title | Content | DateCreated |
| 1 | TitleA | ... | ... |
| 2 | TitleB | ... | ... |
| 3 | TitleC | ... | ... |

¿Debería la tabla principal ser así para poder acceder fácilmente a su estado?

| DocumentID | Title | Content | DateCreated | CurrentStatus |
| 1 | TitleA | ... | ... | s1 |
| 2 | TitleB | ... | ... | s3 |
| 3 | TitleC | ... | ... | s1 |

ACTUALIZACIÓN Acabo de aprender a usar "aplicar", lo que facilita la resolución de estos problemas.

Answer 1

Acabo de aprender a usar cross apply . Aquí está cómo usarlo en este escenario:

 select d.DocumentID, ds.Status, ds.DateCreated
from Documents as d
cross apply
(select top 1 Status, DateCreated
from DocumentStatusLogs
where DocumentID = d.DocumentId
order by DateCreated desc) as ds

Answer 2

En los escenarios en los que desea evitar el uso de row_count (), también puede usar una combinación a la izquierda:

select ds.DocumentID, ds.Status, ds.DateCreated
from DocumentStatusLogs ds
left join DocumentStatusLogs filter
ON ds.DocumentID = filter.DocumentID
-- Match any row that has another row that was created after it.
AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched
where filter.DocumentID is null

Para el esquema de ejemplo, también podría usar un "no en subconsulta", que generalmente compila a la misma salida que la combinación izquierda:

select ds.DocumentID, ds.Status, ds.DateCreated
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
SELECT filter.ID
FROM DocumentStatusLogs filter
WHERE ds.DocumentID = filter.DocumentID
AND ds.DateCreated < filter.DateCreated)

Tenga en cuenta que el patrón de subconsulta no funcionaría si la tabla no tuviera al menos una clave / restricción / índice único de una sola columna, en este caso la clave principal "Id".

Ambas consultas tienden a ser más "caras" que la consulta row_count () (medida por el Analizador de consultas). Sin embargo, puede encontrar situaciones en las que devuelvan resultados más rápido o habiliten otras optimizaciones.

Answer 3

Esta es una de las preguntas más fáciles de encontrar sobre el tema, así que quería dar una respuesta moderna a la misma (tanto para mi referencia como para ayudar a otros). Usando sobre y primer valor puede hacer un trabajo corto de la consulta anterior:

select distinct DocumentID
, first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
, first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

Esto debería funcionar en el servidor SQL 2008 y superiores. Se puede pensar en el primer valor como una manera de lograr seleccionar el top 1 al usar una cláusula sobre. Over permite agrupar en la lista de selección, por lo que en lugar de escribir subconsultas anidadas (como hacen muchas de las respuestas existentes), esto lo hace de una manera más legible. Espero que esto ayude.

Answer 4

Este es el TSQL más vainilla que se me ocurre

 SELECT * FROM DocumentStatusLogs D1 JOIN
(
SELECT
DocumentID,MAX(DateCreated) AS MaxDate
FROM
DocumentStatusLogs
GROUP BY
DocumentID
) D2
ON
D2.DocumentID=D1.DocumentID
AND
D2.MaxDate=D1.DateCreated

Answer 5

Este es un tema bastante antiguo, pero pensé que tiraría mis dos centavos de la misma manera que la respuesta aceptada no funcionó particularmente bien para mí. Probé la solución de gbn en un gran conjunto de datos y encontré que era terriblemente lenta (> 45 segundos en más de 5 millones de registros en SQL Server 2012). Mirando el plan de ejecución, es obvio que el problema es que requiere una operación SORT que ralentiza significativamente las cosas.

Aquí hay una alternativa que saqué del marco de la entidad que no necesita una operación SORT y hace una búsqueda de índice NO agrupado. Esto reduce el tiempo de ejecución a <2 segundos en el conjunto de registros mencionado anteriormente.

SELECT
[Limit1].[DocumentID] AS [DocumentID],
[Limit1].[Status] AS [Status],
[Limit1].[DateCreated] AS [DateCreated]
FROM (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
FROM (SELECT
[Extent2].[ID] AS [ID],
[Extent2].[DocumentID] AS [DocumentID],
[Extent2].[Status] AS [Status],
[Extent2].[DateCreated] AS [DateCreated]
FROM [dbo].[DocumentStatusLogs] AS [Extent2]
WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
) AS [Project2]
ORDER BY [Project2].[ID] DESC) AS [Limit1]

Ahora asumo algo que no está completamente especificado en la pregunta original, pero si el diseño de su tabla es tal que su columna de ID es un ID de incremento automático, y DateCreated se establece en la fecha actual con cada inserción, incluso sin ejecutar mi consulta anterior, en realidad podría obtener un aumento considerable del rendimiento de la solución de gbn (aproximadamente la mitad del tiempo de ejecución) solo con el pedido de ID en lugar de ordenar con DateCreated, ya que esto proporcionará un orden de clasificación idéntico y es más rápido.

Answer 6

He hecho algunos tiempos sobre las diversas recomendaciones aquí, y los resultados realmente dependen del tamaño de la tabla involucrada, pero la solución más consistente es usar la APLICACIÓN CRUZADA. Estas pruebas se ejecutaron contra SQL Server 2008-R2, usando una tabla con 6.500 registros, y otro (esquema idéntico) con 137 millones de registros. Las columnas que se consultan son parte de la clave principal en la tabla, y el ancho de la tabla es muy pequeño (alrededor de 30 bytes). Los tiempos son informados por SQL Server desde el plan de ejecución real.

Query Time for 6500 (ms) Time for 137M(ms)
CROSS APPLY 17.9 17.9
SELECT WHERE col = (SELECT MAX(COL)…) 6.6 854.4
DENSE_RANK() OVER PARTITION 6.6 907.1

Creo que lo realmente asombroso fue lo consistente que fue el tiempo para la APLICACIÓN CRUZADA independientemente del número de filas involucradas.

Answer 7

Mi código para seleccionar el top 1 de cada grupo

select a.* from #DocumentStatusLogs a where datecreated in( select top 1 datecreated from #DocumentStatusLogs b where a.documentid = b.documentid order by datecreated desc )

Answer 8

Prueba esto:

 SELECT [DocumentID],
[tmpRez].value(''/x[2]'',''varchar(20)'') as [Status],
[tmpRez].value(''/x[3]'',''datetime'') as [DateCreated]
FROM (
SELECT [DocumentID],
cast(''<x>''+max(cast([ID] as varchar(10))+''</x><x>''+[Status]+''</x><x>''
+cast([DateCreated] as varchar(20)))+''</x>'' as XML) as [tmpRez]
FROM DocumentStatusLogs
GROUP by DocumentID) as [tmpQry]

Answer 9

Sé que este es un tema antiguo, pero las soluciones TOP 1 WITH TIES son bastante buenas y podrían ser útiles para leer algunas de las soluciones.

select top 1 with ties
DocumentID
,Status
,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

Más información sobre la cláusula TOP se puede encontrar here .

Answer 10

Se comprueba en SQLite que puede utilizar la siguiente consulta simple con GROUP BY

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

Aquí MAX ayuda para obtener el máximo DateCreated DE cada grupo.

Pero parece que MYSQL no asocia * -columns con el valor de max DateCreated :(

Answer 11

Si le preocupa el rendimiento, también puede hacer esto con MAX ():

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER () requiere una clase de todas las filas en su declaración SELECT, mientras que MAX no lo hace. Debe acelerar drásticamente su consulta.

Answer 12

Verificando la asombrosa y correcta respuesta de Clint desde arriba:

El rendimiento entre las dos consultas a continuación es interesante. 52% siendo el mejor. Y el 48% siendo el segundo. Una mejora del 4% en el rendimiento utilizando DISTINCT en lugar de ORDER BY. Pero ORDER BY tiene la ventaja de ordenar por varias columnas.

IF (OBJECT_ID(''tempdb..#DocumentStatusLogs'') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END
CREATE TABLE #DocumentStatusLogs (
[ID] int NOT NULL,
[DocumentID] int NOT NULL,
[Status] varchar(20),
[DateCreated] datetime
)
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, ''S1'', ''7/29/2011 1:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, ''S2'', ''7/30/2011 2:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, ''S1'', ''8/02/2011 3:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, ''S1'', ''7/28/2011 4:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, ''S2'', ''7/30/2011 5:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, ''S3'', ''8/01/2011 6:00:00'')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, ''S1'', ''8/02/2011 7:00:00'')

Opción 1:

 SELECT
[Extent1].[ID],
[Extent1].[DocumentID],
[Extent1].[Status],
[Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
OUTER APPLY (
SELECT TOP 1
[Extent2].[ID],
[Extent2].[DocumentID],
[Extent2].[Status],
[Extent2].[DateCreated]
FROM #DocumentStatusLogs AS [Extent2]
WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

Opcion 2:

SELECT
[Limit1].[DocumentID] AS [ID],
[Limit1].[DocumentID] AS [DocumentID],
[Limit1].[Status] AS [Status],
[Limit1].[DateCreated] AS [DateCreated]
FROM (
SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
OUTER APPLY (
SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
FROM (
SELECT
[Extent2].[ID] AS [ID],
[Extent2].[DocumentID] AS [DocumentID],
[Extent2].[Status] AS [Status],
[Extent2].[DateCreated] AS [DateCreated]
FROM #DocumentStatusLogs AS [Extent2]
WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
) AS [Project2]
ORDER BY [Project2].[ID] DESC
) AS [Limit1]

Management Studio de M $: después de resaltar y ejecutar el primer bloque, resalte la Opción 1 y la Opción 2, haga clic con el botón derecho -> [Mostrar plan de ejecución estimado]. A continuación, ejecute toda la cosa para ver los resultados.

Resultados de la Opción 1:

ID DocumentID Status DateCreated
6 1 S1 8/2/11 3:00
5 2 S3 8/1/11 6:00
6 3 S1 8/2/11 7:00

Resultados de la Opción 2:

ID DocumentID Status DateCreated
6 1 S1 8/2/11 3:00
5 2 S3 8/1/11 6:00
6 3 S1 8/2/11 7:00

Nota:

Tiendo a usar APLICAR cuando quiero que una combinación sea 1-to- (1 de muchas).

Utilizo un comando JOIN si quiero que la unión sea 1 a muchos o muchos a muchos.

Evito CTE con ROW_NUMBER () a menos que tenga que hacer algo avanzado y estoy de acuerdo con la penalización de rendimiento de ventanas.

También evito las subconsultas EXISTS / IN en la cláusula WHERE o ON, ya que he experimentado esto causando algunos planes de ejecución terribles. Pero el kilometraje varía. Revise el plan de ejecución y el rendimiento del perfil donde y cuando sea necesario.

Answer 13

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

Si espera 2 entradas por día, entonces esto elegirá arbitrariamente una. Para obtener ambas entradas por un día, usa DENSE_RANK en su lugar

En cuanto a normalizado o no, depende si quieres:

• mantener el estado en 2 lugares
• preservar el historial de estado
• ...

Tal como está, conservas el historial de estado. Si también desea el último estado en la tabla principal (que es la desnormalización), necesitará un activador para mantener el "estado" en el principal. o soltar esta tabla de historial de estado.

Answer 14

SELECT * FROM
DocumentStatusLogs JOIN (
SELECT DocumentID, MAX(DateCreated) DateCreated
FROM DocumentStatusLogs
GROUP BY DocumentID
) max_date USING (DocumentID, DateCreated)

¿Qué servidor de base de datos? Este código no funciona en todos ellos.

Con respecto a la segunda mitad de su pregunta, me parece razonable incluir el estado como una columna. Puede dejar DocumentStatusLogs como un registro, pero aún así almacenar la información más reciente en la tabla principal.

Por cierto, si ya tiene la columna DateCreated en la tabla Documentos, simplemente puede unirse a DocumentStatusLogs usando eso (siempre que DateCreated sea ​​único en DocumentStatusLogs ).

Edición: MsSQL no es compatible con USING, así que cámbielo a:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

Answer 15

SELECT doc_id,status,date_created FROM (
SELECT a.*,Row_Number() OVER(PARTITION BY doc_id ORDER BY date_created DESC ) AS rnk FROM doc a)
WHERE rnk=1;

Answer 16

SELECT o.*
FROM `DocumentStatusLogs` o
LEFT JOIN `DocumentStatusLogs` b
ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
WHERE b.DocumentID is NULL ;

Si desea devolver solo el pedido de documentos recientes por DateCreated, devolverá solo el 1 documento superior por DocumentID