cumulative table design

การออกแบบตารางสะสม

การออกแบบตารางสะสมเป็นเครื่องมือวิศวกรรมข้อมูลที่ทรงพลังอย่างยิ่งที่วิศวกรข้อมูลทุกคนควรรู้

การออกแบบนี้สร้างตารางที่สามารถวิเคราะห์กรอบเวลาขนาดใหญ่ตามอำเภอใจ (สูงสุด หลายพัน วัน) ได้อย่างมีประสิทธิภาพ

นี่คือไดอะแกรมของการออกแบบไปป์ไลน์ระดับสูงสำหรับรูปแบบนี้:

ในตอนแรกเราสร้างตารางหน่วยเมตริก รายวัน ซึ่งสอดคล้องกับสิ่งที่องค์กรของเราเป็น ข้อมูลนี้ได้มาจากแหล่งเหตุการณ์ใดก็ตามที่เรามีต้นน้ำ

หลังจากที่เรามีตัววัดรายวันแล้ว เรา FULL OUTER JOIN กับตารางสะสมของเมื่อวานพร้อมข้อมูลรายวันของวันนี้ และสร้างอาร์เรย์ตัววัดของเราสำหรับผู้ใช้แต่ละราย สิ่งนี้ทำให้เราสามารถนำประวัติศาสตร์ใหม่เข้ามาได้โดยไม่ต้องสแกนทั้งหมด (เพิ่มประสิทธิภาพอย่างมาก)

อาร์เรย์หน่วยเมตริกเหล่านี้ช่วยให้เราตอบคำถามเกี่ยวกับประวัติของผู้ใช้ทั้งหมดได้อย่างง่ายดาย โดยใช้สิ่งต่างๆ เช่น ARRAY_SUM เพื่อคำนวณหน่วยเมตริกใดก็ตามที่เราต้องการในกรอบเวลาใดก็ตามที่อาร์เรย์อนุญาต

ยิ่งกรอบเวลาการวิเคราะห์ของคุณนานขึ้น รูปแบบนี้ก็ยิ่งสำคัญมากขึ้นเท่านั้น!!

ตัวอย่างกิจกรรมผู้ใช้และการมีส่วนร่วมที่สะสม

ไวยากรณ์แบบสอบถามทั้งหมดใช้ไวยากรณ์และฟังก์ชัน Presto/Trino ตัวอย่างนี้จะต้องได้รับการแก้ไขสำหรับตัวแปร SQL อื่น ๆ

เราจะใช้วันที่:

• 01-01-2022 ใน วันนี้ ในแง่ Airflow คือ {{ ds }}
• 31-12-2564 เหมือน เมื่อวาน ในเงื่อนไขเทมเพลต Airflow นี่คือ {{ yesterday_ds}}

ในตัวอย่างนี้ เราจะดูวิธีสร้างการออกแบบนี้เพื่อคำนวณผู้ใช้ที่ใช้งานรายวัน รายสัปดาห์ และรายเดือน ตลอดจนผู้ใช้ที่ชอบ แสดงความคิดเห็น และแชร์

ตารางต้นฉบับของเราในกรณีนี้คือ เหตุการณ์

• ผู้ใช้มีการใช้งานในวันใดก็ตามหากพวกเขาสร้างกิจกรรมสำหรับวันนั้น
• ตารางมี event_type ซึ่งก็คือ like , comment , share หรือ view

เป็นเรื่องที่น่าสนใจที่จะคิดว่าวิธีแก้ปัญหานี้คือการเรียกใช้ไปป์ไลน์ในลักษณะนี้

    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'

ปัญหาคือเรากำลังสแกนข้อมูลเหตุการณ์เป็นเวลา 30 วันทุกวันเพื่อสร้างตัวเลขเหล่านี้ ไปป์ไลน์ที่ค่อนข้างสิ้นเปลืองแต่เรียบง่าย น่าจะมีวิธีที่เราสแกนข้อมูลเหตุการณ์เพียงครั้งเดียวแล้วรวมกับผลลัพธ์จาก 29 วันที่ผ่านมาใช่ไหม? เราสามารถสร้างโครงสร้างข้อมูลที่นักวิทยาศาสตร์ข้อมูลสามารถสืบค้นข้อมูลของเราและทราบจำนวนการดำเนินการที่ผู้ใช้ทำในจำนวน N วันที่ผ่านมาได้อย่างง่ายดายได้หรือไม่

การออกแบบนี้ค่อนข้างง่ายเพียง 3 ขั้นตอน:

ขั้นตอนตารางรายวัน

• ในขั้นตอนนี้ เราจะรวบรวมเฉพาะกิจกรรมของวันนี้เพื่อดูว่าใครใช้งานอยู่ในแต่ละวัน สคีมาของตารางอยู่ที่นี่
• แบบสอบถามนี้ค่อนข้างเรียบง่ายและตรงไปตรงมาที่นี่
  • GROUP BY user_id แล้วนับว่าเป็นการใช้งานรายวันหากมีกิจกรรมใดๆ
  • เราเพิ่มคำสั่ง COUNT(CASE WHEN event_type = 'like' THEN 1 END) สองสามรายการเพื่อคำนวณจำนวนการถูกใจ ความคิดเห็น และการแชร์ในแต่ละวันด้วย

ขั้นตอนการสะสม

• สคีมาตารางสำหรับขั้นตอนนี้อยู่ที่นี่
• แบบสอบถามสำหรับขั้นตอนนี้ซับซ้อนกว่ามาก มันอยู่ที่นี่
  • ในขั้นตอนนี้ เราจะนำข้อมูล ของวันนี้ จากตารางรายวัน และข้อมูล ของเมื่อวาน จากตารางที่สะสม
  • เรา FULL OUTER JOIN ใน today.user_id = yesterday.user_id
  • หากผู้ใช้เป็นผู้ใช้ใหม่ ผู้ใช้จะไม่อยู่ในข้อมูลของเมื่อวาน นอกจากนี้ หากผู้ใช้ไม่ได้ใช้งานในวันนี้ ผู้ใช้จะไม่อยู่ในข้อมูลของวันนี้
  • ดังนั้นเราจึงจำเป็นต้อง COALESCE(today.user_id, yesterday.user_id) as user_id เพื่อติดตามผู้ใช้ทั้งหมด
  • ต่อไปเราต้องการสร้างคอลัมน์ activity_array เราต้องการให้ activity_array เก็บข้อมูลในช่วง 30 วันที่ผ่านมาเท่านั้น
    • ดังนั้นเราจึงตรวจสอบเพื่อดูว่า CARDINALITY(activity_array) < 30 เพื่อทำความเข้าใจว่าเราสามารถเพิ่มมูลค่าของวันนี้ไปที่ด้านหน้าของอาร์เรย์ได้หรือไม่ หรือเราจำเป็นต้องแบ่งองค์ประกอบออกจากส่วนท้ายของอาร์เรย์ก่อนที่จะเพิ่มมูลค่าของวันนี้ที่ด้านหน้าของอาร์เรย์
    • เราจำเป็นต้องดำเนินการ COALESCE(t.is_active_today, 0) เพื่อใส่ค่าเป็นศูนย์ลงในอาร์เรย์เมื่อผู้ใช้ไม่ได้ใช้งาน
  • เราปฏิบัติตามรูปแบบที่คล้ายกันมากกับวิธีที่เราสร้าง activity_array แต่สำหรับการถูกใจ ความคิดเห็น และการแชร์ด้วยเช่นกัน!
  • หลังจากที่เราสร้างอาร์เรย์หน่วยเมตริกทั้งหมดแล้ว การคำนวณกิจกรรมรายสัปดาห์และรายเดือนก็ค่อนข้างตรงไปตรงมา
    • CASE WHEN ARRAY_SUM(activity_array) > 0 THEN 1 ELSE 0 END ให้การใช้งานรายเดือนแก่เราเนื่องจากเราจำกัดขนาดอาร์เรย์ไว้ที่ 30
    • CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END ให้เราใช้งานรายสัปดาห์เนื่องจากเราตรวจสอบเฉพาะ 7 องค์ประกอบแรกของอาร์เรย์ (เช่น 7 วันที่ผ่านมา)
  • สรุปจำนวนไลค์ คอมเมนต์ และแชร์ในช่วง 7 วันที่ผ่านมาก็เป็นเรื่องง่ายเช่นกัน
    • ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7d แสดงจำนวนการถูกใจที่ผู้ใช้รายนี้ทำในช่วง 7 วันที่ผ่านมา
    • ARRAY_SUM(like_array) as num_likes_30d ให้จำนวนการถูกใจที่ผู้ใช้รายนี้ทำในช่วง 30 วันที่ผ่านมานับตั้งแต่อาร์เรย์ได้รับการแก้ไขเป็นขนาดนั้น

ขั้นตอน DAG

• คุณสามารถดูตัวอย่าง DAG ได้ที่นี่
• สิ่งสำคัญที่ต้องจำสำหรับ DAG คือ:
  • DAG สะสมควรเป็น depends_on_past: True เสมอ
  • ขั้นตอนการรวมรายวันจะต้องอยู่ต้นทางของขั้นตอนการสะสม