#选出城市在北京,性别为女的是个用户
select user_name
from user_info
where city = 'beijing' and sex = 'female'
limit 10;
#选出在2019年4月9号,购买的商品品类是food的用户名、购买数量、支付金额
select user_name,piece,pay_amount
from user_trade
where dt = '2019-04-09' and goods_category = 'food';
#2019年一月到四月,每个品类有多少人购买,累计金额是多少
select goods_category,
count(distinct user_name) as num,
sum(pay_amount) as total_amount
from user_trade
where dt between '2019-01-01' and '2019-04-30'
group by goods_category;
#group by的作用:分类汇总
常用聚合函数:
1.count():计数count(distinct...)去重计数
2.sum():求和
3.avg():平均值
4.max():最大值
5.min():最小值
group by .....having
#2019年4月,支付金额超过5万元的用户