Wie zu tun, subselects in BigQuery

Wir haben eine riesige event-Tabelle mit Benutzer-Registrierung und das spielen unserer Spiele.

Nun möchte ich ermitteln, zweiten Tag Aufbewahrung für jeden Tag ist der Prozentsatz der Spieler, Anmeldung am Tag vorher spielt auch noch am Tag danach.

Also angenommen, wir haben drei Felder

timestamp ts
int  userId
int  eventId               (I.e. 1 = Register, 2 = Login)

Wie ist dies in BigQuery syntax? also ich möchte die folgende Ausgabe:

Date         Register    Logins day after    % Second day retention
2013-08-23   25 563      4 567               17.8

Habe ich nicht mit subselects und joins, aber es muss machbar sein!

InformationsquelleAutor Gunnar Eketrapp | 2013-08-27
  1. 10

    Wie über diese Abfrage mit öffentlichen Daten:

    SELECT
  a.day, first_day, return_next_day,
  integer((return_next_day /first_day) * 100) percent
FROM (
  SELECT COUNT(DISTINCT actor, 50000) first_day,
    STRFTIME_UTC_USEC(
      UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
  FROM
    [publicdata:samples.github_timeline]
  GROUP BY day) a
JOIN (
  SELECT
    COUNT(*) return_next_day, day
  FROM (
    SELECT
      a.day day, a.actor, b.day, b.actor
    FROM (
      SELECT
        STRFTIME_UTC_USEC(
          UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
        MAX(STRFTIME_UTC_USEC(86400000000 + UTC_USEC_TO_DAY(
          PARSE_UTC_USEC(created_at)), "%Y-%m-%d")) dayplus,
        actor
      FROM
        [publicdata:samples.github_timeline]
      GROUP EACH BY actor, day) a
    JOIN EACH (
      SELECT
        STRFTIME_UTC_USEC(
          UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
        actor
      FROM
        [publicdata:samples.github_timeline]
      GROUP EACH BY actor, day) b
      ON a.actor = b.actor
      AND a.dayplus = b.day
      )
  GROUP BY day) b
  ON a.day = b.day

    Das gibt mir die gewünschten Ergebnisse:

    Wie zu tun, subselects in BigQuery

    Hinweis: die Abfrage verwendet STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day viele Male, zu konvertieren, die Quelle string-Daten zu einem Datum. Wenn ich im Besitz der Daten, würde ich eine ETL-über den Tisch vorab, überspringen Sie diesen sich wiederholenden Schritt.

    Der Abfrage joins über 2 Tabellen:

    • Erste Tabelle zählt, wie viele verschiedene "Akteure", wo in einem bestimmten Datum. Hinweis: der zweite parameter in COUNT DISTINCT, dem Grafen präzise.

    • Zweiten Tabellen-JOINs einem bestimmten Tag mit dem nächsten Tag, gegeben, dass die gleichen Schauspieler in beiden Tagen. Dann können Sie zählen, wie viele Akteure zugegen, die in einem bestimmten Tag, und in den nächsten Tag.

    • Verbinden beider Tabellen erhalten Sie beide zählt, und Sie können fortfahren, sich zu teilen.

    Gibt es alternative Wege, dies ist nur einer von vielen Ansätzen. Es ist auch möglich, optimieren diese Abfrage noch weiter.

    InformationsquelleAutor Felipe Hoffa
Schreibe einen Kommentar