33 / 100

ROLLUP

Ierarxik guruhlash

Kirish: Ushbu qo'llanmada siz bir nechta agregatsiya darajalarini yaratish uchun PostgreSQL ROLLUP dan qanday foydalanishni o'rganasiz.

PostgreSQL ROLLUP umumiy ma'lumoti

PostgreSQLda GROUP BY bandi qatorlarni bir nechta guruhlarga guruhlaydi va siz har bir guruhga agregat funksiyasini qo'llashingiz mumkin. Biroq, GROUP BY bandi faqat bitta darajadagi agregatsiyani hosil qila oladi.

Bir nechta darajadagi agregatsiyani hosil qilish uchun siz GROUP BY bandining ROLLUP opsiyasidan foydalanasiz.

Quyida ROLLUP opsiyasi bilan GROUP BY sintaksisi keltirilgan:

query.sql

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

Ushbu sintaksisda ROLLUP(column1, column2) uchta guruhlashni hosil qiladi:

(column1, column2): Ham column1, ham column2 qiymatlari bo'yicha agregat ma'lumotlarni o'z ichiga olgan odatiy guruhlashlar.
(column1, NULL): Har bir column1 bo'yicha oraliq jami (subtotal).
(NULL, NULL): Umumiy yakuniy jami (grand total).

ROLLUP column1 va column2 o'rtasida ierarxik munosabat mavjud deb hisoblaydi:

query.sql

column1 > column2

ROLLUP ierarxik tarzda oraliq jami (subtotal) va umumiy jami (grand total) qatorlarini yaratishga yordam beradi.

PostgreSQL ROLLUP misollari

ROLLUP qanday ishlashini namoyish qilish uchun 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 *;

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

Bitta ustun bilan ROLLUPdan foydalanish

Quyidagi so'rov har bir ombor uchun umumiy miqdorni hisoblash uchun GROUP BY bandidan foydalanadi:

query.sql

SELECT
  warehouse,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  warehouse
ORDER BY
  warehouse;

Natija:

kod

   warehouse   | total_quantity
---------------+----------------
 San Francisco |            300
 San Jose      |            150
 Texas         |            450

Umumiy yakuniy jami (grand total) qatorini kiritish uchun ROLLUP dan quyidagicha foydalanishingiz mumkin:

query.sql

SELECT
  warehouse,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  ROLLUP(warehouse)
ORDER BY
  warehouse;

Natija:

kod

   warehouse   | total_quantity
---------------+----------------
 San Francisco |            300
 San Jose      |            150
 Texas         |            450
 NULL          |            900   -> grand total

Ushbu misolda ROLLUP(warehouse) barcha omborlar bo'yicha umumiy miqdorni o'z ichiga olgan yana bitta qator qo'shadi.

Bir nechta ustunlar bilan ROLLUPdan foydalanish

Quyidagi so'rov ombor va brend bo'yicha umumiy miqdorni hosil qilish uchun GROUP BY bandidan foydalanadi:

query.sql

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

Natija:

kod

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

Chiqishda ombor bo'yicha oraliq jami va barcha omborlar bo'yicha umumiy miqdor ko'rsatilmaydi.

Oraliq jami (subtotal) va umumiy jami (grand total) ni hosil qilish uchun ROLLUP opsiyasidan quyidagicha foydalanasiz:

query.sql

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

Natija:

kod

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

Ushbu misolda ROLLUP(warehouse, brand):

ROLLUP opsiyasidan foydalanmagan avvalgi misoldagi kabi ombor va brend bo'yicha umumiy miqdorni hosil qiladi.
Har bir ombor uchun oraliq jami (subtotal) qatorini qo'shadi (brand ustunida NULL).
Umumiy jami (grand total) qatorini qo'shadi (ham warehouse, ham brand ustunlarida NULL).

Agar siz ROLLUP ichidagi warehouse va brand ustunlarining tartibini o'zgartirsangiz, natijalar to'plami boshqacha bo'ladi. Masalan:

query.sql

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

Endi ROLLUP quyidagi ierarxiyani nazarda tutadi:

query.sql

brand > warehouse

Natija:

kod

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

Xulosa

Bir nechta agregatsiya darajalarini yaratish uchun ROLLUP dan foydalaning.

Oldingi

Keyingi