学习Presto入门之比较Presto和Hive的SQL语法

src_url

• 在2017年4月1日发生交易的数量

Style	SQL
Presto	select count(*) from record where trancation_date = date '2017-04-01'
Hive	select count(*) from record where trancation_date = date '2017-04-01'

• 不同年龄消费的情况

Presto

select 
cast((year(CURRENT_DATE)-year(birth)) as integer) as age,
sum(price) as totalPrice
from record 
join user_dimension 
on record.uid=user_dimension.uid
group by cast((year(CURRENT_DATE)-year(birth)) as integer)
order by totalPrice desc

Hive

select 
cast(DATEDIFF(CURRENT_DATE, birth)/365 as int) as age,
sum(price) as totalPrice
from record 
join user_dimension 
on record.uid=user_dimension.uid
group by cast(DATEDIFF(CURRENT_DATE, birth)/365 as int)
order by totalPrice desc;

• 不同品牌被消费的情况

Presto=Hive

select 
brand,sum(price) as totalPrice
from record 
join brand_dimension 
on record.bid=brand_dimension.bid
group by brand_dimension.brand
order by totalPrice desc;

• 不同省份消费的情况

Presto=Hive

select 
province, sum(price) as totalPrice
from record 
join user_dimension 
on record.uid=user_dimension.uid
group by province
order by totalPrice desc;

• 不同年龄消费的商品类别情况(不同年龄消费不同商品类别的总价)

Presto

select 
cast((year(CURRENT_DATE)-year(birth)) as integer) as age, 
category, 
sum(price) as totalPrice
from record 
join user_dimension 
on record.uid=user_dimension.uid
join brand_dimension 
on record.bid=brand_dimension.bid
group by cast((year(CURRENT_DATE)-year(birth)) as integer), category
order by age, category, totalPrice;

Hive

select 
cast(DATEDIFF(CURRENT_DATE, user_dimension.birth)/365 as int) as age, 
category, 
sum(price) as totalPrice
from record 
join user_dimension 
on record.uid=user_dimension.uid
join brand_dimension 
on record.bid=brand_dimension.bid
group by cast(DATEDIFF(CURRENT_DATE, user_dimension.birth)/365 as int), category
order by age, category, totalPrice;