</> Softromeda Edu

9-bo‘lim

Oyna funksiyalari

OVER nima qiladi, ROW_NUMBER va RANK farqi, PARTITION BY, LAG va LEAD, yig'ilib boruvchi jami va oyna ramkasi.

🕑 11 daqiqa o‘qish 📄 661 so‘z 👁 0 marta ko‘rilgan
Ushbu bo‘lim mundarijasi
  1. GROUP BY va OVER farqi
  2. Tartib raqami va reyting
  3. LAG va LEAD - qo'shni qatorga qarash
  4. Yig'ilib boruvchi jami
  5. O'rtacha bilan solishtirish
  6. Xulosa

Agregat funksiya qatorlarni birlashtiradi: o'nta qatordan bitta natija chiqadi. Ba'zan esa hisob kerak, lekin qatorlar ham qolishi kerak.

Aynan shu vazifani oyna funksiyalari bajaradi.

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

INSERT INTO sotuv (filial, oy, summa) VALUES
    ('Toshkent', 1, 450000), ('Toshkent', 2, 520000), ('Toshkent', 3, 380000),
    ('Namangan', 1, 310000), ('Namangan', 2, 275000), ('Namangan', 3, 410000),
    ('Samarqand',1, 380000), ('Samarqand',2, 380000), ('Samarqand',3, 295000);

GROUP BY va OVER farqi #

Bir xil hisob, boshqa natija shakli GROUP BY 9 ta qator 3 ta qator qatorlar YO'QOLADI tafsilotni ko'rib bo'lmaydi OVER (oyna) 9 ta qator 9 ta qator + yangi ustun qatorlar QOLADI tafsilot ham, jami ham ko'rinadi Qachon oyna kerak "Har sotuvni ko'rsat VA yonida filialning jamisini yoz" - bu oyna "Faqat filiallar bo'yicha jami" - bu GROUP BY
Oyna funksiyasi qatorni yo'qotmaydi - u faqat ustun qo'shadi

Farqni yonma-yon ko'ramiz:

SQL
SELECT filial, oy, summa,
       sum(summa) OVER (PARTITION BY filial) AS filial_jami,
       sum(summa) OVER () AS umumiy_jami
FROM sotuv
ORDER BY filial, oy;
Natija
  filial   | oy |   summa   | filial_jami | umumiy_jami
-----------+----+-----------+-------------+-------------
 Namangan  |  1 | 310000.00 |   995000.00 |  3400000.00
 Namangan  |  2 | 275000.00 |   995000.00 |  3400000.00
 Namangan  |  3 | 410000.00 |   995000.00 |  3400000.00
 Samarqand |  1 | 380000.00 |  1055000.00 |  3400000.00
 Samarqand |  2 | 380000.00 |  1055000.00 |  3400000.00
 Samarqand |  3 | 295000.00 |  1055000.00 |  3400000.00
 Toshkent  |  1 | 450000.00 |  1350000.00 |  3400000.00
 Toshkent  |  2 | 520000.00 |  1350000.00 |  3400000.00
 Toshkent  |  3 | 380000.00 |  1350000.00 |  3400000.00
(9 rows)

Har qator o'z joyida qoldi, lekin yoniga ikkita yangi ustun qo'shildi.

YozuvMa'nosi
OVER ()Butun natija - bitta katta oyna
OVER (PARTITION BY filial)Har filial - alohida oyna

PARTITION BY - bu GROUP BY ga o'xshaydi, lekin qatorlarni yo'qotmaydi.

Tartib raqami va reyting #

Uchta o'xshash funksiya bor va ularning farqi teng qiymatlarda ko'rinadi:

SQL
SELECT filial, summa,
       row_number() OVER (ORDER BY summa DESC) AS raqam,
       rank()       OVER (ORDER BY summa DESC) AS oddiy_reyting,
       dense_rank() OVER (ORDER BY summa DESC) AS zich_reyting
FROM sotuv
ORDER BY summa DESC;
Natija
  filial   |   summa   | raqam | oddiy_reyting | zich_reyting
-----------+-----------+-------+---------------+--------------
 Toshkent  | 520000.00 |     1 |             1 |            1
 Toshkent  | 450000.00 |     2 |             2 |            2
 Namangan  | 410000.00 |     3 |             3 |            3
 Samarqand | 380000.00 |     4 |             4 |            4
 Toshkent  | 380000.00 |     5 |             4 |            4
 Samarqand | 380000.00 |     6 |             4 |            4
 Namangan  | 310000.00 |     7 |             7 |            5
 Samarqand | 295000.00 |     8 |             8 |            6
 Namangan  | 275000.00 |     9 |             9 |            7
(9 rows)
Uchta funksiya, uchta xatti-harakat

Natijada ikkita 380000.00 bor - ular teng. Mana shu yerda farq ko'rinadi:

FunksiyaTeng qiymatlardaKeyingi raqam
row_number()Har xil raqam beradiketma-ket
rank()Bir xil raqam beradisakraydi
dense_rank()Bir xil raqam beradisakramaydi

Sport misolida: ikki chempion birinchi o'rinni bo'lishsa, keyingisi rank() da uchinchi, dense_rank() da esa ikkinchi bo'ladi.

row_number() teng qiymatlarda qaysi qatorga qaysi raqam tushishini kafolatlamaydi - barqaror natija kerak bo'lsa, ORDER BY ga qo'shimcha ustun qo'shing.

PARTITION BY bilan birga ishlatilsa, har guruhda alohida reyting chiqadi:

SQL
SELECT filial, oy, summa,
       row_number() OVER (PARTITION BY filial ORDER BY summa DESC) AS orin
FROM sotuv
ORDER BY filial, orin;
Natija
  filial   | oy |   summa   | orin
-----------+----+-----------+------
 Namangan  |  3 | 410000.00 |    1
 Namangan  |  1 | 310000.00 |    2
 Namangan  |  2 | 275000.00 |    3
 Samarqand |  2 | 380000.00 |    1
 Samarqand |  1 | 380000.00 |    2
 Samarqand |  3 | 295000.00 |    3
 Toshkent  |  2 | 520000.00 |    1
 Toshkent  |  1 | 450000.00 |    2
 Toshkent  |  3 | 380000.00 |    3
(9 rows)

Bu 7-bo'limdagi LATERAL vazifasining ikkinchi yechimi. Har filialning eng yaxshi oyini olish uchun endi orin = 1 deb filtrlash kifoya.

Oyna funksiyasini WHERE da ishlatib bo'lmaydi

Bu mantiqiy tuzoq:

SQL
WHERE row_number() OVER (...) = 1

Bunday yozuv xato beradi. Sabab - bajarilish tartibi: WHERE oyna funksiyalaridan oldin ishlaydi, ya'ni o'sha paytda tartib raqami hali hisoblanmagan.

Yechim - so'rovni ichki qilib o'rash:

SQL
SELECT * FROM (
    SELECT filial, summa,
           row_number() OVER (PARTITION BY filial
                              ORDER BY summa DESC) AS orin
    FROM sotuv
) t WHERE orin = 1;

10-bo'limdagi WITH yozuvi bu naqshni ancha o'qiluvchan qiladi.

LAG va LEAD - qo'shni qatorga qarash #

Oldingi yoki keyingi qator bilan solishtirish - hisobotlarda eng ko'p uchraydigan vazifa:

SQL
SELECT filial, oy, summa,
       lag(summa) OVER (PARTITION BY filial ORDER BY oy) AS oldingi_oy,
       summa - lag(summa) OVER (PARTITION BY filial ORDER BY oy) AS ozgarish
FROM sotuv
ORDER BY filial, oy;
Natija
  filial   | oy |   summa   | oldingi_oy |  ozgarish
-----------+----+-----------+------------+------------
 Namangan  |  1 | 310000.00 |            |
 Namangan  |  2 | 275000.00 |  310000.00 |  -35000.00
 Namangan  |  3 | 410000.00 |  275000.00 |  135000.00
 Samarqand |  1 | 380000.00 |            |
 Samarqand |  2 | 380000.00 |  380000.00 |       0.00
 Samarqand |  3 | 295000.00 |  380000.00 |  -85000.00
 Toshkent  |  1 | 450000.00 |            |
 Toshkent  |  2 | 520000.00 |  450000.00 |   70000.00
 Toshkent  |  3 | 380000.00 |  520000.00 | -140000.00
(9 rows)

Birinchi oyda oldingi_oy bo'sh - undan oldin qator yo'q. Buni lag(summa, 1, 0) bilan nolga almashtirish mumkin.

lead() esa teskarisi - keyingi qatorga qaraydi.

Yig'ilib boruvchi jami #

Oyna ramkasi har qator uchun qaysi qatorlar hisobga olinishini belgilaydi:

SQL
SELECT filial, oy, summa,
       sum(summa) OVER (
           PARTITION BY filial ORDER BY oy
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS yigilgan
FROM sotuv
ORDER BY filial, oy;
Natija
  filial   | oy |   summa   |  yigilgan
-----------+----+-----------+------------
 Namangan  |  1 | 310000.00 |  310000.00
 Namangan  |  2 | 275000.00 |  585000.00
 Namangan  |  3 | 410000.00 |  995000.00
 Samarqand |  1 | 380000.00 |  380000.00
 Samarqand |  2 | 380000.00 |  760000.00
 Samarqand |  3 | 295000.00 | 1055000.00
 Toshkent  |  1 | 450000.00 |  450000.00
 Toshkent  |  2 | 520000.00 |  970000.00
 Toshkent  |  3 | 380000.00 | 1350000.00
(9 rows)

UNBOUNDED PRECEDING AND CURRENT ROW - "boshidan shu qatorgacha". Shuning uchun har qatorda jami o'sib boradi.

ORDER BY oyna ichida ramkani o'zgartiradi

Bu nozik, lekin muhim.

YozuvStandart ramka
OVER (PARTITION BY x)Butun bo'lim
OVER (PARTITION BY x ORDER BY y)Boshidan joriy qatorgacha

Ya'ni oyna ichiga ORDER BY qo'shishning o'zi sum() ni "jami" dan "yig'ilib boruvchi jami" ga aylantiradi.

Ko'p odam buni bilmasdan ORDER BY qo'shib, natija o'zgarganiga hayron bo'ladi. Ramkani aniq yozsangiz, hech qanday sir qolmaydi.

O'rtacha bilan solishtirish #

Oyna funksiyalari har qatorni guruh o'rtachasi bilan solishtirishni osonlashtiradi:

SQL
SELECT filial, oy, summa,
       round(avg(summa) OVER (PARTITION BY filial), 2) AS filial_ortacha,
       round(summa - avg(summa) OVER (PARTITION BY filial), 2) AS farq
FROM sotuv
ORDER BY filial, oy;
Natija
  filial   | oy |   summa   | filial_ortacha |   farq
-----------+----+-----------+----------------+-----------
 Namangan  |  1 | 310000.00 |      331666.67 | -21666.67
 Namangan  |  2 | 275000.00 |      331666.67 | -56666.67
 Namangan  |  3 | 410000.00 |      331666.67 |  78333.33
 Samarqand |  1 | 380000.00 |      351666.67 |  28333.33
 Samarqand |  2 | 380000.00 |      351666.67 |  28333.33
 Samarqand |  3 | 295000.00 |      351666.67 | -56666.67
 Toshkent  |  1 | 450000.00 |      450000.00 |      0.00
 Toshkent  |  2 | 520000.00 |      450000.00 |  70000.00
 Toshkent  |  3 | 380000.00 |      450000.00 | -70000.00
(9 rows)

GROUP BY bilan buni qilish uchun jadvalni o'zi bilan birlashtirish kerak bo'lardi.

Amaliy topshiriq
  1. OVER () bilan umumiy jamini har qatorga qo'shing.
  2. PARTITION BY qo'shing va farqni kuzating.
  3. row_number(), rank() va dense_rank() natijalarini solishtiring.
  4. Teng qiymatlarda ularning farqini yozing.
  5. Har filialning eng yuqori oyini orin = 1 bilan toping.
  6. WHERE ga oyna funksiyasini yozib ko'ring - qanday xato chiqdi?
  7. lag bilan oldingi oyga nisbatan o'zgarishni hisoblang.
  8. lag(summa, 1, 0) yozing - birinchi qator qanday o'zgardi?
  9. Oyna ichiga ORDER BY qo'shing va sum natijasi qanday o'zgarishini ko'ring.
  10. Har qatorni filial o'rtachasi bilan solishtiring.

Xulosa #

  • Oyna funksiyasi hisoblaydi, lekin qatorlarni yo'qotmaydi.
  • OVER () - butun natija, OVER (PARTITION BY x) - har guruh alohida.
  • row_number() teng qiymatlarga har xil, rank() va dense_rank() bir xil raqam beradi.
  • rank() dan keyingi raqam sakraydi, dense_rank() da esa sakramaydi.
  • Oyna funksiyasini WHERE da ishlatib bo'lmaydi - u kechroq hisoblanadi.
  • Yechim: so'rovni ichki qilib o'rash yoki WITH ishlatish.
  • lag() oldingi, lead() keyingi qatorga qaraydi.
  • Uchinchi argument bilan bo'sh qiymatga standart berish mumkin: lag(x, 1, 0).
  • Oyna ichidagi ORDER BY ramkani o'zgartiradi - sum yig'ilib boruvchi bo'lib qoladi.
  • Ramkani (ROWS BETWEEN ...) ochiq yozish chalkashlikni yo'q qiladi.

Keyingi bo'limda murakkab so'rovlarni bo'laklarga ajratishni va rekursiv so'rovlarni ko'ramiz.

Xatolik topdingizmi?

Imlo xatosi, ishlamaydigan kod yoki noto‘g‘ri ma‘lumotni ko‘rsangiz - bizga xabar bering. Har bir xabar administrator tomonidan ko‘rib chiqiladi.