Symmetrische Summen

Symmetrische Summen in Looker sind eine sehr leistungsstarke Funktion. Da symmetrische Summen jedoch etwas einschüchternd wirken und meistens im Hintergrund ablaufen, kann es etwas verwirrend sein, sie zu begegnen. Auf dieser Seite finden Sie die folgenden Informationen zu symmetrischen Summen:

Warum symmetrische Summen erforderlich sind

SQL, die Sprache der Datenanalyse, ist extrem leistungsstark. Aber große Leistung bringt auch große Verantwortung mit sich, und Analysten müssen vermeiden, versehentlich falsche Summen, wie Summen, Durchschnittswerte und Zahlen zu berechnen.

Es ist überraschend leicht, diese Berechnungen falsch durchzuführen, und solche Fehlberechnungen können bei Fachkräften für Datenanalyse zu großer Frustration führen. Das folgende Beispiel zeigt, wie Sie Fehler machen können.

Angenommen, Sie haben zwei Tabellen, orders und order_items. In der Tabelle order_items wird eine Zeile für jedes Element in einem Auftrag erfasst, sodass die Beziehung zwischen den Tabellen 1:n ist. Die Beziehung ist eine 1:n-Beziehung, da ein Auftrag viele Artikel umfassen kann, aber jeder Artikel nur Teil eines Auftrags sein kann. Auf der Seite mit den Best Practices für Beziehungsparameter wird beschrieben, wie Sie die richtige Beziehung für einen Join 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. Daraus ergibt sich 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, z. B. 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 noch 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 mit dem Wert von order_id 1 zweimal gezählt, da die Bestellung zwei verschiedene Artikel enthält (mit den item_id-Werten 50 und 63). Die Summe der 24.12 für Zeilen, in denen order_id den Wert 2 enthält, wird dreimal gezählt, da diese Bestellung drei verschiedene Artikel enthält. Das Ergebnis der Berechnung SUM(total) für diese Tabelle lautet daher 223.44 und nicht die richtige Antwort, nämlich 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. Das ist genau die Art von Fehleinschätzungen, die jemand machen könnte, ohne es zu merken. Dieses Problem wird durch symmetrische Aggregate gelöst.

Funktionsweise symmetrischer Aggregatfunktionen

Symmetrische Aggregatfunktionen verhindern, dass Analysten und alle anderen, die Looker verwenden, Aggregate wie Summen, Durchschnittswerte und Zählungen versehentlich falsch berechnen. Symmetrische Aggregate entlasten die Analysten erheblich, 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 Summen, dass total eine Eigenschaft von orders und nicht von 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 die Fachkraft für Datenanalyse in Looker definiert hat. Das bedeutet: Wenn Looker Berechnungen mit der verknüpften Tabelle durchführt, erkennt es, dass die Gesamtsumme nicht doppelt gezählt werden sollte, auch wenn der Wert von order_id in zwei Zeilen 1 ist, da diese Summe bereits in die Berechnung einbezogen wurde und dass die Gesamtsumme nur einmal für die drei Zeilen gezählt werden sollte, in denen order_id den Wert 2 hat.

Symmetrische Summen sind von einem eindeutigen Primärschlüssel und der korrekten Join-Beziehung abhängig, die im Modell angegeben werden müssen. Wenn Sie also falsche Ergebnisse erhalten, wenden Sie sich an eine Fachkraft für Datenanalyse, um sicherzustellen, dass alles korrekt eingerichtet ist.

Warum symmetrische Summen kompliziert aussehen

Das Aussehen symmetrischer Summen kann 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 Summen können die SQL-Schreibvorgänge in Looker 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 für symmetrische Summen hängt vom Dialekt von SQL ab, den Looker schreibt. Alle Formate haben jedoch denselben Primärschlüssel: Wenn mehrere Zeilen denselben Primärschlüssel haben, zählt die Funktion für symmetrische Summen 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 Ihre vorherige Berechnung mit symmetrischen Summen durcharbeiten. Von den sieben Spalten in den verknüpften Tabellen benötigen Sie nur zwei: die eine, 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. Dies geschieht in der Regel mit einer Hash-Funktion, deren Details nicht auf dieser Seite erläutert werden. 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 Folgendes durch: 

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 Summen wird nicht von wiederholten Zeilen oder mehreren Bestellungen mit der gleichen Gesamtsumme getäuscht. 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 Summen einige Leistungskosten verursachen, wird durch die Fähigkeit von Looker, zu erkennen, wann und wann nicht, symmetrische Summen die von Looker generierte SQL-Abfrage weiter optimieren, sie so effizient wie möglich machen und gleichzeitig die richtige Antwort garantieren.