在BigQuery中筛选同日安装卸载应用的用户并计算耗时

Hey there! Let's figure out how to fix your BigQuery query and get the results you need—users who did both first_open and app_remove on the same day, plus the time difference between those two actions.

Why Your Original Approach Isn't Working #

First, let's break down why your initial attempts didn't give the right output:

• Using OR in the WHERE clause pulls all first_open and app_remove events, but it doesn't filter for users who have both events on the same day. That's why you're seeing lots of rows where a user only did one of the actions.
• Using AND is impossible here—each row only has one event_name, so no single row will ever match both conditions at the same time.

Solution 1: Window Functions (Clean & Flexible) #

This method uses window functions to flag users who have both events on a given day, then pivots the timestamps to calculate the time difference easily.

WITH user_daily_events AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    user_pseudo_id,
    event_name,
    TIMESTAMP_MICROS(event_timestamp) AS event_time,
    -- Count how many distinct event types this user has on the day
    COUNT(DISTINCT event_name) OVER (PARTITION BY user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp))) AS event_type_count
  FROM `mybits-54f8c.analytics_179636122.events_20200917`
  WHERE event_name IN ("app_remove", "first_open")
),
pivoted_timestamps AS (
  SELECT
    event_date AS Date,
    user_pseudo_id,
    -- Grab the timestamp of the first_open event for the user/day
    MAX(IF(event_name = "first_open", event_time, NULL)) AS first_open_time,
    -- Grab the timestamp of the app_remove event for the user/day
    MAX(IF(event_name = "app_remove", event_time, NULL)) AS app_remove_time
  FROM user_daily_events
  WHERE event_type_count = 2 -- Only keep users with both events that day
  GROUP BY event_date, user_pseudo_id
)
SELECT
  Date,
  user_pseudo_id,
  first_open_time,
  app_remove_time,
  -- Calculate time difference (use SECOND, MINUTE, HOUR, etc. as needed)
  TIMESTAMP_DIFF(app_remove_time, first_open_time, SECOND) AS time_diff_seconds
FROM pivoted_timestamps
ORDER BY user_pseudo_id, Date;

How This Works:

1. The first CTE (user_daily_events) adds a count of distinct event types per user per day. If a user has both events, this count will be 2.
2. The second CTE (pivoted_timestamps) groups by user and date, then extracts the timestamps for each event type using MAX() (use MIN() instead if you want the earliest event of each type).
3. The final query calculates the time difference between the two events with TIMESTAMP_DIFF—adjust the interval (like MINUTE or HOUR) based on your needs.

Solution 2: Self-Join (Straightforward) #

If you prefer a more direct approach, you can join the table to itself to match first_open and app_remove events for the same user on the same day.

SELECT
  DATE(TIMESTAMP_MICROS(fo.event_timestamp)) AS Date,
  fo.user_pseudo_id,
  TIMESTAMP_MICROS(fo.event_timestamp) AS first_open_time,
  TIMESTAMP_MICROS(ar.event_timestamp) AS app_remove_time,
  TIMESTAMP_DIFF(TIMESTAMP_MICROS(ar.event_timestamp), TIMESTAMP_MICROS(fo.event_timestamp), SECOND) AS time_diff_seconds
FROM `mybits-54f8c.analytics_179636122.events_20200917` fo
INNER JOIN `mybits-54f8c.analytics_179636122.events_20200917` ar
  ON fo.user_pseudo_id = ar.user_pseudo_id
  AND DATE(TIMESTAMP_MICROS(fo.event_timestamp)) = DATE(TIMESTAMP_MICROS(ar.event_timestamp))
WHERE fo.event_name = "first_open"
  AND ar.event_name = "app_remove"
ORDER BY fo.user_pseudo_id, Date;

Note:

If a user has multiple first_open or app_remove events on the same day, this join will return duplicate rows (one for each combination of events). If that's an issue, you can add DISTINCT or aggregate to pick the latest/earliest timestamps.

内容的提问来源于stack exchange，提问作者Shantanu Jain