J'essaie de calculer le décalage horaire entre 2 événements dans BigQuery (il s'agit de 2 événements personnalisés que nous avons configurés dans Firebase). Le premier est event_a, le second est un event_b qui est déclenché après event_a (peu importe quand).
J'ai essayé avec la requête suivante:
user_pseudo_id event timestamp aaa event_a 1587995938387000 bbb event_a 1590948191239003 aaa event_b 1587995943075005 ccc event_a 1589130017650008 aaa event_a 1593078261900005 aaa event_b 1593078881226002 bbb event_b 1590948208425007 ccc event_b 1589130462706020
Exemple de données:
SELECT round(AVG(time_diff),2) avg_duration_minutes
FROM(
SELECT user_pseudo_id,
CASE WHEN event_name = 'event_a' AND
LEAD(event_name,1) OVER(PARTITION BY user_id ORDER BY event_timestamp ASC) = 'event_b'
THEN TIMESTAMP_DIFF(TIMESTAMP_MICROS(LEAD(event_timestamp, 1) OVER(PARTITION BY user_id ORDER BY event_timestamp ASC)), TIMESTAMP_MICROS(event_timestamp), minute) END time_diff
FROM `database`
WHERE event_name in ('event_a', 'event_b')
)
where time_diff > 0.2
Le résultat que je voudrais obtenir est le temps moyen entre event_a et event_b par utilisateur et au total.
Avez-vous Aucune suggestion? il serait important de savoir combien de temps se passe entre 2 événements spécifiques (peu importe quand le second se produit).
3 Réponses :
Je répondrais comme ceci:
with data as (
select user_pseudo_id, event_name, event_timestamp from `database` where event_name in ('event_a', 'event_b')
),
ea as (
-- Get first event_a per user
select user_pseudo_id, min(event_timestamp) as first_a_ts from data where event_name = 'event_a' group by 1
),
eb as (
-- Get first event_b per user
select user_pseudo_id, min(event_timestamp) as first_b_ts from data where event_name = 'event_b' group by 1
),
joined (
-- Assume we only want to calculate duration if user has an event_b, hence inner join
select *
from ea
inner join eb using(user_pseudo_id)
where first_b_ts > first_a_ts
)
select
avg(timestamp_diff(first_b_ts, first_a_ts, second))/60.0 as avg_duration_minutes
from joined
Je n'ai pas inclus votre .2 car je ne sais pas pourquoi vous filtrez arbitrairement les différences inférieures à 12 secondes.
Si vous souhaitez obtenir l'heure de l'événement b après l'événement a, vous pouvez utiliser un minimum cumulatif conditionnel:
SELECT ab.*
FROM (SELECT user_pseudo_id, event_timestamp as event_a_timestamp,
MIN(CASE WHEN event_name = 'event_b' THEN event_timestamp END) OVER
(PARTITION BY user_id
ORDER BY event_timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as event_b_timestamp
FROM `database`
WHERE event_name in ('event_a', 'event_b')
) ab
WHERE event_name = 'event_a'
Votre question ne fournit pas suffisamment de détails pour déterminer ce qui pourrait avoir besoin d'autre à faire.
Ci-dessous, pour BigQuery Standard SQL
#standardSQL
SELECT
user_pseudo_id,
AVG(duration) AS avg_duration,
SUM(duration) AS total_duration
FROM (
SELECT *, LEAD(timestamp) OVER(win) - timestamp AS duration
FROM `project.dataset.table`
WHERE event IN ('event_a', 'event_b')
WINDOW win AS (PARTITION BY user_pseudo_id ORDER BY timestamp)
)
WHERE event = 'event_a'
GROUP BY user_pseudo_id
Veuillez fournir des exemples de données et les résultats souhaités. De plus, «ne semble pas correct» n'est pas utile. Décris le problème.
Je suis d'accord avec @GordonLinoff, pouvez-vous fournir des exemples de données et la sortie souhaitée. Il est donc possible d'écrire une requête et de la tester.
@GordonLinoff Je suis désolé que ce soit la première fois que j'utilise ce site Web. Est-ce mieux maintenant? J'ai ajouté un tableau simple avec des données similaires à celles que j'ai dans ma base de données