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
- Funktionsweise symmetrischer Summen
- Warum symmetrische Summen kompliziert aussehen
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.
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.