SQL Academy · บทเรียน

ตารางข้อเท็จจริงและตารางมิติ

องค์ประกอบพื้นฐานของคลังข้อมูล

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

คลังข้อมูลคืออะไร

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

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

คำจำกัดความของตารางข้อเท็จจริง

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

โดยทั่วไปตารางข้อเท็จจริงจะมีจำนวนแถวมــากและจำนวนคอลัมน์น้อย โดยคอลัมน์ส่วนใหญ่เป็นคีย์นอกที่อ้างอิงตารางมิติ หรือเป็นค่ามาตรวัดเชิงตัวเลข เช่น quantity หรือ revenue

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL
);

คำจำกัดความของตารางมิติ

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

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

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_cost    NUMERIC(10, 2)
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(200),
  country      VARCHAR(100),
  segment      VARCHAR(50)
);

มิติวันที่

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

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

CREATE TABLE dim_date (
  date_key       INT PRIMARY KEY,  -- e.g. 20240315
  full_date      DATE NOT NULL,
  day_of_week    VARCHAR(10),
  day_of_month   INT,
  month_num      INT,
  month_name     VARCHAR(20),
  quarter        INT,
  year           INT,
  is_holiday     BOOLEAN DEFAULT FALSE,
  fiscal_quarter INT
);

-- Sample row
INSERT INTO dim_date VALUES
  (20240315, '2024-03-15', 'Friday', 15, 3, 'March', 1, 2024, FALSE, 2);

รูปแบบสคีมาแบบดาว

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

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

-- Join fact to two dimensions to enrich a sales report
SELECT
  dp.product_name,
  dp.category,
  SUM(fs.quantity)     AS total_units_sold,
  SUM(fs.total_amount) AS total_revenue
FROM fact_sales fs
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_date     dd ON dd.date_key     = fs.date_key
WHERE dd.year = 2024
GROUP BY dp.product_name, dp.category
ORDER BY total_revenue DESC;

คีย์ตัวแทนเทียบกับคีย์ธรรมชาติ

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

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

-- Surrogate key approach: integer join is fast
SELECT fs.sale_id, dc.full_name, fs.total_amount
FROM fact_sales fs
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dc.country = 'Germany'
LIMIT 10;

-- Natural key approach (avoid in warehouses): slower string join
-- JOIN dim_customer dc ON dc.email = fs.customer_email

ระดับรายละเอียดในตารางข้อเท็จจริง

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

ระดับรายละเอียดที่กำหนดไว้อย่างชัดเจนช่วยป้องกันการรวมค่าที่กำกวม หากแต่ละแถวแสดงเหตุการณ์คนละประเภท ผลลัพธ์จาก SUM และ COUNT ของคุณจะไม่มีความหมาย

-- Grain: one row per product per order line
-- Each row = one line item sold in one transaction
SELECT
  sale_id,
  date_key,
  product_key,
  quantity,
  unit_price,
  total_amount
FROM fact_sales
WHERE date_key = 20240315
ORDER BY sale_id;

มาตรวัดแบบบวกได้ แบบบวกได้บางส่วน และแบบบวกไม่ได้

ข้อเท็จจริงแบ่งออกเป็นสามประเภทตามวิธีที่คุณสามารถรวมค่าได้:

แบบบวกได้ — สามารถรวมค่าข้ามทุกมิติได้ (เช่น revenue, quantity)
แบบบวกได้บางส่วน — สามารถรวมค่าข้ามบางมิติได้ แต่ไม่ใช่ทุกมิติ (เช่น balance ของบัญชีสามารถรวมข้ามลูกค้าได้ แต่ไม่สามารถรวมข้ามเวลาได้)
แบบบวกไม่ได้ — ไม่สามารถรวมค่าอย่างมีความหมายได้ (เช่น unit_price, ratio) ให้ใช้ AVG หรือการรวมค่าแบบอื่นแทน

SELECT
  dd.month_name,
  SUM(fs.total_amount)         AS total_revenue,   -- additive
  AVG(fs.unit_price)           AS avg_unit_price,   -- non-additive: use AVG
  SUM(fs.quantity)             AS total_units       -- additive
FROM fact_sales fs
JOIN dim_date dd ON dd.date_key = fs.date_key
WHERE dd.year = 2024
GROUP BY dd.month_name, dd.month_num
ORDER BY dd.month_num;

มิติที่เปลี่ยนแปลงช้า (SCD ประเภท 1 และ 2)

คุณลักษณะของมิติเปลี่ยนแปลงไปตามเวลา — ลูกค้าย้ายประเทศ ผลิตภัณฑ์เปลี่ยนหมวดหมู่ มิติที่เปลี่ยนแปลงช้า (SCD) ใช้จัดการการเปลี่ยนแปลงเหล่านี้:

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

-- SCD Type 2: add a new version of the row
ALTER TABLE dim_customer ADD COLUMN valid_from DATE;
ALTER TABLE dim_customer ADD COLUMN valid_to   DATE;
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Expire the old row
UPDATE dim_customer
SET is_current = FALSE,
    valid_to   = CURRENT_DATE - INTERVAL '1 day'
WHERE email = 'anna@example.com' AND is_current = TRUE;

-- Insert the updated version
INSERT INTO dim_customer (full_name, email, country, segment, valid_from, valid_to, is_current)
VALUES ('Anna Muller', 'anna@example.com', 'Austria', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

มิติแบบไร้ตาราง

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

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

-- order_number is a degenerate dimension:
-- it lives in the fact table, no dim_order table needed
CREATE TABLE fact_order_lines (
  line_id      SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,  -- degenerate dimension
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  quantity     INT NOT NULL,
  line_total   NUMERIC(12, 2) NOT NULL
);

SELECT order_number, SUM(line_total) AS order_total
FROM fact_order_lines
GROUP BY order_number
ORDER BY order_total DESC
LIMIT 5;

การสืบค้นสคีมาแบบดาวทั้งหมด

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

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

SELECT
  dd.year,
  dd.quarter,
  dp.category,
  dc.country,
  SUM(fs.quantity)     AS units_sold,
  SUM(fs.total_amount) AS revenue
FROM fact_sales fs
JOIN dim_date     dd ON dd.date_key     = fs.date_key
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dd.year IN (2023, 2024)
  AND dp.category = 'Electronics'
GROUP BY dd.year, dd.quarter, dp.category, dc.country
ORDER BY dd.year, dd.quarter, revenue DESC;

ตรวจสอบอย่างรวดเร็ว: ข้อเท็จจริงเทียบกับมิติ

ตรวจสอบความเข้าใจของคุณเกี่ยวกับความแตกต่างระหว่างตารางข้อเท็จจริงและตารางมิติในสคีมาแบบดาว

ทบทวนบทเรียน

ในบทเรียนนี้ คุณได้เรียนรู้องค์ประกอบพื้นฐานสำคัญของสคีมาแบบดาวในคลังข้อมูล:

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

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

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

บทเรียน “ตารางข้อเท็จจริงและตารางมิติ” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “ตารางข้อเท็จจริงและตารางมิติ”

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

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

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

บทเรียน “ตารางข้อเท็จจริงและตารางมิติ” ใช้เวลานานแค่ไหน

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

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

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

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

← กลับไปที่ SQL Academy