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

สร้างชุดตัวเลขและวันที่

ใช้การเรียกซ้ำเพื่อสร้างลำดับสำหรับเติมช่องว่างและสร้างปฏิทิน

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

การเรียกซ้ำโดยไม่มีลำดับชั้น

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

โจทย์คลาสสิกคือ "แสดงยอดขายรายวันของทั้งเดือน รวมวันที่ไม่มียอดขายด้วย" คุณไม่สามารถแสดงวันที่หายไปได้ หากไม่สร้างวันที่ทั้งหมดขึ้นมาก่อน

ชุดตัวเลขอย่างง่าย

จุดตั้งต้นกำหนดตัวเลขแรก ส่วนสมาชิกแบบเรียกซ้ำจะเพิ่มทีละหนึ่งในแต่ละรอบ และ WHERE ในส่วนสมาชิกแบบเรียกซ้ำจะหยุดการทำงาน ชุดนี้สร้างตัวเลขตั้งแต่ 1 ถึง 10

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

เงื่อนไขหยุดการทำงาน

ต่างจากแผนผังองค์กร ชุดตัวเลขไม่มี โหนดปลายสุดตามธรรมชาติ ที่ใช้หยุดการทำงาน — คุณอาจเพิ่มตัวเลขต่อไปได้ตลอด ดังนั้นต้องเพิ่มเงื่อนไขหยุดอย่างชัดเจนในส่วนสมาชิกแบบเรียกซ้ำ: WHERE n < 10

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

กำหนดช่วงด้วยพารามิเตอร์

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

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 2 FROM nums WHERE n + 2 <= 99
)
SELECT n FROM nums;  -- odd numbers 1,3,5,...,99

การสร้างชุดวันที่

เปลี่ยนจากการคำนวณจำนวนเต็มเป็นการคำนวณวันที่ แล้วคุณจะได้ปฏิทิน จุดตั้งต้นคือวันที่เริ่มต้น ส่วนสมาชิกแบบเรียกซ้ำจะเพิ่มหนึ่งวันไปเรื่อย ๆ จนเกินวันที่สิ้นสุด

ไวยากรณ์สำหรับการเพิ่มหนึ่งวันแตกต่างกันไปตามภาษาของระบบฐานข้อมูล — รูปแบบของ Postgres นี้ใช้ช่วงเวลา

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM cal
    WHERE d < DATE '2024-01-31'
)
SELECT d FROM cal;

การเติมช่องว่างด้วย LEFT JOIN

ตอนนี้ให้นำปฏิทินมารวมกับข้อมูลจริง สร้างวันที่ทุกวันก่อน แล้วใช้ LEFT JOIN เชื่อมกับตารางยอดขาย เพื่อให้วันที่ไม่มียอดขายปรากฏขึ้นพร้อมค่า NULL ซึ่งคุณเปลี่ยนเป็น 0 ได้ด้วย COALESCE

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

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day' FROM cal
    WHERE d < DATE '2024-01-07'
)
SELECT cal.d, COALESCE(SUM(s.amount), 0) AS total
FROM cal
LEFT JOIN sales s ON s.sale_date = cal.d
GROUP BY cal.d
ORDER BY cal.d;

แกนข้อมูลรายเดือนและรายสัปดาห์

เปลี่ยนค่าที่เพิ่มเพื่อสร้างปฏิทินที่มีช่วงห่างมากขึ้น เพิ่ม INTERVAL '1 month' สำหรับแกนข้อมูลรายเดือน หรือ INTERVAL '7 day' สำหรับรายสัปดาห์ ซึ่งมีประโยชน์เมื่อผู้สัมภาษณ์ต้องการรายงานรายเดือนที่รวมเดือนซึ่งไม่มีข้อมูลด้วย

WITH RECURSIVE months AS (
    SELECT DATE '2024-01-01' AS m
    UNION ALL
    SELECT m + INTERVAL '1 month' FROM months
    WHERE m < DATE '2024-12-01'
)
SELECT m FROM months;

ความแตกต่างของการคำนวณวันที่ตามระบบ

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

  • Postgres: d + INTERVAL '1 day'.
  • MySQL: DATE_ADD(d, INTERVAL 1 DAY).
  • SQL Server: DATEADD(DAY, 1, d).
  • SQLite: date(d, '+1 day').

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

การเรียกซ้ำเทียบกับการสร้างชุดลำดับ

Postgres มี generate_series() ในตัวสำหรับสร้างตัวเลขหรือวันที่โดยไม่ต้องใช้การเรียกซ้ำ และทำงานได้เร็วกว่าและอ่านเข้าใจง่ายกว่า:

SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day');

หากฐานข้อมูลที่ใช้ในการสัมภาษณ์รองรับฟังก์ชันนี้ ควรเลือกใช้ แต่ระบบจำนวนมาก เช่น MySQL และ SQL Server รุ่นก่อน ๆ ไม่มีฟังก์ชันนี้ — ในกรณีนั้น CTE แบบเรียกซ้ำคือทางเลือกสำรองที่ใช้ข้ามระบบได้

ระวังขีดจำกัดการเรียกซ้ำ

การสร้างชุดลำดับขนาดใหญ่อาจชนขีดจำกัดการเรียกซ้ำของระบบ SQL Server กำหนดค่าเริ่มต้นไว้ที่ MAXRECURSION 100 ดังนั้นปฏิทิน 365 วันจะล้มเหลว เว้นแต่คุณจะต่อท้าย OPTION (MAXRECURSION 0) เพื่อยกเลิกขีดจำกัด

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

-- SQL Server: lift the 100-row recursion cap
-- ...recursive CTE here...
SELECT * FROM cal
OPTION (MAXRECURSION 0);

การเชื่อมแบบ CROSS JOIN กับชุดลำดับ

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

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

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT o.order_id, nums.n AS unit
FROM orders o
JOIN nums ON nums.n <= o.quantity;

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

เหตุใดเงื่อนไขหยุดจึงสำคัญในชุดตัวเลขหรือชุดวันที่

สรุปทบทวน

การเรียกซ้ำสามารถสร้างแถวที่ไม่มีอยู่ในตารางใดเลยได้:

  • กำหนดค่าแรกในจุดตั้งต้น แล้วเพิ่มค่าในส่วนสมาชิกแบบเรียกซ้ำ
  • เพิ่ม เงื่อนไขหยุดการทำงาน อย่างชัดเจนเสมอ — ชุดลำดับไม่มีจุดสิ้นสุดตามธรรมชาติ
  • สร้าง แกนข้อมูล สำหรับวันที่หรือตัวเลข แล้วใช้ LEFT JOIN เชื่อมข้อมูลรายการและใช้ COALESCE เพื่อเติมช่องว่าง
  • เลือกใช้ generate_series เมื่อมีให้ใช้ และระวัง MAXRECURSION บน SQL Server

ถัดไป: เทคนิคด้านความปลอดภัยที่ป้องกันไม่ให้การเรียกซ้ำทำงานไม่หยุด

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

บทเรียน “สร้างชุดตัวเลขและวันที่” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “สร้างชุดตัวเลขและวันที่”

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

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

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

บทเรียน “สร้างชุดตัวเลขและวันที่” ใช้เวลานานแค่ไหน

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

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

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

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

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