WisarWisar
Dasturlash kitobi/6-QISM — Database20 daqiqa

6.7-bob: SQL chuqur — JOIN turlari, subquery, CTE, window functions

6-QISM — Ma'lumotlar bazasi (Database) · 7-mavzu

1. Kirish va motivatsiya

Asosiy SQL'ni 6.4-bob va ikki DB'ni (MySQL 6.5, PostgreSQL 6.6) bildik. Endi SQL'ning eng kuchli, professional qismiga o'tamiz — bu qism SQL'ni "o'rtacha"dan "ekspert"ga ko'taradi va sizni boshqalardan ajratadi: JOIN (jadvallarni birlashtirish), subquery (so'rov ichida so'rov), CTE (WITH — o'qiladigan murakkab so'rov), va window functions (reyting, yugurib boruvchi yig'indi — analitikaning siri).

Relatsion DB'ning butun kuchi — ma'lumot bir nechta jadvalda bog'langan holda saqlanishida (6.1: 2.5 — normalizatsiya). Foydalanuvchilar bir jadvalda, buyurtmalar boshqasida, mahsulotlar uchinchisida. Lekin hisobot kerak bo'lganda — "har foydalanuvchining buyurtma summasi", "eng ko'p sotilgan mahsulotlar" — bu jadvallarni birlashtirish kerak. Bu — JOINning vazifasi. JOIN'ni bilmasdan relatsion DB'dan foydalanib bo'lmaydi.

Window functions — SQL'ning eng kuchli, lekin kam o'rganiladigan qismi. Ular reyting (kim 1-o'rinda), yugurib boruvchi yig'indi (running total), oldingi/keyingi qator bilan taqqoslash kabi murakkab analitikani bir so'rovda beradi. Dashboard, hisobot, tahlil — hammasida zarur. Bu bobni o'zlashtirish — SQL'da haqiqiy ekspertlik belgisi (intervyularda ham juda qadrlanadi).

O'xshatish (JOIN): ikki ro'yxat — biri "talabalar" (ID, ism), biri "baholar" (talaba_ID, baho). "Kim qanday baho oldi?" bilish uchun ikkalasini talaba_ID bo'yicha moslashtirib birlashtirasiz. JOIN — aynan shu: umumiy ustun (kalit) bo'yicha jadvallarni "ulash". INNER JOIN — faqat ikkalasida mos kelganlar; LEFT JOIN — chap ro'yxatning hammasi (mos kelmasa ham).

Nega muhim?

  • Relatsion DB kuchi — bog'langan ma'lumotni JOIN bilan birlashtirish.
  • Hisobot/analitika — window functions bilan murakkab tahlil bir so'rovda.
  • Professional daraja — JOIN/CTE/window — ekspert SQL belgisi (intervyu).
  • O'qiladigan kod — CTE murakkab so'rovni tushunarli qiladi.

2. Nazariya — chuqur tushuntirish

2.1. Nega JOIN (bog'langan ma'lumot)

Ma'lumot normalizatsiya bo'yicha bir necha jadvalda (6.1: 2.10):

text
  users:                orders:
  | id | ism  |         | id | user_id | summa  |
  | 1  | Ali  |         | 10 | 1       | 50000  |
  | 2  | Vali |         | 11 | 1       | 30000  |
                        | 12 | 2       | 80000  |

  Savol: "Har buyurtma KIMNIKI?"  orders va users'ni BIRLASHTIRISH (JOIN)
  Natija: | summa | ism  |
          | 50000 | Ali  |    orders.user_id = users.id bo'yicha mos

JOIN — ikki (yoki ko'p) jadvalni umumiy ustun (kalit — FK) bo'yicha birlashtiradi. Bu — relatsion DB'ning butun mohiyati (MongoDB'da bu — $lookup/embed — 6.3).

2.2. INNER JOIN (faqat moslar)

INNER JOIN — ikkala jadvalda ham mos keladigan qatorlar (devcapsules):

sql
SELECT o.summa, u.ism
FROM orders o
INNER JOIN users u ON o.user_id = u.id;   -- mos shart (FK = PK)
-- Faqat user_id mos keladigan buyurtmalar (yo'q user'li buyurtma chiqmaydi)
text
  INNER JOIN — kesishma (ikkalasida bor):
  users ∩ orders  faqat bog'langan qatorlar
  (buyurtmasiz user CHIQMAYDI; user'siz buyurtma CHIQMAYDI)

INNER JOIN — eng ko'p ishlatiladigan. Faqat bog'langan ma'lumot kerak bo'lganda (har buyurtma + uning egasi). ON — moslik sharti (odatda FK = PK). INNER so'zini tushirish mumkin (JOIN = INNER JOIN).

2.3. LEFT JOIN (chap jadval to'liq)

LEFT JOIN — chap jadvalning hammasi + o'ng jadvaldan mos kelganlari (mos kelmasa NULL):

sql
SELECT u.ism, o.summa
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- HAMMA foydalanuvchi (buyurtmasizlar ham — summa NULL bo'ladi)
text
  LEFT JOIN — chap to'liq:
  | ism  | summa |
  | Ali  | 50000 |
  | Ali  | 30000 |
  | Vali | 80000 |
  | Hasan| NULL  |    Hasan buyurtma qilmagan, lekin CHIQADI (NULL bilan)

LEFT JOIN — juda muhim: "hamma foydalanuvchi, buyurtmasi bor-yo'qligidan qat'i nazar". "Buyurtma qilmaganlar"ni topish: LEFT JOIN ... WHERE o.id IS NULL (mos kelmaganlar). Bu — analitikada keng ishlatiladi.

2.4. RIGHT JOIN va FULL JOIN

text
  RIGHT JOIN — o'ng jadval to'liq (LEFT'ning teskarisi — kam ishlatiladi)
  FULL JOIN  — IKKALA jadval to'liq (mos kelmaganlar ikki tomondan NULL)
sql
-- FULL JOIN (PostgreSQL; MySQL'da yo'q — UNION bilan)
SELECT u.ism, o.summa FROM users u FULL JOIN orders o ON u.id = o.user_id;
-- Hamma user + hamma order (bog'lanmaganlar ham, ikki tomondan)

RIGHT JOIN — kamdan-kam (LEFT'ni jadvallarni almashtirib ishlatish odatiy). FULL JOIN — ikkala tomonni to'liq (mos kelmaganlarni ham — masalan "user'siz buyurtmalar VA buyurtmasiz userlar"). MySQL'da FULL JOIN yo'q (LEFT UNION RIGHT bilan).

CROSS JOIN — ikki jadval Dekart ko'paytmasi (har qatorni har qatorga — ON yo'q). SELF JOIN — jadval o'zi bilan birlashtiriladi (bir jadval ichidagi bog'lanish — masalan xodim boshlig'i):

sql
-- CROSS JOIN — barcha kombinatsiyalar (o'lchamlar × ranglar  to'liq katalog)
SELECT o.nom AS olcham, r.nom AS rang FROM olchamlar o CROSS JOIN ranglar r;
-- 3 o'lcham × 4 rang = 12 qator

-- SELF JOIN — xodim va uning boshlig'i (bir "employees" jadvali)
SELECT x.ism AS xodim, b.ism AS boshliq
FROM employees x
LEFT JOIN employees b ON x.boshliq_id = b.id;   -- jadval o'ziga ulanadi (alias shart)

CROSS JOIN — ONsiz (har qatorga har qator; ehtiyot — natija N×M kattalashadi). SELF JOIN — jadvalni ikki alias bilan o'ziga ulash (iyerarxik yoki bir jadval ichidagi bog'lanish — xodim/boshliq, do'st/do'st). Alias shart (bir nomdagi jadvalni ajratish uchun).

2.5. JOIN turlari — vizual xulosa

text
  INNER JOIN:  A ∩ B  (faqat moslar)
  LEFT JOIN:   A butun + B'dan moslar
  RIGHT JOIN:  B butun + A'dan moslar
  FULL JOIN:   A butun + B butun (hammasi)

       A          B
     ┌───┐      ┌───┐
     │ ░░│██████│░░ │
     └───┘      └───┘
   LEFT    INNER    RIGHT
   (░+██)   (██)   (██+░)

2.6. Ko'p jadvalni JOIN (zanjir)

sql
-- 3 jadval: buyurtma + foydalanuvchi + mahsulot
SELECT u.ism, p.nom, oi.miqdor, oi.narx
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id
JOIN products p ON oi.product_id = p.id
WHERE o.holat = 'tugallandi';

JOIN'larni zanjirlash mumkin (ko'p jadval). Har JOIN — yangi bog'lanish. Murakkab so'rovlar shunday quriladi (e-commerce hisoboti — buyurtma+user+mahsulot). Aliaslar (u, p, oi) — qisqalik uchun.

2.7. Subquery (so'rov ichida so'rov)

Subquery — boshqa so'rov ichidagi so'rov (qavs ichida — sourcetable):

sql
-- O'rtachadan qimmat mahsulotlar
SELECT nom, narx FROM products
WHERE narx > (SELECT AVG(narx) FROM products);   -- subquery: o'rtacha narx

-- IN bilan (subquery ro'yxat qaytaradi)
SELECT ism FROM users
WHERE id IN (SELECT user_id FROM orders WHERE summa > 100000);   -- katta xarid qilganlar

Subquery turlari: skalyar (bitta qiymat — AVG), ro'yxat (IN bilan), korrelyatsion (tashqi so'rovga bog'liq — har qator uchun ishlaydi, sekin). Subquery — murakkab shartlar uchun. Ba'zan JOIN yoki CTE bilan almashtirsa, o'qiladigan/tezroq.

To'plam amallari (UNION/INTERSECT/EXCEPT) — ikki so'rov natijasini vertikal (qatorlab) birlashtiradi (JOIN — gorizontal, ustunlab):

sql
SELECT ism FROM mijozlar
UNION      SELECT ism FROM yetkazuvchilar;   -- ikkisining birlashmasi (dublikatsiz)
-- UNION ALL — dublikatni saqlaydi (tezroq); UNION — dublikatni olib tashlaydi

SELECT user_id FROM orders_2025
INTERSECT  SELECT user_id FROM orders_2026;   -- IKKALA yilda ham xarid qilganlar (kesishma)

SELECT user_id FROM orders_2025
EXCEPT     SELECT user_id FROM orders_2026;   -- 2025'da bor, 2026'da YO'Q (ayirma)

UNION (birlashma, dublikatsiz), UNION ALL (dublikat bilan — tezroq), INTERSECT (ikkalasida bor — kesishma), EXCEPT (birinchida bor, ikkinchida yo'q — ayirma). Shart: ustun soni va turlari mos kelishi kerak. MySQL'da INTERSECT/EXCEPT eski versiyalarda yo'q (8.0.31+ da bor; PostgreSQL'da azaldan). JOIN qatorlarni yonma-yon ulasa, to'plam amallari ustma-ust qo'yadi.

2.8. CTE (Common Table Expression — WITH)

CTE — WITH bilan nomlangan vaqtinchalik natija (murakkab so'rovni o'qiladigan qiladi — sourcetable):

sql
WITH faol_mijozlar AS (                          -- nomlangan vaqtinchalik (CTE)
  SELECT user_id, SUM(summa) AS jami
  FROM orders
  WHERE holat = 'tugallandi'
  GROUP BY user_id
)
SELECT u.ism, fm.jami
FROM faol_mijozlar fm                             -- CTE'ni jadval kabi ishlat
JOIN users u ON fm.user_id = u.id
WHERE fm.jami > 100000;

CTE afzalligi: murakkab so'rovni bo'laklarga bo'lib, o'qiladigan qiladi (subquery'ning ichma-ich chalkashligidan ko'ra toza). Nomlangan ("faol_mijozlar") — niyat aniq. Bir CTE'ni bir necha marta ishlatish mumkin. Murakkab so'rovlarda — afzal.

2.9. Rekursiv CTE (iyerarxiya)

CTE o'zini chaqirishi mumkin — iyerarxik ma'lumot uchun (kategoriya daraxti, tashkilot tuzilmasi):

sql
WITH RECURSIVE kategoriya_daraxti AS (
  SELECT id, nom, parent_id FROM categories WHERE parent_id IS NULL   -- ildiz
  UNION ALL
  SELECT c.id, c.nom, c.parent_id
  FROM categories c
  JOIN kategoriya_daraxti kd ON c.parent_id = kd.id                   -- bolalar (rekursiya)
)
SELECT * FROM kategoriya_daraxti;

Rekursiv CTE — daraxt/graf (3.6, 3.7) ni SQL'da kezish (kategoriya subkategoriya ...). MongoDB'da bu qiyin; SQL'da — rekursiv CTE. Murakkab, lekin kuchli (iyerarxiya, BOM, do'stlar zanjiri).

2.10. Window functions — nima va nega (eng kuchli)

Window function — qatorlar to'plami bo'yicha hisoblash, lekin qatorlarni saqlab (GROUP BY qatorlarni yig'adi; window — saqlaydi — sqlnoir):

text
  GROUP BY (yig'adi):              Window (saqlaydi + qo'shadi):
  | kategoriya | jami |           | nom | narx | kategoriya | kategoriya_ortacha |
  | telefon    | 5000 |           | A   | 100  | telefon    | 150                |
  | kiyim      | 3000 |           | B   | 200  | telefon    | 150                |
  (qatorlar yo'qoldi)             (har qator turibdi + hisob qo'shildi)
sql
SELECT nom, narx, kategoriya,
  AVG(narx) OVER (PARTITION BY kategoriya) AS kategoriya_ortacha   -- window!
FROM products;
-- Har mahsulot + uning kategoriyasidagi o'rtacha narx (qatorlar saqlanadi)

OVER (...) — window'ni belgilaydi (window function'ning kaliti). PARTITION BY — guruhlash (GROUP BY kabi, lekin qator saqlanadi). Bu — window function'ning mohiyati: kontekst (boshqa qatorlar) bilan, lekin har qatorni saqlab.

2.11. Ranking funksiyalari (ROW_NUMBER, RANK, DENSE_RANK)

sql
SELECT nom, narx,
  ROW_NUMBER() OVER (ORDER BY narx DESC) AS qator_raqam,   -- 1,2,3,4 (noyob)
  RANK() OVER (ORDER BY narx DESC) AS reyting,             -- 1,2,2,4 (teng  sakraydi)
  DENSE_RANK() OVER (ORDER BY narx DESC) AS zich_reyting   -- 1,2,2,3 (teng  sakramaydi)
FROM products;

Uch ranking farqi (datalemur): ROW_NUMBER — har doim noyob (1,2,3,4 — teng bo'lsa ham); RANK — teng qiymatga bir xil, keyin sakraydi (1,2,2,4); DENSE_RANK — teng qiymatga bir xil, sakramaydi (1,2,2,3). "Top 3 mahsulot", "har kategoriyada 1-o'rin" — shu funksiyalar.

2.12. PARTITION BY (guruhlab oynalash)

sql
-- Har kategoriyada eng qimmat 3 mahsulot
SELECT * FROM (
  SELECT nom, kategoriya, narx,
    ROW_NUMBER() OVER (PARTITION BY kategoriya ORDER BY narx DESC) AS rn   -- har kategoriyada qayta
  FROM products
) t
WHERE rn <= 3;                                   -- har guruhda top 3 (2.14)

PARTITION BY — window'ni guruhlarga bo'ladi (har kategoriya alohida). ROW_NUMBER() OVER (PARTITION BY kategoriya ORDER BY narx DESC) — har kategoriyada narx bo'yicha 1,2,3. "Har guruhda top N" — klassik naqsh (top-N-per-group).

2.13. Running total va LAG/LEAD

sql
-- Yugurib boruvchi yig'indi (running total)
SELECT sana, summa,
  SUM(summa) OVER (ORDER BY sana) AS jami_yigindi   -- har kungacha to'plangan
FROM kunlik_savdo;

-- Oldingi/keyingi qator bilan taqqoslash (LAG/LEAD)
SELECT sana, summa,
  LAG(summa) OVER (ORDER BY sana) AS kechagi,        -- oldingi qator
  summa - LAG(summa) OVER (ORDER BY sana) AS farq    -- o'sish
FROM kunlik_savdo;

Running total (SUM() OVER (ORDER BY ...)) — to'plangan yig'indi (balans tarixi, kumulyativ savdo). LAG/LEAD — oldingi/keyingi qator (o'sish hisoblash, trend). Bu — analitikaning kuchli vositalari (dashboard, moliyaviy hisobot).

2.14. Window function'ni filtrlash (CTE bilan)

Window function WHEREda ishlatib bo'lmaydi (WHERE'dan keyin hisoblanadi — sqlnoir):

sql
--  Ishlamaydi (WHERE window'dan oldin)
SELECT nom, ROW_NUMBER() OVER (...) AS rn FROM products WHERE rn <= 3;

--  CTE/subquery bilan o'rab, tashqi qatlamda filtr (2.12)
WITH ranked AS (
  SELECT nom, ROW_NUMBER() OVER (ORDER BY narx DESC) AS rn FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

Bajarilish tartibi: WHERE GROUP BY HAVING window SELECT. Window oxirroq hisoblanadi, shuning uchun WHERE'da yo'q. CTE/subquery bilan o'rab, tashqi qatlamda filtrlash (2.8, 2.12). Bu — eng ko'p uchraydigan window xatosi.

2.15. Aggregate vs Window (taqqoslash)

text
  GROUP BY (aggregate):  qatorlarni YIG'ADI (10 qator  3 guruh)
  Window function:       qatorlarni SAQLAYDI (10 qator  10 qator + hisob)

  GROUP BY: "har kategoriya jami" (jami natija)
  Window:   "har mahsulot + kategoriya o'rtachasi" (tafsilot + kontekst)

2.16. Node.js'da murakkab so'rov (pg/mysql2)

js
// Murakkab so'rov ham parametrli (injection — 6.4: 2.15, 14)
const { rows } = await pool.query(`
  WITH faol AS (
    SELECT user_id, SUM(summa) AS jami FROM orders
    WHERE created_at >= $1 GROUP BY user_id
  )
  SELECT u.ism, f.jami,
    RANK() OVER (ORDER BY f.jami DESC) AS reyting
  FROM faol f JOIN users u ON f.user_id = u.id
`, ["2026-01-01"]);

Murakkab SQL (JOIN/CTE/window) ham Node.js'da parametrli (foydalanuvchi qiymatlari — $1). Ustun/jadval nomlari kodda (qattiq), qiymatlar parametr (xavfsiz — 14). ORM 6.14-bob bunday so'rovlarni qiyinroq ifodalaydi — ko'pincha xom SQL.

2.17. Performance va best practices (6.13 ko'prik)

text
   JOIN ustunlariga indeks (FK — 6.6: 2.11) — JOIN tezligi
   Faqat kerakli ustunlar (SELECT * emas — 6.4)
   Subquery o'rniga JOIN/CTE (ko'pincha tezroq, o'qiladigan)
   Window — katta ma'lumotda xotira (ehtiyot)
   EXPLAIN bilan tekshiring 6.13-bob
   CTE — o'qish uchun; lekin ba'zi DB'da optimizatsiya farqi

3. Sintaksis — tez ma'lumotnoma

sql
-- JOIN (2.2-2.4)
SELECT ... FROM a JOIN b ON a.id = b.a_id;           -- INNER (moslar)
SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;      -- LEFT (a butun)

-- Subquery (2.7)
WHERE x > (SELECT AVG(x) FROM t);  WHERE id IN (SELECT ... FROM ...);

-- CTE (2.8)
WITH nom AS (SELECT ...) SELECT ... FROM nom;

-- Window (2.10-2.13)
ROW_NUMBER() OVER (PARTITION BY kat ORDER BY narx DESC)   -- ranking
SUM(x) OVER (ORDER BY sana)                               -- running total
LAG(x) OVER (ORDER BY sana)                               -- oldingi qator

-- Window filtri  CTE/subquery o'rab, tashqida WHERE (2.14)

4. Batafsil kod namunalari

Misol 1 — JOIN turlari (2.2-2.4)

sql
-- INNER — buyurtmasi bor foydalanuvchilar (2.2)
SELECT u.ism, o.summa
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT — HAMMA foydalanuvchi (buyurtmasizlar ham — 2.3)
SELECT u.ism, COUNT(o.id) AS buyurtma_soni
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.ism;                              -- har user buyurtma soni (0 ham)

-- Buyurtma QILMAGAN foydalanuvchilar (LEFT + IS NULL — 2.3)
SELECT u.ism
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;                               -- mos kelmaganlar

Misol 2 — Ko'p jadval JOIN (e-commerce — 2.6)

sql
-- To'liq buyurtma tafsiloti (4 jadval — 2.6)
SELECT
  o.id AS buyurtma, u.ism AS mijoz, p.nom AS mahsulot,
  oi.miqdor, oi.narx, (oi.miqdor * oi.narx) AS jami
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.holat = 'tugallandi'
ORDER BY o.created_at DESC;

Misol 3 — Subquery (2.7)

sql
-- O'rtachadan qimmat mahsulotlar (skalyar subquery — 2.7)
SELECT nom, narx FROM products
WHERE narx > (SELECT AVG(narx) FROM products);

-- Hech narsa sotib olmagan foydalanuvchilar (NOT IN subquery)
SELECT ism FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

-- Korrelyatsion subquery (har user uchun oxirgi buyurtma)
SELECT u.ism,
  (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS oxirgi_buyurtma
FROM users u;

Misol 4 — CTE (o'qiladigan murakkab so'rov — 2.8)

sql
-- Top mijozlar (CTE bilan — 2.8)
WITH mijoz_statistika AS (
  SELECT user_id, SUM(summa) AS jami, COUNT(*) AS buyurtma_soni, AVG(summa) AS ortacha
  FROM orders
  WHERE holat = 'tugallandi'
  GROUP BY user_id
)
SELECT u.ism, u.email, ms.jami, ms.buyurtma_soni, ms.ortacha
FROM mijoz_statistika ms
JOIN users u ON ms.user_id = u.id
WHERE ms.jami > 500000                            -- CTE natijasini filtrlash
ORDER BY ms.jami DESC;

Misol 5 — Rekursiv CTE (kategoriya daraxti — 2.9)

sql
-- Kategoriya iyerarxiyasi (rekursiv — 2.9)
WITH RECURSIVE daraxt AS (
  -- Boshlang'ich (ildiz kategoriyalar)
  SELECT id, nom, parent_id, 1 AS daraja, nom::TEXT AS yol
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  -- Rekursiv (bolalar)
  SELECT c.id, c.nom, c.parent_id, d.daraja + 1, d.yol || ' > ' || c.nom
  FROM categories c
  JOIN daraxt d ON c.parent_id = d.id
)
SELECT daraja, yol FROM daraxt ORDER BY yol;
-- Natija: "Elektronika", "Elektronika > Telefon", "Elektronika > Telefon > Smartfon"

Misol 6 — Ranking (top N — 2.11)

sql
-- Eng qimmat mahsulotlar reytingi (2.11)
SELECT nom, narx,
  ROW_NUMBER() OVER (ORDER BY narx DESC) AS raqam,
  RANK() OVER (ORDER BY narx DESC) AS reyting,
  DENSE_RANK() OVER (ORDER BY narx DESC) AS zich_reyting
FROM products
ORDER BY narx DESC;

-- Top 3 (window'ni CTE bilan filtrlash — 2.14)
WITH ranked AS (
  SELECT nom, narx, ROW_NUMBER() OVER (ORDER BY narx DESC) AS rn FROM products
)
SELECT nom, narx FROM ranked WHERE rn <= 3;

Misol 7 — Har guruhda top N (PARTITION BY — 2.12)

sql
-- Har kategoriyada eng qimmat 3 mahsulot (2.12)
WITH ranked AS (
  SELECT nom, kategoriya, narx,
    ROW_NUMBER() OVER (PARTITION BY kategoriya ORDER BY narx DESC) AS rn   -- har kategoriya
  FROM products
)
SELECT kategoriya, nom, narx FROM ranked WHERE rn <= 3
ORDER BY kategoriya, narx DESC;

Misol 8 — Running total va trend (LAG — 2.13)

sql
-- Kunlik savdo + yugurib boruvchi yig'indi + o'sish (2.13)
SELECT
  sana,
  summa,
  SUM(summa) OVER (ORDER BY sana) AS jami_yigindi,           -- running total
  LAG(summa) OVER (ORDER BY sana) AS kechagi,                -- oldingi kun
  summa - LAG(summa) OVER (ORDER BY sana) AS farq,           -- o'sish/pasayish
  ROUND(AVG(summa) OVER (ORDER BY sana ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0)
    AS hafta_ortacha                                          -- 7 kunlik harakatlanuvchi o'rtacha
FROM kunlik_savdo
ORDER BY sana;

Misol 9 — Node.js'da (parametrli — 2.16)

js
// Top mijozlar (CTE + window + parametrli — 2.16, 14)
export async function topMijozlar(sanadan, limit) {
  const { rows } = await pool.query(`
    WITH stat AS (
      SELECT user_id, SUM(summa) AS jami, COUNT(*) AS soni
      FROM orders
      WHERE holat = 'tugallandi' AND created_at >= $1
      GROUP BY user_id
    )
    SELECT u.ism, s.jami, s.soni,
      RANK() OVER (ORDER BY s.jami DESC) AS reyting
    FROM stat s JOIN users u ON s.user_id = u.id
    ORDER BY s.jami DESC
    LIMIT $2
  `, [sanadan, limit]);                              // parametrli (14)
  return rows;
}

5. To'g'ri va noto'g'ri holatlar

1) JOIN o'rniga ko'p alohida so'rov (N+1)

js
//  har user uchun alohida so'rov (N+1 — sekin)
const users = await q("SELECT * FROM users");
for (const u of users) u.orders = await q("SELECT * FROM orders WHERE user_id = $1", [u.id]);

//  bitta JOIN
await q("SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id");

2) Window function'ni WHERE'da

sql
--  ishlamaydi (2.14)
SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t WHERE rn <= 3;

--  CTE bilan o'rab
WITH r AS (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t) SELECT * FROM r WHERE rn <= 3;

3) GROUP BY'da window kerak bo'lganda (qator yo'qoladi)

sql
--  GROUP BY qatorlarni yig'adi (tafsilot yo'qoladi — 2.15)
SELECT kategoriya, AVG(narx) FROM products GROUP BY kategoriya;

--  window (har qator + o'rtacha)
SELECT nom, narx, AVG(narx) OVER (PARTITION BY kategoriya) FROM products;

4) INNER JOIN kerak bo'lganda LEFT (yoki teskari)

sql
--  buyurtmasizlar ham chiqadi (NULL — kerak bo'lmasa)
SELECT u.ism, o.summa FROM users u LEFT JOIN orders o ON u.id = o.user_id;

--  faqat buyurtmasi borlar — INNER
SELECT u.ism, o.summa FROM users u INNER JOIN orders o ON u.id = o.user_id;

5) JOIN ustuniga indekssiz

text
 FK indekssiz  JOIN sekin (katta jadvalda — 2.17)
 JOIN ustunlariga (FK) indeks (6.6: 2.11)

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — column reference is ambiguous

Sababi: ikki jadvalda bir xil ustun nomi (id), alias yo'q. Yechimi: alias bilan aniqlashtiring (u.id, o.id).

Xato 2 — JOIN natija ko'paydi (dublikat)

Sababi: one-to-many JOIN (bir user — ko'p buyurtma user takrorlanadi). Yechimi: kutilgan natija (har buyurtma qator); yoki GROUP BY/agregat.

Xato 3 — Window WHEREda ishlamaydi

Sababi: window WHERE'dan keyin 2.14-bob. Yechimi: CTE/subquery bilan o'rab, tashqida filtr.

Xato 4 — RANK kutilmagan raqamlar (sakraydi)

Sababi: RANK teng qiymatda sakraydi 2.11-bob. Yechimi: sakramaslik kerak bo'lsa — DENSE_RANK; noyob kerak bo'lsa — ROW_NUMBER.

Xato 5 — Rekursiv CTE cheksiz (loop)

Sababi: rekursiya to'xtash sharti yo'q (sikl — 2.9). Yechimi: ma'lumotda sikl yo'qligini tekshiring; daraja chegarasi qo'shing.

Xato 6 — FULL JOIN MySQL'da ishlamaydi

Sababi: MySQL FULL JOIN'ni qo'llamaydi 2.4-bob. Yechimi: LEFT UNION RIGHT; yoki PostgreSQL.

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

  • SQL asoslari 6.4-bob: SELECT/WHERE — bu yerda kengaytirilgan.
  • MySQL/PostgreSQL (6.5, 6.6): JOIN/CTE/window ikkalasida (kichik farq).
  • MongoDB 6.3-bob: $lookup (JOIN), aggregation (GROUP BY) ekvivalenti.
  • Tree/Graph (3.6, 3.7): rekursiv CTE — iyerarxiya.
  • Normalizatsiya 6.10-bob: JOIN — normalizatsiyaning narxi/kuchi.
  • Query optimization 6.13-bob: JOIN indeks, EXPLAIN.
  • Dashboard (11): window funksiyalar — analitika.
  • ORM 6.14-bob: murakkab so'rov — ko'pincha xom SQL.

8. Eng yaxshi amaliyotlar (best practices)

  • To'g'ri JOIN turi (INNER — moslar; LEFT — chap to'liq — 2.2, 2.3).
  • JOIN ustunlariga indeks (FK — tezlik — 2.17, 6.6: 2.11).
  • CTE bilan murakkab so'rovni o'qiladigan qiling (subquery chalkashligi o'rniga — 2.8).
  • Window function — tafsilot + kontekst (GROUP BY qator yo'qotadi — 2.15).
  • Window'ni CTE/subquery bilan filtrlang (WHERE'da yo'q — 2.14).
  • To'g'ri ranking (ROW_NUMBER noyob, RANK sakraydi, DENSE_RANK sakramaydi — 2.11).
  • N+1 o'rniga JOIN (ko'p so'rov o'rniga bitta — 1-holat).
  • Alias bilan aniqlik (ambiguous ustun — Xato 1).
  • Murakkab so'rov ham parametrli (injection — 2.16, 14).
  • EXPLAIN bilan tekshiring 6.13-bob; rekursiv CTE'da to'xtash sharti 2.9-bob.

9. Amaliy loyiha: "Analitik SQL Hisobotlar"

SQL'ning kuchli imkoniyatlarini mustahkamlash.

Maqsad

JOIN, CTE va window functions bilan murakkab analitik hisobotlar yozish (e-commerce dashboard).

Talablar (requirements)

  1. JOIN hisobotlari: buyurtma+user+mahsulot (ko'p jadval); buyurtma qilmaganlar (LEFT+NULL — Misol 1, 2).
  2. Subquery: o'rtachadan qimmat; hech narsa olmaganlar (Misol 3, 2.7).
  3. CTE: top mijozlar statistikasi (o'qiladigan — Misol 4, 2.8).
  4. Rekursiv CTE: kategoriya daraxti (Misol 5, 2.9).
  5. Ranking: mahsulot reytingi; top 3 (ROW_NUMBER/RANK/DENSE_RANK — Misol 6, 2.11).
  6. PARTITION BY: har kategoriyada top 3 (Misol 7, 2.12).
  7. Running total + LAG: kunlik savdo, o'sish, harakatlanuvchi o'rtacha (Misol 8, 2.13).
  8. Node.js: murakkab so'rovni parametrli ishlat (Misol 9, 2.16).
  9. Window filtri: CTE bilan to'g'ri 2.14-bob.

Maslahatlar (hint)

  • INNER (moslar) vs LEFT (chap to'liq) — to'g'ri tanlang (2.2, 2.3, 4-holat).
  • Window filtri: CTE bilan o'rab (2.14, 3-xato).
  • Ranking farqi: ROW_NUMBER/RANK/DENSE_RANK 2.11-bob.
  • PARTITION BY — har guruhda qayta 2.12-bob.
  • LAG — oldingi qator 2.13-bob.
  • JOIN ustuniga indeks (2.17, 5-holat).
  • Murakkab so'rov ham parametrli 2.16-bob.

"Tayyor" mezonlari (acceptance criteria)

  • JOIN turlari (INNER/LEFT) to'g'ri ishlatilgan.
  • Ko'p jadval JOIN hisoboti.
  • Subquery (skalyar, IN, korrelyatsion).
  • CTE bilan murakkab so'rov.
  • Rekursiv CTE (iyerarxiya).
  • Ranking (3 turi) + top N.
  • PARTITION BY (har guruhda top N).
  • Running total + LAG.
  • Window CTE bilan filtrlangan.
  • Node.js'da parametrli.

Yechim kodi ataylab berilmagan — bu loyihani o'zingiz yozib ko'ring.

10. Xulosa va keyingi bobga ko'prik

Bu bobda SQL'ning ekspert qismini o'rgandik:

  • JOIN (bog'langan jadvallar — 2.1): INNER (moslar — 2.2), LEFT (chap to'liq — 2.3), RIGHT/FULL/CROSS/SELF 2.4-bob; ko'p jadval 2.6-bob.
  • Subquery (so'rov ichida — skalyar/IN/korrelyatsion — 2.7); to'plam amallari (UNION/INTERSECT/EXCEPT — 2.7); CTE (WITH — o'qiladigan — 2.8); rekursiv CTE (iyerarxiya — 2.9).
  • Window functions (qator saqlab hisoblash — 2.10): ranking (ROW_NUMBER/RANK/DENSE_RANK — 2.11), PARTITION BY (top-N-per-group — 2.12), running total/LAG 2.13-bob; CTE bilan filtr 2.14-bob.
  • Aggregate vs window (yig'adi vs saqlaydi — 2.15); Node.js'da parametrli 2.16-bob.

Keyingi bob — 6.8-bob: Stored procedures, functions, triggers, views. SQL so'rovlarini ekspert darajada bildik; endi DB ichida mantiq saqlash — stored procedures (saqlangan protseduralar), functions (funksiyalar), triggers (avtomatik ishga tushuvchi), va views (virtual jadvallar) — o'rganamiz. Bu — DB'ni shunchaki "ombor"dan "aqlli tizim"ga aylantiradi.

Foydalanilgan rasmiy/ishonchli manbalar

  • SQLNoir — SQL Window Functions visual guide 2026; DataLemur — ranking funksiyalar
  • DevCapsules — SQL JOIN types; Sourcetable — outer joins, subqueries, window functions
  • PostgreSQL docs — JOIN, WITH (CTE), window functions

Izohlar (0)

Izoh yozish uchun kiring.

  • Hozircha izoh yo'q. Birinchi bo'ling!
6.7-bob: SQL chuqur — JOIN turlari, subquery, CTE, window functions — Wisar