π Π ΡΠΊΠΎΠ²ΠΎΠ΄ΡΡΠ²ΠΎ ΠΏΠΎ SQL Π΄Π»Ρ Π½Π°ΡΠΈΠ½Π°ΡΡΠΈΡ . Π§Π°ΡΡΡ 3: ΡΡΠ»ΠΎΠΆΠ½ΡΠ΅ΠΌ Π·Π°ΠΏΡΠΎΡΡ, ΠΈΠΌΠ΅Π½ΡΠ΅ΠΌ Π²Π»ΠΎΠΆΠ΅Π½Π½ΡΠ΅ Π·Π°ΠΏΡΠΎΡΡ, Π°Π½Π°Π»ΠΈΠ·ΠΈΡΡΠ΅ΠΌ ΡΠΊΠΎΡΠΎΡΡΡ Π·Π°ΠΏΡΠΎΡΠ°
Π ΠΏΠΎΡΠ»Π΅Π΄Π½Π΅ΠΉ ΡΠ°ΡΡΠΈ ΡΠΈΠΊΠ»Π° ΡΡΠ°ΡΠ΅ΠΉ ΠΌΡ ΡΠΎΡΡΠ°Π²ΠΈΠΌ ΡΠ»ΠΎΠΆΠ½ΡΠ΅ Π·Π°ΠΏΡΠΎΡΡ, ΠΏΠΎΡΠ°Π±ΠΎΡΠ°Π΅ΠΌ Ρ Π²Π»ΠΎΠΆΠ΅Π½Π½ΡΠΌΠΈ Π·Π°ΠΏΡΠΎΡΠ°ΠΌΠΈ, ΠΏΠΎΠ³ΠΎΠ²ΠΎΡΠΈΠΌ ΠΎ ΡΠΎΡ ΡΠ°Π½Π½ΠΎΡΡΠΈ Π΄Π°Π½Π½ΡΡ ΠΈ Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ ΠΎ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎΡΡΠΈ ΠΈΠ½Π΄Π΅ΠΊΡΠΎΠ².
Π‘Π°ΠΌΠΎΡΡΠΎΡΡΠ΅Π»ΡΠ½ΠΎΠ΅ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΠ΅ (self join)
ΠΠ½ΠΎΠ³Π΄Π° Π²ΠΎΠ·Π½ΠΈΠΊΠ°Π΅Ρ ΡΠΈΡΡΠ°ΡΠΈΡ, ΠΊΠΎΠ³Π΄Π° Π΄Π»Ρ ΠΏΠΎΠ»ΡΡΠ΅Π½ΠΈΡ Π½Π΅ΠΎΠ±Ρ
ΠΎΠ΄ΠΈΠΌΡΡ
Π΄Π°Π½Π½ΡΡ
Π½Π°ΠΌ ΠΏΠΎΠ½Π°Π΄ΠΎΠ±ΠΈΡΡΡ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½ΠΈΡΡ ΡΠ°Π±Π»ΠΈΡΡ ΡΠ°ΠΌΡ Ρ ΡΠΎΠ±ΠΎΠΉ. ΠΠ°Π³Π»ΡΠ΄Π½ΡΠΉ ΠΏΡΠΈΠΌΠ΅Ρ Π²Ρ ΠΌΠΎΠΆΠ΅ΡΠ΅ ΠΏΠΎΡΠΌΠΎΡΡΠ΅ΡΡ ΠΏΠΎ ΡΡΠΎΠΉ ΡΡΡΠ»ΠΊΠ΅. Π‘ΡΡΡ Π² ΡΠΎΠΌ, ΡΡΠΎ, Π΅ΡΠ»ΠΈ ΡΡΡΠΎΠΊΠΈ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ ΡΠΎΠ΄Π΅ΡΠΆΠ°Ρ Π΄Π°Π½Π½ΡΠ΅, ΠΊΠΎΡΠΎΡΡΠ΅ ΠΌΠΎΠ³ΡΡ Π±ΡΡΡ ΡΠ²ΡΠ·Π°Π½Ρ Ρ Π΄Π°Π½Π½ΡΠΌΠΈ ΠΈΠ· Π΄ΡΡΠ³ΠΈΡ
ΡΡΡΠΎΠΊ ΡΡΠΎΠΉ ΠΆΠ΅ ΡΠ°Π±Π»ΠΈΡΡ (Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ id
), ΠΌΡ ΠΌΠΎΠΆΠ΅Ρ ΡΠ΄Π΅Π»Π°ΡΡ join
ΡΠ°Π±Π»ΠΈΡΡ ΠΊ Π½Π΅ΠΉ ΡΠ°ΠΌΠΎΠΉ.
ΠΠ°Π²Π°ΠΉΡΠ΅ Π΄Π»Ρ Π½Π°ΡΠ°Π»Π° Π΄ΠΎΠ±Π°Π²ΠΈΠΌ ΠΏΠΎΠ»Π΅ Ρ ΠΈΠ΄Π΅Π½ΡΠΈΡΠΈΠΊΠ°ΡΠΎΡΠΎΠΌ Π»ΡΡΡΠ΅Π³ΠΎ Π΄ΡΡΠ³Π° best_friend_id
Π² ΡΠ°Π±Π»ΠΈΡΡ ΡΡΠ΅Π½ΠΈΠΊΠΎΠ² ΠΈ Π²ΡΡΠ°Π²ΠΈΠΌ Π² Π½Π΅Π³ΠΎ Π½Π΅ΠΊΠΎΡΠΎΡΡΠ΅ Π΄Π°Π½Π½ΡΠ΅.
ALTER TABLE students ADD best_friend_id INT; UPDATE students SET best_friend_id = 5 WHERE id = 1; UPDATE students SET best_friend_id = 4 WHERE id = 2; UPDATE students SET best_friend_id = 2 WHERE id = 3; UPDATE students SET best_friend_id = 2 WHERE id = 4; UPDATE students SET best_friend_id = 1 WHERE id = 5; SELECT * FROM students; /* id | name | classroom_id | best_friend_id --- | -------- | ------------ | -------------- 1 | Adam | 1 | 5 2 | Betty | 1 | 4 3 | Caroline | 2 | 2 4 | Dina | [null] | 2 5 | Evan | [null] | 1 */
ΠΡΠ°ΠΊ, Π² ΡΠ°Π±Π»ΠΈΡΠ΅ ΠΌΡ Ρ
ΡΠ°Π½ΠΈΠΌ id
Π»ΡΡΡΠ΅Π³ΠΎ Π΄ΡΡΠ³Π° Π΄Π»Ρ ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ ΡΡΠ΅Π½ΠΈΠΊΠ°. ΠΡΠΎ ΡΡΡΠ΅ΠΊΡΠΈΠ²Π½ΠΎ, Π½ΠΎ Π½Π΅ ΠΎΡΠ΅Π½Ρ ΡΠΈΡΠ°Π±Π΅Π»ΡΠ½ΠΎ. Π§ΡΠΎΠ±Ρ ΠΎΠΏΡΠ΅Π΄Π΅Π»ΠΈΡΡ, ΠΊΡΠΎ ΠΆΠ΅ ΡΠ²Π»ΡΠ΅ΡΡΡ Π»ΡΡΡΠΈΠΌ Π΄ΡΡΠ³ΠΎΠΌ, Π½Π°ΠΌ ΠΏΡΠΈΠ΄ΡΡΡΡ ΡΠΎΠ΅Π΄ΠΈΠ½ΠΈΡΡ ΡΠ°Π±Π»ΠΈΡΡ ΡΠ°ΠΌΡ Ρ ΡΠΎΠ±ΠΎΠΉ. ΠΠΎΠ·ΡΠΌΠ΅ΠΌ ΡΠ°Π±Π»ΠΈΡΡ students
ΠΈ ΡΠ΄Π΅Π»Π°Π΅ΠΌ ΡΠΎΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΠ΅ (join
) ΡΠ½ΠΎΠ²Π° Ρ ΡΠ°Π±Π»ΠΈΡΠ΅ΠΉ students
, Π² ΠΊΠ°ΡΠ΅ΡΡΠ²Π΅ ΠΊΠ»ΡΡΠ΅ΠΉ Π΄Π»Ρ ΡΠ²ΡΠ·ΠΈ Ρ Π½Π°Ρ Π±ΡΠ΄Π΅Ρ id
ΡΡΡΠ΄Π΅Π½ΡΠ° ΠΈ id
Π»ΡΡΡΠ΅Π³ΠΎ Π΄ΡΡΠ³Π° (best_friend_id
). ΠΠΏΡΠ΅Π΄Π΅Π»ΠΈΠΌ ΠΏΡΠ΅Π²Π΄ΠΎΠ½ΠΈΠΌΡ ΡΠ°Π±Π»ΠΈΡ x
ΠΈ y
Π΄Π»Ρ ΡΠ»ΡΡΡΠ΅Π½ΠΈΡ ΠΏΠΎΠ½ΠΈΠΌΠ°Π½ΠΈΡ ΠΊΠΎΠ΄Π°.
SELECT x.name, y.name AS best_friend FROM students AS x INNER JOIN students AS y ON y.id = x.best_friend_id; /* name | best_friend -------- | ----------- Adam | Evan Betty | Dina Caroline | Betty Dina | Betty Evan | Adam */
ΠΠΊΠΎΠ½Π½ΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ (Window functions)
ΠΠΊΠΎΠ½Π½ΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ ΠΎΡΠ΅Π½Ρ ΠΏΠΎΡ
ΠΎΠΆΠΈ Π½Π° Π°Π³ΡΠ΅Π³Π°ΡΠ½ΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ (group by
) ΡΠ΅ΠΌ, ΡΡΠΎ ΠΎΠ½ΠΈ ΠΏΡΠΈΠΌΠ΅Π½ΡΡΡΡΡ Π΄Π»Ρ Π²ΡΡΠΈΡΠ»Π΅Π½ΠΈΠΉ ΡΠ³ΡΡΠΏΠΏΠΈΡΠΎΠ²Π°Π½Π½ΡΡ
Π½Π°Π±ΠΎΡΠΎΠ² Π΄Π°Π½Π½ΡΡ
. ΠΠΎ Π² ΠΎΡΠ»ΠΈΡΠΈΠ΅ ΠΎΡ Π°Π³ΡΠ΅Π³Π°ΡΠ½ΡΡ
ΡΡΠ½ΠΊΡΠΈΠΉ, ΠΎΠΊΠΎΠ½Π½ΡΠ΅ Π½Π΅ ΡΠΌΠ΅Π½ΡΡΠ°ΡΡ ΠΊΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΡΡΡΠΎΠΊ Π² ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ°Ρ
Π·Π°ΠΏΡΠΎΡΠ°.
ΠΠ°Π²Π°ΠΉΡΠ΅ Π²ΡΡΠΈΡΠ»ΠΈΠΌ ΡΡΠ΅Π΄Π½ΠΈΠΉ Π±Π°Π»Π» Π΄Π»Ρ ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ ΡΡΠ΅Π½ΠΈΠΊΠ°. ΠΠ° ΡΡΡΠΎΠΊΠ°Ρ
Ρ ΡΠ΅ΡΠ²Π΅ΡΡΠΎΠΉ ΠΏΠΎ ΡΠ΅ΡΡΡΡ Π² Π·Π°ΠΏΡΠΎΡΠ΅ Π½ΠΈΠΆΠ΅ ΠΌΡ Π΄ΠΎΠ±Π°Π²ΠΈΠ»ΠΈ OVER
ΠΈ PARTITION BY
, ΡΡΠΎΠ±Ρ ΠΈΠ· Π°Π³ΡΠ΅Π³Π°ΡΠ½ΠΎΠΉ ΡΡΠ½ΠΊΡΠΈΠΈ ΡΠ΄Π΅Π»Π°ΡΡ ΠΎΠΊΠΎΠ½Π½ΡΡ.
SELECT s.name, g.score, AVG(g.score) OVER ( PARTITION BY s.name ) FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id; /* name | score | avg ------| ----- | ---------- Adam | 82 | 80.8000... Adam | 82 | 80.8000... Adam | 80 | 80.8000... Adam | 75 | 80.8000... Adam | 85 | 80.8000... Betty | 74 | 70.4000... Betty | 75 | 70.4000... ... | ... | ... */
ΠΠ»Ρ Π°Π³ΡΠ΅Π³ΠΈΡΡΡΡΠΈΡ
ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡΠΎΠ² AVG
, MIN
, ΠΈΠ»ΠΈ MAX
, ΠΊΠ°ΠΆΠ΄Π°Ρ ΡΡΡΠΎΠΊΠ° Π² Π³ΡΡΠΏΠΏΠΈΡΠΎΠ²ΠΊΠ΅ PARTITION BY
Π±ΡΠ΄Π΅Ρ ΠΎΡΠΎΠ±ΡΠ°ΠΆΠ°ΡΡ ΠΎΠ΄ΠΈΠ½Π°ΠΊΠΎΠ²ΠΎΠ΅ Π·Π½Π°ΡΠ΅Π½ΠΈΠ΅. ΠΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎ, Π΄Π»Ρ ΠΎΠΏΡΠ΅Π΄Π΅Π»Π΅Π½Π½ΡΡ
Π²ΠΈΠ΄ΠΎΠ² Π°Π½Π°Π»ΠΈΠ·Π° ΡΡΠΎ ΠΈ ΠΏΠΎΠ½Π°Π΄ΠΎΠ±ΠΈΡΡΡ, Π½ΠΎ Π½Π° ΡΠ°ΠΌΠΎΠΌ Π΄Π΅Π»Π΅, ΡΠΈΠ»Π° ΠΎΠΊΠΎΠ½Π½ΡΡ
ΡΡΠ½ΠΊΡΠΈΠΉ Π² Π΄ΡΡΠ³ΠΎΠΌ.
ΠΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΡ ΡΠ°Π½ΠΆΠΈΡΠΎΠ²Π°Π½ΠΈΡ Π΄Π°Π½Π½ΡΡ
Π±ΠΎΠ»Π΅Π΅ ΠΏΠΎΠ»Π΅Π·Π½ΡΠΉ ΡΠ»ΡΡΠ°ΠΉ. ΠΠΎΠΏΡΠΎΠ±ΡΠ΅ΠΌ ΡΠ΄Π΅Π»Π°ΡΡ ΡΡΠΎ Ρ ΠΎΡΠ΅Π½ΠΊΠ°ΠΌΠΈ Π½Π°ΡΠΈΡ
ΡΡΠ΅Π½ΠΈΠΊΠΎΠ². Π§ΡΠΎΠ±Ρ ΡΠ°Π½ΠΆΠΈΡΠΎΠ²Π°ΡΡ ΠΎΡΠ΅Π½ΠΊΠΈ Π²ΡΠ΅Ρ
ΡΡΠ΅Π½ΠΈΠΊΠΎΠ² ΠΌΡ Π±ΡΠ΄Π΅ΠΌ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡ RANK() OVER
, Π² ΠΊΠΎΡΠΎΡΡΠΉ ΠΌΡ ΠΏΠ΅ΡΠ΅Π΄Π°Π΄ΠΈΠΌ Π½ΡΠΆΠ½ΡΠΉ Π½Π°ΠΌ ΡΡΠΎΠ»Π±Π΅Ρ.
SELECT s.name, g.score, RANK() OVER ( ORDER BY g.score ) FROM grades AS g INNER JOIN students AS s ON s.id = g.student_id; /* name | score | rank ----- | ----- | ---- Betty | 64 | 1 Dina | 64 | 1 Evan | 67 | 3 ... | ... | ... */
ΠΠΎΠ±Π°Π²ΠΈΠΌ Π²ΡΠ΅Π³ΠΎ ΠΎΠ΄Π½Ρ ΡΡΡΠΎΠΊΡ Π² Π½Π°Ρ Π·Π°ΠΏΡΠΎΡ, Π° ΠΈΠΌΠ΅Π½Π½ΠΎ PARTITION BY s.name
ΠΊ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡΡ OVER
. Π ΠΈΡΠΎΠ³Π΅ ΠΌΡ ΠΏΠΎΠ»ΡΡΠΈΠ»ΠΈ ΡΠ°Π½ΠΆΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ Π² ΡΠ°ΠΌΠΊΠ°Ρ
ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΡΡΠ΄Π΅Π½ΡΠ°.
SELECT s.name, g.score, RANK() OVER ( PARTITION BY s.name -- ranks by student ORDER BY g.score ) FROM grades AS g INNER JOIN students AS s ON s.id = g.student_id; /* name | score | rank -------- | ----- | ---- Adam | 75 | 1 Adam | 80 | 2 Adam | 82 | 3 Adam | 82 | 3 Adam | 85 | 5 Betty | 64 | 1 Betty | 69 | 2 Betty | 70 | 3 Betty | 74 | 4 Betty | 75 | 5 Caroline | 90 | 1 Caroline | 92 | 2 ... | ... | ... */
Π£ ΠΎΠΊΠΎΠ½Π½ΡΡ
ΡΡΠ½ΠΊΡΠΈΠΉ Π΅ΡΡΡ Π΅ΡΠ΅ Π½Π΅ΡΠΊΠΎΠ»ΡΠΊΠΎ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΡΡ
Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΠ΅ΠΉ. ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ, ΡΡΠ½ΠΊΡΠΈΠΈ ΡΠ°ΡΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΡ cumulative distribution
, ΡΡΠ½ΠΊΡΠΈΠΈ dense
ΠΈ percent ranks
. dense_rank
β ΡΡΠ½ΠΊΡΠΈΡ Π²ΠΎΠ·Π²ΡΠ°ΡΠ°Π΅Ρ ΡΠ°Π½Π³ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΡΡΡΠΎΠΊΠΈ, Π½ΠΎ Π² ΠΎΡΠ»ΠΈΡΠΈΠ΅ ΠΎΡ ΡΡΠ½ΠΊΡΠΈΠΈ RANK
, ΠΎΠ½Π° Π΄Π»Ρ ΠΎΠ΄ΠΈΠ½Π°ΠΊΠΎΠ²ΡΡ
Π·Π½Π°ΡΠ΅Π½ΠΈΠΉ Π²ΠΎΠ·Π²ΡΠ°ΡΠ°Π΅Ρ ΡΠ°Π½Π³, Π½Π΅ ΠΏΡΠΎΠΏΡΡΠΊΠ°Ρ ΡΠ»Π΅Π΄ΡΡΡΠΈΠΉ. Π‘ ΡΡΠ½ΠΊΡΠΈΡΠΌΠΈ lag
ΠΈ lead
Π²Ρ ΠΌΠΎΠΆΠ΅ΡΠ΅ Π±ΠΎΠ»Π΅Π΅ ΠΏΠΎΠ΄ΡΠΎΠ±Π½ΠΎ ΠΎΠ·Π½Π°ΠΊΠΎΠΌΠΈΡΡΡΡ ΠΏΠΎ ΡΡΡΠ»ΠΊΠ΅.
ΠΠΏΠ΅ΡΠ°ΡΠΎΡ WITH
ΠΠ°Π²Π°ΠΉΡΠ΅ ΠΏΠΎΠ·Π½Π°ΠΊΠΎΠΌΠΈΠΌΡΡ Π΅ΡΠ΅ Ρ ΠΎΠ΄Π½ΠΈΠΌ ΠΈΠ½ΡΡΡΡΠΌΠ΅Π½ΡΠΎΠΌ β ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Π½ΠΈΠ΅ Π²Π»ΠΎΠΆΠ΅Π½Π½ΡΡ
Π·Π°ΠΏΡΠΎΡΠΎΠ². ΠΠΏΠ΅ΡΠ°ΡΠΎΡ WITH
ΠΏΠΎΠ·Π²ΠΎΠ»ΡΠ΅Ρ Π½Π°ΠΌ ΠΏΡΠΈΡΠ²Π°ΠΈΠ²Π°ΡΡ ΠΈΠΌΡ Π½Π°ΡΠ΅ΠΌΡ ΠΏΠΎΠ΄Π·Π°ΠΏΡΠΎΡΡ, Π° ΡΡΠΎ Π·Π½Π°ΡΠΈΡ, ΡΡΠΎ ΠΌΡ ΡΠΌΠΎΠΆΠ΅ΠΌ ΠΎΠ±ΡΠ°ΡΠ°ΡΡΡΡ ΠΊ Π΅Π³ΠΎ ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ°ΠΌ Π² Π΄ΡΡΠ³ΠΈΡ
ΠΌΠ΅ΡΡΠ°Ρ
.
ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ, ΠΌΡ Ρ
ΠΎΡΠΈΠΌ ΡΡΠ°Π²Π½ΠΈΡΡ ΠΎΡΠ΅Π½ΠΊΡ ΡΡΠ΅Π½ΠΈΠΊΠ° (ΠΊΠΎΠ»ΠΎΠ½ΠΊΠ° grades
) Ρ Π΅Π³ΠΎ ΡΡΠ΅Π΄Π½ΠΈΠΌ Π±Π°Π»Π»ΠΎΠΌ. Π‘Π΄Π΅Π»Π°ΡΡ ΡΡΠΎ Π² ΠΎΠ΄Π½ΠΎΠΌ Π·Π°ΠΏΡΠΎΡΠ΅ Π²ΡΠΎΠ΄Π΅ Π±Ρ Π½Π΅ΡΠ»ΠΎΠΆΠ½ΠΎ, Π½Π°ΠΌ ΡΠ½Π°ΡΠ°Π»Π° Π½ΡΠΆΠ½ΠΎ Π²ΡΡΠΈΡΠ»ΠΈΡΡ ΡΡΠ΅Π΄Π½ΠΈΠΉ Π±Π°Π»Π» Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡΠ° GROUP BY
, Π° Π·Π°ΡΠ΅ΠΌ Π½Π°ΠΏΠΈΡΠ°ΡΡ ΡΡΠΎ-ΡΠΎ Π²ΡΠΎΠ΄Π΅ g.score > avg
. ΠΠ°ΡΠ½Π΅ΠΌ Ρ Π°Π³ΡΠ΅Π³Π°ΡΠΈΠΈ GROUP BY
.
SELECT s.name, ROUND(AVG(g.score),1) AS avg FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id GROUP BY s.name; /* name | avg -------- | ---- Dina | 79.6 Evan | 83.4 Betty | 70.4 Caroline | 94.6 Adam | 80.8 */
ΠΡΠΎ Π±ΡΠ»ΠΎ ΠΏΡΠΎΡΡΠΎ. ΠΠΎ ΠΊΠ°ΠΊ ΠΆΠ΅ Π½Π°ΠΌ ΡΡΠ°Π²Π½ΠΈΡΡ ΠΊΠ°ΠΆΠ΄ΡΡ ΠΎΡΠ΅Π½ΠΊΡ ΡΠΎ ΡΡΠ΅Π΄Π½ΠΈΠΌ Π±Π°Π»Π»ΠΎΠΌ? ΠΠ·Π³Π»ΡΠ½ΠΈΡΠ΅ Π½Π° ΠΏΡΠΈΠΌΠ΅ΡΡ Π·Π°ΠΏΡΠΎΡΠΎΠ² Π½ΠΈΠΆΠ΅, Π²ΡΠ΅ ΠΎΠ½ΠΈ Π²ΡΠ΄Π°Π΄ΡΡ ΠΎΡΠΈΠ±ΠΊΡ.
SELECT s.name, ROUND(AVG(g.score),1) AS avg, g.score > avg ... -- ERROR: column "avg" does not exist SELECT s.name, ROUND(AVG(g.score),1) AS avg, g.score > ROUND(AVG(g.score),1) ... -- ERROR: column "g.score" must appear in the GROUP BY -- clause or be used in an aggregate function
ΠΡ ΠΌΠΎΠΆΠ΅ΠΌ Π΄Π²Π°ΠΆΠ΄Ρ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ ΠΎΠΊΠΎΠ½Π½ΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ, Π½ΠΎ ΡΡΠΎ Π²ΡΠ³Π»ΡΠ΄ΠΈΡ ΠΊΠ°ΠΊ ΠΌΠΈΠ½ΠΈΠΌΡΠΌ Π½Π΅ ΠΎΡΠ΅Π½Ρ ΡΠΈΡΠ°Π±Π΅Π»ΡΠ½ΠΎ.
SELECT s.name, AVG(g.score) OVER (PARTITION BY s.name), g.score > AVG(g.score) OVER (PARTITION BY s.name) ...
Π§ΡΠΎΠ±Ρ Π½Π°Ρ Π·Π°ΠΏΡΠΎΡ Π±ΡΠ» ΡΠΈΡΠ°Π±Π΅Π»ΡΠ½ΡΠΌ ΠΈ ΠΌΠ°ΡΡΡΠ°Π±ΠΈΡΡΠ΅ΠΌΡΠΌ, Π±ΡΠ΄Π΅ΠΌ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ WITH
. Π Π°Π·Π΄Π΅Π»ΠΈΠΌ Π½Π°Ρ Π·Π°ΠΏΡΠΎΡ Π½Π° Π΄Π²Π° ΠΏΠΎΠ΄Π·Π°ΠΏΡΠΎΡΠ°: ΠΏΠ΅ΡΠ²ΡΠΉ Π±ΡΠ΄Π΅Ρ ΡΡΠΈΡΠ°ΡΡ ΡΡΠ΅Π΄Π½ΠΈΠΉ Π±Π°Π»Π», Π° Π²ΡΠΎΡΠΎΠΉ Π΄ΠΎΠ±Π°Π²ΠΈΡ Π·Π°ΠΏΠΈΡΠΈ Ρ ΠΈΠ½Π΄ΠΈΠ²ΠΈΠ΄ΡΠ°Π»ΡΠ½ΡΠΌΠΈ ΠΎΡΠ΅Π½ΠΊΠ°ΠΌΠΈ grades
.
WITH averages AS ( SELECT s.id, ROUND(AVG(g.score),1) AS avg_score FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id GROUP BY s.id ) SELECT s.name, g.score, a.avg_score, g.score > a.avg_score AS above_avg FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN averages AS a ON a.id = s.id; /* name | score | avg_score | above_avg ----- | ----- | --------- | --------- Adam | 82 | 80.8 | true Adam | 82 | 80.8 | true Adam | 80 | 80.8 | false Adam | 75 | 80.8 | false Adam | 85 | 80.8 | true Betty | 74 | 70.4 | true Betty | 75 | 70.4 | true */
ΠΠ°, Π±Π΅Π·ΡΡΠ»ΠΎΠ²Π½ΠΎ ΡΡΠΎΡ Π·Π°ΠΏΡΠΎΡ ΠΏΠΎΠ»ΡΡΠΈΠ»ΡΡ Π·Π°ΠΌΠ΅ΡΠ½ΠΎ Π±ΠΎΠ»ΡΡΠ΅, Π½Π΅ΠΆΠ΅Π»ΠΈ Π½Π°ΠΏΠΈΡΠ°Π½ΠΈΠ΅ Π΄Π²ΡΡ ΠΎΠΊΠΎΠ½Π½ΡΡ ΡΡΠ½ΠΊΡΠΈΠΉ. ΠΠ΄Π½Π°ΠΊΠΎ, ΡΠ°ΠΊΠΎΠΉ ΠΏΠΎΠ΄ΡΠΎΠ±Π½ΡΠΉ Π·Π°ΠΏΡΠΎΡ ΠΈΠΌΠ΅Π΅Ρ Π΄Π²Π° Π³Π»Π°Π²Π½ΡΡ ΠΏΡΠ΅ΠΈΠΌΡΡΠ΅ΡΡΠ²Π°: ΡΠΈΡΠ°Π±Π΅Π»ΡΠ½ΠΎΡΡΡ ΠΈ ΠΌΠ°ΡΡΡΠ°Π±ΠΈΡΡΠ΅ΠΌΠΎΡΡΡ.
ΠΠ°ΠΏΡΠΎΡΡ ΠΌΠΎΠ³ΡΡ Π±ΡΡΡ ΡΡΠ΄ΠΎΠ²ΠΈΡΠ½ΠΎ Π΄Π»ΠΈΠ½Π½ΡΠ΅. ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ Π² ΠΊΠΎΠΌΠΏΠ°Π½ΠΈΠΈ Meta*, ΠΌΠ½Π΅ Π²ΡΡΡΠ΅ΡΠ°Π»ΡΡ Π·Π°ΠΏΡΠΎΡ, ΡΠΎΠ΄Π΅ΡΠΆΠ°ΡΠΈΠΉ Π² ΡΠ΅Π±Π΅ 1000 ΡΡΡΠΎΠΊ ΠΈ Π²ΡΠ·ΡΠ²Π°Π» ΡΡΠ°Π·Ρ 25 ΡΠ°Π±Π»ΠΈΡ. ΠΡΠΎΡ Π·Π°ΠΏΡΠΎΡ Π±ΡΠ» Π±Ρ ΡΠΎΠ²Π΅ΡΡΠ΅Π½Π½ΠΎ Π½Π΅ΡΠΈΡΠ°Π΅ΠΌΡΠΌ Π±Π΅Π· ΠΏΡΠΈΠΌΠ΅Π½Π΅Π½ΠΈΡ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡΠ° WITH
, ΠΊΠΎΡΠΎΡΡΠΉ ΡΠ°Π·Π³ΡΠ°Π½ΠΈΡΠΈΠ²Π°Π΅Ρ ΠΎΡΠ΄Π΅Π»ΡΠ½ΡΠ΅ ΡΡΠ°ΡΡΠΊΠΈ ΠΊΠΎΠ΄Π° ΠΈ ΠΏΡΠΈΡΠ²Π°ΠΈΠ²Π°Π΅Ρ ΠΈΠΌ ΠΏΡΠ΅Π²Π΄ΠΎΠ½ΠΈΠΌΡ.
Π Π°Π±ΠΎΡΠ°Ρ Ρ Π±ΠΎΠ»ΡΡΠΈΠΌ ΠΎΠ±ΡΠ΅ΠΌΠΎΠΌ Π΄Π°Π½Π½ΡΡ , ΠΌΡ Π½Π΅ ΠΈΠΌΠ΅Π΅ΠΌ ΡΠ°ΠΊΠΈΡ ΡΠΎΡΠΊΠΎΡΠ½ΡΡ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΠ΅ΠΉ ΠΊΠ°ΠΊ ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°ΡΠ΅Π»ΡΠ½ΠΎΠ΅ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ ΠΏΠΎΠ΄Π·Π°ΠΏΡΠΎΡΠΎΠ², ΡΠΎΡ ΡΠ°Π½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½ΡΡ Π² CSV ΡΠΎΡΠΌΠ°Ρ, Π·Π°ΡΠ΅ΠΌ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΠ΅ ΠΏΠΎΠ»ΡΡΠ΅Π½Π½ΡΡ Π΄Π°Π½Π½ΡΡ ΠΈ Π°Π½Π°Π»ΠΈΠ·Π° ΠΈΡ Ρ ΠΏΠΎΠΌΠΎΡΡΡ Python. ΠΡΠ΅ Π²Π·Π°ΠΈΠΌΠΎΠ΄Π΅ΠΉΡΡΠ²ΠΈΡ Ρ Π±Π°Π·ΠΎΠΉ Π΄Π°Π½Π½ΡΡ Π΄ΠΎΠ»ΠΆΠ½Ρ ΠΏΡΠΎΠΈΡΡ ΠΎΠ΄ΠΈΡΡ Π·Π° ΠΎΠ΄ΠΈΠ½ ΡΠ°Π·.
ΠΠ°Π²Π°ΠΉΡΠ΅ ΡΠ°Π·Π±Π΅ΡΠ΅ΠΌ Π΅ΡΠ΅ ΠΎΠ΄ΠΈΠ½ Π²Π°ΡΠΈΠ°Π½Ρ. ΠΡΠ΅Π΄ΠΏΠΎΠ»ΠΎΠΆΠΈΠΌ, Π² Π½Π°ΡΠ΅ΠΉ ΡΠΊΠΎΠ»Π΅ ΡΠ²ΠΎΠ»ΠΈΠ»ΠΈ Π΄ΠΈΡΠ΅ΠΊΡΠΎΡΠ°, ΠΈ ΡΠΈΡΡΠ΅ΠΌΠ° ΠΎΡΠ΅Π½ΠΎΠΊ ΠΏΡΠ΅ΡΠ΅ΡΠΏΠ΅Π»Π° ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΡ. Π£ Π½Π°Ρ ΠΏΠΎΡΠ²ΠΈΠ»ΡΡ ΡΠ°ΠΊΠΎΠΉ ΠΏΠΎΠΊΠ°Π·Π°ΡΠ΅Π»Ρ ΠΊΠ°ΠΊ ΡΡΠ΅Π΄Π½Π΅Π²Π·Π²Π΅ΡΠ΅Π½Π½ΡΠΉ Π±Π°Π»Π», ΠΊΠΎΡΠΎΡΡΠΉ Π²ΡΡΠΈΡΠ»ΡΠ΅ΡΡΡ ΠΊΠ°ΠΊ ΠΎΡΠ΅Π½ΠΊΠ° (grades
) ΡΠΌΠ½ΠΎΠΆΠ΅Π½Π½Π°Ρ Π½Π° ΠΊΠΎΡΡΡΠΈΡΠΈΠ΅Π½Ρ ΡΠ»ΠΎΠΆΠ½ΠΎΡΡΠΈ (weight
). Π’Π΅ΠΏΠ΅ΡΡ Π΄Π»Ρ ΡΠ΄Π°ΡΠΈ ΡΠΊΠ·Π°ΠΌΠ΅Π½Π° Π½Π΅ΠΎΠ±Ρ
ΠΎΠ΄ΠΈΠΌΠΎ ΠΈΠΌΠ΅ΡΡ: ΡΡΠ΅Π΄Π½Π΅Π²Π·Π²Π΅ΡΠ΅Π½Π½ΡΠΉ Π±Π°Π»Π» ΠΏΠΎ Π²ΡΠ΅ΠΌ ΠΏΡΠ΅Π΄ΠΌΠ΅ΡΠ°ΠΌ Π½Π΅ Π½ΠΈΠΆΠ΅ 85% ΠΈΠ»ΠΈ ΡΡΠΎΡ ΠΆΠ΅ Π±Π°Π»Π» Π½Π΅ Π½ΠΈΠΆΠ΅ 70% ΠΏΠΎ ΡΠΎΠ±ΡΡΠ²Π΅Π½Π½ΠΎΠΌΡ ΠΏΡΠΎΠ΅ΠΊΡΡ. ΠΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½ΠΎΠΉ Π»ΠΎΠ³ΠΈΠΊΠΈ Π² ΠΎΠ΄ΠΈΠ½ Π±Π»ΠΎΠΊ CASE WHEN
Π΄ΠΎΡΡΠ°ΡΠΎΡΠ½ΠΎ ΡΠ»ΠΎΠΆΠ½ΠΎ, Π½ΠΎ, Π΅ΡΠ»ΠΈ ΠΌΡ ΡΠ°Π·Π΄Π΅Π»ΠΈΠΌ Π·Π°ΠΏΡΠΎΡ Ρ ΠΏΠΎΠΌΠΎΡΡΡ WITH
Π²ΡΡ ΡΡΠ°Π½Π΅Ρ Π³ΠΎΡΠ°Π·Π΄ΠΎ ΠΏΡΠΎΡΠ΅.
ΠΠ°Π²Π°ΠΉΡΠ΅ Π½Π°ΡΠ½Π΅ΠΌ Ρ ΡΠΎΠ³ΠΎ, ΡΡΠΎ Π²ΡΡΠ²ΠΈΠΌ ΡΡΠ΅Π½ΠΈΠΊΠΎΠ², Ρ ΠΊΠΎΡΠΎΡΡΡ ΡΡΠ΅Π΄Π½Π΅Π²Π·Π²Π΅ΡΠ΅Π½Π½ΡΠΉ Π±Π°Π»Π» Π±ΠΎΠ»ΡΡΠ΅ 85%.
SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id GROUP BY s.name HAVING SUM(g.score * a.weight) > 85; /* name -------- Caroline */
ΠΡΠ΅ΠΊΡΠ°ΡΠ½ΠΎ. Π’Π΅ΠΏΠ΅ΡΡ ΡΠ·Π½Π°Π΅ΠΌ Ρ ΠΊΠΎΠ³ΠΎ ΠΈΠ· ΡΡΠ΅Π½ΠΈΠΊΠΎΠ² Π±ΠΎΠ»ΡΡΠ΅ 70% ΠΏΠΎ ΠΏΠ΅ΡΡΠΎΠ½Π°Π»ΡΠ½ΠΎΠΌΡ ΠΏΡΠΎΠ΅ΠΊΡΡ.
SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id WHERE a.name = 'biography' AND g.score > 70 /* name -------- Adam Caroline Evan */
ΠΠΎ, ΠΊΠ°ΠΊ ΠΌΡ ΠΏΠΎΠΌΠ½ΠΈΠΌ, Π½Π°ΠΌ Π½ΡΠΆΠ½ΠΎ, ΡΡΠΎΠ±Ρ Π½Π°Ρ Π·Π°ΠΏΡΠΎΡ Π²ΡΠ΄Π°Π» Π²ΡΠ΅Ρ ΡΡΠ΅Π½ΠΈΠΊΠΎΠ², ΠΊΠΎΡΠΎΡΡΠ΅ ΡΠ΄ΠΎΠ²Π»Π΅ΡΠ²ΠΎΡΡΡΡ Π»ΠΈΠ±ΠΎ ΠΏΠ΅ΡΠ²ΠΎΠΌΡ, Π»ΠΈΠ±ΠΎ Π²ΡΠΎΡΠΎΠΌΡ ΡΡΠ»ΠΎΠ²ΠΈΡ.
SELECT DISTINCT name FROM students WHERE name IN <people_who_passed_final> OR name IN <people_who_passed_project>;
ΠΡΠΎ ΠΎΡΠ΅Π½Ρ ΠΏΡΠΎΡΡΠΎ Ρ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°Π½ΠΈΠ΅ΠΌ WITH
. ΠΡ ΠΏΡΠΈΡΠ²ΠΎΠΈΠΌ ΠΏΡΠ΅Π²Π΄ΠΎΠ½ΠΈΠΌΡ Π½Π°ΡΠΈΠΌ Π·Π°ΠΏΡΠΎΡΠ°ΠΌ weighted_pass
ΠΈ project_pass
, Π° ΠΏΠΎΡΠΎΠΌ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½ΠΈΠΌ ΠΈΡ
.
WITH weighted_pass AS ( SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id GROUP BY s.name HAVING SUM(g.score * a.weight) > 85 ), project_pass AS ( SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id WHERE a.name = 'biography' AND g.score > 70 ) SELECT DISTINCT name FROM students WHERE name IN (SELECT name FROM weighted_pass) OR name IN (SELECT name FROM project_pass); /* name -------- Evan Caroline Adam */
Π‘ΠΌΠΎΡΡΠΈΠΌ Π²Π³Π»ΡΠ±Ρ β EXPLAIN
Π Π·Π°Π²Π΅ΡΡΠ΅Π½ΠΈΠΈ Π΄Π°Π½Π½ΠΎΠΉ ΡΡΠ°ΡΡΠΈ, Π΄Π°Π²Π°ΠΉΡΠ΅ ΡΠ°Π·Π±Π΅ΡΠ΅ΠΌ Π΅ΡΠ΅ ΠΎΠ΄Π½Ρ Π²Π°ΠΆΠ½ΡΡ ΡΠ΅ΠΌΡ. Π§Π΅ΠΌ Π±ΠΎΠ»ΡΡΠ΅ ΠΌΡ ΠΈΠ·ΡΡΠ°Π΅ΠΌ SQL
, ΡΠ΅ΠΌ Π±ΠΎΠ»ΡΡΠ΅ ΡΠΏΠΎΡΠΎΠ±ΠΎΠ² ΠΏΠΎΡΡΡΠΎΠ΅Π½ΠΈΡ ΡΠ»ΠΎΠΆΠ½ΡΡ
Π·Π°ΠΏΡΠΎΡΠΎΠ² Π½Π°ΠΌ ΠΈΠ·Π²Π΅ΡΡΠ½ΠΎ. ΠΠΎΠ³Π΄Π° Π»ΡΡΡΠ΅ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ EXCEPT
, Π° ΠΊΠΎΠ³Π΄Π° NOT IN
? ΠΡΠΆΠ½ΠΎ Π»ΠΈ Π½Π°ΠΌ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ Π½Π΅ΡΠΊΠΎΠ»ΡΠΊΠΎ JOIN
Π΄Π»Ρ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΡ ΡΠ°Π±Π»ΠΈΡ, Π»ΠΈΠ±ΠΎ Π»ΡΡΡΠ΅ ΠΏΡΠΈΠΌΠ΅Π½ΠΈΡΡ WITH
ΠΈ UNION ALL
?
Π Π² ΠΈΡΠΎΠ³Π΅, ΠΊΠ°ΠΊ Π½Π°ΠΌ ΠΏΠΎΠ½ΡΡΡ, ΡΡΠΎ ΠΎΠ΄ΠΈΠ½ Π·Π°ΠΏΡΠΎΡ Π±ΠΎΠ»Π΅Π΅ ΡΡΡΠ΅ΠΊΡΠΈΠ²Π΅Π½, ΡΠ΅ΠΌ Π΄ΡΡΠ³ΠΎΠΉ?
Postgres ΠΌΠΎΠΆΠ΅Ρ Π½Π°ΠΌ ΠΎΠ± ΡΡΠΎΠΌ ΡΠ°ΡΡΠΊΠ°Π·Π°ΡΡ. ΠΠ»ΡΡΠ΅Π²ΠΎΠ΅ ΡΠ»ΠΎΠ²ΠΎ explain
ΠΏΡΠ΅Π΄ΠΎΡΡΠ°Π²Π»ΡΠ΅Ρ Π½Π°ΠΌ ΠΏΠ»Π°Π½ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ, ΠΊΠΎΡΠΎΡΡΠΉ ΠΏΠΎΠ΄ΡΠΎΠ±Π½ΠΎ ΠΎΠΏΠΈΡΡΠ²Π°Π΅Ρ ΠΊΠ°ΠΊ Π²ΡΠΏΠΎΠ»Π½ΡΠ΅ΡΡΡ Π²Π°Ρ Π·Π°ΠΏΡΠΎΡ. ΠΠ΅ΡΠ½Π΅ΠΌΡΡ ΠΊ Π·Π°ΠΏΡΠΎΡΡ ΠΈΠ· Π½Π°ΡΠ°Π»Π° ΡΡΠ°ΡΡΠΈ ΠΈ ΡΠ²ΠΈΠ΄ΠΈΠΌ, ΡΡΠΎ Postgres Π²ΡΠΏΠΎΠ»Π½ΡΠ΅Ρ Π·Π°ΠΏΡΠΎΡ ΡΠΎΠ²Π΅ΡΡΠ΅Π½Π½ΠΎ Π½Π΅ Π² ΡΠΎΠΌ ΠΏΠΎΡΡΠ΄ΠΊΠ΅, Π² ΠΊΠ°ΠΊΠΎΠΌ ΠΌΡ Π½Π°ΠΏΠΈΡΠ°Π»ΠΈ.
EXPLAIN SELECT s.id AS student_id, g.score FROM students AS s LEFT JOIN grades AS g ON s.id = g.student_id WHERE g.score > 90 ORDER BY g.score DESC; /* QUERY PLAN ---------- Sort (cost=80.34..81.88 rows=617 width=8) [...] Sort Key: g.score DESC [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8) [...] Hash Cond: (g.student_id = s.id) [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8) [...] Filter: (score > 90) [...] -> Hash (cost=13.10..13.10 rows=310 width=4) [...] -> Seq Scan on students s (cost=0.00..13.20 rows=320 width=4) */
ΠΠΎΠΆΠ½ΠΎ ΠΏΠΎΠΉΡΠΈ Π΅ΡΠ΅ Π΄Π°Π»ΡΡΠ΅ ΠΈ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡ EXPLAIN ANALYZE
, ΠΊΠΎΡΠΎΡΡΠΉ ΠΎΡΠΎΠ±ΡΠ°Π·ΠΈΡ Π΅ΡΠ΅ Π±ΠΎΠ»Π΅Π΅ Π΄Π΅ΡΠ°Π»ΡΠ½ΡΡ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ (Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ, Π²ΡΠ΅ΠΌΡ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΡΠ°ΡΡΠΈ Π·Π°ΠΏΡΠΎΡΠ° ΠΈ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌΡΡ ΠΏΠ°ΠΌΡΡΡ).
EXPLAIN ANALYZE SELECT s.id AS student_id, g.score FROM students AS s LEFT JOIN grades AS g ON s.id = g.student_id WHERE g.score > 90 ORDER BY g.score DESC; /* QUERY PLAN ---------- Sort (cost=80.34..81.88 rows=617 width=8) (actual tiem=0.169..0.171 rows=6 loops=1) [...] Sort Key: g.score DESC [...] Sort Method: quicksort Memory: 25kB [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8) (actual time=0.115..0.145 rows=6 loops=1) [...] Hash Cond: (g.student_id = s.id) [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8) (actual time=0.045..0.052 rows=6 loops=1) [...] Filter: (score > 90) Rows removed by Filter: 19 [...] -> Hash (cost=13.10..13.10 rows=310 width=4) (actual time=0.059..0.060 rows=5 loops=1) [...] Buckets: 1024 Batches: 1 Memory Usage: 9kB [...] -> Seq Scan on students s (cost=0.00..13.10 rows=310 width=4) (actual time=0.022..0.027 rows=5 loops=1) Planning Time: 0.379 ms Execution Time: 0.227 ms */
ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ Π² ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ΅ Π²ΡΡΠ΅ ΠΌΠΎΠΆΠ½ΠΎ ΡΠ²ΠΈΠ΄Π΅ΡΡ, ΡΡΠΎ Postgres ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°ΡΠ΅Π»ΡΠ½ΠΎ ΡΠΊΠ°Π½ΠΈΡΡΠ΅Ρ (Seq Scan
) ΡΠ°Π±Π»ΠΈΡΡ grades
ΠΈ students
, ΠΏΠΎΡΠΎΠΌΡ ΡΡΠΎ ΠΎΠ½ΠΈ Π½Π΅ ΠΈΠ½Π΄Π΅ΠΊΡΠΈΡΠΎΠ²Π°Π½Ρ. ΠΠ½Π°ΡΠ΅ Π³ΠΎΠ²ΠΎΡΡ, Postgres Π½Π΅ Π·Π½Π°Π΅Ρ ΡΡΠΎ ΡΡΡΠΎΠΊΠ° Π²Π½ΠΈΠ·Ρ ΡΠ°Π±Π»ΠΈΡΡ ΠΈΠΌΠ΅Π΅Ρ id
ΠΌΠ΅Π½ΡΡΠ΅ ΠΈΠ»ΠΈ Π±ΠΎΠ»ΡΡΠ΅ ΡΠ΅ΠΌ ΡΡΡΠΎΠΊΠ° Π½Π°Π²Π΅ΡΡ
Ρ ΡΠ°Π±Π»ΠΈΡΡ. Π Π½Π°ΡΠ΅ΠΌ ΡΠ»ΡΡΠ°Π΅, ΡΡΠΎ Π½Π΅ ΠΈΠΌΠ΅Π΅Ρ ΠΎΡΠΎΠ±ΠΎΠ³ΠΎ Π·Π½Π°ΡΠ΅Π½ΠΈΡ Π²Π²ΠΈΠ΄Ρ ΡΠ°Π·ΠΌΠ΅ΡΠΎΠ² Π½Π°ΡΠ΅ΠΉ Π±Π°Π·Ρ Π΄Π°Π½Π½ΡΡ
, Π½ΠΎ Π΅ΡΠ»ΠΈ Π±Ρ ΠΌΡ ΡΠ°Π±ΠΎΡΠ°Π»ΠΈ Ρ ΠΌΠΈΠ»Π»ΠΈΠΎΠ½Π°ΠΌΠΈ ΡΡΡΠΎΠΊ Π΄Π°Π½Π½ΡΡ
, ΡΠΎ Π½Π°ΠΌ ΠΎΠΏΡΠ΅Π΄Π΅Π»Π΅Π½Π½ΠΎ ΠΏΠΎΡΡΠ΅Π±ΠΎΠ²Π°Π»ΠΎΡΡ Π±Ρ Π½Π°ΠΉΡΠΈ ΠΈ ΡΡΡΡΠ°Π½ΠΈΡΡ ΡΠ°ΠΊΠΈΠ΅ ΡΠ·ΠΊΠΈΠ΅ ΠΌΠ΅ΡΡΠ° Π² ΠΏΡΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡΠ΅Π»ΡΠ½ΠΎΡΡΠΈ.
ΠΠ°Π½Π½Π°Ρ ΡΡΠ°ΡΡΡ Π±ΡΠ»Π° ΠΎΠ±Π·ΠΎΡΠΎΠΌ ΡΠ΅Ρ Π½Π°Π²ΡΠΊΠΎΠ² Π² SQL, ΠΊΠΎΡΠΎΡΡΠ΅ Π²Π°ΠΌ ΡΠΎΡΠ½ΠΎ ΠΏΠΎΠ½Π°Π΄ΠΎΠ±ΡΡΡΡ ΡΡΠ°Π·Ρ ΠΏΠΎΡΠ»Π΅ ΠΏΠΎΠ»ΡΡΠ΅Π½ΠΈΡ Π±Π°Π·ΠΎΠ²ΡΡ Π·Π½Π°Π½ΠΈΠΉ. ΠΡ Π½Π°ΡΠ°Π»ΠΈ Ρ ΡΡΡΠ°Π½ΠΎΠ²ΠΊΠΈ Postgres ΠΈ pgAdmin, ΡΠ΅ΠΌ ΡΠ°ΠΌΡΠΌ ΠΏΠΎΠ»ΡΡΠΈΠ² Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΡ ΡΠΊΡΠΏΠ΅ΡΠΈΠΌΠ΅Π½ΡΠΈΡΠΎΠ²Π°ΡΡ Ρ Π±Π°Π·ΠΎΠΉ Π΄Π°Π½Π½ΡΡ Π½Π° ΡΠ²ΠΎΡΠΌ ΠΊΠΎΠΌΠΏΡΡΡΠ΅ΡΠ΅.
ΠΠ°ΡΠ΅ΠΌ ΠΌΡ ΡΠ·Π½Π°Π»ΠΈ ΠΏΡΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΡΠ΅ ΡΠΈΠ½ΡΠ°ΠΊΡΠΈΡΠ΅ΡΠΊΠΈΠ΅ ΠΊΠΎΠ½ΡΡΡΡΠΊΡΠΈΠΈ, ΠΊΠΎΡΠΎΡΡΠ΅ ΠΏΠΎΠ·Π²ΠΎΠ»ΠΈΠ»ΠΈ Π½Π°ΠΌ ΡΠΎΡΡΠ°Π²Π»ΡΡΡ Π±ΠΎΠ»Π΅Π΅ ΡΠ»ΠΎΠΆΠ½ΡΠ΅ Π·Π°ΠΏΡΠΎΡΡ. ΠΡ Π½Π°ΡΠ°Π»ΠΈ Ρ ΡΠΈΠ»ΡΡΡΠ°ΡΠΈΠΈ Π΄Π°Π½Π½ΡΡ
ΠΈ ΡΠ·Π½Π°Π»ΠΈ, ΡΠ΅ΠΌ ΠΎΡΠ»ΠΈΡΠ°Π΅ΡΡΡ WHERE
ΠΎΡ HAVING
. ΠΠ°Π»Π΅Π΅ ΠΌΡ ΠΏΠΎΠ·Π½Π°ΠΊΠΎΠΌΠΈΠ»ΠΈΡΡ Ρ ΡΡΠ»ΠΎΠ²Π½ΡΠΌΠΈ ΠΊΠΎΠ½ΡΡΡΡΠΊΡΠΈΡΠΌΠΈ, ΡΠ·Π½Π°Π»ΠΈ ΠΊΠ°ΠΊ ΡΠ΅Π³ΠΌΠ΅Π½ΡΠΈΡΠΎΠ²Π°ΡΡ Π΄Π°Π½Π½ΡΠ΅ Ρ ΠΏΠΎΠΌΠΎΡΡΡ CASE WHEN
ΠΈ ΠΎΠ±ΡΠ°Π±Π°ΡΡΠ²Π°ΡΡ null
, ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΡ COALESCE
. ΠΡ ΠΏΠ΅ΡΠ΅ΡΠ»ΠΈ ΠΎΡ Π³ΠΎΡΠΈΠ·ΠΎΠ½ΡΠ°Π»ΡΠ½ΠΎΠ³ΠΎ ΠΊ Π²Π΅ΡΡΠΈΠΊΠ°Π»ΡΠ½ΠΎΠΌΡ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΡ ΡΠ°Π±Π»ΠΈΡ Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΎΠΏΠ΅ΡΠ°ΡΠΈΠΉ Π½Π°Π΄ ΠΌΠ½ΠΎΠΆΠ΅ΡΡΠ²Π°ΠΌΠΈ, ΡΠ°Π·ΠΎΠ±ΡΠ°Π² ΠΊΠ°ΠΊ UNION
, UNION ALL
, INTERSECT
, ΠΈ EXCEPT
ΡΠ°Π±ΠΎΡΠ°ΡΡ Ρ ΠΏΠΎΠ²ΡΠΎΡΡΡΡΠΈΠΌΠΈΡΡ ΡΡΡΠΎΠΊΠ°ΠΌΠΈ Π² ΡΠ°Π±Π»ΠΈΡΠ°Ρ
. Π Π² ΠΊΠΎΠ½ΡΠ΅ Π²ΡΠΎΡΠΎΠΉ ΡΠ°ΡΡΠΈ ΡΡΠ°ΡΡΠΈ ΡΠ·Π½Π°Π»ΠΈ ΠΊΠ°ΠΊ ΡΠΎΠ·Π΄Π°Π²Π°ΡΡ ΠΌΠ°ΡΡΠΈΠ²Ρ ΠΈ ΡΠ°Π±ΠΎΡΠ°ΡΡ Ρ Π½ΠΈΠΌΠΈ.
Π ΡΠΈΠ½Π°Π»ΡΠ½ΠΎΠΉ ΡΠ°ΡΡΠΈ ΠΌΡ ΡΠ°Π·ΠΎΠ±ΡΠ°Π»ΠΈ, ΠΊΠ°ΠΊ ΡΠΎΡΡΠ°Π²Π»ΡΡΡ Π΅ΡΠ΅ Π±ΠΎΠ»Π΅Π΅ ΡΠ»ΠΎΠΆΠ½ΡΠ΅ Π·Π°ΠΏΡΠΎΡΡ, ΡΠ°ΠΊΠΈΠ΅ ΠΊΠ°ΠΊ ΠΎΠ±ΡΠ΅Π΄ΠΈΠ½Π΅Π½ΠΈΠ΅ ΡΠ°Π±Π»ΠΈΡΡ Ρ ΡΠ°ΠΌΠΎΠΉ ΡΠΎΠ±ΠΎΠΉ, ΠΎΠΊΠΎΠ½Π½ΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ Π΄Π»Ρ ΡΡΠ°Π²Π½Π΅Π½ΠΈΡ Π΄Π°Π½Π½ΡΡ
, ΠΈ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡ WITH
Π΄Π»Ρ ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Π½ΠΈΡ Π²Π»ΠΎΠΆΠ΅Π½Π½ΡΡ
Π·Π°ΠΏΡΠΎΡΠΎΠ². Π Π² ΠΊΠΎΠ½ΡΠ΅ ΠΌΡ ΡΠ·Π½Π°Π»ΠΈ, ΡΡΠΎ Ρ ΠΏΠΎΠΌΠΎΡΡΡ EXPLAIN
ΠΈ EXPLAIN ANALYZE
ΠΌΠΎΠΆΠ½ΠΎ ΠΎΡΠ΅Π½ΠΈΡΡ ΠΏΡΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡΠ΅Π»ΡΠ½ΠΎΡΡΡ Π½Π°ΡΠΈΡ
Π·Π°ΠΏΡΠΎΡΠΎΠ² ΠΈ ΡΠ·Π½Π°ΡΡ Π² ΠΊΠ°ΠΊΠΈΡ
ΠΌΠ΅ΡΡΠ°Ρ
ΠΈΡ
ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°ΡΡ.
Π ΠΊΠΎΠ½Π΅ΡΠ½ΠΎ ΠΆΠ΅ ΡΡΠΎ Π΄Π°Π»Π΅ΠΊΠΎ Π½Π΅ Π²ΡΡ. ΠΡΡΡ Π΅ΡΠ΅ ΠΌΠ½ΠΎΠ³ΠΎ ΡΡΠ½ΠΊΡΠΈΠΉ, ΠΏΡΠΎ ΠΊΠΎΡΠΎΡΡΠ΅ ΡΡΠΎΠΈΡ Π·Π½Π°ΡΡ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ CAST
(Π΄Π»Ρ ΠΏΡΠΈΠ²Π΅Π΄Π΅Π½ΠΈΡ ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΠΈΠΏΠ° Π΄Π°Π½Π½ΡΡ
ΠΊ Π΄ΡΡΠ³ΠΎΠΌΡ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ float
ΠΊ integer
), ΠΈΠ»ΠΈ ΡΡΠ½ΠΊΡΠΈΠΈ, ΠΎΠΏΡΠ΅Π΄Π΅Π»ΡΠ΅ΠΌΡΠ΅ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΌ (user-defined functions
), ΠΊΠΎΡΠΎΡΡΠ΅ ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ Π΄Π»Ρ Π΄Π°Π»ΡΠ½Π΅ΠΉΡΠ΅Π³ΠΎ ΡΠΏΡΠΎΡΠ΅Π½ΠΈΡ ΠΊΠΎΠ΄Π°. ΠΡΡ ΡΡΠΎ Π±Π΅Π·ΡΡΠ»ΠΎΠ²Π½ΠΎ ΠΎΡΠ΅Π½Ρ ΠΏΠΎΠ»Π΅Π·Π½ΠΎ, Π½ΠΎ Ρ Π±Ρ ΡΠ΅ΠΊΠΎΠΌΠ΅Π½Π΄ΠΎΠ²Π°Π» Π²ΡΠ΅Π³Π΄Π° Π΄ΡΠΌΠ°ΡΡ ΠΎΠ± ΠΎΠΏΡΠΈΠΌΠΈΠ·Π°ΡΠΈΠΈ Π·Π°ΠΏΡΠΎΡΠΎΠ². ΠΠ°ΠΆΠ΅ Π² FAANG ΠΊΠΎΠΌΠΏΠ°Π½ΠΈΡΡ
Ρ ΠΏΡΠ°ΠΊΡΠΈΡΠ΅ΡΠΊΠΈ Π½Π΅ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½Π½ΡΠΌΠΈ Π²ΡΡΠΈΡΠ»ΠΈΡΠ΅Π»ΡΠ½ΡΠΌΠΈ ΡΠ΅ΡΡΡΡΠ°ΠΌΠΈ, Π·Π°ΠΏΡΠΎΡΡ ΠΌΠΎΠ³ΡΡ Π·Π°Π²Π΅ΡΡΠ°ΡΡΡΡ Ρ ΠΎΡΠΈΠ±ΠΊΠΎΠΉ Π² ΡΠΎΠΌ ΡΠ»ΡΡΠ°Π΅, Π΅ΡΠ»ΠΈ ΠΎΠ½ΠΈ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΡΡ Π±ΠΎΠ»ΡΡΠ΅ ΠΏΠ°ΠΌΡΡΠΈ, ΡΠ΅ΠΌ ΠΌΠΎΠΆΠ΅Ρ ΠΏΡΠ΅Π΄ΠΎΡΡΠ°Π²ΠΈΡΡ ΡΠ΅ΡΠ²Π΅Ρ. ΠΡΠ±ΠΎΡ ΠΏΡΠ°Π²ΠΈΠ»ΡΠ½ΠΎΠ³ΠΎ ΠΏΠΎΠ΄Ρ
ΠΎΠ΄Π° ΠΊ Π½Π°ΠΏΠΈΡΠ°Π½ΠΈΡ Π·Π°ΠΏΡΠΎΡΠ°, ΡΠΏΡΠΎΡΠ°Π΅Ρ ΡΠ°Π±ΠΎΡΡ Ρ ΠΏΠΎΡΠΎΠΊΠΎΠΌ Π΄Π°Π½Π½ΡΡ
, ΡΡΠΎ ΡΠ½ΠΈΠΆΠ°Π΅Ρ ΡΠ°Π½Ρ ΠΏΠΎΠ»ΡΡΠΈΡΡ Π³Π½Π΅Π²Π½ΡΠΉ Π·Π²ΠΎΠ½ΠΎΠΊ Π² ΠΏΠΎΠ»Π½ΠΎΡΡ.
ΠΠΎΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅
1. CASCADE
Π§ΡΠΎ ΠΆΠ΅ Π½Π° ΡΠ°ΠΌΠΎΠΌ Π΄Π΅Π»Π΅ ΠΏΡΠΎΠΈΡΡ
ΠΎΠ΄ΠΈΡ ΠΊΠΎΠ³Π΄Π° ΠΌΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌ CASCADE
ΠΏΡΠΈ ΡΠ΄Π°Π»Π΅Π½ΠΈΠΈ ΡΠ°Π±Π»ΠΈΡΡ?
ΠΠ°Π²Π°ΠΉΡΠ΅ ΠΏΡΠ΅Π΄ΠΏΠΎΠ»ΠΎΠΆΠΈΠΌ, ΡΡΠΎ ΠΌΡ ΡΠ΄Π°Π»ΡΠ΅ΠΌ ΡΠ°Π±Π»ΠΈΡΡ classrooms
ΠΈ Π½Π΅ Π·Π°ΡΡΠ°Π³ΠΈΠ²Π°Π΅ΠΌ ΠΎΡΡΠ°Π»ΡΠ½ΡΠ΅. ΠΠ°Π½Π½ΡΠ΅ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ students
Π½Π΅ Π·Π°ΡΡΠΎΠ½ΡΡΡ, Π² ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ΅ Π·Π°ΠΏΡΠΎΡΠ° ΠΌΡ ΠΏΠΎ ΠΏΡΠ΅ΠΆΠ½Π΅ΠΌΡ Π²ΠΈΠ΄ΠΈΠΌ id
ΠΊΠ»Π°ΡΡΠ°.
SELECT s.name, s.classroom_id, c.teacher FROM students AS s LEFT JOIN classrooms AS c ON c.id = s.classroom_id; /* name | classroom_id | teacher -------- | ------------ | ------- Adam | 1 | Mary Betty | 1 | Mary Caroline | 2 | Jonah Dina | [null] | [null] Evan | [null] | [null] */ DROP TABLE classrooms CASCADE; /* DROP TABLE Query returned successfully in 71 msec. */ SELECT * FROM students; /* id | name | classroom_id | best_friend_id -- | -------- | ------------ | -------------- 1 | Adam | 1 | 5 2 | Betty | 1 | 4 3 | Caroline | 2 | 2 4 | Dina | [null] | 2 5 | Evan | [null] | 1 */
Π’Π΅ΠΏΠ΅ΡΡ Π·Π°Π½ΠΎΠ²ΠΎ ΡΠΎΠ·Π΄Π°Π΄ΠΈΠΌ ΡΠ°Π±Π»ΠΈΡΡ classrooms
ΠΈ Π΄ΠΎΠ±Π°Π²ΠΈΠΌ Π² Π½Π΅Π΅ Π½ΠΎΠ²ΡΡ
ΡΡΠΈΡΠ΅Π»Π΅ΠΉ, ΠΊΠΎΡΠΎΡΡΡ
Π½Π΅ Π±ΡΠ»ΠΎ Π² ΠΎΡΠΈΠ³ΠΈΠ½Π°Π»ΡΠ½ΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΠ΅. Π‘Π²ΡΠ·Ρ ΠΌΠ΅ΠΆΠ΄Ρ ΡΠ°Π±Π»ΠΈΡΠ°ΠΌΠΈ students
ΠΈ classrooms
ΡΠΆΠ΅ Π½Π΅ ΡΠΎΡΠ½Π°Ρ, ΠΏΠΎΡΠΊΠΎΠ»ΡΠΊΡ ΠΈΠΌΠ΅Π½Π° ΡΡΠΈΡΠ΅Π»Π΅ΠΉ ΠΈΠ·ΠΌΠ΅Π½ΠΈΠ»ΠΈ Π² ΡΡΠ°Π²Π½Π΅Π½ΠΈΠΈ Ρ ΠΎΡΠΈΠ³ΠΈΠ½Π°Π»ΡΠ½ΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΠ΅ΠΉ.
CREATE TABLE classrooms ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, teacher VARCHAR(100) ); /* CREATE TABLE Query returned successfully in 139 msec. */ INSERT INTO classrooms (teacher) VALUES ('Dr. Random'), ('Alien Banana'); /* INSERT 0 2 Query returned successfully in 99 msec. */ SELECT s.name, s.classroom_id, c.teacher FROM students AS s LEFT JOIN classrooms AS c ON c.id = s.classroom_id; /* name | classroom_id | teacher -------- | ------------ | ----------- Adam | 1 | Dr. Random Betty | 1 | Dr. Random Caroline | 2 | Alien Banana Dina | [null] | [null] Evan | [null] | [null] */
Π’Π°ΠΊ ΠΏΡΠΎΠΈΠ·ΠΎΡΠ»ΠΎ, ΠΏΠΎΡΠΎΠΌΡ ΡΡΠΎ CASCADE
ΡΠ΄Π°Π»ΠΈΠ» Π²Π½Π΅ΡΠ½ΠΈΠΉ ΠΊΠ»ΡΡ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ students
. ΠΡ ΠΌΠΎΠΆΠ΅ΠΌ ΡΡΠΎ ΠΏΡΠΎΠ²Π΅ΡΠΈΡΡ. ΠΠ·ΠΌΠ΅Π½ΠΈΠΌ Π·Π½Π°ΡΠ΅Π½ΠΈΠ΅ classroom_id
Π² ΡΠ°Π±Π»ΠΈΡΠ΅ students
, ΠΏΠΎΡΠΊΠΎΠ»ΡΠΊΡ ΠΎΠ½ΠΎ Π±ΠΎΠ»ΡΡΠ΅ Π½Π΅ ΡΠ²Π»ΡΠ΅ΡΡΡ Π²Π½Π΅ΡΠ½ΠΈΠΌ ΠΊΠ»ΡΡΠΎΠΌ ΠΈ Π½Π΅ ΡΠ²ΡΠ·Π°Π½ΠΎ Ρ ΡΠ°Π±Π»ΠΈΡΠ΅ΠΉ classrooms
, Π·Π°ΠΏΡΠΎΡ Π²ΡΠΏΠΎΠ»Π½ΠΈΡΡΡ ΡΡΠΏΠ΅ΡΠ½ΠΎ. ΠΠΎ Π΅ΡΠ»ΠΈ ΠΌΡ ΠΏΠΎΠΏΡΠΎΠ±ΡΠ΅ΠΌ ΡΠ΄Π΅Π»Π°ΡΡ ΡΠΎ ΠΆΠ΅ ΡΠ°ΠΌΠΎΠ΅ Ρ ΠΏΠΎΠ»Π΅ΠΌ student_id
Π² ΡΠ°Π±Π»ΠΈΡΠ΅ grades
, ΠΌΡ ΠΏΠΎΠ»ΡΡΠΈΠΌ ΠΎΡΠΈΠ±ΠΊΡ, ΠΏΠΎΡΠΎΠΌΡ ΡΡΠΎ student_id
β ΡΡΠΎ Π²Π½Π΅ΡΠ½ΠΈΠΉ ΠΊΠ»ΡΡ.
UPDATE students SET classroom_id = 10 WHERE id = 1; /* UPDATE 1 Query returned successfully in 37 msec. */ UPDATE grades SET student_id = 10 WHERE id = 1; /* ERROR: insert or update on table "grades" violates foreign key constraint "fk_students" DETAIL: Key (student_id)=(10) is not present in table "students". SQL state: 23503 */
Π Π΅ΡΠ΅ ΠΎΠ΄Π½ΠΎ Π·Π°ΠΌΠ΅ΡΠ°Π½ΠΈΠ΅ ΠΏΠΎ ΠΏΠΎΠ²ΠΎΠ΄Ρ CASCADE
. ΠΡΠ»ΠΈ ΠΌΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌ ON DELETE CASCADE
ΠΏΡΠΈ ΡΠΎΠ·Π΄Π°Π½ΠΈΠΈ Π²Π½Π΅ΡΠ½Π΅Π³ΠΎ ΠΊΠ»ΡΡΠ° Π² ΡΠ°Π±Π»ΠΈΡΠ΅ students
, Π° ΠΏΠΎΡΠΎΠΌ ΡΠ΄Π°Π»ΡΠ΅ΠΌ Π·Π°ΠΏΠΈΡΡ Π² classrooms
, Ρ Π½Π°Ρ ΡΠ°ΠΊΠΆΠ΅ ΠΈΡΡΠ΅Π·Π½ΡΡ ΡΠ²ΡΠ·Π°Π½Π½ΡΠ΅ ΡΡΡΠΎΠΊΠΈ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ students
. Π’Π°ΠΊΠΎΠΉ ΡΠΏΠΎΡΠΎΠ± ΠΌΠΎΠΆΠ΅Ρ Π±ΡΡΡ ΠΏΠΎΠ»Π΅Π·Π΅Π½ ΠΏΡΠΈ ΡΠ°Π±ΠΎΡΠ΅ Ρ Π»ΠΈΡΠ½ΡΠΌΠΈ Π΄Π°Π½Π½ΡΠΌΠΈ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΉ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ ΠΌΡ Ρ
ΠΎΡΠΈΠΌ ΡΠ΄Π°Π»ΠΈΡΡ Π²ΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ ΠΎ ΠΊΠ»ΠΈΠ΅Π½ΡΠ΅ ΠΈΠ»ΠΈ ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠ΅.
DROP TABLE IF EXISTS students CASCADE; CREATE TABLE students ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), classroom_id INT, CONSTRAINT fk_classrooms FOREIGN KEY(classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE ); INSERT INTO students (name, classroom_id) VALUES ('Adam', 1), ('Betty', 1), ('Caroline', 2); SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 1 | Adam | 1 2 | Betty | 1 3 | Caroline | 2 */ DELETE FROM classrooms WHERE id = 1; SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 3 | Caroline | 2 */
2. Π‘ΠΎΡ ΡΠ°Π½Π½ΠΎΡΡΡ Π΄Π°Π½Π½ΡΡ
Π‘ΠΈΡΡΠ΅ΠΌΠ°ΡΠΈΠ·ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ ΡΡ Π΅ΠΌΡ Π±Π°Π·Ρ Π΄Π°Π½Π½ΡΡ ΠΈ ΡΠΎΠ·Π΄Π°Π½ΠΈΠ΅ ΡΠΊΡΠΈΠΏΡΠΎΠ² β ΡΡΠΎ Π»ΡΡΡΠ°Ρ ΠΏΡΠ°ΠΊΡΠΈΠΊΠ° Ρ ΠΈΠ½ΠΆΠ΅Π½Π΅ΡΠ½ΠΎΠΉ ΡΠΎΡΠΊΠΈ Π·ΡΠ΅Π½ΠΈΡ, Π½ΠΎ ΠΏΡΠΈ ΡΠ°Π±ΠΎΡΠ΅ Ρ ΡΠ΅Π°Π»ΡΠ½ΡΠΌΠΈ Π΄Π°Π½Π½ΡΠΌΠΈ ΠΎΡΠ΅Π½Ρ ΡΠ°ΡΡΠΎ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΡΡΡΡ ΡΠ΅Π·Π΅ΡΠ²Π½ΡΠ΅ ΠΊΠΎΠΏΠΈΠΈ. Π‘ΡΡΠ΅ΡΡΠ²ΡΠ΅Ρ ΠΌΠ½ΠΎΠ³ΠΎ ΠΏΠΎΠ΄Ρ ΠΎΠ΄ΠΎΠ², ΠΈΠ½ΠΎΠ³Π΄Π° ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΡΡΡΡ ΠΏΠΎΠ»Π½ΡΠ΅ ΠΊΠΎΠΏΠΈΠΈ Π±Π°Π· Π΄Π°Π½Π½ΡΡ , ΠΊΠΎΡΠΎΡΡΠ΅ Π·Π°Π½ΠΈΠΌΠ°ΡΡ Π±ΠΎΠ»ΡΡΠΎΠ΅ ΠΊΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΠΏΠ°ΠΌΡΡΠΈ, Π° ΠΈΠ½ΠΎΠ³Π΄Π° ΡΠΈΠΊΡΠΈΡΡΡΡ ΡΠΎΠ»ΡΠΊΠΎ ΠΊΠΎΠ½ΠΊΡΠ΅ΡΠ½ΡΠ΅ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΡ. Π ΠΈΠ΄Π΅Π°Π»Π΅, ΡΠ°ΠΊΠΈΠ΅ ΠΊΠΎΠΏΠΈΠΈ Π΄ΠΎΠ»ΠΆΠ½Ρ Ρ ΡΠ°Π½ΠΈΡΡΡΡ Π½Π° Π³Π΅ΠΎΠ³ΡΠ°ΡΠΈΡΠ΅ΡΠΊΠΈ ΡΠ΄Π°Π»Π΅Π½Π½ΠΎΠΌ ΡΠ΅ΡΠ²Π΅ΡΠ΅, ΡΡΠΎΠ±Ρ ΡΠ½ΠΈΠ·ΠΈΡΡ Π²Π°ΡΠΈΠ°Π½Ρ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΠ³ΠΎ Π²Π»ΠΈΡΠ½ΠΈΡ ΡΡΠΈΡ ΠΈΠΈ Π½Π° ΡΠΎΡ ΡΠ°Π½Π½ΠΎΡΡΡ Π²Π°ΡΠΈΡ Π΄Π°Π½Π½ΡΡ .
3. ΠΠ»ΠΈΠ½Π° ΡΡΡΠΎΠΊ
ΠΡ ΡΡΡΠ°Π½ΠΎΠ²ΠΈΠ»ΠΈ, ΡΡΠΎ ΡΠΈΠΏ Π΄Π°Π½Π½ΡΡ
Π² ΠΊΠΎΠ»ΠΎΠ½ΠΊΠ΅ ΡΡΠΈΡΠ΅Π»Ρ
β ΡΡΠΎ ΡΡΡΠΎΠΊΠ° Ρ ΠΌΠ°ΠΊΡΠΈΠΌΠ°Π»ΡΠ½ΠΎΠΉ Π΄Π»ΠΈΠ½ΠΎΠΉ Π² 100 ΡΠΈΠΌΠ²ΠΎΠ»ΠΎΠ², ΡΠ°ΠΊ ΠΊΠ°ΠΊ Π½Π΅ ΠΏΡΠ΅Π΄ΠΏΠΎΠ»Π°Π³Π°Π΅ΠΌ, ΡΡΠΎ Π±ΡΠ΄ΡΡ Π·Π½Π°ΡΠ΅Π½ΠΈΡ Π΄Π»ΠΈΠ½Π½Π΅Π΅. ΠΠΎ Π½Π° ΡΠ°ΠΌΠΎΠΌ Π»ΠΈ Π΄Π΅Π»Π΅ ΠΌΡ ΡΠΊΠΎΠ½ΠΎΠΌΠΈΠΌ ΡΠ°ΠΊΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ ΠΌΠ΅ΡΡΠΎ Π½Π° Π΄ΠΈΡΠΊΠ΅?
Π‘ ΡΠ΅Ρ Π½ΠΈΡΠ΅ΡΠΊΠΎΠΉ ΡΠΎΡΠΊΠΈ Π·ΡΠ΅Π½ΠΈΡ, Π² Postgres Π½Π΅ ΠΈΠΌΠ΅Π΅Ρ Π·Π½Π°ΡΠ΅Π½ΠΈΡ, ΠΊΠ°ΠΊΡΡ Π΄Π»ΠΈΠ½Ρ ΡΡΡΠΎΠΊΠΈ ΠΌΡ ΠΎΠΏΡΠ΅Π΄Π΅Π»ΠΈΠ»ΠΈ (10, 100 ΠΈΠ»ΠΈ 500 ΡΠΈΠΌΠ²ΠΎΠ»ΠΎΠ²). Π£ΠΊΠ°Π·Π°Π½ΠΈΠ΅ Π΄Π»ΠΈΠ½Ρ ΡΡΡΠΎΠΊΠΈ β ΡΡΠΎ ΡΠΊΠΎΡΠ΅Π΅ Ρ ΠΎΡΠΎΡΠΈΠΉ ΡΠΏΠΎΡΠΎΠ± ΠΊΠΎΠΌΠΌΡΠ½ΠΈΠΊΠ°ΡΠΈΠΈ ΠΌΠ΅ΠΆΠ΄Ρ ΡΠ°Π·ΡΠ°Π±ΠΎΡΡΠΈΠΊΠ°ΠΌΠΈ, ΠΏΠΎΡΠΎΠΌΡ ΡΡΠΎ ΡΠ°ΠΊΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ Π²Ρ ΡΠΊΠ°Π·ΡΠ²Π°Π΅ΡΠ΅, ΡΡΠΎ Π²Ρ ΠΎΠΆΠΈΠ΄Π°Π΅ΡΠ΅ ΠΏΠΎΠ»ΡΡΠΈΡΡ Π² ΡΡΠΎΠΌ ΠΏΠΎΠ»Π΅.
ΠΠΎ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ, Π² MySQL Π΄Π»ΠΈΠ½Π° ΡΡΡΠΎΠΊ ΠΈΠΌΠ΅Π΅Ρ Π·Π½Π°ΡΠ΅Π½ΠΈΠ΅: Π²ΡΠ΅ΠΌΠ΅Π½Π½ΡΠ΅ ΡΠ°Π±Π»ΠΈΡΡ ΠΈ ΡΠ°Π±Π»ΠΈΡΡ MEMORY
Π±ΡΠ΄ΡΡ Ρ
ΡΠ°Π½ΠΈΡΡ ΡΡΡΠΎΠΊΠΈ ΠΎΠ΄ΠΈΠ½Π°ΠΊΠΎΠ²ΠΎΠΉ Π΄Π»ΠΈΠ½Ρ ΠΈ Π΄ΠΎΠΏΠΎΠ»Π½ΡΡΡ ΠΈΡ
Π΄ΠΎ ΠΌΠ°ΠΊΡΠΈΠΌΠ°Π»ΡΠ½ΠΎΠ³ΠΎ Π·Π½Π°ΡΠ΅Π½ΠΈΡ. ΠΡΠΎ ΠΎΠ·Π½Π°ΡΠ°Π΅Ρ, ΡΡΠΎ, Π΅ΡΠ»ΠΈ Π²Ρ ΡΠΊΠ°Π·Π°Π»ΠΈ ΡΠΈΠΏ ΠΏΠΎΠ»Ρ VARCHAR(1000)
, ΡΠΎ Π²ΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ Π² ΡΡΠΎΠΉ ΠΊΠΎΠ»ΠΎΠ½ΠΊΠ΅ Π±ΡΠ΄ΡΡ Π΄ΠΎΠΏΠΎΠ»Π½ΡΡΡΡΡ Π΄ΠΎ ΡΠΊΠ°Π·Π°Π½Π½ΠΎΠΉ Π΄Π»ΠΈΠ½Ρ, Π΄Π°ΠΆΠ΅ Π΅ΡΠ»ΠΈ ΡΡΡΠΎΠΊΠ° ΡΠΎΡΡΠΎΠΈΡ ΠΈΠ· 100 ΡΠΈΠΌΠ²ΠΎΠ»ΠΎΠ².
4. ΠΠΏΠ΅ΡΠ°ΡΠΎΡ If
ΠΡΠ»ΠΈ Π²Π°ΠΌ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎ, Π½ΠΈΠΆΠ΅ ΠΏΡΠΈΠΌΠ΅Ρ ΡΠΎΠ³ΠΎ, ΠΊΠ°ΠΊ Π² Postgres Π²ΡΠ³Π»ΡΠ΄ΠΈΡ ΡΠΈΠ½ΡΠ°ΠΊΡΠΈΡ Ρ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡΠΎΠΌ if
.
DO $$ BEGIN IF (SELECT COUNT(*) FROM grades) > (SELECT COUNT(*) FROM students) THEN RAISE NOTICE 'More grades than students.'; ELSE RAISE NOTICE 'Equal or more students than grades.'; END IF; END $$; /* NOTICE: More grades than students. */
5. ΠΠ½Π΄Π΅ΠΊΡΡ
ΠΡΠΈ ΡΠ²Π΅Π»ΠΈΡΠ΅Π½ΠΈΠΈ Π±Π°Π·Ρ Π΄Π°Π½Π½ΡΡ
, ΡΡΡΠ°Π½ΠΎΠ²ΠΊΠ° ΠΈΠ½Π΄Π΅ΠΊΡΠΎΠ² ΡΡΠ°Π½ΠΎΠ²ΠΈΡΡΡ ΠΊΡΠΈΡΠΈΡΠ΅ΡΠΊΠΈ Π²Π°ΠΆΠ½ΠΎΠΉ. ΠΠ»Ρ ΠΏΡΠΈΠΌΠ΅ΡΠ°, Π΄Π°Π²Π°ΠΉΡΠ΅ ΡΠΎΠ·Π΄Π°Π΄ΠΈΠΌ ΠΈΠ½Π΄Π΅ΠΊΡ Π½Π° ΠΏΠΎΠ»Π΅ Ρ ΠΎΡΠ΅Π½ΠΊΠ°ΠΌΠΈ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ grades
:
CREATE INDEX score_index ON grades(score);
Π’Π΅ΠΌ Π½Π΅ ΠΌΠ΅Π½Π΅Π΅, Π΅ΡΠ»ΠΈ ΡΠ΅ΠΉΡΠ°Ρ ΠΌΡ ΠΏΡΠΈΠΌΠ΅Π½ΠΈΠΌ ΠΎΠΏΠ΅ΡΠ°ΡΠΎΡ EXPLAIN ANALYZE
, ΠΌΡ ΡΠ²ΠΈΠ΄ΠΈΠΌ, ΡΡΠΎ Postgres ΠΏΠΎ-ΠΏΡΠ΅ΠΆΠ½Π΅ΠΌΡ Π²ΡΠΏΠΎΠ»Π½ΡΠ΅Ρ ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°ΡΠ΅Π»ΡΠ½ΠΎΠ΅ ΡΠΊΠ°Π½ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅. Π’Π°ΠΊ ΠΏΡΠΎΠΈΡΡ
ΠΎΠ΄ΠΈΡ ΠΏΠΎΡΠΎΠΌΡ, ΡΡΠΎ Postgres ΡΠΆΠ΅ Π΄ΠΎΡΡΠ°ΡΠΎΡΠ½ΠΎ Π½Π΅ΠΏΠ»ΠΎΡ
ΠΎ ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½. ΠΡΠ»ΠΈ ΠΊΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ Π·Π°ΠΏΠΈΡΠ΅ΠΉ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ Π½Π΅Π²Π΅Π»ΠΈΠΊΠΎ, Π±ΡΡΡΡΠ΅Π΅ Π²ΡΠΏΠΎΠ»Π½ΠΈΡΡ ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°ΡΠ΅Π»ΡΠ½ΠΎΠ΅ ΡΠΊΠ°Π½ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅, Π½Π΅ΠΆΠ΅Π»ΠΈ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ ΠΈΠ½Π΄Π΅ΠΊΡΡ. Π’ΠΎ Π΅ΡΡΡ Postgres ΡΠ°ΠΌ Π²ΡΠ±ΡΠ°Π» Π½Π°ΠΈΠ±ΠΎΠ»Π΅Π΅ Π±ΡΡΡΡΡΠΉ ΠΏΡΡΡ.