Получить верхнюю 1 строку каждой группы

у меня есть стол, который я хочу получить последнюю запись для каждой группы. Вот таблица:

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   |

таблица будет сгруппирована по DocumentID и сортируется DateCreated в порядке убывания. Для каждого DocumentID, Я хочу получить последний статус.

мой предпочтительный выход:

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

  • есть ли агрегатная функция, чтобы получить только верхнюю часть из каждой группы? Смотрите псевдо-код GetOnlyTheTop ниже:

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

  • если такая функция не существует,есть ли способ, которым я могу достичь желаемого результата?

  • или, во-первых, это может быть вызвано ненормализованной базой данных? Я думаю, так как то, что я ищу, это всего лишь одна строка, если это status также находиться в родительской таблице?

дополнительную информацию см. в родительской таблице:

настоящее Documents Таблица

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

должна ли родительская таблица быть такой, чтобы я мог легко получить доступ к ее статусу?

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

обновление Я только что узнал, как использовать "применить", что облегчает решение таких проблем.

15 391
sql tsql greatest-n-per-group sql-server-2005 group-by

15 ответов:

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

Если вы ожидаете 2 записей в день, то это будет произвольно выбрать один. Чтобы получить обе записи в течение дня, используйте dense_rank вместо

что касается нормализованного или нет, это зависит от того, хотите ли вы:

  • поддерживать статус в 2 местах
  • сохранить историю статусов
  • ...

как она стоит, вы сохраняете историю. Если вы хотите, чтобы последний статус в родительской таблице тоже (что является денормализацией), вам понадобится триггер, чтобы поддерживайте "статус" в родителе. или удалите эту таблицу истории состояния.

580

Я только что научился использовать cross apply. Вот как использовать его в этом сценарии:

 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
121

Я сделал некоторые тайминги по различным рекомендациям здесь, и результаты действительно зависят от размера таблицы, но наиболее последовательным решением является использование перекрестного применения эти тесты были запущены против SQL Server 2008-R2, используя таблицу с 6500 записями и другую (идентичную схему) с 137 миллионами записей. Запрашиваемые столбцы являются частью первичного ключа таблицы, а ширина таблицы очень мала (около 30 байт). "Таймс" сообщает SQL-серверу из фактического плана выполнения.

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

Я думаю, что действительно удивительная вещь заключалась в том, насколько последовательным было время для перекрестного применения независимо от количества строк.

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

какой сервер баз данных? Этот код не работает на всех из них.

что касается второй половины вашего вопроса, мне представляется разумным включить статус в колонке. Вы можете уйти DocumentStatusLogs как журнал, но по-прежнему хранить последнюю информацию в главной таблице.

кстати, если у вас уже есть DateCreated столбец в таблице документов вы можете просто присоединиться DocumentStatusLogs используя это (пока DateCreated - Это уникальный в DocumentStatusLogs).

Edit: MsSQL не делает поддержка использования, поэтому измените его на:

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

Если вы беспокоитесь о производительности, вы также можете сделать это с максимальной():

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

ROW_NUMBER () требует сортировки всех строк в операторе SELECT, в то время как MAX-нет. Должно резко ускорить ваш запрос.

19

Это довольно старая тема, но я думал, что брошу свои два цента точно так же, как принятый ответ не работал особенно хорошо для меня. Я попробовал решение gbn на большом наборе данных и обнаружил, что оно ужасно медленное (>45 секунд на 5 миллионах плюс записи в SQL Server 2012). Глядя на план выполнения, очевидно, что проблема заключается в том, что он требует операции сортировки, которая значительно замедляет работу.

вот альтернатива, которую я поднял из сущности платформа, которая не требует операции сортировки и выполняет некластеризованный Поиск индекса. Это сокращает время выполнения до 

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]

теперь я предполагаю, что что-то не совсем указано в исходном вопросе, но если ваш дизайн таблицы таков, что ваш столбец ID является идентификатором автоматического приращения, а DateCreated установлен на текущую дату с каждой вставкой, то даже без запуска с моим запросом выше вы могли бы получить значительный размер повышение производительности до решения gbn (около половины времени выполнения) только от заказ на ID вместо того, чтобы заказ на выполните команду поскольку это обеспечит идентичный порядок сортировки, и это более быстрая сортировка.

9

Я знаю, что это старый нить, но TOP 1 WITH TIES решения довольно приятно и может быть полезно для некоторых чтения через решения.

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

подробнее о верхнем предложении можно найти здесь.

8

мой код, чтобы выбрать топ-1 из каждой группы

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

Это один из самых легко найденных вопросов по теме, поэтому я хотел дать современный ответ на него (как для моей справки, так и для помощи другим). С помощью over и first value вы можете сделать короткую работу над приведенным выше запросом:

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

Это должно работать в sql server 2008 и выше. Первое значение можно рассматривать как способ выполнения select top 1 при использовании предложения over. Over позволяет группировать в списке выбора, поэтому вместо написания вложенных подзапросов (как и многие из них существующие ответы делают), это делает его более читаемым способом. Надеюсь, это поможет.

3

проверка удивительный и правильный ответ Клинта сверху:

производительность между двумя приведенными ниже запросами интересна. 52% - самый высокий. И 48% - второй. 4% улучшение производительности с использованием DISTINCT вместо ORDER BY. Но ORDER BY имеет преимущество для сортировки по нескольким столбцам.

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')

Вариант 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])

Вариант 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]

M$'S Management Studio: после выделения и запуска первого блок, выделите как вариант 1, так и Вариант 2, щелкните правой кнопкой мыши -> [отобразить расчетный план выполнения]. Затем запустите все это, чтобы увидеть результаты.

Вариант 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

Вариант 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

Примечание:

Я обычно использую APPLY, когда я хочу, чтобы соединение было 1-к-(1 из многих).

Я использую соединение, если я хочу, чтобы соединение было 1-ко-многим или много-ко-многим.

Я избегаю CTE с ROW_NUMBER () если мне не нужно чтобы сделать что-то продвинутое, и я в порядке с штрафом за производительность окна.

Я также избегаю EXISTS / IN подзапросов в предложении WHERE или ON, поскольку я испытал это, вызывая некоторые ужасные планы выполнения. Но пробег варьируется. Просмотрите план выполнения и производительность профиля, где и когда это необходимо!

2

в сценариях, где вы хотите избежать использования row_count (), вы также можете использовать левое соединение:

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

для примера схемы вы также можете использовать "не в подзапросе", который обычно компилируется в тот же вывод, что и левое соединение:

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)

обратите внимание, что шаблон подзапроса не будет работать, если в таблице нет хотя бы одного одностолбцового уникального ключа/ограничения/индекса, в данном случае первичного ключа "Id".

оба этих запроса, как правило, больше "дорого", чем запрос row_count () (как измеряется анализатором запросов). Однако вы можете столкнуться со сценариями, в которых они быстрее возвращают результаты или включают другие оптимизации.

0

попробуйте это:

        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]
0
SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

Если вы хотите вернуть только недавний порядок документов по датам, он вернет только верхний 1 документ по DocumentID

0

Это самый ванильный TSQL, который я могу придумать

    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
-1

в SQLite проверяется, что вы можете использовать следующий простой запрос с ГРУППЫ ПО

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

здесь макс помогите получить максимум DateCreated от каждой группы.

но кажется, что MYSQL не связывает * - столбцы со значением max DateCreated : (

-2