Memahami agregat simetris

Agregat simetris di Looker adalah fitur yang sangat canggih. Namun, karena agregat simetris dapat terlihat sedikit mengintimidasi dan sebagian besar terjadi di belakang layar, mungkin agak membingungkan untuk menghadapinya. Laman ini menyediakan informasi berikut tentang agregat simetris:

Mengapa agregat simetris diperlukan

SQL, bahasa analisis data, sangat ampuh. Tetapi dengan kekuatan besar harus ada tanggung jawab yang besar, dan analis memiliki tanggung jawab untuk menghindari penghitungan agregat yang tidak benar secara tidak sengaja, seperti penjumlahan, rata-rata, dan perhitungan.

Sangatlah mudah untuk melakukan perhitungan secara tidak benar, dan jenis perhitungan yang salah ini dapat menjadi sumber frustrasi besar bagi analis. Contoh berikut menggambarkan bagaimana Anda bisa melakukan kesalahan.

Bayangkan Anda memiliki dua tabel, orders dan order_items. Tabel order_items mencatat satu baris untuk setiap item secara berurutan, sehingga hubungan di antara tabel tersebut adalah one-to-many. Hubungannya adalah one-to-many karena satu pesanan dapat memiliki banyak item, tetapi setiap item hanya dapat menjadi bagian dari satu pesanan. Lihat halaman Cara mendapatkan parameter hubungan dengan benar untuk mendapatkan panduan dalam menentukan hubungan yang benar untuk join.

Dalam contoh ini, misalkan tabel orders terlihat seperti ini:

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

Dalam tabel orders ini, jumlah nilai di kolom total (SUM(total)) sama dengan 124.84.

Misalkan tabel order_items berisi enam baris:

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

Mendapatkan jumlah item yang dipesan itu mudah. Jumlah nilai dalam kolom quantity (SUM(quantity)) adalah 7.

Sekarang, misalkan Anda menggabungkan tabel orders dan tabel order_items menggunakan kolom bersama, order_id. Ini menghasilkan tabel berikut:

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

Tabel sebelumnya memberikan informasi baru, seperti dua item dipesan pada 1 Desember (2017-12-01 di kolom order_date) dan empat item dipesan pada 2 Desember (2017-12-02). Beberapa penghitungan sebelumnya, seperti penghitungan SUM(quantity), masih valid. Namun, Anda akan mengalami masalah jika mencoba menghitung total pengeluaran.

Jika Anda menggunakan penghitungan sebelumnya, SUM(total), nilai total 50.36 dalam tabel baru untuk baris dengan nilai order_id 1 akan dihitung dua kali, karena urutan mencakup dua item yang berbeda (dengan item_id nilai 50 dan 63). Total 24.12 untuk baris dengan order_id 2 dihitung tiga kali, karena urutan ini mencakup tiga item berbeda. Hasilnya, hasil penghitungan SUM(total) untuk tabel ini adalah 223.44, bukan jawaban yang benar, yaitu 124.84.

Meskipun mudah untuk menghindari kesalahan semacam ini saat Anda bekerja dengan dua tabel contoh kecil, memecahkan masalah ini akan jauh lebih rumit di kehidupan nyata, dengan banyak tabel dan banyak data. Persis seperti inilah salah perhitungan yang bisa dibuat tanpa disadari. Ini adalah masalah yang dipecahkan oleh agregat simetris.

Cara kerja agregat simetris

Agregat simetris mencegah analis — dan siapa pun yang menggunakan Looker — dari kesalahan penghitungan agregat seperti penjumlahan, rata-rata, dan penghitungan secara tidak sengaja. Agregat simetris membantu mengurangi beban analis, karena analis dapat percaya bahwa pengguna tidak akan menagih terlalu banyak dengan data yang salah. Agregat simetris melakukannya dengan memastikan untuk menghitung setiap fakta dalam perhitungan dengan frekuensi yang benar serta dengan melacak apa yang Anda hitung.

Pada contoh sebelumnya, fungsi agregat simetris mengenali bahwa total adalah properti orders (bukan order_items), sehingga perlu menghitung total setiap pesanan hanya sekali untuk mendapatkan jawaban yang benar. Fungsi melakukannya dengan menggunakan {i>primary key<i} unik yang telah ditentukan oleh analis di Looker. Artinya, saat Looker melakukan penghitungan di tabel gabungan, Looker menyadari bahwa meskipun ada dua baris dengan nilai order_id 1, tidak seharusnya menghitung total dua kali karena total tersebut sudah disertakan dalam penghitungan, dan seharusnya hanya menghitung total satu kali untuk tiga baris yang nilai order_id-nya adalah 2.

Perlu diperhatikan bahwa agregat simetris bergantung pada kunci utama unik dan hubungan gabungan yang benar yang ditentukan dalam model. Jadi, jika hasil yang Anda dapatkan terlihat salah, bicarakan dengan seorang analis untuk memastikan bahwa semuanya telah diatur dengan benar.

Mengapa agregat simetris terlihat rumit

Tampilan agregat simetris bisa sedikit membingungkan. Tanpa agregat simetris, Looker biasanya akan menulis SQL yang bagus dan berperilaku baik, seperti dalam contoh berikut: 

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

Dengan agregat simetris, penulisan SQL Looker mungkin terlihat seperti contoh berikut: 

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

Format persis yang diambil oleh agregat simetris bergantung pada dialek SQL yang ditulis Looker, tetapi semua format melakukan hal dasar yang sama: Jika beberapa baris memiliki kunci utama yang sama, fungsi agregat simetris hanya menghitungnya satu kali. Hal ini dilakukan dengan menggunakan fungsi SUM DISTINCT dan AVG DISTINCT yang kurang dikenal yang merupakan bagian dari standar SQL.

Untuk melihat bagaimana hal ini terjadi, Anda dapat melakukan penghitungan yang Anda lakukan sebelumnya dan mengerjakannya dengan agregat simetris. Dari tujuh kolom dalam tabel gabungan, Anda hanya memerlukan dua kolom: kolom yang Anda gabungkan (total) dan kunci utama yang unik untuk pesanan (order_id).

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

Agregat simetris mengambil kunci utama (dalam hal ini, order_id) dan membuat angka yang sangat besar untuk setiap kunci, yang dijamin unik dan selalu memberikan output yang sama untuk input yang sama. (Biasanya ini dilakukan dengan fungsi {i>hashing<i}, yang detailnya berada di luar cakupan halaman ini.) Hasilnya akan terlihat seperti berikut:

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

Kemudian, untuk setiap baris, Looker melakukan hal ini: 

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

Cara ini akan memberi Anda jumlah total yang diagregasi dengan benar, dengan menghitung setiap total dengan frekuensi yang tepat. Fungsi agregat simetris Looker tidak tertipu oleh baris berulang atau beberapa pesanan yang memiliki total yang sama. Anda dapat mencoba menghitung sendiri untuk lebih memahami bagaimana agregat simetris bekerja.

SQL yang diperlukan untuk melakukan ini bukanlah hal terindah: Dengan CAST(), md5(), dan SUM(DISTINCT), serta STRTOL(), Anda tentu tidak ingin menulis SQL secara manual. Namun, untungnya Anda tidak perlu melakukannya — Looker dapat menulis SQL untuk Anda.

Jika agregasi akan berfungsi dengan baik tanpa memerlukan agregat simetris, Looker akan otomatis mendeteksinya dan tidak menggunakan fungsi tersebut. Karena agregat simetris menimbulkan biaya performa, kemampuan Looker untuk membedakan kapan harus digunakan, dan kapan tidak digunakan, agregat simetris lebih mengoptimalkan SQL yang dihasilkan Looker dan membuatnya seefisien mungkin sambil tetap menjamin jawaban yang tepat.