Informationen zu symmetrischen Summen

Symmetrische Summen in Looker sind eine sehr leistungsstarke Funktion. Da symmetrische Summen jedoch etwas einschüchternd wirken können und meist hinter den Kulissen stattfinden, kann es ein wenig verwirrend sein, sie anzutreffen. Diese Seite enthält die folgenden 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 eine große Verantwortung einher, und Analysefachkräfte sind dafür verantwortlich, nicht versehentlich falsche Summen wie Summen, Durchschnittswerte und Zählungen zu berechnen.

Es ist überraschend einfach, diese Berechnungen falsch durchzuführen, und solche falschen Berechnungen können für Fachkräfte für Datenanalyse zu großer Frustration führen. Das folgende Beispiel zeigt, wie Sie schiefgehen können.

Angenommen, Sie haben zwei Tabellen, orders und order_items. In der Tabelle order_items wird für jedes Element in einer Bestellung eine Zeile aufgezeichnet. Die Beziehung zwischen den Tabellen ist also 1:n. Die Beziehung ist 1:n, da ein Auftrag viele Artikel enthalten kann, aber jedes Element nur Teil eines Auftrags sein kann. Auf der Seite Best Practices für Beziehungsparameter finden Sie eine Anleitung zur Bestimmung der richtigen Beziehung für einen Join.

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 Tabelle orders entspricht die Summe der Werte in der Spalte total (SUM(total)) 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

Es ist ganz einfach, die Anzahl der bestellten Artikel abzurufen. Die Summe der Werte in der Spalte quantity (SUM(quantity)) beträgt 7.

Angenommen, Sie führen die Tabelle orders und die Tabelle order_items über die gemeinsame Spalte order_id zusammen. Daraus ergibt sich 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 obige Tabelle enthält neue Informationen, zum Beispiel, dass zwei Artikel am 1. Dezember bestellt wurden (2017-12-01 in der Spalte order_date) und vier Artikel am 2. Dezember (2017-12-02). Einige der vorherigen Berechnungen, wie die SUM(quantity)-Berechnungen, sind immer noch gültig. Bei der Berechnung der Gesamtausgaben tritt jedoch ein Problem auf.

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

Dieser Fehler lässt sich zwar leicht vermeiden, wenn Sie mit zwei winzigen Beispieltabellen arbeiten, die Lösung dieses Problems wäre in der Praxis jedoch mit vielen Tabellen und vielen Daten wesentlich komplizierter. Genau so viele Fehlberechnungen könnte jemand machen, ohne es zu merken. Dieses Problem lösen symmetrische Summen.

Funktionsweise symmetrischer Summen

Symmetrische Summen verhindern, dass Analysten – und alle anderen, die Looker verwenden – Summen wie Summen, Durchschnittswerte und Zählungen versehentlich falsch berechnen. Symmetrische Summen tragen dazu bei, die Schultern von Fachkräften für Datenanalyse enorm zu entlasten, da die Fachkräfte für Datenanalyse darauf vertrauen können, dass die Nutzenden nicht mit falschen Daten im Voraus bezahlen. Symmetrische Summen erreichen dies, indem sichergestellt wird, dass jeder Fakt in der Berechnung die richtige Anzahl von Malen gezählt und erfasst wird, was Sie berechnen.

Im vorherigen Beispiel erkennt die Funktion für symmetrische Summen, dass total eine Eigenschaft von orders (nicht order_items) ist. Daher muss die Summe jeder Bestellung nur einmal gezählt werden, um die richtige Antwort zu erhalten. Die Funktion tut dies mithilfe eines eindeutigen Primärschlüssels, den die Fachkraft für Datenanalyse in Looker definiert hat. Wenn Looker also Berechnungen für die verknüpfte Tabelle durchführt, erkennt das Tool, dass die Gesamtzahl für die drei Zeilen, in denen order_id den Wert 2 hat, nur einmal gezählt werden, obwohl der Wert von order_id in zwei Zeilen 1 ist.

Beachten Sie, dass symmetrische Summen von einem eindeutigen Primärschlüssel und der richtigen Join-Beziehung im Modell abhängen. Wenn also die Ergebnisse falsch aussehen, wenden Sie sich an eine Fachkraft für Datenanalyse, um sicherzustellen, dass alles korrekt eingerichtet ist.

Warum symmetrische Summen kompliziert aussehen

Die Darstellung symmetrischer Summen kann etwas rätselhaft sein. Ohne symmetrische Summen schreibt Looker in der Regel eine gute und gut funktionierende SQL-Abfrage, 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 Summen könnten die SQL-Schreibvorgänge von Looker in etwa wie im folgenden Beispiel 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 symmetrischer Summen hängt vom Dialekt von SQL ab, den Looker schreibt. Alle Formate tun jedoch im Grunde dasselbe: Wenn mehrere Zeilen denselben Primärschlüssel haben, zählt die Funktion der symmetrischen Aggregatfunktion sie nur einmal. Dazu werden die wenig bekannten Funktionen SUM DISTINCT und AVG DISTINCT verwendet, die Teil des SQL-Standards sind.

Um zu sehen, wie dies geschieht, können Sie die zuvor durchgeführte Berechnung mit symmetrischen Summen durcharbeiten. Von den sieben Spalten in den verknüpften Tabellen benötigen Sie nur zwei: die, 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

Symmetrische Summen verwenden den Primärschlüssel (in diesem Fall order_id) und erstellen jeweils eine sehr große Zahl, die garantiert eindeutig ist und immer die gleiche Ausgabe für dieselbe Eingabe liefert. Dazu wird in der Regel eine Hash-Funktion verwendet, deren Details auf dieser Seite nicht erläutert werden. Das Ergebnis würde 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

Dann führt Looker für jede Zeile Folgendes aus: 

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

So erhalten Sie zuverlässig die korrekten aggregierten Gesamtwerte und zählen jede Summe genau richtig. Die Looker-Funktion für symmetrische Summen wird nicht durch wiederholte Zeilen oder mehrere Bestellungen mit der gleichen Summe getäuscht. Sie können versuchen, die Berechnungen selbst durchzuführen, um ein besseres Gefühl für die Funktionsweise symmetrischer Summen zu bekommen.

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

Wenn eine Aggregation ordnungsgemäß funktioniert, ohne dass symmetrische Summen erforderlich sind, erkennt Looker dies automatisch und verwendet die Funktion nicht. Da symmetrische Aggregationen einige Leistungskosten verursachen, kann Looker erkennen, wann und wann nicht. Dadurch wird der von Looker generierte SQL-Code noch weiter optimiert und so effizient wie möglich gemacht. Gleichzeitig wird die richtige Antwort garantiert.