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

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

DocumentStatusLogs Table

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

ОБНОВЛЕНИЕ Я только что узнал, как использовать "применить", что облегчает решение таких проблем.

Ответ 1

;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-х местах.
сохранить историю состояния
...

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

Ответ 2

Я только что научился использовать 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

Ответ 3

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

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

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

Ответ 4

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

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

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

Кстати, если у вас уже есть столбец DateCreated в таблице "Документы", вы можете просто присоединиться к DocumentStatusLogs с помощью этого (пока DateCreated уникален в DocumentStatusLogs).

Изменить: MsSQL не поддерживает ИСПОЛЬЗОВАНИЕ, поэтому измените его на:

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

Ответ 5

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

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

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

Ответ 6

Я знаю, что это старая ветка, но решения 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)

Подробнее о предложении TOP можно узнать здесь.

Ответ 7

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

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

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]

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

Ответ 8

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

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 и выше. First_value можно рассматривать как способ выполнения Select Top 1 при использовании предложения over. Over позволяет группировать в списке выбора, поэтому вместо написания вложенных подзапросов (как это делают многие из существующих ответов) это делается более читабельно. Надеюсь это поможет.

Ответ 9

Мой код, чтобы выбрать топ 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
)

Ответ 10

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

Интересна производительность между двумя запросами ниже. 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 $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-to-(1 из многих).

Я использую JOIN, если я хочу, чтобы соединение было 1-to-many или many-to-many.

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

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

Ответ 11

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

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

Ответ 12

В сценариях, в которых вы хотите избежать использования 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() (измеренный Query Analyzer). Однако вы можете столкнуться с сценариями, в которых они быстрее возвращают результаты или разрешают другие оптимизации.

Ответ 13

Попробуйте следующее:

        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]

Ответ 14

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;

Ответ 15

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

Подход 1: Использование ROW_NUMBER(). Если индекс хранилища строк не может повысить производительность, вы можете попробовать некластеризованный/кластеризованный индекс columnstore, как для запросов с агрегацией и группировкой, так и для таблиц, которые всегда упорядочены по разным столбцам, индекс columnstore обычно является лучшим выбором.

;WITH CTE AS
    (
       SELECT   *,
                RN = ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
       FROM     DocumentStatusLogs
    )
    SELECT  ID      
        ,DocumentID 
        ,Status     
        ,DateCreated
    FROM    CTE
    WHERE   RN = 1;

Подход 2: Использование FIRST_VALUE. Если индекс хранилища строк не может повысить производительность, вы можете попробовать некластеризованный/кластеризованный индекс columnstore, как для запросов с агрегацией и группировкой, так и для таблиц, которые всегда упорядочены по разным столбцам, индекс columnstore обычно является лучшим выбором.

SELECT  DISTINCT
    ID      = FIRST_VALUE(ID) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DocumentID
    ,Status     = FIRST_VALUE(Status) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DateCreated    = FIRST_VALUE(DateCreated) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
FROM    DocumentStatusLogs;

Подход 3: Использование CROSS APPLY. Создание индекса хранилища строк в таблице DocumentStatusLogs, охватывающей столбцы, используемые в запросе, должно быть достаточным для покрытия запроса без необходимости в индексе columnstore.

SELECT  DISTINCT
    ID      = CA.ID
    ,DocumentID = D.DocumentID
    ,Status     = CA.Status 
    ,DateCreated    = CA.DateCreated
FROM    DocumentStatusLogs D
    CROSS APPLY (
            SELECT  TOP 1 I.*
            FROM    DocumentStatusLogs I
            WHERE   I.DocumentID = D.DocumentID
            ORDER   BY I.DateCreated DESC
            ) CA;

Ответ 16

SELECT documentid, 
       status, 
       datecreated 
FROM   documentstatuslogs dlogs 
WHERE  status = (SELECT status 
                 FROM   documentstatuslogs 
                 WHERE  documentid = dlogs.documentid 
                 ORDER  BY datecreated DESC 
                 LIMIT  1)

Ответ 17

Это самый ванильный 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

Ответ 18

В SQLite проверяется, что вы можете использовать следующий простой запрос с GROUP BY

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

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

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