Dapatkan Jualan Tahun Lepas Dengan LAG() Function

Tidak lagi perlukan subquery yang complex

Photo by Markus Spiske on Unsplash

Dalam rencana kali ini, saya bawakan anda penggunan function LAG() dalam postgres. Saya banyak menggunakan function ini untuk mendapatkan data daripada row sebelum ini sebagai contoh jualan tahun lepas atau jumlah pesanan bulan lepas berdasarkan pelanggan.

Mari kita mulakan.

Kita create table dan masukkan seperti biasa

CREATE TABLE jualan(
	tahun SMALLINT,
	group_id INT NOT NULL,
	jumlah DECIMAL(10,2) NOT NULL
);


---MASUKKAN DATA

INSERT INTO
	jualan(tahun, group_id, jumlah)
VALUES
	(2020,1,1474),
	(2020,2,1787),
	(2020,3,1760),
	(2021,1,1915),
	(2021,2,1911),
	(2021,3,1118),
	(2022,1,1646),
	(2022,2,1975),
	(2022,3,1516),
    (2024,1,1516);

Kita query data tersebut

select * from jualan;

Soalan: Berapakan jualan Group ID 2 dan tunjukkan jumlan jualan tahun sebelumnya?

Untuk memahami soalan ini, kita boleh mendapatkan beberapa maklumat penting:

1. Akan ada filter Group ID = 2

2. Jualan mesti disusun mengikut tahun, dalam kes ini ascending order (kecil kepada besar

Untuk itu SQL dibawah digunakan

SELECT
  tahun,
  jumlah,
  group_id,
  LAG(jumlah) OVER (
    ORDER BY
      tahun
  ) jualan_tahun_lepas
FROM
  jualan
where group_id=2;

Row yang dipulangkan adalah

Oleh kerana 2020 adalah tahun pertama, jadi jualan_tahun_lepas adalah NULL (tiada nilai)

Baik. Selesai satu masalah.

Soalan yang selalunya timbul adalah, data yang kita perlukan adalah untuk setiap Group ID, jadi adakah dengan hanya membuang kata kuncu WHERE itu dapat menyelesaikan masalah ini?

Jom kita cuba.

SELECT
  tahun,
  jumlah,
  group_id,
  LAG(jumlah) OVER (
    ORDER BY
      tahun
  ) jualan_tahun_lepas
FROM
  jualan
;

Row ini dipulangkan

Macam tidak berapa betulkan?

Oleh itu saya perkenalkan lagi satu kata kunci PARTITION BY

SELECT
  tahun,
  jumlah,
  group_id,
  LAG(jumlah) OVER (
    PARTITION BY group_id
    ORDER BY
      tahun
  ) jualan_tahun_lepas
FROM
  jualan
;

Dengan menambah kata kunci ini, query ini akan membahagikan row kepada subset data yang lebih kecil.

Hasilnya.

Setiap Group ID yang baru ianya akan dioffset.

Sekian dan selamat mencuba.