Jana Julat dalam SQL (& Selamat Hari Raya)

Mari kita bedah jawapan daripada chatGPT

Hari Raya kedua dan saya ingin kongsikan satu masalah.

Beberapa hari lepas, saya dibelenggu dengan satu soalan/cabaran SQL postgres yang memerlukan saya untuk menjana satu julat yang berterusan berdasarkan jumlah kelayakkan. Walaupun soalan ini dapat diselesaikan dengan menggunakan stored procedures namun saya berkeras dengan idea ia boleh diselesaikan dengan sebuah SQL query (kepala batu =))

Photo by James Fuller on Unsplash

Setelah beberapa hari memerah otak saya tewas.

Saya mula bertanya kepada chatGPT untuk memberi inspirasai jawapan. Jawapan SQL yang disarankan menarik. Dan hari ini kita akan membincangkan jawapan tersebut.

Latar belakang soalan

Untuk memudahkan pemahaman soalan ini. Biar saya jelaskan sedikit permasalahan tersebut. Anda ada senarai pelanggan yang telah berbelanja jumlah RM X. Untuk seringgit yang dibelanjakan pelanggan tersebut layak untuk mendapat 1 undian cabutan bertuah.

RM 1 = 1 Undian

chatGPT telah menyarankan jawapan berikut:

SELECT
    id_pelanggan,
    jumlah_belanja,
    ROW_NUMBER() OVER (ORDER BY id_pelanggan) AS row_num,
    SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) - jumlah_belanja + 1 AS start_range,
    SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) AS end_range
FROM undian_pelanggan;

Pertama kali saya melihat jawapan ini saya masih tidak percaya kerana sebelum ini pun ada beberapa kali jawapan yang diberikan kurang tepat. Namun selepas run SQL tersebut ternyata ia berjaya mendapatkan jawapan yang ini saya dapat.

Mari bersama-sama saya membedah jawapan ini dan mungkin ia akan membantu anda jika anda mendapat masalah yang sama nanti.

Baik.

Seperti biasa mari kita cipta table and masukkan data

Table

create table undian_pelanggan (
    id_pelanggan  varchar(10),
    jumlah_belanja numeric
);

Masukkan data

insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810001', 200);
insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810002', 19);
insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810003', 2);
insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810004', 24);
insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810005', 300);
insert into undian_pelanggan(id_pelanggan,jumlah_belanja) values ('810006', 200);

Kita select data tersebut

Berdasarkan saranan daripada chatGPT , kita cuba mendapat hasil seperti di bawah:

SELECT
    id_pelanggan,
    jumlah_belanja,
    ROW_NUMBER() OVER (ORDER BY id_pelanggan) AS row_num,
    SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) - jumlah_belanja + 1 AS start_range,
    SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) AS end_range
FROM undian_pelanggan;

Mari kita bedah SQL ini.

Untuk baris

ROW_NUMBER() OVER (ORDER BY id_pelanggan) AS row_num,

Baris ini akan mendapatkan row_num yang disusun berdasarkan id_pelanggan.

Manakala baris :

SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) - jumlah_belanja + 1 AS start_range,

Baris ini akan mendapatkan jumlah jumlah_belanja yang disusun berdasarkan id_pelanggan. Jumlah ini akan ditolak dengan jumlah_belanja dan ditambah 1:

start_range = SUM(jumlah_belanja) - jumlah_belanja + 1

Baris berikutnya lebih mudah kerana ianya hanya sum jumlah_belanja

SUM(jumlah_belanja) OVER (ORDER BY id_pelanggan) AS end_range

Jadi query tersebut dapat memulangkan senarai row yang betul dengan julat yang sepatutnya.

Selamat mencuba!