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

กลุ่มต่อเนื่องเมื่อวันที่และสถานะเปลี่ยนแปลง

จัดกลุ่มช่วงเวลาที่มีสถานะเดียวกันต่อเนื่อง ซึ่งเป็นโจทย์ทั่วไปเกี่ยวกับสถานะการสมัครสมาชิก

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

ช่วงต่อเนื่องที่กำหนดโดยค่าที่เปลี่ยนแปลง

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

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

ชุดข้อมูลตัวอย่างการสมัครสมาชิก

พิจารณาตาราง sub_events สำหรับผู้ใช้หนึ่งคน โดยเรียงตามวันที่:

  • 2026-01-01 ใช้งานอยู่
  • 2026-02-01 ใช้งานอยู่
  • 2026-03-01 หยุดชั่วคราว
  • 2026-04-01 ใช้งานอยู่
  • 2026-05-01 ใช้งานอยู่

ผลลัพธ์ที่ต้องการคือช่วงสถานะสามช่วง: ใช้งานอยู่ ม.ค.-ก.พ., หยุดชั่วคราวในเดือน มี.ค., และใช้งานอยู่ในเดือน เม.ย.-พ.ค. โปรดสังเกตว่าช่วงใช้งานอยู่สองช่วงเป็น ช่วงต่อเนื่องแยกกัน เพราะช่วงหยุดชั่วคราวคั่นกลาง สถานะเดียวกันแต่ไม่ต่อเนื่องกันย่อมหมายถึงคนละช่วงต่อเนื่อง

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

ทำเครื่องหมายเมื่อสถานะเปลี่ยน

ใช้ LAG เปรียบเทียบสถานะของแต่ละแถวกับแถวก่อนหน้า เมื่อสถานะต่างกัน (หรือค่าเดิมเป็น NULL สำหรับแถวแรก) ให้เริ่มช่วงต่อเนื่องใหม่ เราจะให้ค่า 1 เมื่อมีการเปลี่ยนแปลง และให้ค่า 0 ในกรณีอื่น

เรียงตามวันที่อย่างเคร่งครัดภายในผู้ใช้ สำหรับข้อมูลของเรา เครื่องหมายการเปลี่ยนแปลงคือ 1,0,1,1,0 ซึ่งระบุขอบเขตของช่วงเวลาทั้งสามช่วง

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

รวมสะสมเป็นคีย์ช่วงเวลา

เช่นเดียวกับก่อนหน้านี้ ผลรวมสะสมของเครื่องหมายการเปลี่ยนแปลงจะให้คีย์กลุ่มที่คงที่ภายในแต่ละช่วงสถานะ: สำหรับแถวของเราได้ 1,1,2,3,3 คีย์ที่ไม่ซ้ำกันแต่ละค่าคือช่วงเวลาต่อเนื่องหนึ่งช่วง

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

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

รวมแถวให้เป็นช่วงสถานะ

ตอนนี้ให้ GROUP BY user_id, status และคีย์จากผลรวมสะสม เพื่อรายงานช่วงเวลาของแต่ละช่วง การใส่สถานะไว้ใน GROUP BY ปลอดภัย เพราะสถานะคงที่ภายในช่วงนั้น และทำให้คุณเลือกสถานะได้โดยไม่ต้องใช้ฟังก์ชันรวม

ผลลัพธ์มีสามแถวพอดี: ใช้งานอยู่ 01-01 ถึง 02-01, หยุดชั่วคราว 03-01 ถึง 03-01, ใช้งานอยู่ 04-01 ถึง 05-01

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

จากเหตุการณ์สู่ช่วงกึ่งเปิด

ประเด็นละเอียดอ่อนในการสัมภาษณ์: วันที่ของเหตุการณ์ระบุว่าสถานะ เริ่มต้น เมื่อใด และช่วงเวลาจะสิ้นสุดอย่างแท้จริงเมื่อสถานะถัดไปเริ่มต้น ไม่ใช่วันที่เกิดเหตุการณ์ล่าสุดที่มีสถานะเดียวกัน จุดสิ้นสุดของช่วงเวลาที่ถูกต้องมักเป็นจุดเริ่มต้นของช่วงถัดไป ซึ่งจำลองเป็นช่วงกึ่งเปิด [จุดเริ่มต้น, จุดเริ่มต้นถัดไป)

คำนวณจุดเริ่มต้นของช่วงถัดไปด้วย LEAD เหนือช่วงเวลาที่รวมรายการแล้ว โดยปล่อยให้ช่วงเวลาสุดท้ายไม่มีจุดสิ้นสุด (NULL หรือ 'ปัจจุบัน')

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

การจัดการสถานะซ้ำติดต่อกัน

ถ้าบันทึกมีแถวซ้ำซ้อน เช่น active, active, active โดยไม่มีการเปลี่ยนแปลงระหว่างแถวเหล่านั้นจะเป็นอย่างไร แฟล็กการเปลี่ยนแปลงมีค่าเป็น 0 สำหรับรายการซ้ำ ดังนั้นผลรวมสะสมจึงรวมรายการเหล่านั้นไว้ในกลุ่มช่วงต่อเนื่องเดียวกันโดยอัตโนมัติ นี่คือพฤติกรรมที่ต้องการ: สถานะเดียวกันที่อยู่ติดกันจะถูกรวมเป็นช่วงเวลาเดียว

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

เมื่อช่องว่างของเวลาควรแบ่งช่วง

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

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

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

การนับการเปลี่ยนสถานะที่ไม่ซ้ำกัน

คำถามต่อยอดที่เป็นธรรมชาติคือ "ผู้ใช้นี้เปลี่ยนสถานะกี่ครั้ง" คำตอบคือจำนวนแฟล็กการเปลี่ยนแปลงลบด้วยแฟล็กแรกสุด ซึ่งระบุสถานะเริ่มต้น ไม่ใช่การเปลี่ยนสถานะ

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

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

เหตุใดวิธีนี้จึงดีกว่าการเชื่อมตารางกับตัวเอง

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

กระบวนการ LAG-แฟล็ก-ผลรวมสะสม-GROUP BY รองรับช่วงเวลาได้ไม่จำกัดจำนวนในการประมวลผลรอบเดียวโดยไม่ต้องเชื่อมตาราง การอธิบายความแตกต่างนี้ ซึ่งก็คือการประมวลผลรอบเดียวแบบเชิงเส้นเทียบกับการเชื่อมตารางกับตัวเองแบบกำลังสอง คือเหตุผลระดับอาวุโสที่ผู้สัมภาษณ์ให้ความสำคัญ

แม่แบบที่นำกลับมาใช้ได้

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

  1. แฟล็ก: ใช้ CASE ร่วมกับ LAG เพื่อตรวจหากลุ่มช่วงต่อเนื่องใหม่
  2. คีย์: หาผลรวมสะสมของแฟล็ก โดยแบ่งกลุ่มและเรียงลำดับ
  3. การรวมช่วง: ใช้ GROUP BY กับคอลัมน์ที่ใช้แบ่งกลุ่ม สถานะ และคีย์
  4. ช่วงเวลา (ไม่บังคับ): ใช้ LEAD เพื่อหาจุดสิ้นสุดของช่วงกึ่งเปิด

โครงร่างเดียวกันนี้ใช้ได้กับจำนวนเต็ม วันที่ และสถานะ โดยเปลี่ยนเพียงเงื่อนไขใน CASE

ตรวจสอบความเข้าใจ

ยืนยันว่าคุณเข้าใจกฎการจัดกลุ่มช่วงสถานะต่อเนื่อง

สรุป: กลุ่มช่วงสถานะและวันที่

ตอนนี้คุณสามารถแก้ปัญหารูปแบบช่องว่างและกลุ่มช่วงต่อเนื่องที่ซับซ้อนที่สุดได้แล้ว:

  • ความติดกัน = สถานะไม่เปลี่ยนแปลงจากแถวก่อนหน้า แฟล็กจะเปลี่ยนด้วย LAG
  • หาผลรวมสะสมของแฟล็กการเปลี่ยนแปลงให้เป็น คีย์กลุ่ม ประจำแต่ละช่วงเวลา
  • รวมช่วงด้วย GROUP BY user_id, status, key เพื่อให้ได้ช่วงเวลาของแต่ละช่วงต่อเนื่อง
  • ใช้ LEAD เพื่อหาจุดสิ้นสุดของช่วงกึ่งเปิด และขยายแฟล็กให้แบ่งช่วงเมื่อมีช่องว่างของเวลามาก
  • แถวที่เหมือนกันและอยู่ติดกันจะถูกรวมโดยอัตโนมัติ จำนวนครั้งที่เปลี่ยนสถานะคำนวณได้จากแฟล็กชุดเดียวกัน
  • แม่แบบเดียวที่นำกลับมาใช้ได้ครอบคลุมจำนวนเต็ม วันที่ และสถานะ โดยเปลี่ยนเพียง CASE

นี่เป็นการจบบทเรียนเรื่องช่องว่างและกลุ่มช่วงต่อเนื่อง ซึ่งเป็นตัวชี้วัดความสามารถระดับอาวุโสที่น่าเชื่อถือในการสัมภาษณ์ SQL

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

บทเรียน “กลุ่มต่อเนื่องเมื่อวันที่และสถานะเปลี่ยนแปลง” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “กลุ่มต่อเนื่องเมื่อวันที่และสถานะเปลี่ยนแปลง”

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

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

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

บทเรียน “กลุ่มต่อเนื่องเมื่อวันที่และสถานะเปลี่ยนแปลง” ใช้เวลานานแค่ไหน

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

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

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

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

  1. รู้จักโจทย์ช่องว่างและกลุ่มต่อเนื่อง
  2. เทคนิคผลต่างของหมายเลขแถว
  3. ค้นหาช่องว่างในลำดับ
  4. กลุ่มต่อเนื่องเมื่อวันที่และสถานะเปลี่ยนแปลง
← กลับไปที่ Coding Interview Prep