Ежемесячные активные пользователи BigQuery?

В настоящее время я работаю над запросом из этого сообщения. Этот запрос написан на устаревшем SQL и не будет работать в моей среде. Я изменил запрос, чтобы использовать современные функции SQL, и обновил SELECT date as date, чтобы использовать timestamp_micros.

Я также должен упомянуть, что строки, которые я пытаюсь выбрать, поступают из Firebase Analytics.

Мой запрос:

SELECT 
    FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as date,
    SUM(CASE WHEN period = 7  THEN users END) as days_07,
    SUM(CASE WHEN period = 14 THEN users END) as days_14,
    SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
    SELECT
        FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as date,
        periods.period as period,
        COUNT(DISTINCT user_dim.app_info.app_instance_id) as users
    FROM `com_sidearm_fanapp_uiowa_IOS.*` as activity
    CROSS JOIN
        UNNEST(event_dim) as event  
    CROSS JOIN (
        SELECT 
            FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(event.timestamp_micros)) as date
        FROM `com_sidearm_fanapp_uiowa_IOS.*`
        CROSS JOIN
            UNNEST(event_dim) as event 
        GROUP BY event.timestamp_micros
    ) as dates
    CROSS JOIN (
        SELECT 
            period 
        FROM 
            (
                SELECT 7 as period 
                UNION ALL
                SELECT 14 as period 
                UNION ALL
                SELECT 30 as period
            )
    ) as periods
    WHERE 
        dates.date >= activity.date 
    AND 
        SAFE_CAST(FLOOR(TIMESTAMP_DIFF(dates.date, activity.date, DAY)/periods.period) AS INT64) = 0
    GROUP BY 1,2
)
CROSS JOIN 
    UNNEST(event_dim) as event
GROUP BY date
ORDER BY date DESC

firebase-analytics firebase sql google-bigquery
person Joe Scotto    schedule 19.01.2017    source источник
comment
Для других, посещающих этот пост, схема экспорта BQ определяется здесь.   -  person Kato    schedule 19.01.2017

Ответы (1)


arrow_upward
3
arrow_downward

Период имени столбца неоднозначен при ошибке [24:13].

чтобы исправить эту конкретную ошибку - вы должны исправить ниже

    CROSS JOIN (
        SELECT 
            period 
        FROM 
            (SELECT 7 as period), 
            (SELECT 14 as period), 
            (SELECT 30 as period)
    ) as periods

так должно получиться так:

    CROSS JOIN (
        SELECT 
            period 
        FROM 
            (SELECT 7 as period UNION ALL
            SELECT 14 as period UNION ALL
            SELECT 30 as period)
    ) as periods

Ответ на ваш обновленный вопрос

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

SELECT 
    date,
    SUM(CASE WHEN period = 7  THEN users END) as days_07,
    SUM(CASE WHEN period = 14 THEN users END) as days_14,
    SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
    SELECT 
        activity.date as date,
        periods.period as period,
        COUNT(DISTINCT user) as users
    FROM (
      SELECT
          event.timestamp_micros as date, 
           user_dim.app_info.app_instance_id as user
      FROM `yourTable` CROSS JOIN UNNEST(event_dim) as event  
    ) as activity
    CROSS JOIN (
        SELECT 
            event.timestamp_micros  as date
        FROM `yourTable` CROSS JOIN UNNEST(event_dim) as event 
        GROUP BY event.timestamp_micros
    ) as dates
    CROSS JOIN (
        SELECT period 
        FROM 
            (SELECT 7 as period UNION ALL 
            SELECT 14 as period UNION ALL
            SELECT 30 as period)
    ) as periods
    WHERE dates.date >= activity.date 
    AND SAFE_CAST(FLOOR(TIMESTAMP_DIFF(TIMESTAMP_MICROS(dates.date), TIMESTAMP_MICROS(activity.date), DAY)/periods.period) AS INT64) = 0
    GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC
person Mikhail Berlyant    schedule 19.01.2017
comment
Это работает, но теперь возникает другая ошибка: Name date not found inside activity at [31:32] - person Joe Scotto; 19.01.2017
comment
как вы упомянули в своем вопросе - исходный запрос был написан для другой таблицы, в которой есть поле date. поэтому вам нужно убедиться, что вы используете файл, который существует в вашей таблице - person Mikhail Berlyant; 19.01.2017
comment
Поля, которые я запрашиваю, существуют в моей таблице. Я уверен в этом. Я изменил date на timestamp_micros - person Joe Scotto; 19.01.2017
comment
См. Обновленный вопрос. Я изменил формат поля date, чтобы он соответствовал исходному устаревшему SQL-запросу. - person Joe Scotto; 19.01.2017
comment
@JoeScotto - так работает SO - если у вас есть новый вопрос, вы должны опубликовать его как НОВЫЙ вопрос. вместо этого вы постоянно меняете существующий вопрос, тем самым полностью аннулируя ответы на исходный вопрос! в порядке исключения (для себя) я отвечу еще раз - но в будущем - я не буду отвечать на такие нескончаемые вопросы «все в одном». И, кстати, обычно заголовок вашего вопроса должен быть не проектом или названием задачи, над которыми вы работаете, а скорее ясным, кратким и конкретным вопросом, который у вас есть. Я знаю, что это не всегда просто - но так работает SO - person Mikhail Berlyant; 19.01.2017
comment
Вы также можете прочитать Как задать вопрос. - person Mikhail Berlyant; 19.01.2017
comment
Кажется, работает, но исчерпывает ресурсы. Требуется уровень 20. Спасибо. - person Joe Scotto; 19.01.2017