Получить любое значение из столбца, агрегированного другим столбцом и где выбрана максимальная дата

Я новичок в SQL, и у меня есть таблица со следующими столбцами

+-------+--------+----+----+
| email |  date  | IP | ID |
+-------+--------+----+----+

И я хочу сделать что-то вроде этого:

SELECT T.email,
       Max(T.date),
       T.ip     AS User_IP,
       T.id     AS ID

FROM LoginTable as T

WHERE (IP IS NOT NULL)

GROUP BY T.email

Конечно, это не сработает, потому что IP и ID не входят в (максимальную) агрегатную функцию. Но мне нужно, чтобы IP-адрес соответствовал идентификатору и дате, поэтому я не могу использовать (max) в этих столбцах, потому что я получу результаты из разных строк, а это не вариант.

Подводя итог, мне нужно:

  • Одна строка для каждого уникального электронного письма (отсюда и группировка по)
  • Выбрана строка с самой последней датой -> max (дата)
  • Мне нужно, чтобы IP и ID были из той же строки, что и строка, выбранная max(date).

sql sql-server select top-n
person RustyRocketeer    schedule 27.11.2017    source источник
comment
@WhatsThePoint вернет более одной строки для каждого электронного письма, потому что все идентификаторы уникальны, а IP-адреса также могут не совпадать для одного электронного письма пользователя. Ответ от Мурейник и Джофаб работает как шарм. Благодарю вас!   -  person RustyRocketeer    schedule 27.11.2017

Ответы (4)


arrow_upward
4
arrow_downward

На помощь приходят оконные функции:

SELECT email, date, user_ip, id
FROM   (SELECT email, date, ip AS user_ip, logid AS id, 
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY date DESC) AS rn
        FROM   mytable
        WHERE  ip IS NOT NULL) t
WHERE  rn = 1

Примечание. Этот запрос извлечет только одну строку для каждого уникального электронного письма. Если вы хотите поддерживать записи, привязанные к максимальной дате, вы должны использовать rank() вместо row_number().

person Mureinik    schedule 27.11.2017

arrow_upward
2
arrow_downward

Вы можете использовать функции ранжирования доступен на сервере sql вместо GROUP BY.

Одним из примеров является ROW_NUMBER()

;WITH CTE AS (
 SELECT  ROW_NUMBER () OVER (PARTITION BY email ORDER BY DATE DESC) AS rn,
         email, 
         date,
         ip        AS User_IP,
         Logid     AS ID        
 FROM Table T WHERE (IP IS NOT NULL)
)
SELECT * FROM CTE WHERE RN=1
person jophab    schedule 27.11.2017
comment
Извините за глупый вопрос, а что означает первая строка в запросе? ;С ОТВ КАК ( - person RustyRocketeer; 27.11.2017
comment
Это обычное табличное выражение, подробнее здесь docs.microsoft.com/en-us/sql/t-sql/queries/ - person jophab; 28.11.2017

arrow_upward
1
arrow_downward

Это будет отлично работать, если у вас есть индекс на Table(email, date)

SELECT email  AS email,
  date        AS Date,
  ip          AS User_IP,
  Logid       AS ID
FROM Table
WHERE IP IS NOT NULL and 
      (email, date) = (
           SELECT email, Max(date) 
           FROM Table 
           GROUP BY email
       )
person Radim Bača    schedule 27.11.2017

arrow_upward
0
arrow_downward

Попробуйте этот ответ,

SELECT L.email  AS email,
L.date      AS Date,
L.ip        AS User_IP,
L.Logid     AS ID
FROM Table AS T
WHERE EXISTS(
    SELECT email,MAX(Date)Date 
    FROM Table 
    GROUP BY email) AND (IP IS NOT NULL)
GROUP BY T.email

Надеюсь, это поможет вам.

person DineshDB    schedule 27.11.2017