用户的最后一个事件

Question

你能帮我吗？

我有下面的查询，它可以工作并满足我的需求，但我不知道它是否以最佳方式完成，以及是否有一种不那么复杂的方法来构建查询。

目的：捕获user_id的最后一个事件(event_name(，每个用户只捕获一个事件和最近的事件。在Bigquery环境中为GA4事件表开发的查询

CAST(CONCAT(SUBSTR(t2.event_date, 0, 4), '-', SUBSTR(t2.event_date, 5, 2), '-', SUBSTR(t2.event_date, 7, 2)) AS DATE) AS event_timestamp,
DATE_DIFF(CURRENT_DATE("UTC-3"),CAST(CONCAT(SUBSTR(t2.event_date, 0, 4), '-', SUBSTR(t2.event_date, 5, 2), '-', SUBSTR(t2.event_date, 7, 2)) AS DATE),day) AS days_ult_event,
t1.user_id,
t2.event_name
FROM (
SELECT
MAX(TIMESTAMP_MICROS(event_timestamp)) event_timestamp,
USER_ID
FROM
`events_*`
WHERE
user_id IS NOT NULL
GROUP BY
2) t1
LEFT JOIN (
SELECT
DISTINCT user_id,
event_name,
event_date,
TIMESTAMP_MICROS(event_timestamp) AS event_timestamp
FROM
`events_*`) t2
ON
t1.user_id = t2.user_id
AND t1.event_timestamp = t2.event_timestamp

Answer 1

您的方法是有效的，但它不是很好，有点难以阅读。要为用户返回最新事件，请尝试使用row_number((窗口函数：

with _latest as (
SELECT user_id,
event_name,
event_date,
TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
row_number() over (partition by user_id order by TIMESTAMP_MICROS(event_timestamp) desc) as rn
FROM `events_*`
)
select *
from _latest
where rn=1

BigQuery最佳实践建议使用Offset:

SELECT user_id, event_name, event_date,
array_agg(TIMESTAMP_MICROS(event_timestamp) order by event_timestamp desc) [offset(0)] AS event_timestamp
FROM `events_*`
group by user_id, event_name, event_date