Saralash va natijani boshqarish - SQL va MySQL

Ma'lumot topildi - endi uni to'g'ri tartibda ko'rsatish kerak.

`ORDER BY` #

SQL

-- O'sish tartibida (standart)
SELECT ism, ortacha_baho FROM talabalar ORDER BY ortacha_baho;

-- Aniq ko'rsatish
SELECT ism, ortacha_baho FROM talabalar ORDER BY ortacha_baho ASC;

-- Kamayish tartibida
SELECT ism, ortacha_baho FROM talabalar ORDER BY ortacha_baho DESC;

Natija

+----------+--------------+
| ism      | ortacha_baho |
+----------+--------------+
| Malika   |         4.90 |
| Kamola   |         4.75 |
| Husanboy |         4.60 |
| Nodira   |         4.30 |
| Aziza    |         4.10 |
| Sardor   |         3.80 |
| Jasur    |         3.50 |
| Bekzod   |         3.20 |
+----------+--------------+

ORDER BY siz tartib kafolatlanmaydi

SQL

SELECT * FROM talabalar LIMIT 5;

Bu so'rov har safar boshqa 5 ta qator qaytarishi mumkin. Baza qatorlarni o'ziga qulay tartibda qaytaradi - bu id bo'yicha bo'lishi shart emas.

Tartib muhim bo'lsa, har doim ORDER BY yozing.

Bir nechta ustun bo'yicha #

SQL

-- Avval shahar bo'yicha, keyin baho bo'yicha
SELECT ism, shahar, ortacha_baho
FROM talabalar
ORDER BY shahar ASC, ortacha_baho DESC;

Natija

+----------+-----------+--------------+
| ism      | shahar    | ortacha_baho |
+----------+-----------+--------------+
| Jasur    | Andijon   |         3.50 |
| Malika   | Buxoro    |         4.90 |
| Husanboy | Namangan  |         4.60 |
| Kamola   | Samarqand |         4.75 |
| Nodira   | Samarqand |         4.30 |
| Sardor   | Toshkent  |         3.80 |
| Bekzod   | Toshkent  |         3.20 |
| Aziza    | Xiva      |         4.10 |
+----------+-----------+--------------+

Shaharlar alifbo tartibida, har bir shahar ichida esa baholar kamayish tartibida.

Har bir ustun uchun alohida yo'nalish

SQL

ORDER BY shahar ASC, ortacha_baho DESC, familiya ASC

ASC/DESC har bir ustunga alohida qo'llanadi. Bittasini yozib, qolganlari ham shunday bo'ladi deb o'ylamang.

Nima bo'yicha saralash mumkin? #

SQL

-- Ustun nomi
ORDER BY ortacha_baho DESC;

-- Taxallus (ORDER BY SELECT dan keyin bajariladi)
SELECT ortacha_baho * 20 AS ball FROM talabalar ORDER BY ball DESC;

-- Ifoda
ORDER BY ortacha_baho * 20 DESC;

-- Ustun raqami (tavsiya etilmaydi)
SELECT ism, shahar, ortacha_baho FROM talabalar ORDER BY 3 DESC;

-- Funksiya natijasi
ORDER BY LENGTH(ism) DESC;

-- CASE bilan maxsus tartib
ORDER BY CASE shahar
    WHEN 'Toshkent'  THEN 1
    WHEN 'Samarqand' THEN 2
    WHEN 'Buxoro'    THEN 3
    ELSE 99
END;

Ustun raqami bilan saralamang

SQL

SELECT ism, shahar, ortacha_baho FROM talabalar ORDER BY 3 DESC;

Ishlaydi, lekin SELECT ga yangi ustun qo'shsangiz - saralash boshqa ustunga o'tib ketadi va hech qanday xato chiqmaydi. Bu jimgina buziladigan kod.

Har doim ustun nomini yozing.

`NULL` qayerda turadi? #

SQL

SELECT ism, telefon FROM talabalar ORDER BY telefon;

MySQL da NULL qiymatlar ASC da boshida, DESC da oxirida turadi.

SQL

-- NULL larni oxiriga surish
SELECT ism, telefon
FROM talabalar
ORDER BY telefon IS NULL, telefon;

-- NULL larni boshiga
SELECT ism, telefon
FROM talabalar
ORDER BY telefon IS NOT NULL, telefon;

Bu qanday ishlaydi?

telefon IS NULL mantiqiy ifoda 0 yoki 1 qaytaradi. Avval shu bo'yicha saralanadi (0 - telefoni bor, 1 - yo'q), keyin telefon bo'yicha.

PostgreSQL da bu uchun maxsus sintaksis bor: ORDER BY telefon NULLS LAST. MySQL da esa yuqoridagi usul ishlatiladi.

Tasodifiy tartib #

SQL

-- Tasodifiy 3 ta talaba
SELECT ism, familiya FROM talabalar ORDER BY RAND() LIMIT 3;

ORDER BY RAND() katta jadvallarda juda sekin

Baza har bir qator uchun tasodifiy son hisoblab, keyin hammasini saralaydi. Million qatorli jadvalda bu soniyalar davom etadi.

Kichik jadvallarda (10 000 gacha) muammo yo'q. Kattalarida boshqa usul kerak:

SQL

SELECT * FROM talabalar
WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM talabalar)
ORDER BY id LIMIT 1;

`ORDER BY` va `LIMIT` birga #

Bu juftlik "eng yaxshi N ta" masalasini yechadi:

SQL

-- Eng yuqori 3 ta baho
SELECT ism, familiya, ortacha_baho
FROM talabalar
ORDER BY ortacha_baho DESC
LIMIT 3;

-- Eng past 3 ta
SELECT ism, familiya, ortacha_baho
FROM talabalar
ORDER BY ortacha_baho ASC
LIMIT 3;

-- Eng so'nggi 10 ta buyurtma
SELECT * FROM buyurtmalar ORDER BY yaratilgan DESC LIMIT 10;

-- 2-o'rindagi talaba
SELECT ism, ortacha_baho
FROM talabalar
ORDER BY ortacha_baho DESC
LIMIT 1 OFFSET 1;

Bu eng ko'p ishlatiladigan naqsh

Deyarli har bir sahifada shu ko'rinishdagi so'rov bor:

SQL

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

"Filtrla → saralash → cheklash" - bu SQL ning asosiy ish tartibi.

Saralash va indekslar #

SQL

-- Indeks bo'lsa - tez
CREATE INDEX idx_baho ON talabalar (ortacha_baho);
SELECT * FROM talabalar ORDER BY ortacha_baho DESC LIMIT 10;

-- Tarkibiy indeks WHERE + ORDER BY uchun
CREATE INDEX idx_shahar_baho ON talabalar (shahar, ortacha_baho);
SELECT * FROM talabalar WHERE shahar = 'Toshkent' ORDER BY ortacha_baho DESC;

filesort nima?

EXPLAIN natijasida Using filesort ko'rsangiz, baza natijani xotirada qo'shimcha saralashga majbur bo'lgan degani. Kichik natijada bu muammo emas, lekin katta jadvalda sekinlashtiradi.

Indeks to'g'ri bo'lsa, baza qatorlarni allaqachon saralangan holda o'qiydi va filesort kerak bo'lmaydi. Buni 13-bo'limda batafsil ko'ramiz.

`ORDER BY` va matn #

SQL

-- O'zbekcha harflar bilan saralash
SELECT ism FROM talabalar ORDER BY ism;

-- Uzunlik bo'yicha
SELECT ism FROM talabalar ORDER BY CHAR_LENGTH(ism) DESC;

-- Registrga sezgir saralash
SELECT ism FROM talabalar ORDER BY BINARY ism;

Sonlar matn ustunida

SQL

-- Agar 'kod' ustuni VARCHAR bo'lsa:
SELECT kod FROM mahsulotlar ORDER BY kod;

Natija

kod
---
1
10
100
2
20

Matn sifatida "10" "2" dan oldin keladi. To'g'ri saralash uchun:

SQL

ORDER BY CAST(kod AS UNSIGNED);

Yoki eng yaxshisi - ustunni son turida saqlang.

Amaliy misollar #

SQL

-- Reyting jadvali
SELECT
    ROW_NUMBER() OVER (ORDER BY ortacha_baho DESC) AS orin,
    CONCAT(ism, ' ', familiya) AS talaba,
    shahar,
    ortacha_baho
FROM talabalar
ORDER BY ortacha_baho DESC
LIMIT 5;

Natija

+------+------------------+-----------+--------------+
| orin | talaba           | shahar    | ortacha_baho |
+------+------------------+-----------+--------------+
|    1 | Malika Yusupova  | Buxoro    |         4.90 |
|    2 | Kamola Ergasheva | Samarqand |         4.75 |
|    3 | Husanboy Qodirov | Namangan  |         4.60 |
|    4 | Nodira Sobirova  | Samarqand |         4.30 |
|    5 | Aziza Tosheva    | Xiva      |         4.10 |
+------+------------------+-----------+--------------+

SQL

-- Maxsus tartib: faol talabalar oldinda
SELECT ism, familiya, faolmi, ortacha_baho
FROM talabalar
ORDER BY faolmi DESC, ortacha_baho DESC;

-- Aloqa ma'lumoti to'liqlar oldinda
SELECT ism, telefon, email
FROM talabalar
ORDER BY (telefon IS NOT NULL) DESC, ism;

-- Sahifalash: 2-sahifa, har sahifada 3 ta
SELECT ism, ortacha_baho
FROM talabalar
ORDER BY ortacha_baho DESC
LIMIT 3 OFFSET 3;

ROW_NUMBER() - oyna funksiyasi

MySQL 8.0 va MariaDB 10.2 dan boshlab oyna funksiyalari (window functions) mavjud:

SQL

ROW_NUMBER() OVER (ORDER BY baho DESC)     -- 1, 2, 3, 4
RANK()       OVER (ORDER BY baho DESC)     -- 1, 2, 2, 4 (teng bo'lsa sakraydi)
DENSE_RANK() OVER (ORDER BY baho DESC)     -- 1, 2, 2, 3 (sakramaydi)

Guruh ichida raqamlash ham mumkin:

SQL

ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY baho DESC)

Bu "har bir shahardagi eng yaxshi talaba" kabi masalalarni yechadi.

Amaliy topshiriq

kutubxona bazasida:

Kitoblarni yil bo'yicha eng yangisidan boshlab saralang.
Avval janr, keyin narx bo'yicha (kamayish) saralang.
Eng qimmat 5 ta kitobni chiqaring.
Eng arzon 3 ta kitobni chiqaring, narxi NULL bo'lganlarni oxiriga suring.
Kitob nomining uzunligi bo'yicha saralang.
CASE bilan janrlarni o'zingiz xohlagan tartibda saralang.
ROW_NUMBER() bilan reyting ustunini qo'shing.
2-sahifani chiqaring (har sahifada 5 ta kitob).

Xulosa #

ORDER BY siz qatorlar tartibi kafolatlanmaydi.
ASC (standart) - o'sish, DESC - kamayish tartibi.
Bir nechta ustun bo'yicha saralashda har biriga alohida yo'nalish beriladi.
Ustun raqami bilan saralamang - nomini yozing.
MySQL da NULL ASC da boshida turadi; ORDER BY ustun IS NULL, ustun bilan surish mumkin.
ORDER BY RAND() katta jadvallarda juda sekin.
ORDER BY + LIMIT - "eng yaxshi N ta" masalasining standart yechimi.
To'g'ri indeks filesort ni yo'q qiladi.

Keyingi bo'limda ma'lumotni guruhlash va agregat funksiyalarni o'rganamiz.

ORDER BY #

Bir nechta ustun bo'yicha #

Nima bo'yicha saralash mumkin? #

NULL qayerda turadi? #

Tasodifiy tartib #

ORDER BY va LIMIT birga #

Saralash va indekslar #

ORDER BY va matn #

Amaliy misollar #

Xulosa #

Xatolik topdingizmi?

`ORDER BY` #

`NULL` qayerda turadi? #

`ORDER BY` va `LIMIT` birga #

`ORDER BY` va matn #