HAVING

Kirish: ushbu qo'llanmada siz GROUP BY bandi qaytargan guruhlarni filtrlash uchun PostgreSQL HAVING bandidan qanday foydalanishni o'rganasiz.

PostgreSQL HAVING bandi bilan tanishish

HAVING bandi kuchli vosita bo'lib, u GROUP BY bandi qaytaradigan guruhlarni filtrlash uchun shart belgilash imkonini beradi.

Quyida HAVING bandining sintaksisi keltirilgan:

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

Ushbu sintaksisda:

• Birinchidan, FROM bandida qatorlarni qaysi jadvaldan olish kerakligini ko'rsating.
• Ikkinchidan, GROUP BY bandi orqali guruhlash uchun bir yoki bir nechta ustunlarni aniqlang.
• Uchinchidan, GROUP BY qaytargan guruhlarni filtrlash uchun HAVING bandida shart (condition) bering. Agar shart rost (true) bo'lsa, PostgreSQL ushbu guruhni natijalar to'plamiga kiritadi.
• Va nihoyat, yakuniy natijalar to'plamiga kiritmoqchi bo'lgan ustunlarni (yoki ifodalarni) SELECT qismida ko'rsating.

PostgreSQL so'rov bandlarini quyidagi tartibda bajaradi:

• FROM
• GROUP BY
• HAVING
• SELECT

PostgreSQL HAVING bandini SELECT bandidan oldin hisoblaganligi sababli, HAVING ichida ustun taxalluslaridan (column aliases) foydalanib bo'lmaydi.

Alohida qatorlar asosida ma'lumotlarni filtrlaydigan WHERE bandidan farqli o'laroq, HAVING bandi GROUP BY natijalari asosida guruhlarni filtrlaydi.

Bu shuni anglatadiki, PostgreSQL HAVING bandini GROUP BY qatorlarni guruhlab bo'lgandan keyin qo'llaydi, bu esa guruhlarni shart asosida filtrlash imkonini beradi.

Namunaviy jadvallarni sozlash

Ko'rgazmali misollar uchun quyidagi inventories jadvalidan foydalanamiz:

DROP TABLE IF EXISTS inventories CASCADE;
DROP TABLE IF EXISTS warehouses CASCADE;
DROP TABLE IF EXISTS brands CASCADE;

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);

PostgreSQL HAVING bandiga misol

Quyidagi so'rov har bir ombor bo'yicha mahsulot miqdorini hisoblash uchun GROUP BY bandidan foydalanadi:

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

U ombor identifikatori (warehouse_id) va miqdorini o'z ichiga olgan ikkita qatorni qaytaradi.

Quyidagi so'rov faqat mahsulot miqdori 200 dan katta bo'lgan omborlarni olish uchun HAVING bandidan foydalanadi:

SELECT
  warehouse_id,
  SUM(quantity) qty
FROM
  inventories
GROUP BY
  warehouse_id
HAVING
  SUM(quantity) > 200;

Natija:

 warehouse_id | qty 
--------------+-----
            1 | 215

PostgreSQL HAVING bandini SELECT bandidan oldin hisoblaydi; siz HAVING ichida qty ustun taxallusiga murojaat qila olmaysiz.

Qanday ishlashi:

Birinchidan, GROUP BY bandi inventories jadvalidagi qatorlarni ikkita guruhga jamlaydi:

Ikkinchidan, HAVING bandi umumiy miqdor asosida guruhlarni filtrlaydi va faqat shartni qanoatlantirgan guruhni o'z ichiga oladi:

Uchinchidan, SELECT so'rovi ustun taxallusini bergan holda warehouse_id va sum ustunlarini oladi:

Murakkab shartlar bilan PostgreSQL HAVING bandi

Quyidagi misol ombor va brendning har bir kombinatsiyasi uchun umumiy mahsulot miqdorini qaytarish, shuningdek, faqat 1 va 2-brendlarni hamda faqat ushbu brendlar uchun miqdor 50 dan oshgan holatlarni kiritish uchun HAVING bandidan foydalanadi:

SELECT
  warehouse_id,
  brand_id,
  SUM(quantity)
FROM
  inventories
GROUP BY
  warehouse_id,
  brand_id
HAVING
  brand_id IN (1, 2)
  AND SUM(quantity) > 50;

Natija:

 warehouse_id | brand_id | sum 
--------------+----------+-----
            1 |        1 |  55
            2 |        2 |  80

Qanday ishlashi:

Birinchidan, GROUP BY ombor va brend bo'yicha mahsulotlarning umumiy miqdorini qaytaradi:

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

Ikkinchidan, HAVING bandi faqat brand_id 1 va 2 bo'lgan hamda mahsulot miqdori 50 dan ortiq bo'lgan guruhlarni tanlab oladi:

 warehouse_id | brand_id | sum 
--------------+----------+-----
            1 |        1 |  55
            2 |        2 |  80

Xulosa

• GROUP BY qaytargan guruhlarni agregat funksiyalar va shartlar bo'yicha filtrlash uchun PostgreSQL HAVING bandidan foydalaniladi.
• WHERE individual qatorlarni filtrlasa, HAVING esa guruhlangan natijalarni filtrlaydi.