SQL Interview Prep · บทเรียน

การตัดและจัดกลุ่มวันที่

จัดกลุ่มตามสัปดาห์ เดือน และไตรมาสด้วย DATE_TRUNC และคำสั่งเทียบเท่า

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

เหตุใดจึงมีการถามเรื่องการจัดกลุ่มวันที่

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

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

กลุ่มรายสัปดาห์ รายเดือน รายไตรมาส และรายปี
DATE_TRUNC และฟังก์ชันเทียบเท่าในรูปแบบ SQL ต่าง ๆ
การจัดกลุ่มอย่างถูกต้องเพื่อให้ข้อมูลในแผนภูมิเรียงตรงกัน

DATE_TRUNC: เครื่องมือหลัก

ใน PostgreSQL DATE_TRUNC(unit, ts) จะทำส่วนประกอบทุกอย่างที่ละเอียดกว่าหน่วยที่ระบุให้เป็นศูนย์ การตัดทอนเป็น 'month' จะเปลี่ยนการประทับเวลาใด ๆ ในเดือนมีนาคมให้เป็น 2024-03-01 00:00:00

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

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

การจัดกลุ่มรายได้ตามเดือน

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

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

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT เทียบกับ DATE_TRUNC

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

EXTRACT(MONTH FROM ts) จะส่งคืนหมายเลข 3 สำหรับเดือนมีนาคมทุกปี เหมาะสำหรับวิเคราะห์รูปแบบตามฤดูกาล
DATE_TRUNC('month', ts) จะส่งคืน จุดเริ่มต้นของเดือนที่เฉพาะเจาะจง โดยแยกปีออกจากกัน เหมาะสำหรับอนุกรมเวลา

หากจัดกลุ่มด้วย EXTRACT(MONTH ...) เพื่อสร้างแผนภูมิแนวโน้มรายเดือน คุณจะรวมข้อมูลจากหลายปีเข้าด้วยกันโดยไม่รู้ตัว

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

กลุ่มรายสัปดาห์กับคำถามว่าสัปดาห์เริ่มวันจันทร์หรือไม่

การจัดกลุ่มรายสัปดาห์มีรายละเอียดเล็กน้อยที่ผู้สัมภาษณ์ชอบถาม นั่นคือสัปดาห์เริ่มต้นเมื่อใด PostgreSQL ใช้ DATE_TRUNC('week', ts) เพื่อเลื่อนไปยัง วันจันทร์ เสมอ (สัปดาห์แบบ ISO)

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

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

กลุ่มรายไตรมาส

การทำรายงานรายไตรมาสพบได้บ่อยในตำแหน่งงานที่เกี่ยวข้องกับการเงิน DATE_TRUNC('quarter', ts) จะกำหนดการประทับเวลาใด ๆ ให้เป็นวันแรกของไตรมาสนั้น ได้แก่ 1 ม.ค. 1 เม.ย. 1 ก.ค. หรือ 1 ต.ค.

หากต้องการแสดงไตรมาสเป็นหมายเลขแทน ให้ใช้ EXTRACT(QUARTER ...) ร่วมกับปี

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL ไม่มี DATE_TRUNC

คำถามยอดนิยมที่เปรียบเทียบรูปแบบ SQL ต่าง ๆ คือ "MySQL ไม่มี DATE_TRUNC แล้วจะจัดกลุ่มตามเดือนได้อย่างไร" คำตอบที่ใช้ได้กว้างคือจัดรูปแบบวันที่ให้เหลือระดับความละเอียดที่ต้องการ

DATE_FORMAT(ts, '%Y-%m-01') ให้จุดเริ่มต้นของเดือนในรูปข้อความหรือวันที่
DATE_FORMAT(ts, '%Y-%m') ให้คีย์ข้อความที่เรียงลำดับได้ เช่น 2024-03

สำหรับสัปดาห์ MySQL มี YEARWEEK() พร้อมอาร์กิวเมนต์โหมดที่ใช้ควบคุมวันเริ่มต้นสัปดาห์

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

การจัดกลุ่มวันที่ใน SQL Server

ในอดีต SQL Server ไม่มีฟังก์ชันตัดทอนโดยตรง ผู้สมัครจึงใช้ DATEFROMPARTS หรือรูปแบบ DATEADD/DATEDIFF ปัจจุบันเวอร์ชันใหม่ (2022 ขึ้นไป) มี DATETRUNC แล้ว

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

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

การเติมช่วงที่หายไปในอนุกรมเวลา

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

วิธีแก้คือสร้างโครงช่วงเวลาที่ครบถ้วน แล้วใช้ LEFT JOIN เชื่อมข้อมูลเข้ากับโครงนั้น ใน PostgreSQL generate_series ใช้สร้างโครงช่วงเวลาได้

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

ตัวอย่างเชิงลึก: ผู้ใช้ที่ใช้งานอยู่ในแต่ละสัปดาห์

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

ตัดทอนการประทับเวลาของเหตุการณ์เป็นสัปดาห์ แล้วใช้ COUNT(DISTINCT user_id) หากกล่าวถึงการเชื่อมโครงสัปดาห์เพื่อแสดงสัปดาห์ที่ไม่มีกิจกรรมด้วย จะช่วยเพิ่มคะแนนได้

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

การจัดกลุ่มบนคอลัมน์ที่มีดัชนี

มีข้อควรระวังด้านประสิทธิภาพที่ควรกล่าวถึง การครอบคอลัมน์วันที่ด้วย DATE_TRUNC ภายในส่วนคำสั่ง WHERE อาจทำให้ตัววางแผนการประมวลผลไม่สามารถใช้ดัชนีบนคอลัมน์นั้นได้

การใช้วิธีนี้ใน GROUP BY ไม่มีปัญหา แต่สำหรับการกรองข้อมูล ให้เปรียบเทียบคอลัมน์ดิบกับขอบเขตที่คำนวณไว้แทน เราได้กล่าวถึงรูปแบบช่วงที่เปิดด้านหนึ่งนี้ไปก่อนหน้านี้ และยังใช้ได้กับกรณีนี้เช่นกัน

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

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

เลือกเครื่องมือที่เหมาะสมสำหรับแผนภูมิแนวโน้มรายเดือนที่แยกข้อมูลแต่ละปีออกจากกัน

สรุป: การตัดทอนและการจัดกลุ่มวันที่

สิ่งที่ควรจำ:

DATE_TRUNC(unit, ts) จะกำหนดการประทับเวลาให้เป็นจุดเริ่มต้นของช่วงเวลาและแยกปีออกจากกัน จึงเป็นเครื่องมือที่เหมาะสำหรับอนุกรมเวลา
EXTRACT จะส่งคืนเพียงหมายเลข เหมาะสำหรับวิเคราะห์รูปแบบตามฤดูกาล แต่จะรวมข้อมูลจากหลายปีเข้าด้วยกัน
สัปดาห์ของ PostgreSQL เริ่มต้นในวันจันทร์ ให้เลื่อนวันหากต้องการให้เริ่มต้นในวันอาทิตย์
MySQL ใช้ DATE_FORMAT ส่วน SQL Server รุ่นเก่าใช้รูปแบบ DATEADD(DATEDIFF(...)) และรุ่น 2022 ขึ้นไปมี DATETRUNC
ใช้ โครงวันที่ + LEFT JOIN ที่สร้างขึ้นเพื่อแสดงช่วงเวลาที่ไม่มีข้อมูล และอย่าใช้ DATE_TRUNC ใน WHERE เพื่อให้ยังใช้ดัชนีได้

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

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

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

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

จัดกลุ่มตามสัปดาห์ เดือน และไตรมาสด้วย DATE_TRUNC และคำสั่งเทียบเท่า คุณปฏิบัติ 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 ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

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

← กลับไปที่ SQL Interview Prep