T-SQL Получить последнюю дневную метку времени по ID

Question

У нас есть таблица действий пользователей. Каждое действие регистрируется по идентификатору пользователя, отметке времени и идентификатору активности. Пользователь может иметь несколько строк для каждого идентификатора активности каждый день.

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

userID   timestamp  activityID
3241    10/14/2017 7:17 2
3241    10/15/2017 8:17 2
3241    10/16/2017 8:17 2
4355    10/15/2017 8:17 2
4355    10/16/2017 8:17 2
4355    10/17/2017 8:17 2
1234    10/15/2017 8:17 2
2236    10/15/2017 8:17 2
2236    10/16/2017 8:17 2
2002    10/17/2017 8:17 2

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

select t1.[userID], 
       t2.[mxdate]
from TableA t1
inner join
(
  select max([timestamp]) AS mxdate, 
         [userID]
    from TableA
    where activityType = 2
group by [userID]
) t2
  on t1.[userID] = t2.[userID]
  and t1.[timestamp] = t2.mxdate

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

Я направляюсь в правильном направлении по крайней мере? Если да, то какую часть / статью мне не хватает?

Заранее спасибо за любую помощь!

Answer 1

Группируя по части DATE вашей временной метки, вы можете получить максимальную временную метку для каждого действия:

;WITH cte ( userid, [day], activityid, [timestamp] ) AS (
    SELECT t.userid, [day] = CONVERT(NVARCHAR, t.timestamp, 101), t.activityid, MAX(t.timestamp)
      FROM MyTable t
     GROUP BY t.userid, CONVERT(NVARCHAR, t.timestamp, 101), t.activityid
)
SELECT *
  FROM cte
 WHERE cte.activityid = 2

Необходимо было преобразовать метку времени (предположительно DATETIME построить) до ДАТЫ, чтобы по мере роста таблицы и охвата нескольких месяцев вы не группировались в один и тот же день в два разных месяца.

Использование производной таблицы в одном запросе:

SELECT t.userid, t.activityid, t.timestamp
  FROM (SELECT i.userid, i.activityid, maxTS = MAX(i.timestamp)
          FROM MyTable i
         GROUP BY i.userid, i.activityid, CONVERT(DATE, i.timestamp)) maxRows
         INNER JOIN MyTable t
           ON maxRows.userid = t.userid
          AND maxRows.activityid = t.activityid
          AND maxRows.maxTS = t.timestamp
 WHERE t.activity = 2