Ачааллж байна...
Энэ хичээл бол урамшуулал юм. Түвшин 1-д та SQL-ийн үндсэн өгүүлбэрүүдийг, Түвшин 2-т өгөгдлийн санг зөв зохиохыг, Түвшин 3-ын өмнөх хичээлүүдэд салгаж хадгалсан өгөгдлийг JOIN-оор нийлүүлж, aggregate болон GROUP BY-ээр тоо гаргах, HAVING-ээр бүлгийг шүүхийг сурсан. Тэдгээр бүгд нэг бүрчлэн, тусдаа хичээлүүдэд ирсэн. Энэ хичээлд бүгдийг нэг дор хослуулж, номын сангийн эрхлэгчид бодитоор хэрэгтэй дөрвөн тайланг бүтээнэ.
Тайлан бүрийг бид алхам алхмаар угсарна: эхлээд аль хүснэгтүүд хэрэгтэйг шийдэж, дараа нь JOIN-оор нийлүүлж, GROUP BY-ээр сагсалж, aggregate-ээр тоолж, эцэст нь эрэмбэлнэ. Query бүрийг Бүлэг 1-ээс хойшхи дадлаараа монголоор чангаар уншина — эдгээр нь урт query-нүүд тул чангаар унших нь ойлгох хамгийн сайн хэрэгсэл болно.
Мөн энэ хичээлд нэг шударга урхитай нүүр тулна: Бүлэг 7-ийн "мөр үржилт" нь COUNT-ийг DISTINCT-ээр засаж болдог бол SUM-ийг засах боломжгүй тохиолдол бий. Тэр урхийг санаатайгаар нэг тайланд гаргаж, дараагийн бүлгийн эхний хичээл түүнийг хэрхэн шийдэхийг урьдчилан харна. library2.db-г нээгээрэй — бид зөвхөн уншина.
Барилгачин нэг ч шинэ багаж аваагүй ч өмнө нь тус бүрд нь сурсан алх, хөрөө, түвшин, метр дөрөвийг нэг өдөр хамт хэрэглэж анхны бүтэн ширээгээ хийх мөч бий. Багаж бүр таниас өөр юу ч шаардахгүй — гэвч тэдгээрийг ямар дарааллаар, хаана хэрэглэхийг шийдэх нь шинэ ур чадвар юм. Энэ хичээл тийм өдөр. Шинэ SQL түлхүүр үг бараг байхгүй (нэг жижиг нэмэлтээс бусад), харин бүх багаж нэг бүтээлд нийлнэ.
Эрхлэгчийн асуулт: "Аль номнууд хамгийн их зээлэгдсэн бэ? Номын нэр, зохиогч, зээлийн тоог хамт хармаар байна." Хэрэгтэй хүснэгтүүд: loans (зээлийн тоо), books (нэр), book_authors ба authors (зохиогч). Бүлэг 7-ийн сүүлийн хичээлийн Жишээ 5-ыг санаарай — номыг зохиогчидтой нийлүүлэхэд хоёр зохиогчтой ном хоёр мөр болдог. Тиймээс зээлийг тоолохдоо мөр биш, зээлийн PRIMARY KEY-ийг давхардалгүйгээр тоолно (Хичээл 1-ийн Жишээ 6). Query:
sql
SELECT b.title,
group_concat(DISTINCT a.name) AS authors,
COUNT(DISTINCT l.id) AS times_loaned
FROM loans AS l
INNER JOIN books AS b ON l.book_id = b.id
LEFT JOIN book_authors AS ba ON ba.book_id = b.id
LEFT JOIN authors AS a ON ba.author_id = a.id
GROUP BY b.id, b.title
ORDER BY times_loaned DESC, b.title;Үр дүн:
title | authors | times_loaned
-------------------------+--------------------------+-------------
Clean Code | Robert C. Martin | 3
Тунгалаг Тамир | Ч.Лодойдамба | 2
Python Crash Course | Eric Matthes | 1
The Pragmatic Programmer | Andrew Hunt,David Thomas | 1
Миний нутаг | Д.Нацагдорж | 1Энэ query юу гэж хэлж байна вэ гэвэл: "зээлүүдийг номтой нийлүүлж, номыг зохиогчийн холбоос ба зохиогчидтой (холбоосгүй номыг ч хадгалан) нийлүүлээд, номоор сагсалж, ном бүрийн нэр, давхардалгүй зохиогчдын нэрсийг таслалаар залгасан жагсаалт, давхардалгүй зээлийн тоог зээлийн тоогоор ихээс бага руу, тэнцвэл нэрээр эрэмбэлж харуул."
Шинэ хэрэгсэл ганцхан: group_concat(). Энэ нь SQLite-ийн aggregate функц бөгөөд сагсны текстэн утгуудыг таслалаар залгаж нэг текст болгодог — COUNT нь тоолдог бол group_concat нь залгадаг. DISTINCT-ийг дотор нь бичсэн нь Тэмүүлэнгийн нэг зээл хоёр зохиогчтой хослож мөр үржүүлсэн ч нэр бүр нэг удаа л гарахын тулд. Залгасан нэрсийн дараалал баталгаагүй гэдгийг анзаараарай — “David Thomas,Andrew Hunt” гэж гарч болно. PostgreSQL-д ижил үүргийг string_agg() функц гүйцэтгэдэг тул Түвшин 4-т энэ мөрийг солих шаардлагатай болно.
Энд Бүлэг 6-ийн нэг дүрэмтэй зөрчилдөх мэт зүйлд хариулъя. Бид "нэг нүдэнд жагсаалт хадгалж болохгүй" гэж сурсан — гэтэл энд “Andrew Hunt,David Thomas” гэсэн жагсаалттай нүд гарлаа. Ялгаа нь хадгалах ба харуулах хоёрын хооронд. Өгөгдлийн санд зохиогч бүр тусдаа мөрөнд, book_authors холбогч хүснэгтээр зөв хадгалагдсан хэвээр. group_concat нь зөвхөн тайлангийн харагдацад, хүнд уншихад тохиромжтойгоор тэднийг нэг нүдэнд залгасан — Бүлэг 7-ийн JOIN гэж юу вэ хичээлийн "үр дүнгийн цаас хэзээ ч засагдахгүй" гэсэн санаа. Хадгалахдаа салга, уншихдаа нийлүүл — энэ удаад нийлүүлэлт нэг нүдний түвшинд хийгдлээ.
Ер нь хамгийн их зээлэгдсэн ном нь Clean Code — гурван өөр гишүүн зээлжээ. Хэрэв зөвхөн эхний гурвыг хүсвэл төгсгөлд LIMIT 3 нэмэхэд хангалттай; тэнцээг нэрээр шийдсэн тул гурав дахь байранд Python Crash Course тогтвортой гарна.
Эрхлэгчийн асуулт: "Гишүүн бүрийн хувьд: нийт хэдэн ном зээлсэн, одоо гарт хэд байна, хэд нь хоцорч буцаагдсан, хамгийн сүүлд хэзээ зээлсэн бэ? Ном зээлээгүй гишүүд ч жагсаалтад байх ёстой." Энэ бол Бүлэг 6-д Дулмаагийн loan_count-оос эхэлсэн аялалын бүрэн хувилбар.
"Хэдэн нь хоцорсон" гэсэн асуулт нь сагсны дотор зөвхөн нөхцөлд таарсан мөрүүдийг тоолохыг шаарддаг. Үүнд нэг жижиг шинэ хэрэгсэл хэрэгтэй: CASE. Энэ нь SQL-ийн "хэрэв ... бол ... үгүй бол ..." — Python-ы if ... else-тэй адил — бөгөөд мөр бүрт утга сонгодог. CASE WHEN нөхцөл THEN 1 ELSE 0 END нь нөхцөл үнэн бол 1, бусад үед 0 өгнө; эдгээрийг SUM-ээр нийлбэрлэвэл нөхцөлд таарсан мөрийн тоо гарна. Query:
sql
SELECT m.name,
COUNT(l.id) AS total_loans,
COUNT(l.id) - COUNT(l.returned_on) AS on_loan_now,
SUM(CASE WHEN l.returned_on > l.due_on THEN 1 ELSE 0 END) AS late_returns,
MAX(l.loaned_on) AS last_loan
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY total_loans DESC, m.name;Үр дүн:
name | total_loans | on_loan_now | late_returns | last_loan
---------+-------------+-------------+--------------+-----------
Бат | 3 | 1 | 1 | 2026-09-20
Номин | 2 | 0 | 0 | 2026-10-05
Сараа | 2 | 1 | 0 | 2026-10-01
Тэмүүлэн | 1 | 1 | 0 | 2026-10-03
Ану | 0 | 0 | 0 | NULLЭнэ query юу гэж хэлж байна вэ гэвэл: "бүх гишүүнийг зээлүүдтэй нь (зээлгүйг нь ч хадгалан) нийлүүлж, гишүүнээр сагсалж, гишүүн бүрийн нэр, нийт зээлийн тоо, буцаагаагүй зээлийн тоо, хоцорч буцаасан зээлийн тоо, хамгийн сүүлийн зээлсэн огноог нийт зээлээр ихээс бага руу, тэнцвэл нэрээр эрэмбэлж харуул."
Багана бүрийг Батын сагсаар мөшгье — түүний сагсанд гурван мөр: зээл 1 (буцаасан 09-15, хугацаа 09-17), зээл 2 (буцаасан 09-30, хугацаа 09-24), зээл 4 (буцаагаагүй). COUNT(l.id) — гурван зээлийн id, гурав. COUNT(l.returned_on) — буцаасан огноотой хоёр мөр; 3 − 2 = 1 зээл гарт. Энэ бол Хичээл 1-ийн COUNT(*) ба COUNT(багана)-ийн ялгааг арифметик болгон ашигласан арга. CASE мөр бүрт: зээл 1 — 09-15 > 09-17? үгүй, 0; зээл 2 — 09-30 > 09-24? тийм, 1; зээл 4 — NULL > 09-20? "мэдэхгүй", тэгэхээр WHEN үнэн биш тул ELSE 0. Нийлбэр 1. MAX — гурван огнооноос хамгийн сүүлийнх нь 09-20.
Ану-гийн мөр LEFT JOIN-ийн бүх сургамжийг нэгтгэж байна. Түүний сагсанд нэг мөр, loans-ийн бүх нүд NULL. COUNT(l.id) — 0 (Хичээл 2-ын засвар). CASE — NULL нөхцөл ELSE руу орж 0. MAX — NULL утгуудын хамгийн их нь NULL: "хэзээ ч зээлээгүй." Таван гишүүн, үнэн зөв таван мөр — нэг query, ямар ч гараар хадгалсан тоолуургүйгээр. Дулмаагийн хүснэгт энэ тайланг гаргаж чадах байсан ч ганц имэйл солигдох эсвэл нэг зээл устахад худал болох байсан.
Түвшин 2-ын удирдамжтай дасгалд бид номын хүснэгтэд "одоо байгаа хувь" баганыг санаатайгаар нэмээгүй: энэ нь нийт хувь ба буцаагаагүй зээлээс тооцоолж болох утга тул Бүлэг 6-ийн Дүрэм 3-ын дагуу хадгалахгүй, "Бүлэг 8-д тооцоолно" гэж амласан. Одоо тэр амлалтаа биелүүлье. Query:
sql
SELECT b.title,
b.copies,
COUNT(l.id) AS on_loan,
b.copies - COUNT(l.id) AS available
FROM books AS b
LEFT JOIN loans AS l ON l.book_id = b.id AND l.returned_on IS NULL
GROUP BY b.id, b.title, b.copies
ORDER BY b.id;Үр дүн:
title | copies | on_loan | available
-------------------------+--------+---------+----------
Монголын нууц товчоо | 2 | 0 | 2
Тунгалаг Тамир | 3 | 1 | 2
Учиртай гурван толгой | 1 | 0 | 1
Clean Code | 4 | 1 | 3
Python Crash Course | 5 | 0 | 5
The Pragmatic Programmer | 2 | 1 | 1
Clean Architecture | 1 | 0 | 1
Миний нутаг | 2 | 0 | 2Энэ query юу гэж хэлж байна вэ гэвэл: "бүх номыг зөвхөн буцаагаагүй зээлүүдтэй нь (зээлгүйг нь ч хадгалан) нийлүүлж, номоор сагсалж, ном бүрийн нэр, нийт хувь, гарт байгаа зээлийн тоо, нийт хувиас гарт байгааг хассан үлдэгдлийг харуул."
Энэ query-д Бүлэг 7-ийн LEFT JOIN хичээлийн хамгийн нарийн сургамж ажиллаж байна: l.returned_on IS NULL нөхцөл WHERE-д биш, ON-д бичигдсэн. Хэрэв WHERE-д бичсэн бол буцаагдсан зээлтэй, гэхдээ одоо гарт ганц ч хувь байхгүй номнууд (Python Crash Course, Миний нутаг) LEFT JOIN-ийн хадгалсан мөрөө алдаж, тайлангаас алга болох байсан. ON-д бичсэн тул "аль зээлүүд тааралдах вэ" — зөвхөн гарт байгаа нь — гэдгийг шийдэж, бүх найман ном үлдсэн.
b.copies-ийг GROUP BY-д нэмсэн нь Хичээл 2-ын гол дүрмийн дагуу: энэ багана SELECT-д aggregate бус байдлаар бичигдсэн тул сагсны шошго байх ёстой. b.id ном бүрийг тодорхойлох тул copies нь сагс бүрт ганц утгатай — сагсны тоо өөрчлөгдөхгүй. Энэ тайлан бол номын сангийн веб сайтын "одоо зээлэх боломжтой" гэсэн тэмдэглэгээний цаана ажиллах query: хэзээ ч хуучрахгүй, учир нь хадгалагдсан тоолуур байхгүй — зээл бүрийн дараа шинэ үнэн гарна.
Эрхлэгчийн сүүлийн асуулт: "Жанр бүрт хэдэн ном, нийт хэдэн хувь байна, хэдэн удаа зээлэгдсэн бэ?" Энэ нь өмнөх тайлангуудтай адилхан харагдана. Query:
sql
SELECT b.genre,
COUNT(DISTINCT b.id) AS book_count,
SUM(b.copies) AS total_copies,
COUNT(l.id) AS times_loaned
FROM books AS b
LEFT JOIN loans AS l ON l.book_id = b.id
GROUP BY b.genre
ORDER BY b.genre;Үр дүн:
genre | book_count | total_copies | times_loaned
------------+------------+--------------+-------------
Жүжиг | 1 | 1 | 0
Програмчлал | 4 | 20 | 5
Роман | 1 | 6 | 2
Түүх | 1 | 2 | 0
Шүлэг | 1 | 2 | 1Энэ query юу гэж хэлж байна вэ гэвэл: "бүх номыг зээлүүдтэй нь нийлүүлж, жанраар сагсалж, жанр бүрийн давхардалгүй номын тоо, хувийн нийлбэр, зээлийн тоог харуул." book_count ба times_loaned зөв. Гэвч total_copies-ийг Хичээл 2-ын Жишээ 3-тай харьцуулаарай: тэнд програмчлал 12, роман 3 байсан. Энд 20 ба 6. Юу болов?
Програмчлалын сагсыг мөшгье. JOIN-ий дараа Clean Code (4 хувь) гурван зээлтэй тул гурван мөр болсон — тэдгээр мөр бүрт b.copies нь 4. Python Crash Course (5) нэг мөр, The Pragmatic Programmer (2) нэг мөр, Clean Architecture (1) зээлгүй ч LEFT JOIN-оор нэг мөр. SUM эдгээр зургаан мөрийн copies-ийг нэмсэн: 4 + 4 + 4 + 5 + 2 + 1 = 20. Clean Code-ийн хувиуд гурав дахин тоологджээ. "Тунгалаг Тамир" (3 хувь) хоёр зээлтэй тул 3 + 3 = 6. Энэ бол Бүлэг 7-ийн мөр үржилт, гэхдээ SUM-д.
COUNT-ийн хувьд бид COUNT(DISTINCT b.id)-ээр зассан. SUM-ийг ижил аргаар засаж болох уу? SUM(DISTINCT b.copies) гэж бичвэл програмчлалд 4 + 5 + 2 + 1 = 12 — санамсаргүй зөв гарна. Гэвч энэ нь ижил хувийн тоотой хоёр номыг нэг гэж тооцно: хэрэв жанрт хоёулаа 2 хувьтай хоёр ном байвал DISTINCT тэднийг нэгтгэж, 4-ийн оронд 2 өгнө. Манай "Түүх" ба "Шүлэг" жанрууд нэг номтой учраас энэ асуудал одоо харагдахгүй ч энэ нь цаг хугацааны асуудал. SUM(DISTINCT) нь утгын давхардлыг арилгадаг, харин бидэнд мөрийн давхардлыг арилгах хэрэгтэй — тэд өөр зүйл.
Энэ тайланд үнэн хариулт өгөх зөв арга бий: хувийн нийлбэрийг JOIN-оос өмнө, тусад нь тооцоолж, дараа нь зээлийн тоотой нийлүүлэх. Үүнд query-ний дотор query бичих хэрэгсэл хэрэгтэй бөгөөд энэ нь дараагийн бүлгийн эхний хичээлийн сэдэв — subquery. Одоохондоо практик дүрэм: нэг-олон харилцааг JOIN-оор дайрсны дараа "нэг" талын тоон баганыг SUM, AVG хийх нь бараг үргэлж буруу. COUNT-ийг DISTINCT-ээр засаж болно, SUM-ийг болохгүй. Ийм тайлан хэрэгтэй бол хоёр тусдаа query эсвэл subquery ашигла. Энэ урхийг санаатайгаар үзүүлсэн шалтгаан нь бодит ажилд энэ алдаа маш түгээмэл бөгөөд тоо "үнэмшилтэй" харагддаг тул хэн ч анзаардаггүй — 20 хувь гэдэг нь 12-оос огт сэжигтэй сонсогдохгүй.
Энэ хичээлийн query-нүүд 8-10 мөртэй боллоо. Тэдгээрийг бичихэд хэцүү санагдсан бол нууц нь энгийн: query-г дээрээс доош биш, гүйцэтгэх дарааллаар нь угсар. Эхлээд FROM ба JOIN-ууд — аль хүснэгтүүд, ямар дүрмээр? SELECT * хийж, мөрийн тоог шалгаарай. Дараа нь WHERE — мөрийн тоо хэрхэн өөрчлөгдөв? Дараа нь GROUP BY ба нэг aggregate — сагсны тоо хүлээлттэй таарч байна уу? Дараа нь бусад aggregate-ууд, HAVING, ORDER BY. Алхам бүрд үр дүнг хараад дараагийн алхам руу шилж. Тайлан 4-ийн урхийг ийм аргаар барих байсан: JOIN-ий дараах SELECT * Clean Code-ийн гурван мөрийг copies = 4-тэйгээр гурав удаа харуулах бөгөөд SUM хийхээс өмнө "хүлээ, энэ гурав дахин тоологдох нь" гэж анзаарах боломж байсан.
Хэрэв сонирхож байвал зохиогч бүрийн тайлан бүтээж үзээрэй: зохиогчийн нэр, номын санд хэдэн ном нь байгаа, тэдгээр номууд нийт хэдэн удаа зээлэгдсэн. authors-ээс эхэлж LEFT JOIN-оор book_authors, books, loans-ийг нийлүүлэх хэрэгтэй — номгүй Al Sweigart 0, 0 гэсэн мөртэй гарах ёстой. Номын тоог тоолохдоо мөр үржилтийг санаж DISTINCT ашиглаарай (нэг ном олон удаа зээлэгдсэн бол олон мөр болно). Query-г "Урт query уншиж бичих дадал" хэсгийн дарааллаар, алхам бүрд мөрийн тоог шалгаж угсраарай. Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээлүүд тэдгээрийг хийсэн гэж тооцохгүй.
JOIN-ий дараа "нэг" талын баганыг SUM хийх. Тайлан 4-т харсанчлан алдааны мессеж гарахгүй, тоо үнэмшилтэй боловч худал. SUM-ийн үр дүнг нэг хүснэгтийн query-ээр (JOIN-гүйгээр) тооцоолсон тоотой харьцуулж шалгаарай.
SUM(DISTINCT ...)-ээр мөр үржилтийг засах. Дээр тайлбарласанчлан энэ нь утгын давхардлыг арилгадаг тул ижил утгатай өөр мөрүүдийг нэгтгэж, өөр төрлийн худал тоо гаргана. Мөр үржилтийг засах зөв арга нь subquery эсвэл тусдаа query.
CASE-ийг END-гүй бичих. SUM(CASE WHEN l.returned_on > l.due_on THEN 1 ELSE 0) гэж END-ийг мартвал near ")": syntax error гарна. Энэ мессеж юу гэж хэлж байна вэ гэвэл: "хаах хаалтын ойролцоо SQL-ийн дүрэм зөрчигдлөө" — SQLite CASE-ийн төгсгөлийг хүлээж байтал хаалт ирсэн. CASE үргэлж END-ээр хаагдана.
Гарт байгаа зээлийн шүүлтийг WHERE-д бичих. Тайлан 3-т WHERE l.returned_on IS NULL гэж бичвэл гарт ном байхгүй номнууд тайлангаас алга болно — Бүлэг 7-ийн LEFT JOIN-ийн Жишээ 5-ын урхи. Шүүлт ON-д байх ёстой.
group_concat-ийн дарааллыг баталгаатай гэж үзэх. Тайлан 1-ийн зохиогчдын жагсаалт ажиллуулах бүрд ижил дарааллаар гарах албагүй. Хэрэв дараалал чухал бол (жишээ нь тайланг өмнөхтэй нь харьцуулах) энэ нь асуудал үүсгэж болно. PostgreSQL-ийн string_agg() дотор ORDER BY бичих боломжтой бөгөөд Түвшин 4-т үүнийг харна.
Энэ хичээлд та JOIN, WHERE, GROUP BY, HAVING, aggregate, ORDER BY-ийг хослуулж дөрвөн бодит тайлан бүтээлээ. Хамгийн их зээлэгдсэн номнуудыг зохиогчидтой нь COUNT(DISTINCT l.id) болон group_concat-ээр гаргаж, өгөгдлийг хадгалахдаа салгаж харуулахдаа нэг нүдэнд нийлүүлж болдгийг харлаа. Гишүүн бүрийн бүрэн түүхийг LEFT JOIN, COUNT(багана)-ийн арифметик, CASE-тэй SUM-ээр гаргаж, Дулмаагийн тоолуурын асуудлыг бүрмөсөн шийдлээ. Номын "одоо байгаа хувь"-ийг ON-д шүүсэн LEFT JOIN-оор тооцоолж, Түвшин 2-ын амлалтаа биелүүллээ. Жанрын тайланд мөр үржилт SUM-ийг худал болгодгийг, COUNT-оос ялгаатай нь SUM-ийг DISTINCT-ээр засах боломжгүйг шударгаар харлаа. Урт query-г гүйцэтгэх дарааллаар, алхам бүрд мөрийн тоог шалгаж угсрах нь эдгээр урхийг барих хамгийн найдвартай арга юм.
Ингээд Бүлэг 8 дууслаа. Дараагийн бүлэгт Түвшин 3-ын хоёр сүүлийн хэрэгслийг сурна. Эхнийх нь subquery — query-ний дотор бичигдсэн query — бөгөөд Тайлан 4-ийн урхи, Бүлэг 8-ын эхний хичээлийн "дундаж хувиас олон хувьтай номнууд" гэх мэт асуултуудыг шийддэг. Хоёр дахь нь index — номын товьёог шиг ажилладаг, том хүснэгт дээр query-г олон дахин хурдасгадаг бүтэц бөгөөд бид түүний нөлөөг жинхэнэ хэмжилтээр харна.
Бүртгэлтэй болсноор энэ сургалтын бүх хичээлд хандах эрх авна.