SQL query'lar bo'yicha 30 ta intervyu savoli va javoblari

WHERE har qanday guruhlash yoki aggregation qo'llanishidan oldin satrlarni filtrlash uchun ishlatiladi. Quyidagi kod parchasi WHERE'dan qanday foydalanishni ko'rsatadi. U Users jadvalidan Age qiymati 18 dan katta bo'lgan satrlarni ajratib oladi.

SELECT * FROM Users
WHERE Age > 18;

Query natijasi quyidagi jadvalga o'xshaydi.

userId	firstName	lastName	age
1	John	Doe	30
2	Jane	Don	31
3	Will	Liam	25
4	Wade	Great	32
5	Peter	Smith	27

HAVING esa guruhlash va aggregation bajarilgandan keyin guruhlarni filtrlash uchun ishlatiladi. U aggregate function'lar natijasiga qo'llanadi va asosan GROUP BY clause'i bilan birga ishlatiladi.

SELECT FirstName, Age FROM Users
GROUP BY FirstName, Age
HAVING Age > 30;

Yuqoridagi kod FirstName va Age ustunlarini tanlaydi, so'ng satrlarni FirstName va Age bo'yicha guruhlaydi va nihoyat yoshi 30 dan katta bo'lgan yozuvlarni oladi. Query natijasi quyidagicha ko'rinadi:

firstName	age
Wade	32
Jane	31

Takroriy yozuvlarni topish uchun avval takrorlanishni qanday mezon bo'yicha aniqlashni belgilab olishingiz kerak. Takrorlanishlarni ikki yoki undan ortiq ustunning kombinatsiyasi bo'yicha aniqlamoqchimisiz yoki bitta ustun ichidagi takrorlanishlarni qidiryapsizmi?

Quyidagi qadamlar jadvaldagi takroriy ma'lumotlarni topishga yordam beradi.

• Takroriy qiymatlarni tekshirmoqchi bo'lgan ustun(lar) bo'yicha barcha satrlarni guruhlash uchun GROUP BY clause'idan foydalaning.
• Qaysidir guruhda bittadan ortiq yozuv bor-yo'qligini tekshirish uchun HAVING buyrug'ida COUNT funksiyasidan foydalaning.

Avval bitta ustundagi takrorlanishlar bilan qanday ishlashni ko'rib chiqaylik. Users jadvalida 30 yoshli uchta foydalanuvchi bor. Takroriy qiymatlarni topish uchun GROUP BY clause'i va COUNT funksiyasidan foydalanamiz.

SELECT Age, COUNT(Age)
FROM Users
GROUP BY Age
HAVING COUNT(Age) > 1

Query natijasi quyidagicha ko'rinadi:

age	count
30	3

Bir nechta ustundagi (composite) takrorlanishlar bilan ishlash ham bitta ustundagi takrorlanishlar bilan ishlashga o'xshaydi.

SELECT FirstName, LastName, COUNT(*) AS dup_count
FROM Users
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;

Takrorlanishlarni topgach, sizdan ularni qanday o'chirishni so'rashlari mumkin. Takroriy yozuvlarni o'chiradigan query quyida Common Table Expression (CTE) va ROW_NUMBER() yordamida ko'rsatilgan.

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Age ORDER BY id) AS rn
  FROM Users
)
DELETE FROM Users
WHERE id IN (
  SELECT id
  FROM ranked
  WHERE rn > 1
);

Bu query ma'lumotlarning birinchi satrini saqlab qolgan holda barcha takroriy yozuvlarni o'chiradi.

JOIN ikki yoki undan ortiq jadvaldagi ma'lumotlarni ular orasidagi o'zaro bog'liq ustun asosida birlashtiradi. Relational database management system'larda bir nechta jadvalga tarqalgan ma'lumotlarni olish kerak bo'lganda u juda foydali.

INNER JOIN ko'rsatilgan join sharti bo'yicha ikkala jadvalda ham mosligi bor satrlarnigina qaytaradi. Agar mos satrlar bo'lmasa, natija bo'sh bo'ladi. INNER JOIN'ning SQL sintaksisi quyidagi kod parchasida ko'rsatilgan.

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2 FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name

Masalan, quyidagi ma'lumotlarga ega Users va Cities degan ikkita jadval bor:

Users jadvali

userId	firstName	lastName	age	cityId
1	John	Doe	30	1
2	Jane	Don	31	1
3	Will	Liam	25	1
4	Wade	Great	32	1
5	Peter	Smith	27	2
6	Rich	Mond	30	2
7	Rach	Mane	30	2
8	Zach	Ridge	30	3

Cities jadvali

id	name
1	London
2	Manchester

Aytaylik, foydalanuvchilar ro'yxatini ular yashaydigan shahar nomlari bilan birga olmoqchisiz. Buni INNER JOIN query'si yordamida qilish mumkin.

SELECT users.firstName, users.lastName, users.age, cities.name as cityName FROM users
INNER JOIN cities
ON users.cityId = cities.id

firstName	lastName	age	cityName
John	Doe	30	London
Jane	Don	31	London
Will	Liam	25	London
Wade	Great	32	London
Peter	Smith	27	Manchester
Rich	Mond	30	Manchester
Rach	Mane	30	Manchester

LEFT JOIN chap jadvaldagi (1-jadval) barcha satrlarni va o'ng jadvaldagi (2-jadval) ularga mos keladigan satrlarni qaytaradi. Agar o'ng jadvalda (2-jadval) mos satr bo'lmasa, uning o'rniga NULL qiymatlar qaytariladi. Left join'ning SQL sintaksisi quyidagi kod parchasida ko'rsatilgan.

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2 FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name 

Keling, avvalgi Users va Cities jadvallari asosidagi amaliy misolni ko'rib chiqamiz.

LEFT JOIN query'sini bajarganingizda quyidagi jadvalni olasiz.

firstName	lastName	age	cityName
John	Doe	30	London
Jane	Don	31	London
Will	Liam	25	London
Wade	Great	32	London
Peter	Smith	27	Manchester
Rich	Mond	30	Manchester
Rach	Mane	30	Manchester
Zach	Ridge	30	null

Bizda Salaries jadvali bor deylik:

id	salary
1	1000
2	2000
3	3000
4	4000

Ikkinchi eng yuqori maoshni topadigan query quyidagi kod parchasida ko'rsatilgan

SELECT DISTINCT Salary
FROM Salaries
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

Query natijasi quyida ko'rsatilgan

	salary
1	3000

UNION takroriy yozuvlarni olib tashlaydi, UNION ALL esa barcha takrorlanishlarni saqlab qoladi. Takrorlanishlarni olib tashlash (de-duplication) tufayli UNION UNION ALL'ga qaraganda sekinroq ishlaydi. Faqat noyob yozuvlarni olmoqchi bo'lsangiz UNION'dan, satrlar takrorlansa ham ularning hammasi kerak bo'lsa UNION ALL'dan foydalanasiz.

Ma'lumotlar bazasidagi index'lar kitoblardagi ko'rsatkichlarga o'xshaydi. Ular ma'lumotlar bazasidan ma'lumot olish tezligini oshiradi. Jadvaldan ma'lumot o'qimoqchi bo'lganingizda, index'lar jadvalning barcha satrlarini birma-bir ko'rib chiqish o'rniga to'g'ridan-to'g'ri siz qidirayotgan satrga borishga yordam beradi.

Ular SELECT query'larini yaxshilaydi, unumdorlikni oshiradi hamda saralash va filtrlashni tezlashtiradi. Shuningdek, ular data integrity'ni ta'minlaydi. Index'larning turli xillari bor, jumladan:

• B-Tree index
• Composite index
• Unique index
• Full text index
• Bitmap index
• Clustered index
• Non-clustered index

Primary key — jadvaldagi ma'lumotlar satrining noyob identifikatori. U har bir satrni yagona tarzda aniqlash uchun ishlatiladi va ikkita satr bir xil primary key'ga ega bo'lishi mumkin emas. Primary key ustuni null bo'la olmaydi. Quyidagi misolda user_id primary key hisoblanadi.

CREATE TABLE users (
   user_id INT PRIMARY KEY,
   name VARCHAR(100),
   phoneNumber VARCHAR(100)
);

Foreign key ikki jadval o'rtasidagi ko'prikka o'xshaydi. Bir jadvaldagi foreign key boshqa jadvaldagi primary key bo'ladi. U ikki jadvalni bir-biriga bog'lovchi vazifasini bajaradi.

GROUP BY — ko'rsatilgan ustunda bir xil qiymatga ega satrlarni guruhlaydigan standart SQL buyrug'i. Uni COUNT, MIN, MAX va shu kabi aggregate function'lar bilan birga ishlatish kerak.

Quyidagi query GROUP BY clause'ini qanday ishlatishni ko'rsatadi:

SELECT columnName FROM Table
GROUP BY columnName

Agar GROUP BY clause'ida bo'lmagan ustunni SELECT qilsangiz, ustun GROUP BY clause'ida yoki aggregate function ichida bo'lishi kerakligi haqida xato chiqadi. Misol tariqasida quyidagi jadvaldan foydalanamiz.

firstName	lastName	phoneNumber
John	Doe	+23410910
Jack	Ray	+23410911
Irene	Rotherdam	+23410911

Agar ma'lumotlar bazasida quyidagi query'ni ishga tushirsangiz:

SELECT firstName, phoneNumber FROM phoneNumbers
GROUP BY phoneNumber

Natijada xato chiqadi, chunki firstName GROUP BY clause'ida yo'q va aggregate function ichida ham ishlatilmagan.

Quyidagi ko'rinishdagi Users jadvali berilgan bo'lsin:

userId	firstName	lastName	age	country
1	John	Doe	30	Portugal
2	Jane	Don	31	Belgium
3	Will	Liam	25	Argentina
4	Wade	Great	32	Denmark
5	Peter	Smith	27	USA
6	Rich	Mond	30	USA
7	Rach	Mane	30	Argentina
8	Zach	Ridge	30	Portugal

Foydalanuvchilar sonini mamlakatlar bo'yicha COUNT qiladigan query:

SELECT country, COUNT(country) FROM users
GROUP BY country

Bu query foydalanuvchilarni mamlakat bo'yicha guruhlash uchun GROUP BY clause'idan foydalanadi, so'ng ularning sonini keyingi ustunda ko'rsatadi. Query natijasi quyidagicha ko'rinadi:

country	count
USA	2
Portugal	2
Argentina	2
Belgium	1
Denmark	1

Agar GROUP BY clause'ini aggregate function'siz ishlatsangiz, bu DISTINCT buyrug'ini ishlatish bilan bir xil bo'ladi. Masalan, quyidagi buyruq:

SELECT phoneNumber FROM phoneNumbers
GROUP BY phoneNumber

quyidagiga teng:

SELECT DISTINCT phoneNumber FROM phoneNumbers

Farqi shundaki, COUNT(*) NULL qiymatlarni ham qo'shib, ma'lumotlarning barcha satrlarini sanaydi, COUNT(column_name) esa ko'rsatilgan ustundagi faqat NULL bo'lmagan qiymatlarni sanaydi. Buni Users nomli jadval misolida ko'rib chiqamiz.

userId	firstName	lastName	age	country
1	John	Doe	30	Portugal
2	Jane	Don	31	Belgium
3	Zach	Ridge	30	Norway
4	null	Tom	25	Denmark

Agar COUNT(*) ishlatsangiz, natija 4 bo'ladi, COUNT(firstName) ishlatsangiz esa null qiymatni hisobga olmasdan 3 ni qaytaradi.

Subquery — boshqa query ichida joylashgan query. U murakkab mantiq talab qiladigan query'larda ishlatiladi. Subquery natijasidan boshqa query uchun foydalanmoqchi bo'lsangiz, subquery'lardan foydalaning. Quyidagi misolda subquery qavs ichida berilgan.

SELECT firstName,
  (SELECT COUNT(*)
  FROM cities
  WHERE cities.id = users.city_id) AS cityCount
FROM users;

JOIN esa ikki yoki undan ortiq jadvalni ular orasidagi o'zaro bog'liq ustunlar asosida birlashtiradi. Bog'liq ustun odatda foreign key bo'ladi. Turli jadvallardagi o'zaro bog'liq ma'lumotlarni bir joyga jamlamoqchi bo'lsangiz, JOIN'lardan foydalaning. Quyidagi kod JOIN'dan qanday foydalanishni ko'rsatadi.

SELECT firstName, COUNT(*) FROM users
JOIN cities ON users.city_id = cities.id

Quyidagi holatlarda JOIN subquery'dan tezroq ishlaydi:

• Bir nechta jadvaldan ma'lumotlarni query qilayotganingizda.
• Index'langan ustunlar bo'yicha filtrlayotganingizda yoki join qilayotganingizda.

id, name va salary ustunlariga ega, quyidagi ko'rinishdagi Employees jadvali berilgan bo'lsin:

id	name	salary
1	Irene	1000
2	Peter	1230
3	Raymond	1450
4	Henry	1790
5	Naomi	2350
6	Bridget	2000
7	Emily	2500
8	Great	3000
9	Mercedes	2750
10	Zoe	2900

O'rtacha maoshdan ko'proq maosh oladigan xodimlarni topadigan query:

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

id	name	salary
5	Naomi	2350
7	Emily	2500
8	Great	3000
9	Mercedes	2750
10	Zoe	2900

Correlated subquery — outer query'dagi qiymatga bog'liq bo'lgan subquery. Bu shuni anglatadiki, subquery outer query'da tanlanishi mumkin bo'lgan har bir satr uchun alohida hisoblanadi. Quyida correlated subquery'ga misol keltirilgan.

SELECT name, country_id, salary
FROM employees em
WHERE salary > (
  SELECT AVG(salary) FROM employees
  country_id = em.country_id);

Yuqoridagi kod:

• Outer query'ni jadvalning har bir satri bo'ylab ishga tushiradi.
• employees jadvalidan country_id qiymatini oladi.
• Qolgan satrlar bo'ylab yurib, xuddi shu hisob-kitobni bajaradi.

Shu sababli jadvaldagi ma'lumotlar ko'paygan sari unumdorlik pasayib boradi.

Agar har bir satrga xos amallarni bajarmoqchi bo'lsangiz yoki biror amalni JOIN yoki boshqa aggregate function'lar yordamida bajara olmasangiz, correlated subquery'dan foydalaning.

EXISTS ham, IN ham subquery'larda natijalarni filtrlash uchun ishlatiladi, lekin qanday ishlatilishiga qarab ular turli vazifalarni bajaradi.

EXISTS'dan quyidagi holatlarda foydalanish kerak:

• Haqiqiy qiymatlarni emas, balki satr mavjud yoki yo'qligini tekshirmoqchi bo'lsangiz.
• Subquery correlated query bo'lsa.
• Subquery ko'p satr qaytarsa-yu, sizga faqat birinchi moslik kerak bo'lsa.

IN'dan quyidagi holatlarda foydalanish kerak:

• Ustunni qiymatlar ro'yxati bilan solishtirayotganingizda.
• Subquery kichik yoki o'zgarmas (static) ro'yxat qaytarsa.

Ha, murakkab mantiqni amalga oshirmoqchi bo'lsangiz, subquery'larni bir necha daraja chuqurlikda ichma-ich joylashtirishingiz mumkin. Nested subquery — boshqa subquery ichida joylashgan subquery bo'lib, subquery'lardan iborat qatlamlarni hosil qiladi. Ko'pgina SQL engine'lar subquery'larning bir nechta qatlamiga ruxsat beradi, biroq bu kodning o'qilishini qiyinlashtiradi va unumdorlikni pasaytiradi.

Window function — joriy satrga bog'liq bo'lgan muayyan satrlar to'plami ustida amallar bajarish imkonini beradigan funksiya. Butun dataset ustida hisob-kitob qiladigan aggregate function'lardan farqli o'laroq, window function'lar ma'lumotlarning bir qismi (subset) ustida amal bajara oladi. Bunday hisob-kitoblar asl dataset'ni o'zgartirmagan holda aggregation, ranking va kumulyativ yig'indilarni hisoblash uchun qo'llaniladi.

Bu query uchun misol sifatida Sales jadvalidan foydalanamiz. Unda uchta ustun bor: id, hafta kunini bildiruvchi day va AQSh dollarida sotilgan summani bildiruvchi amount. Jadval quyidagicha ko'rinadi:

id	day	amount
1	Monday	200
2	Tuesday	300
3	Wednesday	600
4	Thursday	390
5	Friday	900

Running total'ni hisoblaydigan query:

SELECT
  id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM
  sales;

Bu query har bir satr uchun summani qo'shib borish va running total'ni saqlash maqsadida OVER window function'idan foydalanadi. U har bir kun uchun summani olib, avvalgi yig'indilarga qo'shadi. Query natijasi quyidagicha ko'rinadi:

id	day	amount	running_total
1	Monday	200	200
2	Tuesday	300	500
4	Thursday	390	1100
3	Wednesday	600	1490
5	Friday	900	2390

Rasmdan ko'rinib turibdiki, oxirgi ustun running_total, bo'lib, u joriy kunning summasini olib, joriy qiymatini hosil qilish uchun uni avvalgi qiymatiga qo'shadi.

RANK() funksiyasi har bir satrga o'sish yoki kamayish tartibiga ko'ra rank beradi. Agar qiymatlar bir xil bo'lsa, ularga bir xil o'rin beradi va keyingi rank uchun navbatdagi sonni tashlab ketadi. Masalan, ikkita satr teng qiymatga ega bo'lib, ikkalasiga ham 1-rank berilgan bo'lsa, keyingi rank 2 emas, 3 bo'ladi.

RANK() funksiyasini ko'rsatish uchun oldingi savoldagi Sales jadvalidan foydalanamiz. Summa bo'yicha tartiblab rank beradigan query quyidagicha:

SELECT
  id,
  day,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM
  sales;

Natija quyidagi rasmda ko'rsatilgan. Ko'rib turganingizdek, 900 summasi birinchi rank'ni, 200 esa eng past rank'ni olgan. Shuningdek, ikkita qiymat bir xil rank'ga ega bo'lgani uchun 2 va 4-rank'lar orasida oraliq paydo bo'lgan. Bundan eng ko'p savdo juma kuni, eng kami esa dushanba kuni bo'lganini ham bilib olish mumkin.

id	day	amount	amount_rank
5	Friday	900	1
3	Wednesday	600	2
6	Saturday	600	2
4	Thursday	390	4
2	Tuesday	300	5
1	Monday	200	6

DENSE_RANK() funksiyasi satrlarga rank berishi jihatidan RANK()'ga o'xshaydi, ammo farqi shundaki, ikki yoki undan ortiq teng qiymat bo'lganda DENSE_RANK oraliq qoldirmaydi. Buni yuqoridagi Sales jadvali misolida ko'rsatamiz. Query quyida keltirilgan.

SELECT
  id,
  day,
  amount,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM
  sales;

Natija quyida ko'rsatilgan. Ko'rib turganingizdek, RANK funksiyasidagidan farqli ravishda rank'lar orasida oraliq yo'q.

id	day	amount	amount_rank
5	Friday	900	1
3	Wednesday	600	2
6	Saturday	600	2
4	Thursday	390	3
2	Tuesday	300	4
1	Monday	200	5

ROW_NUMBER siz ko'rsatgan tartibga qarab har bir satrga noyob raqam beradi. U hech bir raqamni tashlab ketmaydi: teng qiymatlar bo'lsa ham, ularga turli raqamlar beradi. Bu jihatdan u teng qiymatlarga bir xil rank beradigan RANK va DENSE_RANK funksiyalaridan farq qiladi.

Misol uchun yana o'sha Sales jadvalidan foydalanamiz. Quyidagi query ROW_NUMBER funksiyasidan qanday foydalanishni ko'rsatadi.

SELECT
  id,
  day,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rowNumber
FROM
  sales;

Natija quyidagi rasmda ko'rsatilgan. Ko'rib turganingizdek, rownumber ustuni ortib boradi va bir xil qiymatlar bo'lsa ham, har biriga shunchaki noyob satr raqami beriladi.

id	day	amount	amount_rank
5	Friday	900	1
3	Wednesday	600	2
6	Saturday	600	3
4	Thursday	390	4
2	Tuesday	300	5
1	Monday	200	6

LAG() va LEAD() — ko'rsatilgan satrdan oldingi va keyingi satrlardagi ma'lumotlarni olish uchun ishlatiladigan window function'lar. Ularni positional SQL function'lar deb ham atash mumkin.

LAG() joriy satrdan oldingi satrlarda saqlangan qiymatga murojaat qilish imkonini beradi. Bu satr bevosita oldingi satr yoki bir necha satr oldingisi bo'lishi mumkin. Sintaksisini ko'rib chiqamiz:

LAG(column_name, offset, default_value)

U uchta argument qabul qiladi.

• column_name: oldingi satrdan qaysi ustun qiymatini olishni belgilaydi.
• offset: ixtiyoriy argument bo'lib, necha satr orqaga qarash kerakligini belgilaydi. Standart qiymati 1.
• default_value: oldingi satr mavjud bo'lmaganda beriladigan qiymat. U ixtiyoriy, standart qiymati NULL.

LAG() funksiyasini Sales jadvali misolida ko'rsatamiz. Bu query oldingi kungi savdoni topish uchun ishlatiladi. LAG() o'tgan voqealar bo'yicha hisobotlar tayyorlashda foydali.

id	day	amount
1	Monday	200
2	Tuesday	300
3	Wednesday	600
4	Thursday	390
5	Friday	900
6	Saturday	600

SELECT
  id,
  day,
  amount,
  LAG(amount) OVER (ORDER BY id) AS previous_day_sales
FROM
  sales;

Query natijasi quyidagicha ko'rinadi:

id	day	amount	previous_day_sales
1	Monday	200	null
2	Tuesday	300	200
3	Wednesday	600	300
4	Thursday	390	600
5	Friday	900	390
6	Saturday	600	900

LEAD() funksiyasi joriy satrdan keyingi satrlardagi ma'lumotlarni olish uchun ishlatiladi. Uning sintaksisi LAG() funksiyasinikiga o'xshaydi. Undan oldinga qarab, kelajakdagi trend'larni prognoz qilishda foydalanish mumkin.

LEAD() funksiyasidan foydalanilgan query quyida ko'rsatilgan.

SELECT
  id,
  day,
  amount,
  LEAD(amount) OVER (ORDER BY id) AS previous_day_sales
FROM
  sales;

id	day	amount	previous_day_sales
1	Monday	200	300
2	Tuesday	300	600
3	Wednesday	600	390
4	Thursday	390	900
5	Friday	900	600
6	Saturday	600	null

Har bir foydalanuvchi uchun sanalar ketma-ketligidagi bo'shliqlarni aniqlashda LAG() funksiyasidan foydalanasiz. Har bir sanani oldingisi bilan solishtirib, ular orasidagi farq 1 dan katta yoki katta emasligini tekshirasiz.

Bo'shliqlarni qanday aniqlashni ko'rsatish uchun ClockIns jadvalidan foydalanamiz. Jadvalda ikkita ustun bor: userId va clockInDate. Ular mos ravishda foydalanuvchining identifikatsiya raqamini va foydalanuvchi kirish kartasi orqali binoga kirib, kelganini qayd etgan sanani bildiradi. Jadval quyidagicha ko'rinadi:

userId	clockInDate
1	2025-01-01
1	2025-01-02
1	2025-01-05
1	2025-01-06
2	2025-01-06
2	2025-01-06
2	2025-01-07
3	2025-01-02
3	2025-01-04
3	2025-01-06
3	2025-01-07

Har bir foydalanuvchi bo'yicha bo'shliqlarni topadigan query quyidagicha ko'rinadi:

WITH clockInGaps AS (
  SELECT
    userid,
    clockInDate,
    LAG(clockInDate) OVER (PARTITION BY userId ORDER BY clockInDate) AS previousClockInDate
  FROM
    clockIns
)

SELECT 
  userId,
  previousClockInDate AS gapStart,
  clockInDate AS gapEend,
  clockInDate - previousClockInDate - 1 AS gapDays
FROM clockInGaps
WHERE clockInDate - previousClockInDate > 1
ORDER BY userId, gapStart;

Yuqoridagi kod avval clockInGaps ifodasini yaratadi: u har bir foydalanuvchi va uning clockInDate qiymatlarini query qiladi hamda har bir foydalanuvchi uchun oldingi sanani olishda LAG funksiyasidan foydalanadi. So'ng asosiy query har bir satrni filtrlab, joriy sana va oldingi sana orasidagi bo'shliqlarni topadi. Query natijasi quyidagicha ko'rinadi:

userId	gapStart	gapEnd	gapDays
1	2025-01-02	2025-01-05	2
2	2025-01-07	2025-01-10	2
3	2025-01-02	2025-01-04	1
3	2025-01-04	2025-01-06	1

NTILE() — satrlarni oldindan belgilangan miqdordagi taxminan teng guruhlarga ajratadigan window function. Bu ma'lumotlaringizni o'zingiz belgilagan mezonlar asosida turli to'plamlarga bo'lishga o'xshaydi. Masalan, sizda talabalarning 1 dan 100 gacha bo'lgan ballari bor deylik; NTILE() funksiyasi yordamida bu ballarni turli guruhlarga yoki bucket'larga ajratishingiz mumkin.

NTILE() funksiyasining sintaksisi:

NTILE(n) OVER (ORDER BY some_column)

• n: satrlarni nechta guruhga ajratmoqchi ekaningizni bildiradi.
• ORDER BY: funksiya qo'llanadigan har bir guruhdagi satrlar tartibini belgilaydi.

Keling, Scores jadvali misolida amaliy namunani ko'rib chiqamiz. Jadvalda talabalarning testda olgan ballari saqlanadi. NTILE() funksiyasidan qanday foydalanishni ko'ramiz.

userId	score
1	78
2	70
3	90
4	98
5	60
6	88
7	100
8	66

NTILE() funksiyasidan foydalanilgan query quyidagicha ko'rinadi:

SELECT
  id,
  score,
  NTILE(3) OVER (ORDER BY score DESC) AS category
FROM scores;

userId	score	category
7	100	1
4	98	1
3	90	1
6	88	2
1	78	2
2	70	2
8	66	3
5	60	3

NTILE() funksiyasi ma'lumotlar tahlilida foydali, chunki u dataset'dagi outlier'larni aniqlashi va ma'lumotlarning histogram'larini yaratishi mumkin. Shuningdek, u ma'lumotlar taqsimoti uchun percentile va quartile'larni ham hosil qila oladi.

Sekin ishlayotgan query'larni optimallashtirish uchun avval nimani optimallashtirish kerakligini bilish maqsadida query'ni tahlil qilishingiz kerak. Query'ga qarab turli optimallashtirishlarni bajarish mumkin. Ulardan ba'zilari:

• Index'lardan samarali foydalanish: Index'lar ma'lumotlar bazasiga muayyan mezonlarga mos yozuvlarni tez topish imkonini berib, query'larni tezlashtiradi. Indexing — bir yoki bir nechta ustun qiymatlarini noyob qiymatga moslashtirish jarayoni bo'lib, u qidiruv mezoniga mos satrlarni topishni osonlashtiradi. WHERE, JOIN va ORDER BY clause'larida tez-tez ishlatiladigan ustunlarga index yaratishingiz mumkin. Biroq juda ko'p index yaratish ma'lumot kiritish, yangilash va o'chirish amallarini sekinlashtirishi mumkinligini yodda tuting.

• SELECT *'dan qoching: SELECT * statement'idan foydalanish query unumdorligini pasaytirishi mumkin, chunki u jadvaldagi barcha ustunlarni, jumladan query uchun kerak bo'lmaganlarini ham qaytaradi. Optimal unumdorlik uchun query'da faqat sizga kerak bo'lgan ustunlarni tanlang. Shuning uchun barcha ustunlarni tanlaydigan query'ni ko'rganingizda, bu ustunlarning hammasi haqiqatan kerakmi va query zanjirida keyinchalik ishlatiladimi, tekshirib ko'ring.

• Subquery'lardan qoching: Subquery'lar, ayniqsa WHERE yoki HAVING clause'larida ishlatilganda, query unumdorligini pasaytiradi. Iloji boricha subquery'lardan qochib, ularning o'rniga JOIN'lar yoki boshqa usullardan foydalaning.

• Stored procedure'lardan foydalaning: Stored procedure'lar — ma'lumotlar bazasida saqlanadigan, oldindan kompilyatsiya qilingan SQL statement'lari bo'lib, ularni ilovadan yoki to'g'ridan-to'g'ri query'dan chaqirish mumkin. Stored procedure'lardan foydalanish ma'lumotlar bazasi va ilovangiz o'rtasida uzatiladigan ma'lumotlar hajmini kamaytirish orqali query unumdorligini oshiradi, shuningdek, SQL statement'larini kompilyatsiya qilishga ketadigan vaqtni tejaydi.

Production kodingizda quyidagi sabablarga ko'ra SELECT * ishlatishdan iloji boricha qoching:

• IO ortadi: SELECT * ishlatganingizda keraksiz ma'lumotlar ham qaytarilishi mumkin. Jadvaldagi barcha ma'lumotlarni o'qiyotganingiz sababli bu ma'lumotlar bazasi darajasida Input/Output sikllarining ko'payishiga olib keladi. Bu ta'sir ma'lumotlari ko'p bo'lgan jadvalda yanada sezilarli bo'ladi va hatto query'ni sekinlashtiradi.

• Tarmoq trafigi ortadi: SELECT * mijozga keragidan ko'proq ma'lumot qaytaradi, bu esa zarur bo'lganidan ko'proq network bandwidth sarflaydi. Bandwidth sarfining oshishi ma'lumotlarning mijoz ilovasiga yetib borishini sekinlashtiradi va ilova unumdorligiga ta'sir qiladi.

• Ilova ko'proq xotira talab qiladi: Ko'p ma'lumot qaytarilishi ilovangizdan foydalanmasligingiz mumkin bo'lgan keraksiz ma'lumotlarni saqlash uchun ko'proq xotira talab qiladi va bu ilova unumdorligiga ta'sir qiladi.

• Kodni qo'llab-quvvatlash qiyinlashadi: SELECT * ishlatish kodni qo'llab-quvvatlashni murakkablashtiradi. Agar jadval tuzilmasi ustun qo'shish, olib tashlash yoki qayta nomlash orqali o'zgarsa, SELECT * ishlatadigan query'lar kutilmaganda buzilishi mumkin. Ma'lumotlar bazasi schema'sidagi ehtimoliy o'zgarishlarga chidamli bo'lish uchun ma'lumot olmoqchi bo'lgan ustunlarni aniq ko'rsating.

Index'larning yo'qligi, ayniqsa ma'lumotlar ko'paygan sari, query'lar unumdorligiga ta'sir qilishi mumkin. Index'lar yo'qligining asosiy oqibatlari quyida keltirilgan:

• Sekin query'lar: Index'lar bo'lmasa, har bir o'qish query'si mos satrlarni topish uchun butun jadvalni ko'rib chiqadi. Jadvaldagi ma'lumotlar ko'paygani sari bu holat yomonlashib boradi.
• Locking va concurrency muammolari: Index'siz jadvalni skanerlash ko'proq vaqt oladi, jadvalning lock qilinishi esa boshqa query'larning bajarilishiga to'sqinlik qilib, ilova unumdorligiga ta'sir qilishi mumkin.
• Samarasiz join'lar: Join key'larida index bo'lmagan jadvallar ustidagi join'lar juda sekin ishlaydi va query unumdorligini yomonlashtiradi.
• Yomon foydalanuvchi tajribasi: Index'larning yo'qligi ilovalaringizda foydalanuvchi tajribasining yomonlashishiga olib kelishi mumkin. Bu sahifalarning sekin yuklanishiga, ma'lumotlar bazasidan ma'lumot olinayotganda ilovaning qotib qolishiga sabab bo'lishi mumkin.

SARGable — Search Argumentable query degan ma'noni anglatadi: bunday query index'lardan foydalanadi va samarali bajariladi. Agar query SARGable bo'lsa, database engine index'lar yordamida satrlarni tez topadi, butun jadvalni skanerlashdan qochadi va query unumdorligini oshiradi.

GROUP BY clause'idan foydalanishda ko'p uchraydigan xatolar quyidagilar:

• GROUP BY clause'ida bo'lmagan, aggregation qilinmagan ustunlarni tanlash: Bu yangi boshlovchilar ham, tajribali mutaxassislar ham ko'p yo'l qo'yadigan xato. Bunday query'ga misol:

SELECT day, amount FROM Sales
GROUP BY day

Yuqoridagi query'da amount ustuni GROUP BY clause'iga kirmagan, shuning uchun u GROUP BY clause'ida bo'lishi kerakligi haqida xato chiqadi. Buni tuzatish uchun amount ustuniga aggregate function qo'llash kerak.

SELECT day, MAX(amount) FROM Sales
GROUP BY day

• Aggregate function'lardan foydalanmaslik: GROUP BY'ni aggregate function'larsiz ishlatish ham ko'p uchraydigan xato. GROUP BY odatda MAX, MIN, COUNT kabi aggregate function'lar bilan birga ishlatiladi.
• Bir nechta ustun bo'yicha guruhlash: Ko'p ustunlar bo'yicha guruhlash query'ni ma'nosiz qilib qo'yishi mumkin. Ko'p ustunlar bo'yicha guruhlash odatiy hol emas, shunday holat yuz berganda esa haqiqatan ham shu ustunlar bo'yicha guruhlash kerakmi yoki yo'qligini tekshirib ko'ring.

NULL noma'lum qiymat bo'lgani uchun, mumkin bo'lgan qiymatlar ro'yxatida NULL bo'lgan NOT IN query'si NULL qiymat keltirib chiqaradigan noma'lum natija tufayli doim 0 ta yozuv qaytaradi. SQL qiymat o'sha ro'yxatda yo'qligini aniq hal qila olmaydi.

Buni quyidagi ko'rinishdagi Sales jadvali misolida ko'rsatamiz:

id	day	amount
1	Monday	200
2	Tuesday	300
3	Wednesday	600
4	Thursday	390
5	Friday	900
6	Saturday	600

Quyidagi query'ni ishga tushirsangiz, u bo'sh natija qaytaradi, chunki hech narsa NULL'ga teng ham, teng emas ham bo'lmaydi va SQL qiymat ro'yxatda yo'qligini aniqlay olmaydi.

SELECT * from sales
WHERE amount NOT IN (200, 300, 600, NULL);