Bo'lim yakuni: uch jadvalni birga bog'laymiz

Bu bo'limda biz jadvallarni bog'lashni (JOIN) o'rgandik:

• relyatsion model — ma'lumotni bir-biriga bog'langan jadvallarga bo'lib saqlash;
• JOIN va ON — ikki jadvalni umumiy kalit (id) orqali ulash;
• JOIN + GROUP BY — bog'langan jadvallar ustida agregat (SUM, COUNT, ...) hisoblash.

Endi shularning hammasini bitta loyihada birlashtiramiz. Bu — bo'limning yakuniy darsi, ya'ni bilimlarni haqiqiy vazifada sinab ko'rish vaqti.

Bu safar bizga uchta jadval kerak bo'ladi. Ular bilan tanishib chiqamiz.

mijozlar jadvali — do'kon mijozlari:

mahsulotlar jadvali — sotuvdagi tovarlar:

buyurtmalar jadvali — kim qaysi mahsulotdan nechta sotib olgani:

E'tibor bering: buyurtmalar jadvali o'zida ism yoki narxni saqlamaydi. Uning o'rniga u faqat kalitlarni saqlaydi — mijoz_id (qaysi mijoz) va mahsulot_id (qaysi mahsulot). Mijozning ismini mijozlar jadvalidan, mahsulotning narxini esa mahsulotlar jadvalidan olamiz. Aynan shu — relyatsion modelning mohiyati.

Nega uchta jadval kerak?

Vazifamiz — har bir mijozning jami xarid summasini hisoblash. Har bir buyurtma uchun summa narx * soni ga teng (masalan, 5 ta daftar 8000 * 5 = 40000 so'm).

Lekin bitta jadvalning o'zi bizga yetarli emas:

• buyurtmalar da soni bor, lekin narx yo'q;
• mahsulotlar da narx bor, lekin u qaysi buyurtmaga tegishli ekani yo'q;
• mijozlar da ism bor, lekin xarid haqida hech narsa yo'q.

Demak, uchala jadvalni ham bog'lashimiz kerak. Buni ikki bosqichda qilamiz: avval buyurtmalar ni mijozlar bilan, keyin esa shu natijani mahsulotlar bilan ulaymiz.

Birinchi bog'lash: buyurtma → mijoz

Avval har bir buyurtmaga uning mijozining ismini qo'shamiz. Buning uchun buyurtmalar.mijoz_id ni mijozlar.id bilan tenglashtiramiz:

SELECT m.ism, b.mahsulot_id, b.soni
FROM mijozlar m
JOIN buyurtmalar b ON m.id = b.mijoz_id
ORDER BY b.id;

Bu yerda mijozlar ga m, buyurtmalar ga b degan qisqa nom (alias) berdik — shunda har gal to'liq nomni yozib o'tirmaymiz. Natija:

Ali|1|1
Ali|4|3
Vali|2|1
Soli|6|10
Dilnoza|3|2
Ali|5|5
Jasur|8|4

Endi har bir buyurtmada kim sotib olgani (ism) va nima sotib olgani (mahsulot_id) ko'rinib turibdi. Lekin bizga hali narx ham kerak.

Ikkinchi bog'lash: buyurtma → mahsulot

Endi xuddi shu so'rovga ikkinchi JOIN ni qo'shamiz. Bu safar buyurtmalar.mahsulot_id ni mahsulotlar.id bilan ulaymiz, shunda har bir buyurtma o'z narxiga ega bo'ladi:

SELECT m.ism, p.nomi, p.narx, b.soni
FROM mijozlar m
JOIN buyurtmalar b ON m.id = b.mijoz_id
JOIN mahsulotlar p ON b.mahsulot_id = p.id
ORDER BY b.id;

mahsulotlar ga p aliasini berdik. E'tibor bering — ikkinchi JOIN ni shunchaki birinchisidan keyin yozdik, har birining o'z ON sharti bor. Natija:

Ali|Telefon|5000000|1
Ali|Kitob|50000|3
Vali|Noutbuk|12000000|1
Soli|Ruchka|3000|10
Dilnoza|Quloqchin|300000|2
Ali|Daftar|8000|5
Jasur|Stul|250000|4

Mana endi bizda hamma narsa bor: ism (mijozlar dan), narx (mahsulotlar dan) va soni (buyurtmalar dan). Uchala jadval bitta natijaga birlashdi.

Eslatma: JOIN larni zanjir kabi ketma-ket yozasiz. Har bir yangi JOIN o'zidan oldingi natijaga yana bitta jadvalni ulaydi. Uchta jadval uchun ikkita JOIN kerak bo'ladi.

Summani hisoblaymiz va guruhlaymiz

Endi oxirgi qadam. Har bir buyurtma uchun summa p.narx * b.soni ga teng. Bizga esa har bir mijoz bo'yicha umumiy summa kerak — demak, qatorlarni mijoz bo'yicha guruhlab, har bir guruhda SUM(p.narx * b.soni) ni hisoblaymiz:

SELECT m.ism, SUM(p.narx * b.soni)
FROM mijozlar m
JOIN buyurtmalar b ON m.id = b.mijoz_id
JOIN mahsulotlar p ON b.mahsulot_id = p.id
GROUP BY m.id
ORDER BY SUM(p.narx * b.soni) DESC;

Bu yerda:

• SUM(p.narx * b.soni) — avval har bir buyurtmaning summasini (narx * soni) hisoblab, keyin ularni guruh ichida qo'shadi;
• GROUP BY m.id — qatorlarni mijozning id si bo'yicha guruhlaydi (id har bir mijoz uchun yagona, shuning uchun ism bo'yicha emas, aynan id bo'yicha guruhlash xavfsizroq);
• ORDER BY SUM(...) DESC — natijani jami summa bo'yicha kamayish (DESC) tartibida tartiblaydi, ya'ni eng ko'p xarid qilgan mijoz tepada bo'ladi.

Natija:

Vali|12000000
Ali|5190000
Jasur|1000000
Dilnoza|600000
Soli|30000

Mana — bitta so'rov har bir mijozning jami xaridini ko'rsatib berdi. Masalan, Ali uch marta xarid qilgan: 5000000*1 + 50000*3 + 8000*5 = 5000000 + 150000 + 40000 = 5190000. Vali esa bitta noutbuk olgan, lekin u eng qimmati bo'lgani uchun ro'yxatda birinchi turibdi.

Muhim: GROUP BY bir nechta qator qaytaradi, shuning uchun natija aniq va takrorlanadigan bo'lishi uchun oxiriga doimo ORDER BY qo'shing. Bu yerda eng ko'p xarid qilganlardan boshlash uchun ORDER BY SUM(p.narx * b.soni) DESC ishlatdik.

📝 Vazifa

Pastdagi kod oynasida mijozlar, mahsulotlar va buyurtmalar jadvallari allaqachon yaratilgan va to'ldirilgan. Sizning vazifangiz — har bir mijozning jami xarid summasini hisoblang:

• har bir buyurtma summasi narx * soni ga teng;
• uchala jadvalni bog'lang (mijozlar, buyurtmalar, mahsulotlar);
• mijozning ismini va jami summasini chiqaring;
• natija summa bo'yicha kamayish tartibida bo'lsin.