hive的SQL练习3

根据如上表格信息，实现如下需求：

1. 查询五一期间（2023-05-01 ~ 2023-05-07），每个餐厅的订单总数量及排名
2. with t as (
   select *,
   count(1) over(partition by restaurant_id) countNum
   from orders where substr(order_date,1,10)
   between '2023-05-01' and '2023-05-07'
   )
   select distinct restaurant_id,countNum,dense_rank() over (order by countNum desc) pm from t
   order by pm ;
3. 查看最近一个月内在一家餐厅重复购买 3 次以上的餐厅名字、用户名字、购买次数
4. with t as (
   select restaurant_id,user_id,
   count(1) buyCount
   from orders
   where substr(order_date,1,10) >= add_months(`current_date`(),-1)
   and substr(order_date,1,10) <=`current_date`()
   group by restaurant_id,user_id
   having buyCount >=3
   )
   select
   r.restaurant_name,
   u.user_name,
   t.buyCount
   from t left join restaurants r on t.restaurant_id=r.restaurant_id
   left join `users` u on t.user_id=u.user_id;

《hive的SQL练习3》 是转载文章，点击查看原文。