0Pricing
SQL Academy · Pelajaran

Menulis Kueri Analitis

Iris, uraikan, dan agregasikan metrik.

Menulis Kueri Analitis adalah pelajaran SQL Academy gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar SQL Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus SQL Academy mencakup 4 pelajaran total.

Apa Itu Kueri Analitik?

Kueri analitik lebih dari sekadar pencarian baris sederhana. Alih-alih menanyakan pesanan mana yang dibuat pelanggan 42?, kueri ini menanyakan berapa total pendapatan berdasarkan wilayah dan kuartal? atau bagaimana perbandingan bulan ini dengan bulan lalu?

Dalam gudang data yang dibangun berdasarkan skema bintang, kueri analitik melakukan pengirisan (menyaring satu dimensi), pemotongan (menyaring beberapa dimensi), dan penggulungan (mengagregasikan ke tingkat perincian yang lebih kasar) pada fakta untuk menampilkan wawasan bisnis.

Penyegaran Kembali Skema Bintang

Skema bintang memiliki satu tabel fakta pusat (misalnya, fact_sales) yang dikelilingi oleh tabel dimensi (misalnya, dim_date, dim_product, dim_store). Kueri analitik menggabungkan tabel fakta dengan dimensi yang diperlukan untuk analisis saat ini.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Pengirisan: Menyaring Satu Dimensi

Pengirisan berarti membatasi kumpulan hasil ke satu nilai dari satu dimensi—misalnya, hanya melihat data untuk tahun 2024. Klausa WHERE adalah alat pengirisan Anda.

Dengan melakukan pengirisan lebih awal, Anda mengurangi jumlah baris yang harus diagregasikan oleh basis data sehingga kueri tetap cepat pada tabel fakta berukuran besar.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Pemotongan: Menyaring Beberapa Dimensi

Pemotongan berarti menerapkan penyaringan pada dua dimensi atau lebih secara bersamaan—misalnya, melihat penjualan elektronik di wilayah Utara selama kuartal pertama. Setiap kondisi WHERE tambahan menghasilkan kubus data yang lebih kecil.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Penggulungan: Mengagregasikan ke Tingkat Perincian yang Lebih Tinggi

Penggulungan berarti berpindah dari tingkat perincian yang mendetail (penjualan harian per toko) ke tingkat perincian yang lebih kasar (penjualan bulanan per wilayah). Anda melakukannya dengan menghapus kolom tingkat bawah dari GROUP BY lalu mengagregasikan ulang.

Pengubah ROLLUP memungkinkan Anda menghasilkan subtotal dan total keseluruhan dalam satu kueri, alih-alih menulis beberapa blok UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Perbandingan Antarperiode dengan LAG

Salah satu pola analitik yang paling umum adalah membandingkan suatu metrik dengan metrik yang sama pada periode sebelumnya. Fungsi jendela LAG() memungkinkan Anda mengambil nilai baris sebelumnya secara langsung ke dalam baris saat ini tanpa penggabungan tabel dengan dirinya sendiri.

Di sini, kita menghitung pertumbuhan pendapatan bulanan dibandingkan bulan sebelumnya dalam bentuk persentase.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Total Kumulatif dengan SUM OVER

Total kumulatif (jumlah kumulatif) menambahkan nilai setiap baris ke total semua baris sebelumnya dalam urutan yang ditentukan. Cara ini sangat sesuai untuk melacak pendapatan kumulatif sepanjang tahun atau memantau pengurangan anggaran.

Klausa bingkai ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW membuat jendela menjadi eksplisit dan tidak ambigu.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Pemeringkatan Dimensi dengan DENSE_RANK

Pemeringkatan memungkinkan Anda menemukan hasil terbaik atau terburuk dalam suatu kelompok. DENSE_RANK() memberikan peringkat berurutan tanpa celah ketika terdapat nilai yang sama, sehingga menjadi pilihan yang disarankan untuk papan peringkat dalam laporan BI.

Membungkus hasil yang telah diberi peringkat dalam CTE lalu menyaring berdasarkan peringkat menjadikan pola N teratas rapi dan mudah dibaca.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Persentase Kontribusi dengan SUM Berbasis Jendela

Mengetahui pendapatan absolut suatu produk memang berguna, tetapi mengetahui bahwa produk tersebut menyumbang 38% dari pendapatan kategori lebih berguna untuk menentukan tindakan. SUM() berbasis jendela pada seluruh partisi memberikan penyebut tanpa penggabungan subkueri.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Rata-rata Bergerak untuk Menghaluskan Tren

Angka penjualan harian atau mingguan sering berfluktuasi. Rata-rata bergerak menghaluskan fluktuasi jangka pendek sehingga Anda dapat melihat tren yang mendasarinya. Di sini, rata-rata bergerak selama 3 bulan dihitung menggunakan bingkai jendela geser.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE untuk Semua Kombinasi Dimensi

CUBE memperluas ROLLUP dengan menghitung subtotal untuk setiap kombinasi yang mungkin dari dimensi yang dicantumkan, bukan hanya jalur penggulungan hierarkis. Cara ini menghasilkan ringkasan lintas dimensi lengkap dalam satu kali proses—berguna untuk dasbor multidimensi yang memungkinkan pengguna mengubah susunan data dengan bebas.

NULL pada kolom pengelompokan berarti semua nilai dari dimensi tersebut—gunakan GROUPING() untuk membedakan NULL yang memang ada dalam data dari NULL akibat penggulungan.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Operasi mana yang membatasi hasil ke satu nilai dimensi?

Uji pemahaman Anda tentang terminologi kueri analitik yang digunakan dalam pergudangan data.

Ringkasan: Menulis Kueri Analitik

Dalam pelajaran ini, Anda mempelajari pola-pola inti untuk menulis kueri analitik pada skema bintang:

  • Pengirisan—menyaring satu dimensi dengan WHERE untuk berfokus pada segmen tertentu.
  • Pemotongan—menyaring beberapa dimensi secara bersamaan untuk menghasilkan kubus data yang spesifik.
  • Penggulungan—mengagregasikan ke tingkat perincian yang lebih kasar; gunakan ROLLUP atau CUBE untuk subtotal bertingkat.
  • LAG / LEAD—perbandingan antarperiode tanpa penggabungan tabel dengan dirinya sendiri.
  • Total kumulatif & rata-rata bergerak—metrik kumulatif dan yang dihaluskan melalui bingkai jendela.
  • DENSE_RANK—pemeringkatan N teratas yang rapi dalam partisi.
  • Persentase kontribusi—SUM berbasis jendela sebagai penyebut untuk menghitung proporsi.

Dengan menggabungkan pola-pola ini, Anda dapat menangani sebagian besar kebutuhan analitik BI dan pelaporan yang akan Anda temui di gudang data produksi.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Menulis Kueri Analitis” gratis?

Ya — teks lengkap “Menulis Kueri Analitis” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus SQL Academy, upgrade ke CoddyKit PRO. Kursus SQL Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Menulis Kueri Analitis”?

Iris, uraikan, dan agregasikan metrik. Kamu berlatih SQL Academy dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai SQL Academy?

Tidak diperlukan pengalaman sebelumnya. SQL Academy di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “Menulis Kueri Analitis” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran SQL Academy ini?

Ya. Setiap pelajaran SQL Academy menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

  1. OLTP vs OLAP
  2. Tabel Fakta dan Dimensi
  3. Skema Bintang dan Keping Salju
  4. Menulis Kueri Analitis
← Kembali ke SQL Academy