GROUP BY

Танилцуулга

Өмнөх хичээлд aggregate функцууд олон мөрийг нэг утга болгон шахдаг гэж сурсан: найман зээл — нэг тоо “8”, найман номын хувь — нэг тоо “20”. Энэ нь "нийт хэд вэ" гэсэн асуултад төгс тохирно. Гэвч номын сангийн хамгийн хэрэгтэй асуултууд ихэвчлэн "нийт" биш, "тус бүр" гэсэн үгтэй байдаг. "Гишүүн бүр хэдэн ном зээлсэн бэ?" "Жанр бүрт хэдэн ном байна вэ?" "Сар бүр хэдэн зээл гарсан бэ?" Эдгээрийн хариулт нэг тоо биш, харин бүлэг бүрт нэг тоо бүхий жагсаалт юм.

Өмнөх хичээлд бид Сараагийн зээлийн тоог WHERE member_id = 2-оор олсон. Таван гишүүнд таван query — энэ нь Бүлэг 5-ийн гараар дугаар хуулдаг асуудалтай адил. Энэ хичээлд бид бүх гишүүний тоог нэг query-ээр гаргана. Хэрэгсэл нь GROUP BY: мөрүүдийг сагснуудад хувааж, сагс бүрт aggregate функцийг тусад нь ажиллуулдаг. Ингэснээр Бүлэг 6-д устгасан Дулмаагийн loan_count тоолуурыг бүрэн, үргэлж үнэн зөвөөр орлох query гарч ирнэ.

Мөн энэ хичээлд GROUP BY-ийн хамгийн чухал дүрмийг сурна: SELECT-д бичигдсэн багана бүр GROUP BY-д байх эсвэл aggregate функцийн дотор байх ёстой. Өмнөх хичээлийн төгсгөлд "нийт дүнгийн хажууд аль мөрийн нэрийг бичих вэ" гэсэн асуулт энэ дүрмийн цаад шалтгаан юм. SQLite энэ дүрмийг мөрдүүлдэггүй тул бид PostgreSQL-ийн алдааны мессежийг уншиж, яагаад энэ дүрэм зөв болохыг ойлгоно. library2.db-г нээгээрэй — бид зөвхөн уншина.

Зүйрлэл

Номын санч нэг цомог зээлийн хуудас барьж байна гэж бодъё — хуудас бүр нэг зээл. Эрхлэгч "гишүүн бүр хэдэн ном зээлсэн бэ" гэж асуулаа. Номын санч ширээн дээрээ таван сагс тавиад, сагс бүрт нэг гишүүний нэрийг наана. Дараа нь хуудас бүрийг уншиж, түүний гишүүний сагсанд хийнэ. Бүх хуудас ангилагдсаны дараа тэр сагс бүрийг тоолж, сагс бүрийн хажууд нэг тоо бичнэ: "Бат — 3, Сараа — 2..." Эцсийн хариулт нь сагс бүрт нэг мөр.

Энэ бол GROUP BY: мөрүүдийг ижил утгаар нь сагслаад, сагс бүрийг нэг мөр болгон нэгтгэх. Энэ зүйрлэлд хоёр чухал ажиглалт бий. Нэгдүгээрт, сагс бүрийн шошго (гишүүний нэр) тэр сагсны бүх хуудсанд ижил — тиймээс "энэ сагс ямар гишүүнийх вэ" гэдэгт ганц хариулт бий. Хоёрдугаарт, сагсны доторх хуудаснууд өөр өөр огноотой. Хэрэв эрхлэгч "Батын сагсны хажууд зээлсэн огноог бич" гэвэл номын санч "аль огноог? Тэнд гурван өөр огноо байна" гэж асууна. Энэ асуулт GROUP BY-ийн дүрмийн мөн чанар юм.

Анхны жишээ: гишүүн бүрийн зээлийн тоо

loans хүснэгт (хамааралтай багануудаар):

id | member_id | loaned_on
---+-----------+-----------
1  | 1         | 2026-09-03
2  | 1         | 2026-09-10
3  | 2         | 2026-09-12
4  | 1         | 2026-09-20
5  | 4         | 2026-09-25
6  | 2         | 2026-10-01
7  | 3         | 2026-10-03
8  | 4         | 2026-10-05

Query:

sql

SELECT member_id, COUNT(*) AS loan_count
FROM loans
GROUP BY member_id
ORDER BY member_id;

Үр дүн:

member_id | loan_count
----------+-----------
1         | 3
2         | 2
3         | 1
4         | 2

Энэ query юу гэж хэлж байна вэ гэвэл: “loans хүснэгтийн мөрүүдийг member_id-ээр нь сагслаад, сагс бүрийн хувьд member_id ба мөрийн тоог (loan_count гэж нэрлэн) member_id-ээр эрэмбэлж харуул.”

Сагслах явцыг гурван шатаар мөшгье.

Шат 1 — мөрүүд. FROM найман зээлийг авна.

Шат 2 — сагснууд. GROUP BY member_id нь мөр бүрийг member_id-ийнх нь сагсанд хийнэ:

сагс member_id = 1:  зээл 1 (09-03), зээл 2 (09-10), зээл 4 (09-20)
сагс member_id = 2:  зээл 3 (09-12), зээл 6 (10-01)
сагс member_id = 3:  зээл 7 (10-03)
сагс member_id = 4:  зээл 5 (09-25), зээл 8 (10-05)

Шат 3 — сагс бүрт нэг мөр. SELECT сагс бүрийг нэг мөр болгоно: шошго (member_id) ба сагсны доторх мөрийн тоо (COUNT(*)). Дөрвөн сагс — дөрвөн мөр.

Өмнөх хичээлд COUNT(*) бүх хүснэгтийг нэг "Нийт:" мөр болгон шахсан бол одоо тэр нь сагс бүрийг тусад нь шахаж байна. GROUP BY-гүй aggregate query-г "бүх мөр нэг том сагсанд" гэж ойлгож болно. Мессежийн талбар 4 rows returned гэж хэлнэ — дөрвөн өөр member_id утга, дөрвөн сагс. Таван гишүүн байгаа ч Ану (5) энд алга: түүний зээл байхгүй тул түүнд сагс үүсээгүй. Энэ асуудлыг Жишээ 2-т шийднэ.

Бүлэг 1-ийн SELECT-ийн араг ясанд GROUP BY нь WHERE-ийн дараа, ORDER BY-ийн өмнө байрладаг. Гүйцэтгэх дарааллаар ч мөн адил: FROM, WHERE, GROUP BY (сагслах), SELECT (сагс бүрт нэг мөр бодох), ORDER BY, LIMIT. Тиймээс ORDER BY-д loan_count гэсэн alias-ийг ашиглаж болно — тэр мөчид тэр тоо аль хэдийн тооцоологдсон байна.

Олон жишээ

Жишээ 1: нэрээр — JOIN ба GROUP BY

Дугаар биш нэрийг харуулъя. Бүлэг 7-ийн JOIN-ийг GROUP BY-тэй хослуулна. Query:

sql

SELECT m.id, m.name, COUNT(*) AS loan_count
FROM members AS m
INNER JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY loan_count DESC, m.name;

Үр дүн:

id | name     | loan_count
---+----------+-----------
1  | Бат      | 3
4  | Номин    | 2
2  | Сараа    | 2
3  | Тэмүүлэн | 1

Энэ query юу гэж хэлж байна вэ гэвэл: “members-ийг loans-тай нийлүүлж, нийлсэн мөрүүдийг гишүүний id ба нэрээр сагслаад, гишүүн бүрийн id, нэр, мөрийн тоог тоогоор нь ихээс бага руу, тэнцвэл нэрээр эрэмбэлж харуул.” Гүйцэтгэх дарааллаар: JOIN эхлээд найман мөр үүсгэнэ (Бүлэг 7-ийн INNER JOIN), GROUP BY тэдгээрийг дөрвөн сагсанд хуваана, SELECT сагс бүрт нэг мөр бодно.

Бид яагаад m.name-ээр биш, m.id, m.name хоёроор сагсалсан бэ? Бүлэг 4-ийн PRIMARY KEY хичээлийг санаарай: хоёр өөр гишүүн "Бат" нэртэй байж болно. Хэрэв зөвхөн нэрээр сагсалвал хоёр Батын зээлүүд нэг сагсанд холилдож, "Бат — 5" гэсэн худал мөр гарна. Мөрийг хэн болохоор нь ялгадаг зүйл бол id — тиймээс сагсыг id-аар тогтооно. Нэрийг GROUP BY-д нэмсэн нь SELECT-д нэрийг харуулахад хэрэгтэй (доорх дүрмийн дагуу). id нэг сагсанд ганц утгатай тул нэр нь ч ганц утгатай — сагсны тоо өөрчлөгдөхгүй.

Жишээ 2: LEFT JOIN-оор тэг тоог хадгалах — Дулмаагийн тоолуур, зөв хувилбар

Ану Жишээ 1-д алга, учир нь INNER JOIN түүнийг хассан. "Гишүүн бүр хэдэн ном зээлсэн бэ" гэсэн асуултад Ану “0” гэсэн хариутай байх ёстой. Бүлэг 7-ийн LEFT JOIN энд хэрэгтэй — гэхдээ нэг нарийн зүйлтэй. Эхлээд COUNT(*)-оор оролдъё:

sql

SELECT m.name, COUNT(*) AS loan_count
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY loan_count DESC, m.name;

Үр дүн:

name     | loan_count
---------+-----------
Бат      | 3
Номин    | 2
Сараа    | 2
Ану      | 1
Тэмүүлэн | 1

Ану гарч ирсэн — гэхдээ 1 зээлтэй! Энэ бол LEFT JOIN-ийн хичээлийн Түгээмэл алдаанууд хэсэгт анхааруулсан урхи. LEFT JOIN Ануг хадгалахдаа нэг мөр үүсгэсэн — loans-ийн нүднүүд нь NULL. COUNT(*) мөр тоолдог тул тэр мөрийг нэг гэж тоолсон. Ану зээл аваагүй, харин нэг мөртэй.

Засвар нь өмнөх хичээлийн COUNT(багана) юм — NULL-ийг алгасдаг:

sql

SELECT m.name, COUNT(l.id) AS loan_count
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY loan_count DESC, m.name;

Үр дүн:

name     | loan_count
---------+-----------
Бат      | 3
Номин    | 2
Сараа    | 2
Тэмүүлэн | 1
Ану      | 0

Энэ query юу гэж хэлж байна вэ гэвэл: “members-ийн бүх мөрийг хадгалан loans-тай нийлүүлж, гишүүнээр сагслаад, сагс бүрийн зээлийн id-ийн NULL биш утгуудыг тоол.” Анугийн сагсанд ганц мөр бий, түүний l.id нь NULL тул COUNT(l.id) нь 0. Таван гишүүн, таван үнэн тоо.

Энэ query бол Бүлэг 6-д устгасан Дулмаагийн loan_count баганын бүрэн орлуулга. Тэр багана гараар шинэчлэгддэг байсан тул зээл устахад худал болсон. Энэ query-д шинэчлэх зүйл байхгүй — ажиллах бүрдээ loans хүснэгтийн одоогийн байдлаас шууд тооцоолно. Зээл нэмэгдэх, устах, засагдах бүрд дараагийн удаа үнэн тоо гарна. Бүлэг 6-ийн Дүрэм 3-ын "хэрэгтэй үед тооцоол" гэсэн хагас одоо бүрэн хэрэгжлээ. Дүрэм нэгтгэвэл: LEFT JOIN-ийн дараа тоолохдоо баруун хүснэгтийн PRIMARY KEY-ийг COUNT-д бич, COUNT(*)-ийг биш.

Жишээ 3: жанр бүрт хэдэн ном, хэдэн хувь

GROUP BY нь JOIN-гүйгээр нэг хүснэгт дээр ч ажилладаг. Query:

sql

SELECT genre, COUNT(*) AS book_count, SUM(copies) AS total_copies
FROM books
GROUP BY genre
ORDER BY genre;

Үр дүн:

genre       | book_count | total_copies
------------+------------+-------------
Жүжиг       | 1          | 1
Програмчлал | 4          | 12
Роман       | 1          | 3
Түүх        | 1          | 2
Шүлэг       | 1          | 2

Энэ query юу гэж хэлж байна вэ гэвэл: “books-ийн мөрүүдийг жанраар сагслаад, жанр бүрийн номын тоо ба хувийн нийлбэрийг жанраар эрэмбэлж харуул.” Нэг сагсанд хэд хэдэн aggregate функц зэрэг ажиллаж болно — өмнөх хичээлийн Жишээ 3-ын нэгэн адил, гэхдээ одоо сагс бүрт тусдаа. Програмчлалын сагсанд дөрвөн ном: 4 + 5 + 2 + 1 = 12 хувь — өмнөх хичээлийн Жишээ 4-т WHERE-ээр олсон тоотой ижил. Ялгаа нь WHERE нэг жанрыг сонгосон бол GROUP BY бүх жанрыг нэг дор тооцсон. Хүснэгт SELECT DISTINCT genre-тэй ижил таван жанрыг харуулж байгааг анзаараарай — GROUP BY-ийн шошгууд давхардалгүй утгууд, гэхдээ сагс бүр нэмэлт тоотой.

Жишээ 4: илэрхийллээр сагслах — сар бүрийн зээл

Сагсны шошго заавал байгаа багана байх шаардлагагүй, тооцоолсон утга ч байж болно. Сар бүр хэдэн зээл гарсан бэ? Огноо YYYY-MM-DD хэлбэртэй тул эхний долоон тэмдэгт (YYYY-MM) нь сарыг илэрхийлнэ. SQLite-ийн substr(текст, эхлэл, урт) функц текстийн хэсгийг тасалж авдаг. Query:

sql

SELECT substr(loaned_on, 1, 7) AS month, COUNT(*) AS loan_count
FROM loans
GROUP BY month
ORDER BY month;

Үр дүн:

month   | loan_count
--------+-----------
2026-09 | 5
2026-10 | 3

Энэ query юу гэж хэлж байна вэ гэвэл: “loans-ийн мөр бүрийн зээлсэн огнооны эхний долоон тэмдэгтийг month гэж нэрлэж, тэрээр сагслаад, сар бүрийн зээлийн тоог сараар эрэмбэлж харуул.” 9-р сард таван зээл (1-5), 10-р сард гурав (6-8). Энэ нь Бүлэг 3-ын удирдамжтай дасгалд огноог YYYY-MM-DD хэлбэрээр хадгалах дүрмийн бас нэг шагнал: сарыг гаргахын тулд текстийн эхний хэсгийг таслахад хангалттай. SQLite энэ query-д GROUP BY-д alias (month)-ийг ашиглахыг зөвшөөрдөг. Зарим өгөгдлийн санд илэрхийллийг GROUP BY-д бүтнээр нь давтах шаардлагатай байдаг: GROUP BY substr(loaned_on, 1, 7).

GROUP BY-ийн гол дүрэм

Одоо зүйрлэлийн "аль огноог бичих вэ" гэсэн асуулт руу буцъя. Гишүүнээр сагсалсан query-д зээлсэн огноог нэмье:

sql

SELECT member_id, loaned_on, COUNT(*) AS loan_count
FROM loans
GROUP BY member_id;

SQLite энэ query-г алдаагүй ажиллуулж, сагс бүрт нэг огноо харуулна — жишээ нь Батын сагсанд 2026-09-20. Гэвч Батын сагсанд гурван огноо байсан: 09-03, 09-10, 09-20. SQLite тэдгээрээс нэгийг нь, ямар ч дүрэмгүйгээр сонгосон. Өнөөдөр 09-20, өгөгдөл өөрчлөгдсөний дараа өөр огноо гарч магадгүй. Үр дүн хүнд "Бат 09-20-нд 3 ном зээлсэн" гэж уншигдаж болох бөгөөд энэ нь худлаа.

PostgreSQL ижил query-д дараах алдааг өгнө:

column "loans.loaned_on" must appear in the GROUP BY clause or be used in an aggregate function

Энэ мессеж юу гэж хэлж байна вэ гэвэл: “loans.loaned_on багана GROUP BY хэсэгт байх эсвэл aggregate функцэд ашиглагдах ёстой.” Энэ бол GROUP BY-ийн гол дүрэм бөгөөд PostgreSQL түүнийг хууль болгон мөрдүүлдэг, SQLite харин зөвлөмжийн түвшинд үлдээдэг — Бүлэг 4-ийн "төрөл бол зөвлөмж" гэдэгтэй адил хэв маяг. Дүрмийн утга нь зүйрлэлд бий: сагс бүр нэг мөр болдог тул SELECT дахь багана бүр сагс бүрт ганц утгатай байх ёстой. Тийм баганын хоёр төрөл л бий. Нэгдүгээрт, сагсны шошго — GROUP BY-д бичигдсэн багана, сагсны бүх мөрт ижил. Хоёрдугаарт, aggregate функц — сагсны бүх утгыг нэг болгон шахдаг. Үүнээс өөр багана сагсанд олон утгатай байж болох тул "аль нь?" гэсэн асуулт хариултгүй үлдэнэ.

Засах хоёр зам бий, асуултаас хамаарна. Хэрэв "гишүүн бүрийн хамгийн сүүлийн зээлийн огноо" хэрэгтэй бол aggregate функцэд оруулна:

sql

SELECT member_id, MAX(loaned_on) AS last_loan, COUNT(*) AS loan_count
FROM loans
GROUP BY member_id
ORDER BY member_id;

Үр дүн:

member_id | last_loan  | loan_count
----------+------------+-----------
1         | 2026-09-20 | 3
2         | 2026-10-01 | 2
3         | 2026-10-03 | 1
4         | 2026-10-05 | 2

Энэ query юу гэж хэлж байна вэ гэвэл: “loans-ийг гишүүнээр сагслаад, сагс бүрийн хамгийн их (хамгийн сүүлийн) зээлсэн огноо ба мөрийн тоог харуул.” Одоо "Бат — хамгийн сүүлд 09-20, нийт 3" гэсэн үнэн өгүүлбэр уншигдаж байна. Хэрэв харин огноо бүрийг тусад нь харах хэрэгтэй бол огноог GROUP BY-д нэмнэ — гэвч тэгвэл сагс бүр нэг огноо, нэг гишүүнтэй болж, бараг бүх сагс нэг мөртэй болох тул GROUP BY ямар ч утга нэмэхгүй. Энэ хичээлд бид PostgreSQL-ийн дүрмийг SQLite дээр ч сайн дураараа мөрдөнө: SELECT дахь aggregate бус багана бүр GROUP BY-д байх ёстой. Энэ дадал Түвшин 4-т PostgreSQL руу шилжихэд query-нүүдийг тань эвдрэхээс хамгаална.

NULL-ийн сагс

Нэг жижиг нарийн зүйл: GROUP BY нь бүх NULL утгуудыг нэг сагсанд хийдэг. SELECT isbn IS NULL AS no_isbn, COUNT(*) FROM books GROUP BY no_isbn; гэх мэт query-д бол энэ нь хүлээгдэж буй зан. Гэвч хэрэв та GROUP BY author_id гэж зохиогчгүй номнуудтай хүснэгтийг сагсалбал бүх зохиогчгүй ном нэг “NULL” сагсанд орж, нэг мөр болно. Бүлэг 2-т NULL = NULL "мэдэхгүй" гэж сурсан ч DISTINCT-ийн нэгэн адил GROUP BY нь NULL-уудыг нэг бүлэгт хамааруулдаг. Үр дүнд NULL шошготой мөр гарвал "энэ бол утгагүй бүх мөрийн сагс" гэж уншаарай.

Туршаад үзээрэй

Хэрэв сонирхож байвал номын сангийн гишүүдийг хотоор нь сагсалж, хот бүрт хэдэн гишүүн байгааг гаргаж үзээрэй. Дараа нь зээлүүдийг номоор сагсалж, ном бүр хэдэн удаа зээлэгдсэнийг тоолоорой — эхлээд book_id-аар, дараа нь books-той JOIN хийж номын нэрээр. Хэзээ ч зээлэгдээгүй номнуудыг 0 тоотой харуулахыг хүсвэл Жишээ 2-ын LEFT JOIN ба COUNT(l.id) дүрмийг санаарай. Ажиллуулахаасаа өмнө хэдэн мөр, ямар тоонууд гарахыг таамаглаарай. Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээлүүд тэдгээрийг хийсэн гэж тооцохгүй.

Түгээмэл алдаанууд

Сагсанд олон утгатай баганыг SELECT-д бичих. Дээр харсанчлан SQLite алдаа өгөхгүй, дурын утга харуулна; PostgreSQL must appear in the GROUP BY clause or be used in an aggregate function гэсэн алдаа өгнө. SELECT дахь баганыг нэг бүрчлэн шалга: GROUP BY-д байна уу, aggregate дотор байна уу? Хоёулаа биш бол засах хэрэгтэй.

Нэрээр сагслах. Жишээ 1-д тайлбарласанчлан ижил нэртэй хоёр мөр нэг сагсанд холилдож, тоонууд нийлбэрлэгдэнэ. Алдааны мессеж гарахгүй. "Зүйл"-ээр сагслахдаа түүний PRIMARY KEY-ээр сагсалж, нэрийг GROUP BY-д нэмж харуулна.

LEFT JOIN-ийн дараа COUNT(*). Жишээ 2-т харсанчлан зээлгүй гишүүн 0 биш 1 гэж тоологдоно. COUNT-д баруун хүснэгтийн PRIMARY KEY-ийг бич.

GROUP BY-ийг буруу байранд бичих. SELECT member_id, COUNT(*) FROM loans ORDER BY member_id GROUP BY member_id; гэж бичвэл near "GROUP": syntax error гарна. Энэ мессеж юу гэж хэлж байна вэ гэвэл: “GROUP гэдэг үгийн ойролцоо SQL-ийн дүрэм зөрчигдлөө.” Бүлэг 1-ийн араг яс: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT — GROUP BY нь WHERE-ийн дараа, ORDER BY-ийн өмнө.

JOIN-ий дараах мөр үржилтийг сагсанд авчрах. Хэрэв зээлүүдийг зохиогчидтой нийлүүлээд гишүүнээр сагсалж COUNT(*) хийвэл Тэмүүлэнгийн нэг зээл хоёр гэж тоологдоно (Бүлэг 7-ийн сүүлийн хичээлийн Жишээ 5). Засвар нь өмнөх хичээлийн дүрэм: COUNT(DISTINCT l.id).

Товч дүгнэлт

Энэ хичээлд та GROUP BY-ээр мөрүүдийг сагслаж, сагс бүрт aggregate функцийг тусад нь ажиллуулж сурлаа. Мөрүүд эхлээд шошгоороо (GROUP BY-ийн багана эсвэл илэрхийлэл) сагсанд хуваагдаж, дараа нь сагс бүр нэг мөр болдог. JOIN-тэй хослуулахдаа "зүйл"-ийг PRIMARY KEY-ээр нь сагсалж, нэрийг GROUP BY-д нэмж харуулна; LEFT JOIN-ийн дараа баруун хүснэгтийн PRIMARY KEY-ийг COUNT-д бичиж тэг тоог үнэн зөв гаргана — энэ нь Дулмаагийн гараар шинэчлэгддэг тоолуурыг бүрэн орлосон query юм. substr() гэх мэт илэрхийллээр сагслаж сар бүрийн тайлан гаргаж болно. Гол дүрэм: SELECT дахь багана бүр GROUP BY-д эсвэл aggregate функцийн дотор байх ёстой — SQLite үүнийг мөрдүүлдэггүй ч PostgreSQL мөрдүүлдэг тул бид сайн дураараа мөрдөнө. GROUP BY нь NULL утгуудыг нэг сагсанд хийдэг.

Дараагийн хичээлд SQL-д суралцагчдын хамгийн их андуурдаг хоёр хэрэгслийг ялгана: WHERE ба HAVING. Хоёулаа шүүдэг, гэвч өөр өөр мөчид — нэг нь мөрүүдийг сагслахаас өмнө, нөгөө нь сагснуудыг үүссэний дараа. "Хоёроос олон ном зээлсэн гишүүд" гэсэн асуултыг нэг жишээгээр хоёр шатаар мөшгиж, энэ мөнхийн төөрөгдлийг бүрмөсөн шийднэ.