CUBE

Kirish: ushbu qo'llanmada siz ko'rsatilgan ustunlarning barcha mumkin bo'lgan agregatsiyalarini yaratish uchun PostgreSQL CUBE dan qanday foydalanishni o'rganasiz.

PostgreSQL CUBE bilan tanishish

GROUP BY bandi qatorlarni guruhlash va bitta guruhlash to'plamining agregatsiyasini hisoblash imkonini beradi.

Bir qator ustunlarning barcha mumkin bo'lgan kombinatsiyalari bo'yicha agregatsiyalarni hisoblash uchun siz GROUP BY bandidan CUBE opsiyasi bilan foydalanishingiz mumkin.

Quyida CUBE bilan GROUP BY ning sintaksisi keltirilgan:

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

Ushbu sintaksisda CUBE quyidagi guruhlashlarni hosil qiladi:

• (column1, column2): column1 va column2 bo'yicha alohida guruhlar.
• (column1, NULL): column2 dagi qiymatlar uchun oraliq yig'indilar.
• (NULL, column2): column1 dagi qiymatlar uchun oraliq yig'indilar.
• (NULL, NULL): Barcha ustunlar uchun umumiy yig'indi guruhi.

PostgreSQL CUBE misollari

GROUP BY CUBE qanday ishlashini ko'rsatish uchun inventory_reports jadvalidan foydalanamiz:

Quyida inventory_reports jadvalini yaratish skripti keltirilgan:

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

Bitta ustun bilan PostgreSQL CUBE dan foydalanish

Quyidagi so'rov har bir brendning umumiy miqdorini hisoblash uchun GROUP BY bandidan foydalanadi:

SELECT
  brand,
  SUM(quantity) total_quantity
FROM
  inventory_reports
GROUP BY
  brand;

Natija:

  brand  | total_quantity
---------+----------------
 Samsung |            300
 Apple   |            600

Barcha brendlar uchun umumiy miqdorni hisoblaydigan qatorni kiritish uchun siz GROUP BY CUBE dan quyidagicha foydalanishingiz mumkin:

SELECT
  brand,
  SUM(quantity) total_quantity
FROM
  inventory_reports
GROUP BY
  CUBE (brand)
ORDER BY
  brand NULLS LAST;

Natija:

  brand  | total_quantity
---------+----------------
 Apple   |            600
 Samsung |            300 
 NULL    |            900 -> grand total

Ushbu misolda CUBE ikkita guruhlashni hosil qiladi:

• (brand): Brend bo'yicha miqdorlar yig'indisini hisoblaydi.
• (): Barcha brendlarning umumiy miqdorlar yig'indisini hisoblaydi.

Bir nechta ustunlar bilan CUBE dan foydalanish

Quyidagi so'rov oraliq jami (subtotals) va umumiy yakunni (grand total) hosil qilish uchun GROUP BY CUBE dan foydalanadi:

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

Natija:

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

Ushbu misolda CUBE to'rtta guruhlash to'plamini hosil qiladi:

• (brand, warehouse): Brend va ombor bo'yicha umumiy miqdorni hisoblaydi.
• (brand, NULL): Har bir brend uchun ushbu brendning barcha omborlardagi miqdorini jamlab, oraliq summani hisoblaydi. warehouse ustunida NULL.
• (NULL, warehouse): Har bir ombor uchun barcha brendlar bo'yicha oraliq summani hisoblaydi. brand ustunida NULL.
• (NULL, NULL): Barcha omborlar va brendlarni birgalikda jamlab, umumiy yakuniy summani (grand total) hisoblaydi.

GROUPING SETS, ROLLUP va CUBE o'rtasidagi farqlar

Quyidagi jadvalda GROUPING SETS, ROLLUP va CUBE o'rtasidagi farqlar tushuntirilgan:

Xulosa

• Ko'rsatilgan ustunlarning barcha mumkin bo'lgan agregatsiyalarini hisoblash orqali ko'p o'lchovli xulosalar yaratish uchun PostgreSQL CUBE dan foydalaning.