0Pricing
SQL Interview Prep · บทเรียน

สร้างเมทริกซ์การรักษาผู้ใช้

นับผู้ใช้ที่ยังใช้งานอยู่ตาม Cohort และช่วงเวลาที่เลื่อนออกไป เพื่อสร้างตารางการรักษาผู้ใช้

สร้างเมทริกซ์การรักษาผู้ใช้ เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

เมทริกซ์การคงอยู่คืออะไร

ขั้นตอนต่อจากการกำหนดกลุ่มโคฮอร์ตคือเมทริกซ์การคงอยู่ที่มีชื่อเสียง โดยแถวคือกลุ่มโคฮอร์ต คอลัมน์คือระยะห่างของช่วงเวลา (เดือนที่ 0, 1, 2, ...) และแต่ละช่องจะนับจำนวนผู้ใช้จากกลุ่มโคฮอร์ตนั้นที่ยังใช้งานอยู่เมื่อถึงระยะห่างดังกล่าว

ผู้สัมภาษณ์ชอบคำถามนี้เพราะบังคับให้คุณผสานการกำหนดกลุ่มโคฮอร์ต การเชื่อมกลับไปยังข้อมูลกิจกรรม การคำนวณความแตกต่างของช่วงเวลา และการหมุนตารางเข้าด้วยกัน นี่คือแบบสอบถามที่สะท้อนงานวิเคราะห์ผลิตภัณฑ์ได้ดีที่สุด

ข้อมูลนำเข้าสองส่วน

คุณต้องมีข้อมูลสองอย่าง ได้แก่ช่วงเวลาของกลุ่มโคฮอร์ตของผู้ใช้แต่ละราย (จากบทเรียนก่อนหน้า) และรายการทุกช่วงเวลาที่ผู้ใช้ใช้งาน กิจกรรมมาจากตารางเหตุการณ์เดิม โดยยุบรวมให้อยู่ในระดับช่วงเวลา

ดังนั้นควรวางแผนแบบสอบถามเป็นลำดับดังนี้: CTE ของกลุ่มโคฮอร์ต ตามด้วย CTE ของกิจกรรมที่ระบุว่าผู้ใช้แต่ละรายใช้งานในเดือนไหน แล้วจึงเชื่อมข้อมูลทั้งสองส่วนเข้าด้วยกัน

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

การแสดงรายการช่วงเวลาที่มีการใช้งาน

CTE ของกิจกรรมตอบคำถามว่า "ผู้ใช้แต่ละรายใช้งานในเดือนไหนบ้าง" ให้ตัดทอนทุกเหตุการณ์เป็นระดับเดือน แล้วลบรายการซ้ำด้วย DISTINCT หรือ GROUP BY เพื่อให้ผู้ใช้ที่ใช้งาน 40 ครั้งในเดือนมีนาคมมีเพียงหนึ่งแถวของเดือนมีนาคม

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

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

การคำนวณระยะห่างของช่วงเวลา

หัวใจของเมทริกซ์คือหมายเลขช่วงเวลา หรือจำนวนเดือนที่กิจกรรมหนึ่งเกิดขึ้นหลังจากเริ่มกลุ่มโคฮอร์ต ให้ลบเดือนของกลุ่มโคฮอร์ตออกจากเดือนที่มีการใช้งาน

ในโพสต์เกรส วิธีที่ชัดเจนคือการนับจำนวนเดือนเต็มระหว่างวันที่สองค่า สูตรที่ใช้ได้กับหลายระบบคือคูณผลต่างของปีด้วย 12 แล้วบวกผลต่างของเดือน นอกจากนี้หลายระบบยังมีฟังก์ชันช่วยให้ใช้ ระยะห่าง 0 หมายถึงเดือนเริ่มต้นของกลุ่มโคฮอร์ตเอง

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

การเชื่อมกลุ่มผู้ใช้กับกิจกรรม

เชื่อม CTE ของกลุ่มผู้ใช้กับ CTE ของกิจกรรมโดยใช้ user_id แต่ละแถวผลลัพธ์ระบุว่า ผู้ใช้คนนี้ซึ่งอยู่ในกลุ่มผู้ใช้ X ใช้งานในระยะห่าง N การนับผู้ใช้ที่ไม่ซ้ำกันในแต่ละคู่ (กลุ่มผู้ใช้, ระยะห่าง) คือเมทริกซ์ในรูปแบบยาว

เนื่องจากสมาชิกทุกคนในกลุ่มผู้ใช้จะใช้งานในเดือนเริ่มต้นของตนเอง ระยะห่าง 0 จึงควรเท่ากับขนาดของกลุ่มผู้ใช้ ซึ่งเป็นการตรวจสอบความสมเหตุสมผลในตัว

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

ตารางการคงอยู่แบบยาว

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

โปรดสังเกตว่านิพจน์ระยะห่างปรากฏทั้งใน SELECT และ GROUP BY เนื่องจากเป็นค่าที่คำนวณขึ้น ไม่ใช่คอลัมน์ที่จัดเก็บไว้

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

การหมุนเป็นคอลัมน์แบบกว้าง

หากต้องการตารางกริดแบบคลาสสิก ให้หมุนระยะห่างเป็นคอลัมน์ด้วย การรวมแบบมีเงื่อนไข ซึ่งก็คือ SUM ของ CASE สำหรับแต่ละระยะห่าง รูปแบบที่ใช้ได้กับหลายระบบนี้ทำงานได้ในทุกภาษาถิ่นโดยไม่ต้องใช้ไวยากรณ์ PIVOT พิเศษ

แต่ละ CASE จะส่งค่า 1 เมื่อ period_number ของแถวตรงกับคอลัมน์นั้น ดังนั้น SUM จึงนับจำนวนผู้ใช้ที่ยังคงใช้งานอยู่สำหรับระยะห่างนั้น

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

จากจำนวนสู่สัดส่วนการคงอยู่

โดยทั่วไปผู้สัมภาษณ์ต้องการ เปอร์เซ็นต์ ไม่ใช่จำนวนดิบ ให้หารจำนวนผู้ใช้ที่ยังคงใช้งานอยู่ของแต่ละระยะห่างด้วยขนาดของกลุ่มผู้ใช้ (ระยะห่าง 0) ให้แปลงค่าเป็นเลขทศนิยม หรือคูณด้วย 1.0 เพื่อหลีกเลี่ยงการหารจำนวนเต็ม ซึ่งเป็นข้อผิดพลาดเงียบที่พบบ่อยที่สุดในกรณีนี้

ผลลัพธ์คือเส้นโค้งการคงอยู่ โดยเริ่มที่ 100% ในเดือน 0 แล้วลดลงเข้าหาระดับคงที่ ระดับคงที่นี้คือค่าชี้วัดที่ผู้มีส่วนได้ส่วนเสียให้ความสำคัญอย่างแท้จริง

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

กับดักการหารจำนวนเต็ม

จุดหลอกในการสัมภาษณ์ที่มักเกิดขึ้นแน่นอนคือ ในระบบส่วนใหญ่ 120 / 500 มีค่าเท่ากับ 0 ไม่ใช่ 0.24 เนื่องจากตัวถูกดำเนินการทั้งสองเป็นจำนวนเต็ม ดังนั้นเปอร์เซ็นต์การคงอยู่จึงออกมาเป็นศูนย์ทั้งหมดโดยไม่รู้ตัว

แก้ไขโดยทำให้ด้านใดด้านหนึ่งเป็นชนิดตัวเลข เช่น คูณด้วย 100.0 ใช้ CAST กับตัวถูกดำเนินการหนึ่งตัวให้เป็น NUMERIC หรือหารด้วย NULLIF(size, 0) เพื่อป้องกันกรณีกลุ่มผู้ใช้ว่างเปล่าที่อาจทำให้เกิดการหารด้วยศูนย์ด้วย การกล่าวว่า "และ NULLIF ป้องกันการหารด้วยศูนย์" จะช่วยเพิ่มคะแนนพิเศษ

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

เติมระยะห่างที่หายไปด้วยศูนย์

หากกลุ่มผู้ใช้ไม่มีผู้ใช้ที่ยังคงใช้งานอยู่เลยในระยะห่าง 2 การเชื่อมจะไม่สร้างแถวขึ้นมา ทำให้เกิดช่องว่างในเมทริกซ์ หากต้องการแสดง 0 อย่างชัดเจน ให้สร้างกริดเต็มรูปแบบของชุดคู่ (กลุ่มผู้ใช้, ระยะห่าง) แล้วใช้ LEFT JOIN เพื่อเชื่อมจำนวนเข้ามา

สร้างกริดโดยทำ CROSS JOIN ระหว่างกลุ่มผู้ใช้กับรายการตัวเลข/ระยะห่าง จากนั้นเปลี่ยนค่าจำนวนที่หายไปเป็นศูนย์ ผู้สัมภาษณ์จะชื่นชมที่คุณสังเกตเห็นช่องว่างนี้

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

รูปร่างสามเหลี่ยมและอคติจากข้อมูลล่าสุด

ประเด็นที่ควรพูดถึงอีกเรื่องคือ เมทริกซ์มีรูปร่างเป็น สามเหลี่ยม กลุ่มผู้ใช้ที่เริ่มต้นเมื่อเดือนที่แล้วไม่สามารถมีค่าในเดือนที่ 3 ได้ เพราะยังมาไม่ถึงช่วงเวลานั้น ดังนั้นระยะห่างช่วงหลังจึงมีกลุ่มผู้ใช้เข้าร่วมน้อยกว่า

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

ตรวจสอบอย่างรวดเร็ว

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

สรุป: เมทริกซ์การคงอยู่

การสร้างเมทริกซ์การคงอยู่ในการสัมภาษณ์:

  • กำหนด ช่วงเวลาของกลุ่มผู้ใช้ ให้ผู้ใช้แต่ละคน จากนั้นแสดงรายการ ช่วงเวลาที่ผู้ใช้ใช้งาน ของแต่ละคนโดยตัดรายการซ้ำออก
  • เชื่อมรายการทั้งสองเข้าด้วยกันและคำนวณ ระยะห่างของช่วงเวลา (จำนวนเดือนระหว่างกลุ่มผู้ใช้กับกิจกรรม)
  • รวมข้อมูลให้อยู่ในรูปแบบยาวด้วย COUNT(DISTINCT user_id) และหมุนตารางด้วย CASE หากต้องการกริด
  • แปลงจำนวนเป็นสัดส่วนอย่างระมัดระวัง โดยหลีกเลี่ยง การหารจำนวนเต็ม และการหารด้วยศูนย์ด้วย 100.0 และ NULLIF
  • ใช้ LEFT JOIN กับกริดที่สร้างขึ้นเพื่อเติมช่องที่เป็นศูนย์ และอย่าลืมว่าเมทริกซ์มีรูปร่างเป็น สามเหลี่ยม

คำถามที่พบบ่อย

บทเรียน “สร้างเมทริกซ์การรักษาผู้ใช้” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “สร้างเมทริกซ์การรักษาผู้ใช้” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “สร้างเมทริกซ์การรักษาผู้ใช้”

นับผู้ใช้ที่ยังใช้งานอยู่ตาม Cohort และช่วงเวลาที่เลื่อนออกไป เพื่อสร้างตารางการรักษาผู้ใช้ คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน

บทเรียน “สร้างเมทริกซ์การรักษาผู้ใช้” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. กำหนด Cohort จากการกระทำแรก
  2. สร้างเมทริกซ์การรักษาผู้ใช้
  3. การรักษาผู้ใช้ในวันที่ N และแบบเลื่อนต่อเนื่อง
  4. คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน
← กลับไปที่ SQL Interview Prep