MySQL 笔记6

1. GROUP BY 

GROUP BY 一般在WHERE之后，在 ORDER BY 和 LIMIT之前。

Q1:

%%sql 
SELECT gender, breed_group, COUNT(DISTINCT dog_guid) AS Num_Dogs
FROM dogs
GROUP BY gender, breed_group
ORDER BY Num_Dogs DESC

去掉ORDER。

%%sql 
SELECT gender, breed_group, COUNT(DISTINCT dog_guid) AS Num_Dogs
FROM dogs
GROUP BY gender, breed_group

数字简写。

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
'''
test_name would be #1, Month would be #2, and Num_Completed_Tests would be #3
'''
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;

2. HAVING用法。

"""
If you then wanted to output only the test-month pairs that had at least 20 records in them, you would add a HAVING clause, because the stipulation of at least 20 records only makes sense and is only computable at the aggregated group level
"""
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
HAVING COUNT(created_at)>=20
ORDER BY 3 DESC;

Q3:

%%sql
SELECT  gender, breed_group, COUNT(DISTINCT dog_guid) AS Num_Dogs
FROM dogs 
WHERE breed_group != "" AND  breed_group IS NOT  NULL AND breed_group != "None"
GROUP BY 1,2
HAVING Num_Dogs >= 1000
ORDER BY 3 DESC

Q6:

%%sql 
SELECT COUNT(user_guid), state, ZIP
FROM users
WHERE country = "US"
GROUP BY 2, 3
ORDER BY 2 ASC, 3 DESC
LIMIT 10;