Блог

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

 CREATE TABLE [dbo].[Event]
(
    Id int IDENTITY(1,1) NOT NULL,
    ApplicationId nvarchar(32) NOT NULL,
    Name nvarchar(128) NOT NULL,
    Description nvarchar(256) NULL,
    Date nvarchar(16) NOT NULL,
    Time nvarchar(16) NOT NULL,
    EventType nvarchar(16) NOT NULL,
    CONSTRAINT Event_PK PRIMARY KEY CLUSTERED ( Id ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS  = ON
    )
)
  

Итак, проблема в том, что я должен отобразить эти данные в таблице. Есть два требования. Первый — отображать все события независимо от того, какое приложение их выдало. Это просто — оператор select выполнит эту работу очень легко.

Второе требование — иметь возможность группировать события по Application . Другими словами, отображать все события таким образом, что, если ApplicationId повторяется более одного раза, захватывается только последняя запись для каждого приложения. Первичный ключ события (Id) на данный момент больше не нужен в этом запросе / представлении.

Вы также можете заметить, что дата и время события указаны в строковом формате. Это нормально, потому что они соответствуют стандартным форматам даты и времени: мм / дд / гггг и чч: мм: сс. Я могу извлечь их следующим образом:

 Convert( DateTime, (Date   ' '    Time)) AS 'TimeStamp'
  

Моя проблема в том, что если я использую АГРЕГАТНЫЕ функции для остальных столбцов, я не знаю, как они будут себя вести:

 SELECT
    ApplicationId,
    MAX(Name),
    MAX(Description),
    MAX( CONVERT(DateTime, (Date   ' '   Time))) AS 'TimeStamp',
    MAX( EventType )
FROM
    Event
GROUP BY
    ApplicationId
  

Причина, по которой я не решаюсь это сделать, заключается в том, что такая функция, как MAX , вернет наибольшее значение для данного столбца из (под) набора записей. Извлекать последнюю запись необязательно!

Есть идеи о том, как выбрать только последнюю запись для каждого приложения?

Вы можете использовать функцию ранжирования и общее табличное выражение.

 WITH e AS
(
     SELECT *,
         ROW_NUMBER() OVER
         (
             PARTITION BY ApplicationId
             ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC
         ) AS Recency
     FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1
  

Начиная с SQL Server 2012, вы можете просто

 SELECT 
    [Month]
    , [First] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month])
    , [Last]  = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month] DESC)
FROM 
    [dbo].[Table]
GROUP BY [Month]
ORDER BY [Month]
  

Вы можете использовать вложенный запрос с group by — аргумент group by необязательно должен присутствовать в select. Предполагается, что идентификатор автоматически увеличивается, так что самый большой из них является самым последним.

 SELECT
    ApplicationId,
    Name,
    Description,
    CONVERT(DateTime, (Date   ' '   Time)) AS 'TimeStamp',
    EventType
FROM
    Event e
WHERE
    Id in (select max(Id) from Event GROUP BY ApplicationId)
  

 SELECT
    E.ApplicationId,
    E.Name,
    E.Description,
    CONVERT(DateTime, (E.Date   ' '   E.Time)) AS 'TimeStamp',
    E.EventType
FROM
    Event E
    JOIN (SELECT ApplicationId,
                 MAX(CONVERT(DateTime, (Date   ' '   Time))) AS max_date
            FROM Event
        GROUP BY ApplicationId) EM 
      on EM.ApplicationId = E.ApplicationId
     and EM.max_date = CONVERT(DateTime, (E.Date   ' '   E.Time)))
  

Для этого можно использовать вложенный запрос или таблицу CTE:

 ;WITH CTE_LatestEvents as (
SELECT
    ApplicationId,    
    MAX( CONVERT(DateTime, (Date   ' '   Time))) AS 'LatestTimeStamp',
FROM
    Event
GROUP BY
    ApplicationId
)
SELECT
    ApplicationId,
    Name,
    Description,
    CONVERT(DateTime, (Date   ' '   Time))) AS 'TimeStamp',
    EventType
FROM
    Event e
    Join CTE_LatestEvents le 
        on e.applicationid = le.applicationid
        and CONVERT(DateTime, (e.Date   ' '   e.Time))) = le.LatestTimeStamp
  

Поскольку у вас там нет предложения where , подмножество записей — это все записи. Но я думаю, что вы указываете max в неправильных столбцах. Этот запрос даст вам то, что вы ищете.

 Select max(applicationid), name, description, CONVERT(DateTime, (Date   ' '   Time)) 
from event
group by name, description, CONVERT(DateTime, (Date   ' '   Time)) 
  

Я думаю, что это сработает для многих, желающих получить последнюю вставленную запись, и она должна быть сгруппирована по:

выберите * from (выберите * из ПОРЯДКА имен таблиц ПО идентификатору DESC) КАК x GROUP ПО имени поля

Это будет работать для следующего:

Структура таблицы Идентификатор Имя Статус 1 Junaid Да 2 Jawad Нет 3 Fahad Да 4 Junaid Нет 5 Kashif Да

Результаты после приведенного выше запроса Статус имени идентификатора 4 Junaid № 2 Jawad № 3 Fahad Да 4 Kashif Да

Это просто вывод последней записи группы по именам.

Через 6 лет еще один ответ для SQL Server:

 select t1.[Id], t2.[Value]  
from [dbo].[Table] t1  
  outer apply (  
    select top 1 [Value]  
      from [dbo].[Table] t2  
        where t2.[Month]=t1.[Month]  
      order by [dbo].[Date] desc  
  )  
  

Хотя мне гораздо больше нравится решение Postgresql с его отличной функцией on, которую удобнее вводить и намного эффективнее:

 select distinct on (id),val  
from tbl  
order by id,val  
  

Сначала я использовал CTE с row_number, но один пример из курса сертификации SQL server показал мне лучший пример (судя по получению постоянно улучшающихся планов выполнения):

 SELECT
  ApplicationId,
  Name,
  Description,
  CONVERT(DateTime, (Date   ' '   Time)) AS 'TimeStamp',
  EventType
FROM
  Event AS E
WHERE
  NOT EXISTS(SELECT * FROM Event AS Newer WHERE Newer.ApplicationId = E.ApplicationId AND Newer.Id > E.Id)
GROUP BY
  ApplicationId
  

Я предполагаю, что больший идентификатор подразумевает большую дату Время (в противном случае я бы использовал convert to datetime, что, однако, невозможно изменить). Этот запрос найдет самую молодую запись, для которой более молодая запись не существует. Если индексы установлены правильно, будет использоваться поиск по индексу. Альтернатива с функцией ранжирования обычно использует сканирование таблицы, поскольку она ранжирует все записи.

У меня была такая же проблема. Так вот, я не хотел чрезмерно усложнять ситуацию с CTE и «OVER». Вот простой пример. Я написал вложенный запрос с группой по максимуму (измененная дата). Возможно, вы захотите сделать это по ID, если это, например, int, это было бы более точно, чем Дата / Время. В любом случае, как только у вас есть этот подзапрос, вы просто присоединяете его к своему основному запросу, чтобы он действовал как фильтр для записей. Это так просто.

Таблица a — это моя таблица users. Таблица b — это подзапрос, а таблица c — это таблица, которую я хочу «отфильтровать».

 SELECT DISTINCT a.FirstName,a.LastName,a.ImagePath, c.MessageText
        FROM [AuthUsers] a 
            INNER JOIN (SELECT MessageFromId,MAX(DateEntered) AS LastEntered FROM ChatRoomConversation GROUP BY MessageFrom) AS b
                ON a.Id=b.MessageFromId
            INNER JOIN ChatRoomConversation c
                ON b.LastEntered=c.DateEntered