Tengo dos mesas. Uno es una lista de Orders y el otro es una lista de Events .
Para cada Order , quiero unirme al último Event único que ocurrió (usando clicked_at ) antes del created_at del Order .
Probé varias formas de hacer que esto funcionara y probé varias otras respuestas en Stack Overflow, pero tengo dificultades para devolver los datos correctos.
La lógica sudo para la subconsulta en mi mente es algo así como:
SELECT campaign, user_id, created_at FROM `Events` WHERE order.user_id = user_id AND clicked_at < order.created_at ORDER created_at DESC LIMIT 1Por favor, consulte los datos de ejemplo a continuación:
# Orders | order_id | user_id | created_at | ----------------------------------- | 123 | abc | 2020-07-04 | | 456 | abc | 2020-05-01 | # Events | campaign | keyword | user_id | clicked_at | ---------------------------------------------- | facebook | shoes | abc | 2020-07-03 | | google | hair | abc | 2020-07-01 |mi resultado deseado
# Orders with campaign attribution | order_id | user_id | created_at | campaign | keyword | --------------------------------------------------------- | 123 | abc | 2020-07-04 | facebook | shoes | | 456 | abc | 2020-06-04 | null | null |¡Gracias! Alex
with orders as ( select 123 as order_id, 'abc' as user_id, cast('2020-07-04' as date) as created_at union all select 456, 'abc', '2020-05-01' ), events as ( select 'facebook' as campaign, 'shoes' as keyword, 'abc' as user_id, cast('2020-07-03' as date) as clicked_at union all select 'google', 'hair', 'abc', '2020-07-01' ), logic as ( select orders.order_id, orders.user_id, orders.created_at, events.clicked_at, events.campaign, events.keyword, row_number() over (partition by orders.order_id order by events.clicked_at desc) as rn from orders left join events on orders.user_id = events.user_id and events.clicked_at < orders.created_at ) select * except(rn) from logic where rn = 1A continuación se muestra el SQL estándar de BigQuery
#standardSQL SELECT a.*, campaign, keyword FROM `project.dataset.orders` a LEFT JOIN ( SELECT ANY_VALUE(o).*, ARRAY_AGG(STRUCT(campaign, keyword) ORDER BY clicked_at DESC LIMIT 1)[OFFSET(0)].* FROM `project.dataset.orders` o JOIN `project.dataset.events` e ON o.user_id = e.user_id AND clicked_at < created_at GROUP BY FORMAT('%t', o) ) USING(order_id)si se aplica a datos de muestra de nuestra pregunta, el resultado es
Row order_id user_id created_at campaign keyword 1 123 abc 2020-07-04 facebook shoes 2 456 abc 2020-05-01 null null