[大数据]SQL Part3 --- 聚合操作符

聚合操作符 Aggregate Operators

Sailors

Reserves

Boats

• 注意当使用Aggregate Operators时，target list(select 语句)中只能有aggregate attribute,不能有其他的attribute, 除非使用GROUP BY或者HAVING

COUNT(A)

Count the number of sailors

SELECT COUNT (*)
FROM Sailors S;

Count the number of different sailors

SELECT COUNT (DISTINCT S.sname)
FROM Sailors S

SUM(A)

Find the sum of ages of all sailors with a rating of 10

 SELECT SUM (S.age)
FROM Sailors S
WHERE S.rating=10;

AVG(A)

Find the average age of all sailors with rating 10

SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10

MAX(A), MIN(A)

Find the name and age of the oldest sailor

SELECT S.sname, MAX(S.age)
FROM Sailors S;

#上面的写法是错的，因为target list里只能有MAX(S.age)一个属性
#正确写法如下
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = ( SELECT MAX(S2.age)
FROM Sailors S2 );

# 或者也可以这样写
SELECT S.sname, S.age
FROM Sailors S
WHERE ROWNUM <= 1
ORDER BY S.age DESC;

GROUP BY and HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Example: 查找总访问量大于 200 的网站

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

Example: 将相同的business_id分组 HAVING COUNT(business_id) = 2 计算每个分组中相同的business_id的数量，挑选count = 2的business_id

SELECT DISTINCT business_id 
FROM BUSINESS_MAIN_CATEGORIES t
WHERE t.main_category in ('Convenience Stores', 'Dentists')
GROUP BY business_id
HAVING COUNT(business_id) = 2

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖