20 富有的客户数量

[astak16]

题目

查询至少有一个订单金额大于 500 的客户数量。

create table store (
	bill_id int,
	customer_id int,
	amount int
);

insert into store values
(6, 1, 549),
(8, 1, 834),
(4, 2, 394),
(11, 3, 657),
(13, 3, 257);

SQL：方法一

select count(distinct customer_id) as rich_count from store where amount > 500;

解析

• 先筛选出金额大于 500 的数据
• 对 customer_id 去重计数

SQL：方法二

select count(*) as rich_count from (
	select distinct customer_id from store
	group by customer_id having max(amount) > 500
) as temp;

解析

• 按 customer_id 分组，并筛选出 amount 大于 500 的客户，作为临时表 temp
• 对 temp 计数

SQL：方法三

with temp as (
	select distinct customer_id from store
	group by customer_id having max(amount) > 500
) select count(*) as rich_count from temp

解析

方法二的另一种写法，使用 with 创建临时表

SQL：方法四

select count(*) as rich_count from (
	select
		distinct customer_id,
		max(amount) over(partition by customer_id) as `max`
	from store
) as temp where `max` > 500;

解析

• 使用窗口函数筛选出每个用户最大的金额，作为临时表 temp
• 查询 temp 筛选出大于 500 的进行计数