cumulative table design

Desain Tabel Kumulatif

Desain tabel kumulatif adalah alat rekayasa data yang sangat canggih yang harus diketahui oleh semua teknisi data.

Desain ini menghasilkan tabel yang dapat memberikan analisis efisien pada kerangka waktu yang sangat besar (hingga ribuan hari).

Berikut diagram desain jalur pipa tingkat tinggi untuk pola ini:

Kami awalnya membuat tabel metrik harian berdasarkan apa pun entitas kami. Data ini berasal dari sumber peristiwa apa pun yang kami miliki di bagian hulu.

Setelah kita memiliki metrik harian, kita FULL OUTER JOIN tabel kumulatif kemarin dengan data harian hari ini dan membuat susunan metrik untuk setiap pengguna. Hal ini memungkinkan kami memasukkan riwayat baru tanpa harus memindai semuanya. (peningkatan kinerja yang besar)

Array metrik ini memungkinkan kita dengan mudah menjawab pertanyaan tentang riwayat semua pengguna menggunakan hal-hal seperti ARRAY_SUM untuk menghitung metrik apa pun yang kita inginkan pada jangka waktu apa pun yang dimungkinkan oleh array.

Semakin lama jangka waktu analisis Anda, semakin kritis pola ini!!

Contoh Aktivitas dan keterlibatan pengguna terakumulasi

Semua sintaks kueri menggunakan sintaks dan fungsi Presto/Trino. Contoh ini perlu dimodifikasi untuk varian SQL lainnya!

Kami akan menggunakan tanggal:

• 01-01-2022 seperti hari ini dalam istilah Aliran Udara adalah {{ ds }}
• 31-12-2021 seperti kemarin dalam istilah templat Airflow, ini adalah {{ yesterday_ds}}

Dalam contoh ini, kita akan melihat cara membuat desain ini untuk menghitung pengguna aktif harian, mingguan, dan bulanan serta jumlah suka, komentar, dan pembagian pengguna.

Tabel sumber kami dalam hal ini adalah events .

• Seorang pengguna aktif pada hari tertentu jika mereka membuat acara untuk hari itu.
• Tabel memiliki event_type yaitu like , comment , share , atau view

Sangat menggoda untuk berpikir bahwa solusi untuk hal ini adalah dengan menjalankan saluran pipa

    SELECT 
        COUNT(DISTINCT user_id) as num_monthly_active_users,
        COUNT(CASE WHEN event_type = 'like' THEN 1 END) as num_likes_30d,
        COUNT(CASE WHEN event_type = 'comment' THEN 1 END) as num_comments_30d,
        COUNT(CASE WHEN event_type = 'share' THEN 1 END) as num_shares_30d,
        ...
    FROM events
    WHERE event_date BETWEEN DATE_SUB('2022-01-01', 30), AND '2022-01-01'

Masalahnya adalah kami memindai data peristiwa selama 30 hari setiap hari untuk menghasilkan angka-angka ini. Saluran pipa yang cukup boros namun sederhana. Seharusnya ada cara dimana kita hanya perlu memindai data kejadian satu kali dan menggabungkannya dengan hasil dari 29 hari sebelumnya, bukan? Bisakah kita membuat struktur data di mana data scientist dapat menanyakan data kita dan dengan mudah mengetahui jumlah tindakan yang dilakukan pengguna dalam N hari terakhir?

Desain ini cukup sederhana hanya dengan 3 langkah:

Langkah tabel harian

• Pada langkah ini kami hanya mengumpulkan kejadian hari ini untuk melihat siapa yang aktif setiap hari. Skema tabelnya ada di sini
• Kueri ini cukup sederhana dan mudah di sini
  • GROUP BY user_id dan kemudian hitung mereka sebagai aktif harian jika ada acara
  • kami menambahkan beberapa pernyataan COUNT(CASE WHEN event_type = 'like' THEN 1 END) untuk mengetahui jumlah suka, komentar, dan pembagian harian juga

Langkah Akumulasi

• Skema tabel untuk langkah ini ada di sini
• Permintaan untuk langkah ini jauh lebih kompleks. Itu di sini
  • Pada langkah ini kita mengambil data hari ini dari tabel harian dan data kemarin dari tabel akumulasi
  • Kami FULL OUTER JOIN dua kumpulan data ini pada today.user_id = yesterday.user_id
  • Jika pengguna masih baru, mereka tidak akan ada dalam data kemarin. Selain itu, jika pengguna tidak aktif hari ini, mereka tidak ada dalam data hari ini
  • Jadi kita perlu COALESCE(today.user_id, yesterday.user_id) as user_id untuk melacak semua pengguna
  • Selanjutnya kita ingin membangun kolom activity_array . Kami hanya ingin activity_array menyimpan data 30 hari terakhir
    • Jadi kita periksa apakah CARDINALITY(activity_array) < 30 untuk memahami apakah kita bisa menambahkan nilai hari ini ke depan array atau apakah kita perlu memotong elemen dari akhir array sebelum menambahkan nilai hari ini ke depan
    • Kita perlu melakukan COALESCE(t.is_active_today, 0) untuk memasukkan nilai nol ke dalam array ketika pengguna tidak aktif
  • Kami mengikuti pola yang sangat mirip dengan cara kami membuat activity_array tetapi juga untuk suka, komentar, dan berbagi!
  • Setelah kita membuat semua susunan metrik, menghitung aktivitas mingguan dan bulanan cukup mudah
    • CASE WHEN ARRAY_SUM(activity_array) > 0 THEN 1 ELSE 0 END memberi kita aktivitas bulanan karena kita membatasi ukuran array hingga 30
    • CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END memberi kita aktif mingguan karena kita hanya memeriksa 7 elemen pertama dari array (yaitu 7 hari terakhir)
  • Menjumlahkan jumlah like, komentar, dan share dalam 7 hari terakhir juga mudah
    • ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7d memberi kita jumlah suka yang dilakukan pengguna ini dalam 7 hari terakhir
    • ARRAY_SUM(like_array) as num_likes_30d memberi kita jumlah suka yang dilakukan pengguna ini dalam 30 hari terakhir sejak array ditetapkan ke ukuran tersebut

Langkah DAG

• Contoh DAG dapat ditemukan di sini
• Hal-hal penting yang perlu diingat untuk DAG adalah:
  • DAG kumulatif harus selalu depends_on_past: True
  • Langkah agregasi harian harus berada di bagian hulu dari langkah akumulasi