目录

目录

1. WHERE 查询

2. ORDER BY 排序

3. 去重

4. 聚合函数 COUNT / SUM / AVG / MAX / MIN

① COUNT(*) vs COUNT(字段)

③ SUM + CASE WHEN 条件聚合

④MIN / MAX

---

目录

• 1. WHERE 查询
  • ① 等值比较 =
  • ② 范围比较 BETWEEN
  • ③ 集合匹配 IN
  • ④ 模糊匹配 LIKE
  • ⑤ 空值判断 IS NULL / IS NOT NULL
• 2. ORDER BY 排序
  • ① 单字段排序
  • ② 多字段排序
  • ③ 按聚合结果排序
• 3. 去重
  • ① 单字段去重
  • ② 多字段去重
• 4. 聚合函数 COUNT / SUM / AVG / MAX / MIN
  • ① COUNT(*) vs COUNT(字段)
  • ② AVG
  • ③ SUM + CASE WHEN 条件聚合
  • ④MIN / MAX

---

1. WHERE 查询

① 等值比较 =

# 查找所有抖音渠道注册的用户
SELECT user_id, province, city, register_channel
FROM dim_users
WHERE register_channel = 'douyin'
LIMIT 20;

# 查询 2026 年注册的新用户数量
SELECT COUNT(*) AS new_users_cnt
FROM dim_users
WHERE register_date >= '2026-01-01';
# 或者 YEAR(register_date) = 2026

② 范围比较 BETWEEN

# 查找 18-25 岁的用户
SELECT user_id, age, gender
FROM dim_users
WHERE age BETWEEN 18 AND 25
LIMIT 20;

③ 集合匹配 IN

# 查找来自北上广的用户
SELECT user_id, province, city
FROM dim_users
WHERE province IN ('北京', '上海', '广东')
LIMIT 20;

④ 模糊匹配 LIKE

# 查找城市名包含"州"的用户（如广州、杭州、苏州）
SELECT user_id, province, city
FROM dim_users
WHERE city LIKE '%州%'
LIMIT 20;

⑤ 空值判断 IS NULL / IS NOT NULL

# 查找没有填写城市的用户
SELECT user_id, province, city
FROM dim_users
WHERE city IS NULL
LIMIT 20;

2. ORDER BY 排序

① 单字段排序：

# 按年龄从小到大查看用户
SELECT user_id, age, province
FROM dim_users
ORDER BY age ASC
LIMIT 20;

② 多字段排序：

# 先按省份排序，同省份内按年龄从大到小
SELECT user_id, province, city, age
FROM dim_users
ORDER BY province ASC, age DESC  # 先按第一个字段排，第一个相同的再按第二个排
LIMIT 20;

③ 按聚合结果排序：

# 查每个省份用户数，按用户数从多到少排
SELECT province, COUNT(*) AS user_cnt
FROM dim_users
GROUP BY province
ORDER BY user_cnt DESC;

# 查询不同支付方式的订单数量
SELECT payment_method, COUNT(*) AS order_cnt
FROM fact_orders
GROUP BY payment_method
ORDER BY order_cnt DESC;

3. 去重

① 单字段去重：

# 有哪些注册渠道
SELECT DISTINCT register_channel FROM dim_users;

② 多字段去重：

# 每个省份+城市组合有多少种
SELECT DISTINCT province, city FROM dim_users;
# "省份+城市"完全相同才算重复

练习：

# 查询 2026 年 6 月的订单数量和 GMV
SELECT COUNT(*) AS order_cnt,
       ROUND(SUM(payable_amount), 2) AS GMV   # GMV = SUM(payable_amount)
FROM fact_orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01';

# 查询每个注册渠道的用户数量与占比
SELECT register_channel,
       COUNT(*) AS user_cnt,
       COUNT(*)/(SELECT COUNT(*) FROM dim_users) AS ratio # 总用户数可以用子查询获取
FROM dim_users
GROUP BY register_channel;

4. 聚合函数 COUNT / SUM / AVG / MAX / MIN

① COUNT(*) vs COUNT(字段)

-- COUNT(*)：统计所有行，不管有没有 NULL
SELECT COUNT(*) AS total_orders FROM fact_orders;
-- COUNT(字段)：只统计该字段不为 NULL 的行
SELECT COUNT(payment_time) AS paid_orders FROM fact_orders; 
#未支付订单的支付时间为null

② AVG

#计算平均年龄
SELECT AVG(age) AS avg_age FROM dim_users;
#AVG计算时自动跳过null值，所以分母是有年龄记录的用户数，不是总用户数
直观来看：
SELECT COUNT(*) AS total,
       COUNT(age) AS has_age,
       AVG(age) AS avg_age
FROM dim_users;

③ SUM + CASE WHEN 条件聚合

-- 一条 SQL 同时统计多个指标
SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_cnt,
    SUM(CASE WHEN status = 'refunded' THEN payable_amount ELSE 0 END) AS refund_amount,
    SUM(payable_amount) AS total_gmv
FROM fact_orders;

④MIN / MAX

-- 最早和最晚的注册日期
SELECT MIN(register_date) AS first_register, 
       MAX(register_date) AS last_register
FROM dim_users;
-- 每个省份用户最早注册时间
SELECT province, MIN(register_date) AS first_reg_date
FROM dim_users
GROUP BY province
ORDER BY first_reg_date;

练习：

#查询退款订单数量和退款金额
select count(*) as refund_cnt,sum(payable_amount) as refund_amount
from fact_orders
where status = 'refunded';

#查询商品总数、在售商品数、下架商品数
select count(*) as product_cnt,
sum(case when is_active = 1 then 1 else 0 end) as active_cnt,
sum(case when is_active = 0 then 1 else 0 end) as inactive_cnt
from dim_products;

#查询平均标价超过 500 的品类
select category,count(*) as pro_cnt,avg(list_price) as avg_price
from dim_products
group by category
having avg(list_price) > 500;

#having的执行顺序先于select，所以不能使用select中的别名

#查询 2026 年 6 月每日 DAU（DAU = 每天不同用户数）
select event_date,count(distinct user_id) as user_cnt
from fact_events
where event_date >= '2026-06-01' and event_date <'2026-07-01'
group by event_date
order by event_date;

#查询不同设备的访问用户数
select device,count(distinct user_id) as user_cnt
from fact_events
group by device;

下一节也许是与实战相关的综合练习...