JOIN по последней строке в подзапросе в BigQuery

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

Таблица заказов

номер заказаДата заказаitem_skuitem_quantityitem_subtotal
12022-23-07Артикул 1712.34
Артикул219,99
22022-12-07Артикул 111.12
Артикул3532,54

Таблица истории цен

item_skuДата вступления в силуСтоимость
Артикул 12022-20-070,78
Артикул22022-02-034,50
Артикул 12022-02-030,56
Артикул32022-02-034.32

Желаемый результат

номер заказаДата заказаitem_skuitem_quantityitem_subtotalСтоимость
12022-23-07Артикул 1712.340,78
Артикул219,994,50
22022-12-07Артикул 111.120,56
Артикул3532,544.32

Я пытаюсь получить стоимость продукта, найдя стоимость на момент размещения заказа.

SELECT order_id, order_date,
  ARRAY(
    SELECT AS STRUCT
    item_sku,
    item_quantity,
    item_subtotal,
    cost.product_cost
    FROM UNNEST(line_items) as items
    JOIN `price_history_table` as cost
    ON items.item_sku = cost.sku AND effective_date < order_date 
  ) AS line_items,
  FROM
  `order_data_table`

Приведенный выше запрос работает, но создает отдельную строку массива line_item для каждой записи в таблице ценовой истории.

Как я могу сопоставить только самую последнюю цену для этого артикула. Я хочу добавить что-то вроде этого

ORDER BY effective_date DESC LIMIT 1

Но не могу понять, как его добавить.

Да, я знаю, что поставил даты в бессмысленном формате. Это всего лишь пример.

mister_b 28.08.2022 12:30
Получение данных из формы с помощью JavaScript - краткое руководство
Получение данных из формы с помощью JavaScript - краткое руководство
Получить данные из формы с помощью JS очень просто: вы запрашиваете элемент формы, передаете его конструктору new FormData() и, наконец, получаете...
Пользовательские правила валидации в Laravel
Пользовательские правила валидации в Laravel
Если вы хотите создать свое собственное правило валидации, Laravel предоставляет возможность сделать это. Создайте правило с помощью следующей...
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
1
1
108
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

How can I match on just the most recent price for that sku

Вам нужно добавить строку ниже в подзапрос и переместить объединение из выбора, чтобы решить проблему коррелированного подзапроса.

QUALIFY 1 = ROW_NUMBER() OVER(PARTITION BY item_sku ORDER BY effective_date DESC)    

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

SELECT order_id, order_date,
  ARRAY_AGG(line_item) AS line_items
FROM (
  SELECT order_id, order_date,
      STRUCT(item_sku,
      item_quantity,
      item_subtotal,
      cost.product_cost) AS line_item
  FROM `order_data_table`, UNNEST(line_items) AS items
  JOIN `price_history_table` AS cost
  ON items.item_sku = cost.sku AND effective_date < order_date 
  QUALIFY 1 = ROW_NUMBER() OVER(PARTITION BY order_id, order_date, item_sku ORDER BY effective_date DESC)
)
GROUP BY order_id, order_date   

с выходом

enter image description here

Спасибо за это, но это все еще не работает для меня. Я получаю сообщение об ошибке «Коррелированные подзапросы, которые ссылаются на другие таблицы, не поддерживаются, если их нельзя декоррелировать, например, путем преобразования их в эффективное СОЕДИНЕНИЕ».

mister_b 29.08.2022 12:28

я еще раз проверю и вернусь сюда...

Mikhail Berlyant 29.08.2022 16:31

смотрите обновление. проверено сейчас :о) так должно работать! Пожалуйста, попробуйте и дайте мне знать

Mikhail Berlyant 29.08.2022 18:38

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

mister_b 29.08.2022 20:20

На самом деле, это все еще не правильно. Он возвращает только около 10% строк, которые должен. Примерно 350 строк, тогда как должно быть около 3,7 тыс. Похоже, это как-то связано с разделением, как будто я удаляю эту строку, количество строк правильное (но для каждой строки для артикула в таблице ценовой истории есть подстрока line_item).

mister_b 30.08.2022 12:18

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

Mikhail Berlyant 30.08.2022 16:16

Боюсь, у меня та же проблема в упрощенном примере, приведенном в моем первоначальном вопросе. Просто работая с этим примером, я пытаюсь понять, что происходит в вашем решении с агрегацией массива и оконной функцией. Что-то между ними делает что-то смешное. Что также интересно, так это то, что я не только получаю подмножество ожидаемых результатов каждый раз, когда запускаю запрос, я получаю другое подмножество результатов.

mister_b 30.08.2022 17:17

как вы можете видеть, у меня есть скриншот вывода в моем ответе - и он выглядит совершенно правильно для меня на основе представленных входных данных в вашем вопросе. единственная причина, по которой я могу думать о том, почему вы не получаете то же самое, возможно, потому, что, как вы указали в своем комментарии Yes, I know I've put the dates in a nonsense format.

Mikhail Berlyant 30.08.2022 17:25

Спасибо, я ценю, что вы уделили мне время, чтобы ответить мне. Я еще больше упростил свой пример, убрав дату (и Effective_date) из уравнения. У него все та же проблема, но с некоторыми интересными наблюдениями. Похоже, что он возвращает только записи с уникальными кодами артикулов, следовательно, подмножество данных. Я предполагаю, что это связано с разделом на item_sku. Любопытно, почему у вас нет такой же проблемы в вашем примере.

mister_b 30.08.2022 17:45

Не обращайте на меня внимания, я только что вернулся к основам и скопировал ваше решение. Мне не хватало поля даты из раздела. Приносим свои извинения за дальнейшую трату вашего времени!

mister_b 30.08.2022 18:06

Другие вопросы по теме