阿里巴巴研发工程师 笔试题最后大题（数据库查询）

   阿里几乎所有的订单系统的排名都会参考成交量（交易成功）这个指标，因此很多卖家为了使自己的排名靠前，不惜利用刷单行为来提高自己的排名（即虚假成交），如果现在发现虚假刷单的特征其中一点：即低vip_level，买家的vip_level<=2，且买家注册时间和订单下单时间很近（如5天内）。
给出下面3张表，
卖家表 sellers (seller_id, create_time, vip_level),其中seller_id是买家用户id, create_time是注册时间，vip_level是用户等级，等级越高则说明成交数越多。
买家表 buyers (buyer_id, create_time, vip_level),其中buyer_id是买家用户id, create_time是注册时间，vip_level是用户等级，等级越高则说明成交数越多。
订单表 orders (order_id,buyer_id, seller_id,create_time, pay_time, success_time, product_id, price) 订单id, 买家id,卖家id, 创建时间，支付时间，交易完成时间，商品id, 商品价格。

1)  你能否找到满足以上特征，创建订单在20150201当天存在刷单嫌疑的买家和卖家吗(提供sql语句）？

2)  请用一个SQL语句，统计出20150201当天付款金额最高的买家用户id、付款笔数最多的买家用户id？

3)  用一段SQL分析下20150201当天成交额最高的卖家，在之前一个月和之后一个月每天成交额情况。以买家付款时间作为成交时间？

分析：为了简化时间，设当天时间就是20150201，此外这里，视订单支付时间，交易完成时间统一为订单创建时间，即订单创建即支付成功，虽然实际中有不妥，但是此处为简化语句，到时自要替换相应的时间查询字段就可以了。

问题1：

         （1）select  *  from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd');--获得20150201当天的订单,下一步当成子表查询；

         （2）select  a.buyer_id,count(*)  from (select  *  from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd')) a inner join buyers b on a.buyer_id=b.buyer_id  whereround(to_number(to_date('2015-02-01','yyyy-mm-dd')-b.create_time))<=5 and b.vip_level<=2 group by a.buyer_id;

                分析：蓝色部分为条件，其中round()函数用于时间表示，具体的可以参考http://blog.csdn.net/jojo52013145/article/details/6998984;这里边设计ORACLE的时间函数，比较全面。然后把满足条件的buy_id进行分组，并统计出当天的购买次数，次数最多的存在很大可能性的刷单嫌疑。

问题2：

       获得付款笔数最多的买家用户id？

      （1）select buyer_id,count(*) times，sum(price) total from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id;--以用户分组，统计每个用户当天的付款笔数,以及成交总额。

      （2）select max(times) from(select count(*) as times,buyer_id from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id);--取得当日付款最多的笔数为几笔。

      （3）select buyer_id from (select count(*) as times,buyer_id from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id) where times=(

                  select max(times) from(select count(*) as times,buyer_id from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id)          )--利用第二步取得最多的笔数作为子条件，获得最终的买家id。

     获得付款金额最高的买家用户id？

      （1）select max(total) from (select buyer_id,sum(price) as total from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id)--取得当天付款最高的金额

      （2）select buyer_id from (select buyer_id,sum(price) as total from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id) where total= (

               select max(total) from (select buyer_id,sum(price) as total from orders where trunc(create_time)=to_date('2015-2-1','yyyy-mm-dd') group by buyer_id)     )--利用第一步取得当天付款最高的金额作为查询条件，查询出对应买家的id；

问题3：

          分析：目前本人只有一种分析思路，其它还望各位大神提点：

                select create_time,sum(price) from (select * from orders where buyer_id='2000054305' and create_time between to_date('2015-1-1','yyyy-mm-dd') and to_date('2015-3-1','yyyy-mm-dd')) group by create_time

               ---此处涉及到的知识就是时间的间隔了，between and以及to_date将字符按指定规则转换成日期。

好的，大功告成，本人ORACLE初学者，相信以上语句问题可以解决，但性能方面尚且不知，相信会有更优语句，此外，红色部分标注为子查询部分。