GROUP BY

Kirish: ushbu darsda siz bir yoki bir nechta ustunlar qiymatlari asosida qatorlarni guruhlarga ajratish uchun PostgreSQL GROUP BY bandidan qanday foydalanishni o'rganasiz.

PostgreSQL GROUP BY bandi bilan tanishuv

PostgreSQL GROUP BY bandi qatorlarni bir yoki bir nechta ustunlar qiymatlari asosida guruhlarga ajratish imkonini beradi.

Mana GROUP BY bandining sintaksisi:

SELECT column1, column2
FROM table_name
GROUP BY column1, column2;

Ushbu sintaksisda:

• Birinchidan, FROM bandida ma'lumotlarni tanlab olmoqchi bo'lgan jadval nomini ko'rsatasiz.
• Ikkinchidan, GROUP BY bandida qiymatlarini guruhlamoqchi bo'lgan bir yoki bir nechta ustunlarni ko'rsatasiz.
• Uchinchidan, guruhlangan ustunlarni SELECT bandida sanab o'tasiz.

Shuni ta'kidlash juda muhimki, SELECT bandidagi ustunlar albatta GROUP BY bandida ham bo'lishi kerak.

Agar SELECT bandida GROUP BY bandida ko'rsatilmagan ustunni ko'rsatsangiz, xatolikka duch kelasiz.

Oddiy PostgreSQL GROUP BY bandiga misol

Aytaylik, bizda quyidagi inventories (ombor qoldiqlari) jadvali mavjud:

Jadvallarni yaratish va namunaviy ma'lumotlarni kiritish uchun SQL skripti:

CREATE TABLE warehouses(
   warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   warehouse_name VARCHAR(255) NOT NULL UNIQUE 
);

CREATE TABLE brands(
   brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   brand_name VARCHAR(255) NOT NULL UNIQUE   
);

CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(255) NOT NULL,
   quantity INT NOT NULL,
   warehouse_id int not null,
   brand_id int not null,
   FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ON DELETE CASCADE,
   FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE
);

INSERT INTO warehouses (warehouse_name) 
VALUES ('San Jose'),
       ('San Francisco');

INSERT INTO brands (brand_name) 
VALUES ('Apple'),
       ('Samsung'),
       ('Google');

INSERT INTO inventories (product_name, quantity, warehouse_id, brand_id) 
VALUES 
('iPhone 15', 55, 1, 1),
('iPhone 14 Pro Max', 15, 2, 1),
('iPhone 13', 35, 2, 1),
('Galaxy S24', 50, 1, 2),
('Galaxy Note 23', 40, 2, 2),
('Galaxy Z Fold 6', 25, 2, 2),
('Galaxy Z Flip 6', 15, 2, 2),
('Pixel 9', 50, 1, 3),
('Pixel 8 Pro', 25, 2, 3),
('Pixel Fold', 60, 1, 3);

Quyidagi so'rov inventories jadvalidagi qatorlarni warehouse_id ustunidagi qiymatlar bo'yicha guruhlash uchun GROUP BY bandidan foydalanadi:

SELECT
  warehouse_id
FROM
  inventories
GROUP BY
  warehouse_id;

Natija:

 warehouse_id
--------------
            2
            1

Bu qanday ishlaydi:

• Birinchidan, FROM bandi inventories jadvalidagi barcha qatorlarni ko'rib chiqishni boshlaydi.
• Ikkinchidan, GROUP BY bandi warehouse_id ustunidagi noyob (takrorlanmas) qiymatlarni ajratib oladi. Har bir warehouse_id qiymati uchun GROUP BY qatorlarni guruhga birlashtiradi. Shunday qilib, bizda warehouse_id qiymati 1 va 2 bo'lgan ikkita guruh hosil bo'ladi:

• Nihoyat, SELECT bandi guruhlangan warehouse_id ustunini qaytaradi:

GROUP BY bandi agregat (jamlovchi) funksiyalar bilan birga qo'llanilganda yanada foydaliroq bo'ladi.

Agregat funksiyalar

Agregat funksiya qiymatlar guruhini qabul qiladi, hisob-kitobni amalga oshiradi va bitta natijaviy qiymat qaytaradi.

Eng ko'p qo'llaniladigan agregat funksiyalar quyidagilardir:

• MIN funksiyasi — to'plamdagi minimal (eng kichik) qiymatni qaytaradi.
• MAX funksiyasi — to'plamdagi maksimal (eng katta) qiymatni qaytaradi.
• SUM funksiyasi — to'plamning umumiy yig'indisini qaytaradi.
• AVG funksiyasi — to'plamning o'rtacha arifmetik qiymatini qaytaradi.
• COUNT funksiyasi — to'plamdagi elementlar (qatorlar) sonini qaytaradi.

MIN funksiyasi

Quyidagi so'rov inventories jadvalidagi eng kichik miqdorni (quantity) topish uchun MIN() funksiyasidan foydalanadi:

SELECT
  MIN(quantity)
FROM
  inventories;

Natija:

 min
-----
  15

So'rov qanday ishlaydi:

• Birinchidan, FROM bandi inventories jadvalidagi har bir qatorni tekshiradi.
• Ikkinchidan, MIN funksiyasi barcha qatorlar orasidagi eng kichik miqdorni qaytaradi.

MAX funksiyasi

Quyidagi misolda ombordagi mahsulotning eng yuqori miqdorini topish uchun MAX() funksiyasidan foydalaniladi:

SELECT
  MAX(quantity)
FROM
  inventories;

Natija:

 max
-----
  60

SUM funksiyasi

Quyidagi SELECT so'rovi inventories jadvalidagi barcha mahsulotlarning umumiy miqdorini hisoblash uchun SUM() funksiyasidan foydalanadi:

SELECT
  SUM(quantity)
FROM
  inventories;

Natija:

 sum
-----
 370

AVG funksiyasi

Quyidagi so'rov inventories jadvalidagi barcha mahsulotlarning o'rtacha miqdorini hisoblash uchun AVG() funksiyasidan foydalanadi:

SELECT
  AVG(quantity)
FROM
  inventories;

Natija:

         avg
---------------------
 37.0000000000000000

COUNT funksiyasi

Quyidagi so'rov inventories jadvalidagi qatorlar sonini qaytarish uchun COUNT() funksiyasidan foydalanadi:

SELECT
  COUNT(*)
FROM
  inventories;

Natija:

 count
-------
    10

PostgreSQL GROUP BY bandini agregat funksiyalar bilan birga ishlatish

GROUP BY bandini agregat funksiyalar bilan birgalikda ishlatish sintaksisi quyidagicha:

SELECT
  column1,
  aggregate_function (column2)
FROM
  table_name
GROUP BY
  column1;

Ushbu sintaksisda:

• Birinchidan, GROUP BY bandi jadvaldagi qatorlarni column1 asosida guruhlarga ajratadi.
• Ikkinchidan, agregat funksiya hisob-kitobni amalga oshiradi va har bir guruh uchun bitta qiymat qaytaradi.
• Uchinchidan, SELECT bandi har bir guruh uchun guruhlangan ustunni (column1) va agregatlangan qiymatni qaytaradi.

Masalan, quyidagi so'rov har bir ombor (warehouse_id) uchun mahsulotlarning umumiy miqdorini qanday hisoblashni ko'rsatadi:

SELECT
  warehouse_id,
  SUM(quantity)
FROM
  inventories
GROUP BY
  warehouse_id;

Natija:

 warehouse_id | sum
--------------+-----
            2 | 155
            1 | 215

Ushbu misolda:

• FROM bandi inventories jadvalidagi barcha qatorlarni ko'rib chiqadi.
• GROUP BY bandi inventories jadvalidagi barcha qatorlarni ikkita guruhga ajratadi:

• So'ngra SUM funksiyasi har bir guruh uchun miqdorlar yig'indisini hisoblaydi.
• SELECT bandi har bir guruh uchun warehouse_id va umumiy miqdorni qaytaradi.

PostgreSQL GROUP BY bandini INNER JOIN bilan birga ishlatish

Bir nechta jadvallardagi qatorlarni birlashtirish uchun JOIN dan foydalanishingiz va qatorlarni guruhlash uchun GROUP BY bandini ishlatishingiz mumkin.

Masalan, quyidagi so'rov har bir ombor uchun ombor nomini va mahsulotlarning umumiy miqdorini qaytaradi:

SELECT
  warehouse_name,
  SUM(quantity)
FROM
  inventories
  INNER JOIN warehouses USING (warehouse_id)
GROUP BY
  warehouse_name;

Natija:

 warehouse_name | sum
----------------+-----
 San Jose       | 215
 San Francisco  | 155

Bu qanday ishlaydi:

• Birinchidan, FROM bandi inventories jadvalidagi barcha qatorlarni tekshiradi.
• Keyin, INNER JOIN inventories jadvalidagi qatorlarni warehouse_id ustunidagi qiymatlar asosida warehouses jadvalidagi qatorlar bilan birlashtiradi:

• So'ngra, GROUP BY birlashtirilgan qatorlarni warehouse_name dagi qiymatlar bo'yicha guruhlaydi:

• Shundan so'ng, SUM funksiyasi har bir guruh uchun yig'indini hisoblaydi:

• Yakunda, SELECT bandi ombor nomlari va ularning har biridagi umumiy miqdorni qaytaradi:

Bir nechta ustunlar bo'yicha guruhlash (Group by multiple columns)

Quyidagi so'rov inventories jadvalidagi qatorlarni warehouse_id va brand_id ustunlari bo'yicha guruhlash uchun GROUP BY bandidan foydalanadi:

SELECT
  warehouse_id,
  brand_id,
  SUM(quantity)
FROM
  inventories
GROUP BY
  warehouse_id,
  brand_id;

Natija:

 warehouse_id | brand_id | sum
--------------+----------+-----
            1 |        1 |  55
            2 |        3 |  25
            1 |        3 | 110
            2 |        2 |  80
            1 |        2 |  50
            2 |        1 |  50

Ushbu misolda warehouse_id va brand_id ustunlaridagi qiymatlar kombinatsiyasi bitta guruhni hosil qiladi. SUM() funksiyasi esa har bir guruh uchun mahsulot miqdorining umumiy yig'indisini qaytaradi.

Xulosa

• SQL so'rovida qatorlarni bir yoki bir nechta ustunlar asosida jamlovchi guruhlarga ajratish uchun GROUP BY bandidan foydalaning.
• Har bir guruh uchun qiymatni hisoblashda GROUP BY bandi bilan birgalikda agregat funksiyalardan foydalaning.
• Bir nechta jadvallardan qatorlarni birlashtirish va ularni guruhlash uchun JOIN bilan GROUP BY ni birga ishlating.