求助1:想查wanchengdan,liushui,butie的环比.
select b.city, b.busi_name,
count(if(a.order_status=9,a.order_id,null))as 'wanchengdan',
sum(if(a.order_status=9,real_total_price,0))/1000 as 'liushui',
sum(if(order_status=9,ifnull(discount_baidufee_price,0),0))/1000 as 'butie',
sum(if(order_status=9,ifnull(shop_butie,0),0))/1000 as 'shanghubutie',
sum(if(a.order_status=9,real_total_price,0))/sum(if(order_status=9,ifnull(discount_baidufee_price,0)+ifnull(discount_fanquan,0),0)) as 'roi'
from fact_order a,dim_business b
where a.shop_id=b.shop_id
and a.break_type=0
and a.order_day_key between 20161104 and 20161110
and b.is_test=0
and b.ka_extend_lable_id=10
group by 1,2求助2.aoi是商圈,busi_name是店名,想查所有商圈订单量排名前十的店。
select b.city,b.aoi, b.busi_name,
count(if(a.order_status=9,a.order_id,null))as 'wanchengdan'
from fact_order a,dim_business b
where a.shop_id=b.shop_id
and a.break_type=0
and a.order_day_key between 20161111 and 20161117
and b.is_test=0
group by b.city,b.aoi, b.busi_name 跪谢各位大神...
select b.city, b.busi_name,
count(if(a.order_status=9,a.order_id,null))as 'wanchengdan',
sum(if(a.order_status=9,real_total_price,0))/1000 as 'liushui',
sum(if(order_status=9,ifnull(discount_baidufee_price,0),0))/1000 as 'butie',
sum(if(order_status=9,ifnull(shop_butie,0),0))/1000 as 'shanghubutie',
sum(if(a.order_status=9,real_total_price,0))/sum(if(order_status=9,ifnull(discount_baidufee_price,0)+ifnull(discount_fanquan,0),0)) as 'roi'
from fact_order a,dim_business b
where a.shop_id=b.shop_id
and a.break_type=0
and a.order_day_key between 20161104 and 20161110
and b.is_test=0
and b.ka_extend_lable_id=10
group by 1,2求助2.aoi是商圈,busi_name是店名,想查所有商圈订单量排名前十的店。
select b.city,b.aoi, b.busi_name,
count(if(a.order_status=9,a.order_id,null))as 'wanchengdan'
from fact_order a,dim_business b
where a.shop_id=b.shop_id
and a.break_type=0
and a.order_day_key between 20161111 and 20161117
and b.is_test=0
group by b.city,b.aoi, b.busi_name 跪谢各位大神...
order by xxx desc limit 10
fact_order是订单表,
dim_business是商家表
city=城市
busi_name=商家名字
order_status=9 是完成单
real_total_price是流水
两表关联是shop_id
想查XX日到XX日的各城市里各商家完成单和流水情况。现在需要知道完成单和流水上周和本周环比增涨的情况。就想知道环比的语句怎么写。问题2:limit不ok的 因为有个条件是订单完成量的前十
我用的事mysql 跪谢各位。
问题1
fact_order是订单表,
dim_business是商家表
city=城市
busi_name=商家名字
order_status=9 是完成单
real_total_price是流水
两表关联是shop_id
想查XX日到XX日的各城市里各商家完成单和流水情况。现在需要知道完成单和流水上周和本周环比增涨的情况。就想知道环比的语句怎么写。问题2:limit不ok的 因为有个条件是订单完成量的前十
我用的事mysql 跪谢各位。