第一道
90分位。有用户user_id,支付金额pay_amount字段,统计支付金额前90%的用户id。例如总的支付金额是10000,A用户支付金额4000,B用户支付金额3000,C用户支付金额2000,那么这三个总的支付金额就占了前90%,把这三个用户输出就可。
思路:这里还是需要用到开窗。Ntile函数把有序的数据集合平均分配到指定的数量(num)个桶中, 将桶号分配给每一行。这里要统计前90%,所以要分成10个桶,取到前9个桶。
select
a.user_id,
a.sum_pay_amount,
a.level
from
(
select
user_id,
sum(pay_amount) as sum_pay_amount,
ntile(10) over(order by sum(pay_amount) desc) as level
from user_trade
group by user_id
) a
where a.level <= 9
注意:这里面over()后可省略去partition by。直接排序。
第二道
针对双十一期间,写出实现以下要求的sql语句。
要求:单笔订单金额超过1000.00元的订单数量超过3笔的买家
输出:买家id,订单数,总订单金额,总订单金额top 3的买家。
涉及的字段:buyer_id用户id,order_id订单id,amount支付金额。
思路:首先要满足要求,单笔订单金额超过1000.00元的订单数量超过3笔的买家。这句话的字面意思理解,统计到用户id,订单id的维度,筛选出订单金额超过1000的用户,再having count 取有超过3的数据。再给根据用户id作为关联口径,取得总表中该用户的相关统计数值。
1.统计笔订单金额超过1000.00元的订单数量超过3笔的买家
此表作为子查询
select
buyer_id,
order_id,
count(1) as order_nums
from
order_detail
where
amount >1000
group by
buyer_id,order_id
having count(1)>3
2.和原表做关联,在原表中统计出amount和order_nums
select
t1.buyer_id,
count(t1.order_id) as order_nums,
sum (amount) as total_amount
from
(
select
buyer_id,
order_id,
amount
from
order_detail
)t1
join
(
select
buyer_id,
order_id,
count(1) as order_nums_morethan3
from
order_detail
where
amount >1000
group by
buyer_id,order_id
having count(1)>3
)t2
on t1.buyer_id=t2.buyer_id
group by buyer_id