Использование <= в подзапросе для вывода результатов
У меня есть запрос, который сначала объявляет и вставляет данные во временную таблицу. Эта таблица используется для фильтрации основной таблицы на сервере sql, содержащей данные о клиентах.
Запрос с фиктивными данными, заполненными во временную таблицу, выглядит следующим образом:
Declare @myInputTable TABLE (Customer_input INT, Date_of_interest bigint)
insert into @myInputTable values(12345, '20140924'),(22234,'20210508')
select top 1
MainTable.CustomerID,
MainTable.PurchaseDate
From MainTable
Where (customerID in (select Customer_input from @myInputTable) and PurchaseDate <= (select Date_of_interest from @myInputTable))
Когда я комментирую 2-го клиента + интересующую дату, запрос выполняется нормально и дает мне результат, но в тот момент, когда он имеет дело с несколькими комбинациями клиентов и интересующей даты, он выдает следующую ошибку:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Я понимаю, что проблема заключается в "PurchaseDate <=...", но я не уверен, как еще я могу отфильтровать результаты, чтобы они соответствовали идентификаторам клиентов, и выводить только самые близкие записи даты покупки к дате, интересующей каждого клиента. Любые указатели будут оценены!
Тестовые данные из MainTable:
| Пользовательский ИД | Дата покупки |
|---|---|
| 12345 | 20150120 |
| 12345 | 20140213 |
| 12345 | 20120811 |
| 22234 | 20210419 |
| 22234 | 20220322 |
Ожидаемый результат запроса в целом:
| Пользовательский ИД | Дата покупки |
|---|---|
| 12345 | 20140213 |
| 22234 | 20210419 |
Запрос должен исключать все PurchaseDate, которые >Date_of_interest, и отображать только ближайшую прошлую PurchaseDate к Date_of_interest данного CustomerID (сопоставленного с Customer_input). Клиент 12345 имеет 2 подходящие даты (20140213 и 20120811), но выводится только 20140213, поскольку он ближе всего к интересующей дате, указанной в myInputTable (12345, '20140924')
В дополнение к тому, что сказал Дейл, вы можете использовать этот подзапрос, если включите TOP 1, чтобы задать одно значение, но, конечно, вы должны быть уверены, что получаете правильное значение.
спасибо за быстрый ответ @DaleK и Эндрю! Я отредактировал вопрос, чтобы предоставить образцы данных и ожидаемый результат.
Я вижу сейчас. Вам нужно ПРИСОЕДИНЯТЬСЯ @myInputTable и не использовать его в условии. Таким образом, для каждой строки в исходной таблице необходимо получить доступ к обоим значениям из таблицы переменных.
Я добавил ответ, но не проверял его. Если вы можете предоставить полный рабочий образец, я могу попробовать, если он не сработает.
Ответы 3
Поскольку вам нужно сопоставить каждую исходную строку со строкой из вашей таблицы переменных, вы должны выполнить соединение, а не просто проверить, существует ли где-то в ней значение. А поскольку вам нужно только одно значение для каждого клиента, вам нужно будет использовать ROW_NUMBER. Может быть что-то вроде этого (не проверено):
SELECT *
FROM (
SELECT
MT.CustomerID,
MT.PurchaseDate,
Number = ROW_NUMBER() OVER (PARTITION BY MT.CustomerID ORDER BY MT.PurchaseDate DESC)
FROM MainTable MT
INNER JOIN @myInputTable IT
ON MT.CustomerID = IT.Customer_input AND MT.PurchaseDate <= IT.Date_of_interest
) Subquery
WHERE Number = 1
ORDER BY CustomerID, PurchaseDate
Похоже, row_number вызывает проблемы в предложении Where. Я получаю следующую ошибку: оконные функции могут появляться только в предложениях SELECT или ORDER BY. Я попытался использовать select top 1, но это дает мне только 1 строку вместо 1 строки для каждого клиента.
Правильно, я думаю, вам нужно будет использовать подзапрос для этого. Проверьте обновленный ответ, пожалуйста.
Запрос Стю, вероятно, лучше, но я оставлю этот в качестве альтернативы, а также на случай, если пример ROW_NUMBER может пригодиться позже в другой ситуации.
Это также может быть достигнуто путем внутреннего соединения с переменной таблицы и ее агрегирования.
DECLARE @myInputTable TABLE ( Customer_input INT, Date_of_interest INT ); insert into @myInputTable values (12345, '20140924') , (22234, '20210508') ; SELECT t.CustomerID , MAX(PurchaseDate) AS PurchaseDate FROM MainTable t JOIN @myInputTable i ON i.Customer_input = t.CustomerID AND i.Date_of_interest >= t.PurchaseDate GROUP BY t.CustomerID, i.Date_of_interest
CustomerID PurchaseDate 12345 20140213 22234 20210419
Тест на дб <> рабочий пример здесь
Примечание: столбцы, содержащие даты, должны иметь тип данных DATE.
Вы можете вставлять даты в том же формате «ггггММдд».
Я думаю Date_of_interest не должно быть в группе by.
Я бы посоветовал все, что вам нужно, это внутреннее соединение, а затем простое агрегирование для желаемых результатов:
select m.customerId, Max(PurchaseDate) PurchaseDate
From @myInputTable t
join MainTable m on m.CustomerID = t.Customer_input and m.PurchaseDate <= t.Date_of_interest
group by m.customerId;
Это, вероятно, лучше, чем мой ответ. :D
Интересно, спасибо, тоже попробую. Код Эндрю также работал хорошо. Я бы подумал, что Max(PurchaseDate) будет выполняться первым перед соединением, но я предполагаю, что мой порядок операций был неправильным.
Как это обычно бывает, есть много способов добиться одного и того же вывода в SQL. В зависимости от доступных индексов и кардинальности данных этот метод наверное будет более производительным.
Этот
select Date_of_interest from @myInputTableвозвращает несколько результатов, что не имеет смысла в контексте вашего запроса? Вы можете сравнивать только с одним скалярным значением — какое из них вы хотите использовать? Предоставление образцов данных и желаемых результатов сделает ваш вопрос более ясным.