DISTINCT ON

Kirish: Ushbu qo'llanmada siz PostgreSQL SELECT DISTINCT ON bandi yordamida har bir guruhdan faqat bitta (birinchi) qatorni tanlab olishni o'rganasiz.

PostgreSQL DISTINCT ON bandiga kirish

PostgreSQLda SELECT DISTINCT ON ifodasi qatorlarni ON ichida ko'rsatilgan ustun(lar) bo'yicha guruhlaydi va har bir guruhdan ORDER BY tartibiga ko'ra birinchi qatorni saqlab qoladi.

DISTINCT ON sintaksisi quyidagicha:

SELECT DISTINCT ON (column1) column1, column2, column3
FROM table_name
ORDER BY column1, column2;

Ushbu sintaksisda:

1. ON (column1) qatorlarni column1 dagi qiymatlar bo'yicha alohida guruhlarga ajratadi.
2. ORDER BY bandida birinchi ustun sifatida albatta ON ichida ko'rsatilgan ustun (column1) bo'lishi shart. Undan keyingi ustunlar (column2) har bir guruh ichidagi qatorlarni tartiblash uchun xizmat qiladi.
3. PostgreSQL har bir guruhdagi saralangan qatorlarning eng birinchisini natijalar to'plamiga kiritadi va qolganlarini olib tashlaydi.

Eslatma: DISTINCT ON PostgreSQLning o'ziga xos imkoniyati (kengaytmasi) bo'lib, boshqa ba'zi ma'lumotlar bazalarida mavjud bo'lmasligi mumkin.

PostgreSQL DISTINCT ON bandiga misol

Misol uchun ombordagi mahsulotlar qoldig'ini saqlovchi inventories jadvalini yaratamiz:

CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(50) NOT NULL,
   brand VARCHAR(50) NOT NULL,
   warehouse VARCHAR(50) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventories (product_name, brand, warehouse, quantity) 
VALUES
('iPhone 16', 'Apple', 'San Jose', 50),
('iPhone 16', 'Apple', 'San Francisco', 30),
('iPhone 16 Pro', 'Apple', 'San Jose', 40),
('iPhone 16 Pro', 'Apple', 'San Francisco', 20),
('iPhone 16 Pro Max', 'Apple', 'San Francisco', 25),
('Galaxy S22', 'Samsung', 'San Jose', 50),
('Galaxy S22', 'Samsung', 'San Francisco', 30),
('Galaxy S22 Ultra', 'Samsung', 'San Francisco', 20),
('Galaxy Z Fold 4', 'Samsung', 'San Jose', 55),
('Galaxy Z Fold 4', 'Samsung', 'San Francisco', 35);

Har bir mahsulot bo'yicha eng yuqori qoldiqni topish

Quyidagi so'rov har bir mahsulotning nomi, brendi va omborlar orasidagi eng katta qoldig'ini (quantity DESC) olib beradi:

SELECT DISTINCT
  ON (product_name) product_name,
  brand,
  quantity
FROM
  inventories
ORDER BY
  product_name,
  quantity DESC;

Natija:

   product_name    |  brand  | quantity
-------------------+---------+----------
 Galaxy S22        | Samsung |       50
 Galaxy S22 Ultra  | Samsung |       20
 Galaxy Z Fold 4   | Samsung |       55
 iPhone 16         | Apple   |       50
 iPhone 16 Pro     | Apple   |       40
 iPhone 16 Pro Max | Apple   |       25

Bunda:

• ON (product_name) qatorlarni mahsulotlar nomi bo'yicha guruhlaydi.
• ORDER BY product_name, quantity DESC har bir mahsulot ichida qoldiqni kattasidan kichigiga qarab saralaydi.
• DISTINCT ON esa saralangan guruhning eng birinchi (ya'ni eng ko'p qoldiqli) qatorini ajratib oladi.

Har bir mahsulot bo'yicha eng past qoldiqni topish

Agar har bir tovar bo'yicha eng kam qoldiqni olmoqchi bo'lsangiz, ORDER BY da quantity ni o'sish tartibida (ASC) ko'rsatishingiz kifoya:

SELECT DISTINCT
  ON (product_name) product_name,
  brand,
  quantity
FROM
  inventories
ORDER BY
  product_name,
  quantity ASC;

Natija:

   product_name    |  brand  | quantity
-------------------+---------+----------
 Galaxy S22        | Samsung |       30
 Galaxy S22 Ultra  | Samsung |       20
 Galaxy Z Fold 4   | Samsung |       35
 iPhone 16         | Apple   |       30
 iPhone 16 Pro     | Apple   |       20
 iPhone 16 Pro Max | Apple   |       25

Xulosa

• Har bir guruhdan ma'lum bir tartibdagi birinchi qatorni tanlab olish uchun SELECT DISTINCT ON (expression) dan foydalaning.
• Qaysi qator saqlanib qolishini hal qilish uchun ORDER BY bandida mos ustunlar tartibini belgilang.
• ORDER BY bandining birinchi ustuni albatta ON (...) ichida ko'rsatilgan ifodaga mos bo'lishi shart.