Agregatlar va guruhlash - PostgreSQL

Agregat funksiyalar - count, sum, avg - tanish. Bu bo'limda PostgreSQL ularga qo'shgan imkoniyatlarni ko'ramiz.

SQL

CREATE TABLE sotuv (
    id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filial  text NOT NULL,
    janr    text NOT NULL,
    oy      int NOT NULL,
    summa   numeric(10,2) NOT NULL,
    soni    int NOT NULL
);

INSERT INTO sotuv (filial, janr, oy, summa, soni) VALUES
    ('Toshkent', 'roman',  1, 450000, 12),
    ('Toshkent', 'roman',  2, 520000, 15),
    ('Toshkent', 'she''r', 1, 180000,  9),
    ('Toshkent', 'she''r', 2, 210000, 11),
    ('Namangan', 'roman',  1, 310000,  8),
    ('Namangan', 'roman',  2, 275000,  7),
    ('Namangan', 'she''r', 1,  95000,  5),
    ('Samarqand','roman',  1, 380000, 10),
    ('Samarqand','she''r', 2, 145000,  6);

FILTER - shartli agregat #

Klassik vazifa: bitta so'rovda "jami" va "faqat birinchi oy" ni birga hisoblash.

Ko'p bazalarda buning uchun CASE ishlatiladi. PostgreSQL da esa FILTER bor:

SQL

SELECT filial,
       count(*) AS jami_yozuv,
       count(*) FILTER (WHERE oy = 1) AS yanvar,
       sum(summa) AS jami_summa,
       sum(summa) FILTER (WHERE janr = 'roman') AS roman_summa
FROM sotuv
GROUP BY filial
ORDER BY filial;

Natija

  filial   | jami_yozuv | yanvar | jami_summa | roman_summa
-----------+------------+--------+------------+-------------
 Namangan  |          3 |      2 |  680000.00 |   585000.00
 Samarqand |          2 |      1 |  525000.00 |   380000.00
 Toshkent  |          4 |      2 | 1360000.00 |   970000.00
(3 rows)

FILTER va CASE - bir xil natija, boshqa o'qilish

Xuddi shu natijani CASE bilan ham olish mumkin:

SQL

count(CASE WHEN oy = 1 THEN 1 END)

Ikkalasi ham ishlaydi, lekin FILTER ikki jihatdan yaxshiroq:

	`CASE` bilan	`FILTER` bilan
O'qilishi	Shart ichkarida yashirin	Shart ochiq ko'rinadi
`sum` da	`ELSE 0` yozish kerak bo'ladi	Kerak emas
Standart	Hamma bazada bor	SQL standartida bor, lekin hamma bazada emas

sum bilan farq ayniqsa seziladi: CASE da ELSE ni unutsangiz, NULL lar qo'shilmaydi va natija to'g'ri chiqadi - lekin count da xuddi shu xato boshqacha ishlaydi.

FILTER da bunday nozikliklar umuman yo'q.

HAVING va WHERE #

Ikkalasi ham filtrlaydi, lekin turli bosqichda:

HAVING ga qator shartini yozish ishlaydi, lekin sekinroq - u kech filtrlaydi

SQL

SELECT filial, sum(summa) AS jami
FROM sotuv
WHERE oy = 1
GROUP BY filial
HAVING sum(summa) > 200000
ORDER BY jami DESC;

Natija

  filial   |   jami
-----------+-----------
 Toshkent  | 630000.00
 Namangan  | 405000.00
 Samarqand | 380000.00
(3 rows)

WHERE oy = 1 qatorlarni, HAVING sum(...) > 200000 esa guruhlarni filtrladi.

GROUPING SETS - bir necha kesimda birdan #

Hisobotlarda odatda bir nechta kesim kerak bo'ladi: filial bo'yicha, janr bo'yicha va umumiy jami.

Uch marta so'rov yozish o'rniga:

SQL

SELECT filial, janr, sum(summa) AS jami
FROM sotuv
GROUP BY GROUPING SETS ((filial), (janr), ())
ORDER BY filial NULLS LAST, janr NULLS LAST;

Natija

  filial   | janr  |    jami
-----------+-------+------------
 Namangan  |       |  680000.00
 Samarqand |       |  525000.00
 Toshkent  |       | 1360000.00
           | roman | 1935000.00
           | she'r |  630000.00
           |       | 2565000.00
(6 rows)

NULL ko'ringan joy - "bu kesimda bu ustun ishtirok etmaydi" degani. Oxirgi qator esa () - umumiy jami.

ROLLUP bu yozuvni qisqartiradi - u ierarxik jami beradi:

SQL

SELECT filial, janr, sum(summa) AS jami
FROM sotuv
WHERE filial = 'Toshkent'
GROUP BY ROLLUP (filial, janr)
ORDER BY filial NULLS LAST, janr NULLS LAST;

Natija

  filial  | janr  |    jami
----------+-------+------------
 Toshkent | roman |  970000.00
 Toshkent | she'r |  390000.00
 Toshkent |       | 1360000.00
          |       | 1360000.00
(4 rows)

GROUPING() bilan haqiqiy NULL ni ajratish

Yuqoridagi natijada NULL ikki xil ma'no berishi mumkin: "bu kesimda ustun yo'q" yoki "ma'lumotning o'zi NULL".

GROUPING() funksiyasi ularni ajratadi: u 1 qaytarsa, NULL ni guruhlash hosil qilgan; 0 bo'lsa - bu haqiqiy ma'lumot.

SQL

SELECT filial, GROUPING(filial) AS jami_qatormi, sum(summa)
FROM sotuv GROUP BY ROLLUP (filial);

Hisobotni chizayotganda shu ustunga qarab "Jami" so'zini qo'yish mumkin.

Qatorlarni bitta qiymatga yig'ish #

string_agg guruhdagi matnlarni birlashtiradi:

SQL

SELECT filial, string_agg(DISTINCT janr, ', ' ORDER BY janr) AS janrlar
FROM sotuv
GROUP BY filial
ORDER BY filial;

Natija

  filial   |   janrlar
-----------+--------------
 Namangan  | roman, she'r
 Samarqand | roman, she'r
 Toshkent  | roman, she'r
(3 rows)

array_agg esa massiv qaytaradi (11-bo'limda batafsil):

SQL

SELECT janr, array_agg(DISTINCT filial ORDER BY filial) AS filiallar
FROM sotuv
GROUP BY janr
ORDER BY janr;

Natija

 janr  |           filiallar
-------+-------------------------------
 roman | {Namangan,Samarqand,Toshkent}
 she'r | {Namangan,Samarqand,Toshkent}
(2 rows)

O'rtacha va yaxlitlash #

SQL

SELECT filial,
       round(avg(summa), 2) AS ortacha,
       min(summa) AS eng_kam,
       max(summa) AS eng_kop,
       sum(soni) AS jami_dona
FROM sotuv
GROUP BY filial
ORDER BY ortacha DESC;

Natija

  filial   |  ortacha  |  eng_kam  |  eng_kop  | jami_dona
-----------+-----------+-----------+-----------+-----------
 Toshkent  | 340000.00 | 180000.00 | 520000.00 |        47
 Samarqand | 262500.00 | 145000.00 | 380000.00 |        16
 Namangan  | 226666.67 |  95000.00 | 310000.00 |        20
(3 rows)

avg NULL larni e'tiborsiz qoldiradi

avg(summa) hisoblaganda NULL qiymatlar umuman hisobga olinmaydi - ular nol deb qaralmaydi.

Ya'ni uch qatordan biri NULL bo'lsa, o'rtacha ikkitadan olinadi.

Ko'pincha bu to'g'ri xatti-harakat. Lekin "javob bermaganlar nol deb hisoblansin" kerak bo'lsa, uni ochiq yozing:

SQL

avg(COALESCE(summa, 0))

Farqni bilmasdan yozilgan hisobot jimgina noto'g'ri son beradi - va buni topish qiyin.

Amaliy topshiriq

FILTER bilan har filialda ikkinchi oy summasini hisoblang.
Xuddi shuni CASE bilan yozing va natijalarni solishtiring.
WHERE ga sum(summa) > 100 yozib ko'ring - qanday xato chiqdi?
Nima uchun bunday xato chiqishini tushuntiring.
HAVING bilan faqat 500 000 dan ko'p sotgan filiallarni chiqaring.
GROUPING SETS ga (filial, janr) kesimini ham qo'shing.
ROLLUP va GROUPING SETS farqini yozing.
string_agg bilan har janrdagi filiallarni bitta qatorga yig'ing.
ORDER BY ni string_agg ichidan olib tashlang - natija barqarormi?
avg NULL larni qanday hisoblashini o'z so'zingiz bilan yozing.

Xulosa #

FILTER agregatga shart qo'shadi va CASE dan ancha o'qiluvchan.
WHERE qatorni, HAVING esa guruhni filtrlaydi.
WHERE guruhlashdan oldin ishlagani uchun tezroq - shartni imkon qadar unga yozing.
WHERE da agregat ishlatib bo'lmaydi: u bosqichda guruh hali yo'q.
GROUPING SETS bir necha kesimni bitta so'rovda beradi.
ROLLUP ierarxik jami uchun qisqa yozuv.
Natijadagi NULL "bu kesimda ustun yo'q" degani; GROUPING() uni haqiqiy NULL dan ajratadi.
string_agg va array_agg guruhni bitta qiymatga yig'adi.
Ular ichida ORDER BY yozish mumkin - natija barqaror bo'lishi uchun shart.
avg NULL larni hisobga olmaydi; nol deb sanash kerak bo'lsa COALESCE yozing.

Keyingi bo'limda guruhlamasdan hisoblash imkonini beradigan oyna funksiyalari bilan tanishamiz.

FILTER - shartli agregat #

HAVING va WHERE #

GROUPING SETS - bir necha kesimda birdan #

Qatorlarni bitta qiymatga yig'ish #

O'rtacha va yaxlitlash #

Xulosa #

Xatolik topdingizmi?