聚合函数

-- 统计总数
SELECT COUNT(*) FROM users;

-- 统计非NULL的数量
SELECT COUNT(email) FROM users;

-- 统计不重复的数量
SELECT COUNT(DISTINCT city) FROM users;

-- 总销售额
SELECT SUM(price) FROM orders;

-- 某个用户的总消费
SELECT SUM(amount) FROM orders WHERE user_id = 1;

-- 平均价格
SELECT AVG(price) FROM products;

-- 平均评分
SELECT AVG(rating) FROM reviews;

-- 四舍五入
SELECT ROUND(AVG(price), 2) FROM products;

-- 最贵的商品
SELECT MAX(price) FROM products;

-- 最便宜的商品
SELECT MIN(price) FROM products;

-- 最新的订单
SELECT MAX(created_at) FROM orders;

-- 按城市统计用户数
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city;

-- 按分类统计商品数和平均价格
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price
FROM products
GROUP BY category;

-- 多列分组
SELECT 
    category,
    brand,
    COUNT(*) as count
FROM products
GROUP BY category, brand;

-- 找出文章数大于5的用户
SELECT 
    user_id,
    COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5;

-- WHERE在分组前过滤，HAVING在分组后过滤
SELECT 
    category,
    AVG(price) as avg_price
FROM products
WHERE stock > 0  -- 先过滤有库存的
GROUP BY category
HAVING AVG(price) > 100;  -- 再过滤平均价格>100的分类

-- STRING_AGG：字符串聚合
SELECT category, STRING_AGG(name, ', ') as products
FROM products
GROUP BY category;

-- ARRAY_AGG：数组聚合
SELECT user_id, ARRAY_AGG(title) as posts
FROM posts
GROUP BY user_id;

-- JSON聚合
SELECT category, JSON_AGG(name) as products
FROM products
GROUP BY category;