已知一个表STG.ORDER,有如下字段:
Date,Order_id,User_id,amount
请给出sql进行统计:数据样例:
2017-01-01,10029028,1000003251,33.57。
2017-01-02,10029028,1000003251,33.57。
2017-01-03,10029028,1000003251,33.57。
1)给出 2017年每个月的订单数、用户数、总成交金额。
2)给出2017年11月的新客数(指在11月才有第一笔订单)
建表
create table order_tab
(
dt string,
order_id string,
user_id string,
amount decimal(10, 2)
) row format delimited fields terminated by ',';
1)给出 2017年每个月的订单数、用户数、总成交金额。
select date_format(dt, "yyyy-MM") `mon`,
count(order_id),
count(distinct (user_id)),
sum(amount)
from order_tab
where date_format(dt, 'yyyy')='2017'
group by date_format(dt, "yyyy-MM");
如下写法会报错,group by后面不能用上面的别名
select date_format(dt, "yyyy-MM") `mon`,
count(order_id),
count(distinct (user_id)),
sum(amount)
from order_tab
where date_format(dt, 'yyyy')='2017'
group by mon;
org.apache.hadoop.hive.ql.parse.SemanticException:
Line 7:9 Invalid table alias or column reference 'mon': (possible column names are: dt, order_id, user_id, amount)
2)给出2017年1月的新客数(指在1月才有第一笔订单)
select
count(distinct (user_id))
from order_tab
group by date_format(dt,'yyyy-MM')
having date_format(min(dt),'yyyy-MM')='2017-01';
如下写法报错,where后里面不能嵌套min
select count(distinct (user_id))
from order_tab
where date_format(min(dt),'yyyy-MM')='2017-01';
org.apache.hadoop.hive.ql.parse.SemanticException:Line 3:18 Not yet supported place for UDAF 'min'
ps
--如下两种写法是ok的,测试ok
select min(dt) from order_tab;
--2017-01-01
select date_format(min(dt),'yyyy-MM-dd') from order_tab;
--2017-01-01
总结
- min函数可以用于求日期的最小值
- select 后有聚合函数,如count,非聚合字段要出现在group by中