Блог

У меня есть запрос в базе данных Oracle, который я пытаюсь выяснить, почему он отфильтровывает данные. Это то, что бывший сотрудник использовал в запросе для получения определенных клиентов. Итак, у меня есть данные, определенные следующим образом:

 CUSTOMER_ID TYPE CHANGE_DATE
12356       T    12/1/2017
12356       T    6/1/2016
12356       T    11/11/2015
23452       T    7/1/2014
23452       T    7/1/2015
54674       T    1/1/2019
54674       T    11/1/2019
34653       T    12/1/2018
34653       T    1/1/2012
  

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

 SELECT CUSTOMER_ID, "TYPE", MAX(CHANGE_DATE) FROM CUSTOMER_INFO WHERE TYPE = 'T'
GROUP BY CUSTOMER_ID, "TYPE"
  

он возвращает максимальное значение для каждого клиента, как и ожидалось.

 CUSTOMER_ID TYPE CHANGE_DATE
12356       T    12/1/2017
23452       T    7/1/2015
54674       T    11/1/2019
34653       T    12/1/2018
  

Тем не менее, рассматриваемый запрос структурирован следующим образом, с подзапросом к той же таблице CUSTOMER_INFO в поле CHANGE_DATE.

 SELECT CUSTOMER_ID, "TYPE", CHANGE_DATE 
FROM CUSTOMER_INFO B 
WHERE "TYPE" = 'T' AND CHANGE_DATE = 
    (SELECT MAX(CHANGE_DATE) 
    FROM CUSTOMER_INFO 
    WHERE CUSTOMER_ID = B.CUSTOMER_ID)
  

Этот запрос вырезает половину результатов, например:

 CUSTOMER_ID TYPE CHANGE_DATE
54674       T    11/1/2019
34653       T    12/1/2018
  

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

 SELECT CUSTOMER_ID, "TYPE", CHANGE_DATE 
FROM CUSTOMER_INFO B 
WHERE "TYPE" = 'T' AND CHANGE_DATE = 
    (SELECT MAX(CHANGE_DATE) 
    FROM CUSTOMER_INFO 
    WHERE CUSTOMER_ID = B.CUSTOMER_ID)
  

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

Ваш более простой код не беспокоится о том, что максимальное значение change_date может быть любого типа.

Если вы хотите имитировать второй запрос, требующий только одного прохода вашей таблицы, вы могли бы:

 select * from 
(
select customer_id, type, change_date, rank() over (partition by customer_id order by change_date) rnk
from customer info 
)
where rnk = 1
and   type = 'T'
  

Вам также необходимо выполнить фильтрацию в подзапросе:

 SELECT CUSTOMER_ID, "TYPE", CHANGE_DATE 
FROM CUSTOMER_INFO CI
WHERE "TYPE" = 'T' AND CHANGE_DATE = 
    (SELECT MAX(CI2.CHANGE_DATE) 
     FROM CUSTOMER_INFO CI2
     WHERE CI2."TYPE" = CI."TYPE" AND
           CI2.CUSTOMER_ID = CI.CUSTOMER_ID
    );
  

Вы можете сделать это, используя только агрегацию с KEEP :

 SELECT CUSTOMER_ID,
       MAX("TYPE") KEEP (DENSE_RANK FIRST ORDER BY CHANGE_DATE DESC) as "TYPE",
       MAX(CHANGE_DATE)
FROM CUSTOMER_INFO WHERE TYPE = 'T'
GROUP BY CUSTOMER_ID