Membandingkan tanggal

Pelaporan dengan SQL

Tyler Pernes

Learning & Development Consultant

Pertanyaan untuk dijawab

Bulan lalu vs bulan sebelumnya?
Rolling 7 hari?

original_table
+------------+----------+
| date       | revenue  |
|------------+----------|
| 2018-01-01 | 400      |
| 2018-01-02 | 380      |
| 2018-01-03 | 625      |
+------------+----------+

Perbandingan bulan ke bulan

LAG(value, offset) menghasilkan value dari sejumlah baris offset sebelum baris saat ini.
LEAD(value, offset) menghasilkan value dari sejumlah baris offset setelah baris saat ini.

Perbandingan bulan ke bulan

Langkah 1: tampilkan revenue per bulan

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev
FROM original_table
GROUP BY month;

Langkah 2: revenue bulan sebelumnya

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev
FROM original_table
GROUP BY month;

Perbandingan bulan ke bulan

Langkah 3: hitung persentase perubahan

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev,
    SUM(revenue)
    /
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date))-1 AS perc_change
FROM original_table
GROUP BY month;

Perbandingan bulan ke bulan

Langkah 3: hitung persentase perubahan

+--------+--------------+-----------+--------------+
| month  | current_rev  | prev_rev  | perc_change  |
|--------+--------------+-----------+--------------|
| 01     | 15000        | null      | null         |
| 02     | 14000        | 15000     | -.06         |
| 03     | 21000        | 14000     | .50          |
+--------+--------------+-----------+--------------+

Perhitungan rolling

Hanya hitung 7 baris

Klausa baru: ROWS BETWEEN

SUM(value) OVER (ORDER BY value ROWS BETWEEN N PRECEDING AND CURRENT ROW)

Perhitungan rolling

Kueri rolling sum

SELECT 
    date,
    SUM(SUM(revenue)) OVER 
    (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_revenue
FROM original_table
GROUP BY date;

Tabel baru: web_data

web_data
+------------+-------------+--------+
| date       | country_id  | view   |
|------------+-------------+--------|
| 2018-01-01 | 1           | 24313  |
| 2018-01-01 | 2           | 3768   |
| 2018-01-01 | 3           | 26817  |
+------------+-------------+--------+

Ayo berlatih!

Pelaporan dengan SQL