Symmetrische Summen

Symmetrische Summen in Looker sind eine sehr leistungsstarke Funktion. Da symmetrische Aggregate jedoch etwas einschüchternd wirken können und meist im Hintergrund ablaufen, kann es etwas verwirrend sein, wenn sie auftauchen. Auf dieser Seite finden Sie folgende Informationen zu symmetrischen Summen:

Warum symmetrische Summen erforderlich sind

SQL, die Sprache der Datenanalyse, ist äußerst leistungsfähig. Aber mit großer Macht geht auch große Verantwortung einher. Analysten müssen darauf achten, dass sie nicht versehentlich falsche Summen, Durchschnittswerte und Zählungen berechnen.

Es ist erstaunlich einfach, diese Berechnungen falsch durchzuführen. Diese Art von Fehlern kann für Analysten sehr frustrierend sein. Das folgende Beispiel zeigt, wie das schiefgehen kann.

Angenommen, Sie haben zwei Tabellen, orders und order_items. In der Tabelle order_items wird eine Zeile für jeden Artikel in einer Bestellung aufgezeichnet. Die Beziehung zwischen den Tabellen ist also eine 1:n-Beziehung. Die Beziehung ist eine 1:n-Beziehung, da ein Auftrag viele Artikel haben kann, aber jeder Artikel nur zu einem Auftrag gehören kann. Auf der Seite mit Best Practices Richtigen Beziehungsparameter festlegen finden Sie Informationen dazu, wie Sie die richtige Beziehung für eine Verbindung ermitteln.

Angenommen, die Tabelle orders sieht in diesem Beispiel so aus:

order_id user_id total order_date
1 100 50,36 $ 2017-12-01
2 101 24,12 $ 2017-12-02
3 137 50,36 $ 2017-12-02

In dieser orders-Tabelle ist die Summe der Werte in der Spalte total (SUM(total)) gleich 124.84.

Angenommen, die Tabelle order_items enthält sechs Zeilen:

order_id item_id quantity unit_price
1 50 1 23,00 $
1 63 2 13,68 $
2 63 1 13,68 $
2 72 1 5,08 $
2 79 1 5,36 $
3 78 1 50,36 $

Die Anzahl der bestellten Artikel zu ermitteln, ist ganz einfach. Die Summe der Werte in der Spalte quantity (SUM(quantity)) ist 7.

Angenommen, Sie führen einen Join zwischen der Tabelle orders und der Tabelle order_items über die gemeinsame Spalte order_id aus. Das Ergebnis ist die folgende Tabelle:

order_id user_id total order_date item_id quantity unit_price
1 100 50,36 $ 2017-12-01 50 1 23,00 $
1 100 50,36 $ 2017-12-01 63 2 13,68 $
2 101 24,12 $ 2017-12-02 63 1 13,68 $
2 101 24,12 $ 2017-12-02 72 1 5,08 $
2 101 24,12 $ 2017-12-02 79 1 5,36 $
3 137 50,36 $ 2017-12-02 78 1 50,36 $

Die Tabelle oben enthält neue Informationen, z. B. dass am 1. Dezember zwei Artikel (2017-12-01 in der Spalte order_date) und am 2. Dezember vier Artikel (2017-12-02) bestellt wurden. Einige der vorherigen Berechnungen, z. B. die SUM(quantity)-Berechnungen, sind weiterhin gültig. Wenn Sie jedoch versuchen, die Gesamtausgaben zu berechnen, stoßen Sie auf ein Problem.

Wenn Sie die vorherige Berechnung SUM(total) verwenden, wird der Gesamtwert 50.36 in der neuen Tabelle für Zeilen, in denen der Wert von order_id 1 ist, zweimal gezählt, da die Bestellung zwei verschiedene Artikel enthält (mit item_id-Werten von 50 und 63). Die Gesamtsumme von 24.12 für Zeilen, in denen order_id 2 ist, wird dreimal gezählt, da diese Bestellung drei verschiedene Artikel enthält. Daher ist das Ergebnis der Berechnung SUM(total) für diese Tabelle 223.44 anstelle der richtigen Antwort 124.84.

Bei zwei kleinen Beispieltabellen ist es zwar einfach, diese Art von Fehler zu vermeiden, aber in der Praxis mit vielen Tabellen und vielen Daten wäre die Lösung dieses Problems viel komplizierter. Genau diese Art von Fehlkalkulation kann jemand machen, ohne es zu merken. Dieses Problem wird durch symmetrische Aggregate gelöst.

Funktionsweise symmetrischer Summen

Mithilfe symmetrischer Aggregationen wird verhindert, dass Analysten und andere Nutzer von Looker versehentlich Summen, Durchschnittswerte und Zählungen falsch berechnen. Symmetrische Aggregate entlasten die Analysten, da sie sich darauf verlassen können, dass die Nutzer nicht mit falschen Daten arbeiten. Bei symmetrischen Aggregaten wird dafür gesorgt, dass jeder Wert in der Berechnung die richtige Anzahl von Malen gezählt wird. Außerdem wird genau darauf geachtet, was berechnet wird.

Im vorherigen Beispiel erkennt die Funktion für symmetrische Aggregate, dass total eine Eigenschaft von orders (nicht order_items) ist. Daher muss die Gesamtsumme jeder Bestellung nur einmal gezählt werden, um die richtige Antwort zu erhalten. Dazu verwendet die Funktion einen eindeutigen Primärschlüssel, den der Analyst in Looker definiert hat. Das bedeutet, dass Looker bei Berechnungen in der zusammengeführten Tabelle erkennt, dass die Gesamtzahl nicht zweimal gezählt werden sollte, obwohl es zwei Zeilen gibt, in denen der Wert für order_id 1 ist. Diese Gesamtzahl wurde bereits in die Berechnung einbezogen. Die Gesamtzahl sollte nur einmal für die drei Zeilen gezählt werden, in denen der Wert für order_id 2 ist.

Symmetrische Summen sind von einem eindeutigen Primärschlüssel und der korrekten Join-Beziehung abhängig, die im Modell angegeben werden müssen. Wenn die Ergebnisse also falsch aussehen, wenden Sie sich an einen Analysten, um sicherzustellen, dass alles richtig eingerichtet ist.

Warum symmetrische Summen kompliziert aussehen

Symmetric aggregates können etwas verwirrend sein. Ohne symmetrische Summen generiert Looker in der Regel gut funktionierenden SQL-Code, wie im folgenden Beispiel: 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

Bei symmetrischen Aggregaten könnte der SQL-Looker-Code in etwa so aussehen: 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

Das genaue Format von symmetrischen Summen hängt vom SQL-Dialekt ab, der in Looker verwendet wird. Alle Formate funktionieren jedoch auf die gleiche Weise: Wenn mehrere Zeilen denselben Primärschlüssel haben, werden sie von der Funktion für symmetrische Summen nur einmal gezählt. Dazu werden die wenig bekannten Funktionen SUM DISTINCT und AVG DISTINCT verwendet, die Teil des SQL-Standards sind.

Um zu sehen, wie das funktioniert, können Sie die vorherige Berechnung mit symmetrischen Aggregaten durchgehen. Von den sieben Spalten in den zusammengeführten Tabellen benötigen Sie nur zwei: die Spalte, die Sie aggregieren (total) und den eindeutigen Primärschlüssel für Bestellungen (order_id).

order_id total
1 50,36 $
1 50,36 $
2 24,12 $
2 24,12 $
2 24,12 $
3 50,26 $

Bei symmetrischen Aggregaten wird der Primärschlüssel (in diesem Fall order_id) verwendet, um für jeden eine sehr große Zahl zu generieren, die garantiert eindeutig ist und bei derselben Eingabe immer dieselbe Ausgabe liefert. (In der Regel geschieht dies mit einer Hash-Funktion, deren Details den Rahmen dieser Seite sprengen.) Das Ergebnis sollte in etwa so aussehen:

big_unique_number total
802959190063912 50,36 $
802959190063912 50,36 $
917651724816292 24,12 $
917651724816292 24,12 $
917651724816292 24,12 $
110506994770727 50,36 $

Anschließend führt Looker für jede Zeile folgende Schritte aus: 

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

So erhalten Sie zuverlässig die korrekt aggregierten Summen, wobei jede Summe genau die richtige Anzahl von Malen gezählt wird. Die Looker-Funktion für symmetrische Aggregate lässt sich nicht von wiederholten Zeilen oder mehreren Bestellungen mit derselben Gesamtsumme täuschen. Sie können die Berechnungen selbst durchführen, um ein besseres Gefühl für die Funktionsweise symmetrischer Summen zu bekommen.

Die dafür erforderliche SQL-Syntax ist nicht gerade schön anzusehen: Mit CAST(), md5(), SUM(DISTINCT) und STRTOL() würden Sie die SQL-Abfrage sicherlich nicht von Hand schreiben. Aber zum Glück müssen Sie das nicht tun – Looker kann die SQL-Abfrage für Sie schreiben.

Wenn eine Aggregation ohne symmetrische Aggregate funktioniert, erkennt Looker dies automatisch und verwendet die Funktion nicht. Da symmetrische Aggregate einige Leistungskosten verursachen, optimiert Looker die von ihm generierte SQL-Anfrage weiter, indem es erkennt, wann symmetrische Aggregate verwendet werden sollten und wann nicht. So wird die SQL-Anfrage so effizient wie möglich, ohne dass die richtige Antwort infrage gestellt wird.