การจัดกลุ่มข้อมูล

SQL ระดับกลาง

Jasmin Ludolf

Data Science Content Developer, DataCamp

การจัดกลุ่มข้อมูล

ภาพประกอบแสดงกรวยที่มีรูปทรงต่างๆ ถูกจัดกลุ่มเป็นสามประเภท ได้แก่ สี่เหลี่ยม วงกลม และสามเหลี่ยม

GROUP BY กับฟิลด์เดียว

SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification;

|certification|title_count|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
|NC-17        |7          |
...

การจัดการข้อผิดพลาด

SELECT certification, title
FROM films
GROUP BY certification;

column "films.title" must appear in the
GROUP BY clause or be used in an 
aggregate function
LINE 1: SELECT certification, title
                              ^

SELECT 
   certification, 
   COUNT(title) AS count_title
FROM films
GROUP BY certification;

|certification|count_title|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
...

GROUP BY กับหลายฟิลด์

SELECT certification, language, COUNT(title) AS title_count
FROM films
GROUP BY certification, language;

|certification|language |title_count|
|-------------|---------|-----------|
|null         |null     |5          |
|Unrated      |Japanese |2          |
|R            |Norwegian|2          |
...

GROUP BY ร่วมกับ ORDER BY

SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification;

|certification|title_count|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
...

SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC;

|certification|title_count|
|-------------|-----------|
|R            |2118       |
|PG-13        |1462       |
...

ลำดับการประมวลผล

-- Written code:
SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC
LIMIT 3;

-- Order of execution:

SELECT 
       certification, 
       COUNT(title) AS title_count

FROM films

GROUP BY certification

ORDER BY title_count DESC

LIMIT 3;

มาฝึกกันเถอะ!

SQL ระดับกลาง