Избавится от SELECT в выражении WHERE

194

10 июня 2018, 18:30

Есть такая таблица в БД:

|UserId   |DateTo             |serviceCode|status|lastDate           |
|---------|-------------------|-----------|------|-------------------|
|116750128|2018-03-11 22:00:00|PE0753.24  |1     |2018-02-12 20:31:43|
|116750128|2018-03-29 22:00:00|PE0753.24  |1     |2018-01-29 22:00:00|
|116750128|2038-01-19 06:14:07|PE0753.24  |1     |2018-01-30 22:00:00|

Необходимо выбрать lastDate только у тех записей, у которых DateTo равна максимуму среди DateTo которые относятся к UserId, serviceCode и status.

Т.е. в данном случае должно вернутся 2018-01-30 22:00:00, т.к. DateTo=2038-01-19 06:14:07 максимуму, среди UserId=116750128 и serviceCode=PE0753.24 и status=1.

Смог придумать такой запрос:

SELECT
  p1.lastDate, p1.UserId, p1.serviceCode
FROM Users.UserInfo p1
WHERE
  p1.status= 1
  AND p1.UserId>= 116750128
  AND p1.UserId< 116750129
  AND p1.DateTo= (SELECT max(p2.dateTo) FROM Users.UserInfo p2
                      WHERE p2.UserId= p1.UserId
                            AND p2.status= p1.status
                            AND p2.serviceCode= p1.serviceCode);

Может есть способ получше, не нравится подселект в конструкции WHERE. Данный запрос долго отрабатывает на таблице с порядком 200 млн записей.

Answer 1

Для реализации воспользуемся оператором группировки (GROUP BY), сделаем группировку по userId и serviceCode, по status делать не будем, так как он явно указан в запросе. В SELECT выберем максимальную дату из группы MAX(p1.lastDate).

SELECT
  p1.UserId, p1.serviceCode, MAX(p1.lastDate) maxDate
FROM Users.UserInfo p1
WHERE
  p1.status = 1
  AND p1.UserId >= 116750128
  AND p1.UserId < 116750129
  GROUP BY p1.userId, p1.serviceCode;

Answer 2

SELECT p1.lastDate, p1.UserId, p1.serviceCode
  FROM Users.UserInfo p1
  JOIN (SELECT UserId, serviceCode, max(dateTo) dateTo
          FROM Users.UserInfo
         WHERE UserId >= 116750128 AND UserId < 116750129
           AND status = 1
         GROUP BY UserId, serviceCode
       ) p2
    ON p2.UserId = p1.UserId AND p2.serviceCode = p1.serviceCode
   AND p1.dateTo = p2.dateTo
 WHERE p1.status = 1

Должно работать быстро, при наличии индекса по UserId (В идеале по UserId, serviceCode, dateTo если записей с одним userId очень много, но такой индекс сам по себе очень большой и может тормозить вставки)