PricingLoginStart free
SQL Academy · Pelajaran

Jadual Fakta dan Dimensi

Blok binaan gudang data

Pelajaran 2 daripada 413 langkah

Jadual Fakta dan Dimensi ialah pelajaran SQL Academy percuma di CoddyKit. Ini ialah pelajaran 2 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran SQL Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah Gudang Data?

Gudang data ialah repositori pusat yang direka untuk pelaporan dan pertanyaan analitik. Berbeza daripada pangkalan data transaksi yang dioptimumkan untuk operasi tulis pantas, gudang data ditala untuk bacaan pantas merentas jumlah data sejarah yang besar.

Cara paling lazim untuk menyusun gudang data adalah dengan menggunakan skema bintang, yang membahagikan data kepada dua jenis jadual: jadual fakta dan jadual dimensi.

Takrif Jadual Fakta

Jadual fakta menyimpan peristiwa kuantitatif yang boleh diukur — perkara yang ingin anda analisis. Setiap baris mewakili satu kejadian peristiwa perniagaan, seperti jualan, paparan halaman web atau tiket sokongan.

Jadual fakta biasanya lebar (banyak baris) dan sempit (sedikit lajur), dengan kebanyakan lajurnya berupa kunci asing kepada jadual dimensi atau ukuran angka seperti quantity atau 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
);

Takrif Jadual Dimensi

Jadual dimensi menyimpan atribut penerangan yang memberikan konteks kepada setiap fakta. Contohnya termasuk dimensi produk (nama, kategori, jenama) atau dimensi tarikh (hari, bulan, suku tahun, tahun).

Jadual dimensi biasanya pendek (lebih sedikit baris) tetapi lebar (banyak lajur penerangan). Jadual ini digabungkan dengan jadual fakta menggunakan kunci nombor bulat pengganti.

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)
);

Dimensi Tarikh

Dimensi tarikh ialah dimensi yang paling lazim dalam mana-mana gudang data. Daripada menyimpan TIMESTAMP mentah dalam jadual fakta, anda menyimpan kunci nombor bulat yang merujuk kepada jadual kalendar yang dibina terlebih dahulu.

Hal ini membolehkan pertanyaan menapis atau mengumpulkan data mengikut suku tahun fiskal, hari dalam minggu, penanda cuti dan atribut kalendar lain tanpa melakukan aritmetik tarikh pada masa pertanyaan dijalankan.

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);

Corak Skema Bintang

Apabila anda melukis gambar rajah dengan satu jadual fakta di tengah dan jadual dimensi memancar keluar, bentuknya kelihatan seperti bintang — sebab itulah ia dipanggil skema bintang.

Kunci asing dalam jadual fakta menunjuk kepada kunci utama setiap dimensi. Pertanyaan biasanya menggabungkan jadual fakta dengan satu atau lebih dimensi untuk menambahkan konteks penerangan kepada nombor mentah.

-- 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;

Kunci Pengganti berbanding Kunci Semula Jadi

Jadual dimensi menggunakan kunci pengganti — nombor bulat sintetik yang dijana oleh pangkalan data dan tidak bergantung pada sebarang makna perniagaan. Kunci semula jadi (seperti SKU produk atau e-mel pelanggan) boleh berubah dari semasa ke semasa, tetapi kunci pengganti tidak pernah berubah.

Penggunaan kunci pengganti melindungi jadual fakta daripada perubahan sistem huluan dan menjadikan gabungan lebih pantas kerana perbandingan nombor bulat lebih murah berbanding perbandingan rentetan.

-- 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

Granulariti: Tahap Perincian dalam Jadual Fakta

Granulariti jadual fakta menerangkan dengan tepat perkara yang diwakili oleh satu baris. Sebelum membina gudang data, anda mesti menetapkan granulariti — contohnya, satu baris bagi setiap baris produk individu dalam pesanan jualan.

Granulariti yang ditakrifkan dengan baik mengelakkan pengagregatan yang tidak jelas. Jika baris yang berbeza mewakili peristiwa yang berbeza, hasil SUM dan COUNT anda tidak akan bermakna.

-- 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;

Ukuran Aditif, Separa Aditif dan Bukan Aditif

Fakta terbahagi kepada tiga jenis berdasarkan cara anda boleh mengagregatkannya:

  • Aditif — boleh dijumlahkan merentas semua dimensi (contohnya, revenue dan quantity).
  • Separa aditif — boleh dijumlahkan merentas sesetengah dimensi tetapi bukan semua dimensi (contohnya, balance akaun boleh dijumlahkan merentas pelanggan tetapi bukan merentas masa).
  • Bukan aditif — tidak boleh dijumlahkan secara bermakna (contohnya, unit_price dan ratio). Gunakan AVG atau pengagregatan lain.
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;

Dimensi yang Berubah Perlahan (SCD Jenis 1 dan 2)

Atribut dimensi berubah dari semasa ke semasa — pelanggan berpindah negara dan produk bertukar kategori. Dimensi yang Berubah Perlahan (SCD) mengendalikan perubahan ini:

  • Jenis 1 — Tulis ganti nilai lama. Mudah, tetapi sejarah akan hilang.
  • Jenis 2 — Tambahkan baris baharu dengan kunci pengganti baharu dan tarikh kesahan. Kaedah ini mengekalkan sejarah penuh supaya fakta sejarah masih menunjuk kepada versi dimensi yang betul.
-- 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);

Dimensi Degenerat

Kadang-kadang atribut dimensi tidak memerlukan jadualnya sendiri. Dimensi degenerat ialah kunci dimensi yang berada terus dalam jadual fakta tanpa jadual dimensi yang sepadan.

Contoh biasa ialah nombor pesanan, nombor invois atau pengenal tiket. Semua ini memberikan konteks untuk menelusuri perincian, tetapi tidak mempunyai lajur penerangan lain yang wajar disimpan dalam jadual berasingan.

-- 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;

Membuat Pertanyaan pada Skema Bintang Penuh

Apabila semuanya digabungkan: pertanyaan gudang data yang lazim menggabungkan jadual fakta dengan beberapa dimensi, menerapkan penapis pada atribut dimensi dan mengagregatkan ukuran daripada jadual fakta.

Pengoptimum boleh mengendalikan gabungan berbilang hala ini dengan cekap kerana kunci asing jadual fakta telah diindeks dan jadual dimensi agak kecil.

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;

Semakan Pantas: Fakta berbanding Dimensi

Uji pemahaman anda tentang perbezaan antara jadual fakta dan jadual dimensi dalam skema bintang.

Jawab dalam pelajaran interaktif

Ringkasan Pelajaran

Dalam pelajaran ini, anda telah mempelajari asas utama skema bintang gudang data:

  • Jadual fakta menyimpan peristiwa yang boleh diukur (jualan, klik dan transaksi) bersama ukuran angka serta kunci asing.
  • Jadual dimensi memberikan konteks penerangan (siapa, apa, di mana dan bila) menggunakan kunci pengganti.
  • Granulariti mentakrifkan perkara tepat yang diwakili oleh satu baris fakta — tetapkan granulariti sebelum membina gudang data.
  • Ukuran boleh berupa aditif, separa aditif atau bukan aditif, dan perkara ini menentukan cara anda mengagregatkannya.
  • SCD Jenis 2 mengekalkan nilai dimensi sejarah dengan menambahkan baris baharu bersama tarikh kesahan.
  • Dimensi degenerat berada dalam jadual fakta apabila dimensi tersebut tidak mempunyai atribut tambahan untuk diterangkan.

Memahami jadual fakta dan jadual dimensi ialah asas untuk membina gudang data yang pantas, boleh diskalakan dan berkeupayaan analitik tinggi.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus
46
Pelajaran
183
Mula belajar secara percumaLihat kursus penuh

Soalan Lazim

Adakah pelajaran “Jadual Fakta dan Dimensi” percuma?

Ya — teks penuh “Jadual Fakta dan Dimensi” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus SQL Academy, tingkat taraf kepada CoddyKit PRO. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Jadual Fakta dan Dimensi”?

Blok binaan gudang data Anda berlatih SQL Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan SQL Academy?

Tiada pengalaman terdahulu diperlukan. Pembelajaran SQL Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 2 daripada 4.

Berapa lamakah pelajaran “Jadual Fakta dan Dimensi” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran SQL Academy ini?

Ya. Setiap pelajaran SQL Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

  1. OLTP berbanding OLAP
  2. Jadual Fakta dan Dimensi
  3. Skema Bintang dan Kepingan Salji
  4. Menulis Pertanyaan Analitik
← Kembali ke SQL Academy