给出下面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初学者,相信以上语句问题可以解决,但性能方面尚且不知,相信会有更优语句,此外,红色部分标注为子查询部分。