Memahami agregat simetris

Aggregate simetris di Looker adalah fitur yang sangat canggih. Namun, karena agregat simetris dapat terlihat sedikit menakutkan dan sebagian besar terjadi di balik layar, Anda mungkin sedikit bingung saat menemukannya. Halaman ini memberikan informasi berikut tentang agregat simetris:

Alasan agregat simetris diperlukan

SQL, bahasa analisis data, sangat canggih. Namun, dengan kekuatan yang besar, tanggung jawab juga makin besar. Analis memiliki tanggung jawab untuk menghindari penghitungan agregat yang salah secara tidak sengaja, seperti jumlah, rata-rata, dan jumlah.

Sangat mudah untuk melakukan kesalahan dalam melakukan penghitungan ini, dan jenis penghitungan yang salah ini dapat menjadi sumber frustrasi yang besar bagi analis. Contoh berikut menggambarkan kesalahan yang dapat Anda lakukan.

Bayangkan Anda memiliki dua tabel, orders dan order_items. Tabel order_items mencatat satu baris untuk setiap item dalam pesanan, sehingga hubungan antartabel adalah one-to-many. Hubungannya bersifat one-to-many karena satu pesanan dapat memiliki banyak item, tetapi setiap item hanya dapat menjadi bagian dari satu pesanan. Lihat halaman Praktik Terbaik Mendapatkan parameter hubungan dengan tepat untuk mendapatkan panduan tentang cara menentukan hubungan yang benar untuk join.

Dalam contoh ini, anggap 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 bersamanya, order_id. Tindakan ini akan 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 pembelanjaan.

Jika Anda menggunakan penghitungan sebelumnya, SUM(total), total nilai 50.36 dalam tabel baru untuk baris dengan nilai order_id 1 akan dihitung dua kali, karena urutan ini menyertakan dua item yang berbeda (dengan nilai item_id 50 dan 63). Total 24.12 untuk baris dengan order_id 2 akan dihitung tiga kali, karena urutan ini menyertakan tiga item yang berbeda. Akibatnya, 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 menggunakan dua contoh tabel kecil, menyelesaikan masalah ini akan jauh lebih rumit di dunia nyata, dengan banyak tabel dan banyak data. Inilah jenis kesalahan penghitungan yang dapat dilakukan seseorang tanpa disadari. Inilah masalah yang dipecahkan agregat simetris.

Cara kerja agregat simetris

Agregat simetris mencegah analis — dan siapa pun yang menggunakan Looker — salah menghitung agregat seperti jumlah, rata-rata, dan jumlah secara tidak sengaja. Agregat simetris membantu meringankan beban analis, karena analis dapat yakin bahwa pengguna tidak akan melanjutkan dengan data yang salah. Agregat simetris melakukan hal ini dengan memastikan untuk menghitung setiap fakta dalam penghitungan dengan jumlah yang benar serta dengan melacak apa yang Anda hitung.

Pada contoh sebelumnya, fungsi agregat simetris mengenali bahwa total adalah properti dari orders (bukan order_items), sehingga hanya perlu menghitung total setiap pesanan satu kali untuk mendapatkan jawaban yang benar. Fungsi ini melakukannya dengan menggunakan kunci utama unik yang telah ditentukan analis di Looker. Artinya, saat melakukan penghitungan pada tabel yang digabungkan, Looker akan mengenali bahwa meskipun ada dua baris dengan nilai order_id 1, Looker tidak boleh menghitung total dua kali karena total tersebut telah disertakan dalam penghitungan, dan Looker hanya boleh menghitung total satu kali untuk tiga baris dengan nilai order_id 2.

Perlu diperhatikan bahwa agregat simetris bergantung pada kunci utama unik dan hubungan join yang benar yang ditentukan dalam model. Jadi, jika hasil yang Anda dapatkan terlihat salah, hubungi analis untuk memastikan semuanya sudah disiapkan dengan benar.

Alasan agregat simetris terlihat rumit

Tampilan agregat simetris bisa jadi agak membingungkan. Tanpa agregat simetris, Looker biasanya 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 yang tepat yang digunakan 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 akan 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 mengambil penghitungan yang dilakukan sebelumnya dan mengerjakannya dengan agregat simetris. Dari tujuh kolom dalam tabel yang digabungkan, Anda hanya memerlukan dua kolom: kolom yang Anda gabungkan (total) dan kunci utama 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 (order_id, dalam hal ini) dan membuat angka yang sangat besar untuk setiap kunci, yang dijamin unik dan selalu memberikan output yang sama untuk input yang sama. (Fungsi ini biasanya melakukannya dengan fungsi hashing, 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 akan melakukan hal berikut: 

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

Hal ini memberikan total gabungan yang akurat, dengan menghitung setiap total dengan jumlah yang tepat. Fungsi agregat simetris Looker tidak tertipu oleh baris berulang atau oleh beberapa urutan yang memiliki total yang sama. Anda dapat mencoba menghitungnya sendiri untuk mendapatkan pemahaman yang lebih baik tentang cara kerja agregat simetris.

SQL yang diperlukan untuk melakukan hal ini tidak terlalu menarik untuk dilihat: Dengan CAST(), dan md5(), dan SUM(DISTINCT), dan STRTOL(), Anda tentu tidak ingin menulis SQL dengan tangan. Namun, untungnya, Anda tidak perlu melakukannya — Looker dapat menulis SQL untuk Anda.

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