32 / 100

GROUPING SETS

Guruhlash to'plamlari

Kirish: ushbu qo'llanmada siz bitta so'rov doirasida bir nechta guruhlashlarni yaratish uchun PostgreSQL GROUPING SETS dan qanday foydalanishni o'rganasiz.

PostgreSQL GROUPING SETS umumiy ma'lumoti

PostgreSQLda GROUPING SETS — bu bitta so'rov doirasida bir nechta guruhlashlarni yaratish imkonini beruvchi GROUP BY bandining kengaytirilgan imkoniyatidir.

GROUP BY bandi bitta guruhlash yaratish imkonini beradi. Bir nechta guruhlashlarni yaratish uchun siz GROUP BY bandlaridan foydalangan holda bir nechta so'rovlar yozishingiz mumkin.

Muqobil ravishda, GROUP BY bandlariga ega bir nechta so'rovlarni UNION operatorlari yordamida birlashtirishingiz mumkin.

GROUPING SETS bir nechta so'rovlardan yoki UNION operatoridan foydalanmasdan, aynan bir so'rovning o'zida bir nechta guruhlashlarni amalga oshirishga imkon beradi.

Quyida GROUPING SETS ning umumiy sintaksisi keltirilgan:

query.sql

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

Ushbu sintaksisda:

Birinchidan, GROUP BY bandi ichida GROUPING SETS dan foydalaning.
Ikkinchidan, GROUPING SETS ichida guruhlash to'plamlari ro'yxatini belgilang.

So'rov to'rtta guruhlash to'plamini hosil qiladi:

(column1, column2) qatorlarni column1 va column2 bo'yicha guruhlaydigan guruhlashni belgilaydi.
(column1) qatorlarni column1 bo'yicha guruhlaydigan guruhlashni belgilaydi.
(column2) qatorlarni column2 bo'yicha guruhlaydigan guruhlashni belgilaydi.
() jami qatorlar bo'yicha agregatsiyani (umumiy yakunni) ifodalovchi bo'sh to'plamni belgilaydi.

E'tibor bering, sizda ko'proq yoki kamroq guruhlash to'plamlari bo'lishi mumkin. Masalan, quyidagi so'rov uchta guruhlash to'plamini qaytaradi:

query.sql

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

PostgreSQL GROUPING SETS misoli

GROUPING SETS qanday ishlashini tasvirlash uchun quyidagi inventory_reports jadvalidan foydalanamiz.

Quyida inventory_reports jadvalini yaratish skripti keltirilgan:

query.sql

CREATE TABLE inventory_reports(
     warehouse VARCHAR NOT NULL,
     brand VARCHAR NOT NULL,
     quantity INT NOT NULL
);

INSERT INTO inventory_reports(warehouse, brand, quantity)
VALUES
('San Jose', 'Apple', 100),
('San Francisco', 'Apple', 200),
('Texas', 'Apple', 300),
('San Jose', 'Samsung', 50),
('San Francisco', 'Samsung', 100),
('Texas', 'Samsung', 150)
RETURNING *;

Natija:

kod

   warehouse   |  brand  | quantity
---------------+---------+----------
 San Jose      | Apple   |      100
 San Francisco | Apple   |      200
 Texas         | Apple   |      300
 San Jose      | Samsung |       50
 San Francisco | Samsung |      100
 Texas         | Samsung |      150

Quyidagi so'rov bir nechta guruhlashlarni qaytarish uchun GROUPING SETS dan foydalanadi:

query.sql

SELECT
  warehouse,
  brand,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse, brand), (warehouse), (brand), ())
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;

Natija:

kod

   warehouse   |  brand  | total
---------------+---------+-------
 San Francisco | Apple   |   200
 San Francisco | Samsung |   100
 San Francisco | NULL    |   300
 San Jose      | Apple   |   100
 San Jose      | Samsung |    50
 San Jose      | NULL    |   150
 Texas         | Apple   |   300
 Texas         | Samsung |   150
 Texas         | NULL    |   450
 NULL          | Apple   |   600
 NULL          | Samsung |   300
 NULL          | NULL    |   900

Ushbu misolda GROUPING SETS to'rtta guruhlashni hosil qiladi:

(warehouse, brand) guruhlash ombor va brend bo'yicha inventarni guruhlaydi:

kod

   warehouse   |  brand  | total
---------------+---------+-------
 San Francisco | Apple   |   200
 San Francisco | Samsung |   100
 San Jose      | Apple   |   100
 San Jose      | Samsung |    50
 Texas         | Apple   |   300
 Texas         | Samsung |   150

(warehouse) guruhlash inventarni faqat ombor bo'yicha guruhlaydi:

kod

   warehouse   |  brand  | total
---------------+---------+-------
 San Francisco | NULL    |   300
 San Jose      | NULL    |   150
 Texas         | NULL    |   450

(brand) guruhlash inventarni faqat brend bo'yicha guruhlaydi:

kod

   warehouse   |  brand  | total
---------------+---------+-------
 NULL          | Apple   |   600
 NULL          | Samsung |   300

() guruhlash barcha omborlar va barcha brendlarning umumiy inventarini ifodalaydi:

kod

   warehouse   |  brand  | total
---------------+---------+-------
 NULL          | NULL    |   900

Guruhlarni tanlash (Selecting Groupings)

Quyidagi misol ikkita guruhlash yaratish uchun GROUPING SETS dan foydalanadi:

query.sql

SELECT
  warehouse,
  brand,
  SUM(quantity)
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse, brand), ())
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;

Natija:

kod

   warehouse   |  brand  | sum
---------------+---------+-----
 San Francisco | Apple   | 200
 San Francisco | Samsung | 100
 San Jose      | Apple   | 100
 San Jose      | Samsung |  50
 Texas         | Apple   | 300
 Texas         | Samsung | 150
 NULL          | NULL    | 900

Ushbu misolda GROUPING SETS ikkita guruhlashni hosil qiladi:

(warehouse, brand) guruhlash inventarni ombor va brend bo'yicha guruhlaydi.
() guruhlash barcha omborlar va brendlarning umumiy yig'indisini ifodalaydi.

PostgreSQL GROUPING funksiyasi

GROUPING funksiyasi ustunni qabul qiladi va agar u agregatlangan bo'lsa 1, aks holda 0 qaytaradi.

query.sql

GROUPING(column_name)

Masalan:

query.sql

SELECT
  warehouse,
  brand,
  GROUPING(warehouse) warehouse_grouping,
  GROUPING(brand) brand_grouping,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse), (brand), ())
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;

Natija:

kod

   warehouse   |  brand  | warehouse_grouping | brand_grouping | total
---------------+---------+--------------------+----------------+-------
 San Francisco | NULL    |                  0 |              1 |   300
 San Jose      | NULL    |                  0 |              1 |   150
 Texas         | NULL    |                  0 |              1 |   450
 NULL          | Apple   |                  1 |              0 |   600
 NULL          | Samsung |                  1 |              0 |   300
 NULL          | NULL    |                  1 |              1 |   900

Natijada:

brand_grouping 1 bo'lgan qatorlar omborlar bo'yicha barcha brendlarning umumiy inventarini ko'rsatadi.
warehouse_grouping 1 bo'lgan qatorlar brendlar bo'yicha barcha omborlarning umumiy inventarini ko'rsatadi.
Ham warehouse_grouping, ham brand_grouping 1 bo'lgan qator barcha brendlar va omborlarning umumiy yig'indisini ko'rsatadi.

Ustun bo'yicha agregatlangan qatorlarni tanlab olish uchun GROUPING funksiyasidan HAVING bandi ichida foydalanishingiz mumkin.

Masalan, quyidagi so'rov brendlar bo'yicha agregatlangan qatorlarni qaytarish uchun GROUPING funksiyasidan foydalanadi:

query.sql

SELECT
  warehouse,
  brand,
  GROUPING(warehouse) warehouse_grouping,
  GROUPING(brand) brand_grouping,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse), (brand), ())
HAVING
  GROUPING(brand) = 1
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;

Natija:

kod

   warehouse   | brand | warehouse_grouping | brand_grouping | total
---------------+-------+--------------------+----------------+-------
 San Francisco | NULL  |                  0 |              1 |   300
 San Jose      | NULL  |                  0 |              1 |   150
 Texas         | NULL  |                  0 |              1 |   450
 NULL          | NULL  |                  1 |              1 |   900

Xulosa

Bitta so'rovda bir nechta guruhlashlarni yaratish uchun PostgreSQL GROUPING SETS dan foydalaning.
GROUPING funksiyasi agar ustun agregatlangan bo'lsa 1, aks holda 0 qaytaradi.

Oldingi

Keyingi