订单统计

销售额（status=30、40） 成交额（status=40）{卖家} {时间段}
/dbs/api/order/amount?merchantCode=800202&start=2018-01-01&end=2018-04-01&date= &day= &type=1

select decode(round(sum(orderprice),2),null,0,round(sum(orderprice),2)) from bi_order_condition 
where orderstatus in (30,40) 
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
--and statisticstime='2018-01-01'
--and statisticstime=to_char(sysdate-1,'yyyy-MM-dd')
and merchantcode='800202'

销售/成交订单数
/dbs/api/order/count?merchantCode=800202&start=2018-01-01&end=2018-04-01&date=2018-01-01&dayNum&type=1

select decode(sum(orderquantity),null,0,sum(orderQuantity)) from bi_order_condition 
where orderstatus in (30,40)
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01'
--and statisticstime='2018-01-01'
--and statisticstime=to_char(sysdate-1,'yyyy-MM-dd')
and merchantcode='800202'

日均销售/成交额
/dbs/api/order/averageDailyAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select decode(t.daily,null,0,round(t.daily,2)) from 
(select sum(orderprice)/(to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')-min(to_date(STATISTICSTIME,'yyyy-mm-dd'))) daily
from bi_order_condition where orderstatus=40
and merchantcode='800202')t

客单价
/dbs/api/order/aveStoresAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&isCooperate=1&newOrOld=1

select decode(sum(t1.orderprice)/count(t1.customercode),null,0,round(sum(t1.orderprice)/count(t1.customercode),2)) from 
(select customercode,
sum(orderprice) orderprice,sum(orderquantity) orderquantity
from bi_order_condition where orderstatus=40
and merchantcode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
and customercode in 
(select distinct customercode from bi_trading_store where iscooperate=1
and merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' )
group by customercode) t1
left join 
(select customercode,sum(orderquantity) orderqty from bi_order_condition
where orderstatus=40 
and merchantcode='800202'
group by customercode)t2 on t1.customercode=t2.customercode
where t2.orderqty>1

平均订单价
/dbs/api/order/aveOrderAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&isCooperate=1&newOrOld=1

select decode(sum(t1.orderprice)/sum(t1.orderquantity),null,0,round(sum(t1.orderprice)/sum(t1.orderquantity),2)) from 
(select customercode,
sum(orderprice) orderprice,sum(orderquantity) orderquantity
from bi_order_condition where orderstatus=40
and merchantcode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
and customercode in 
(select distinct customercode from bi_trading_store where iscooperate=1
and merchantcode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' )
group by customercode) t1
left join 
(select customercode,sum(orderquantity) orderqty from bi_order_condition
where orderstatus=40 
and merchantcode='800202'
group by customercode)t2 on t1.customercode=t2.customercode
where t2.orderqty>1

订单状态 订单数量
/dbs/api/order/statusByCount?merchantCode=800202&start=2018-01-01&end=2018-04-01

select '发货中',decode(sum(decode(orderstatus,30,orderquantity)),null,0,sum(decode(orderstatus,30,orderquantity)))
from bi_order_condition
where merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-05-02'
union
select '已完成',decode(sum(decode(orderstatus,40,orderquantity)),null,0,sum(decode(orderstatus,40,orderquantity)))
from bi_order_condition
where merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-05-02'
union
select '已关闭',decode(sum(decode(orderstatus,0,orderquantity)),null,0,sum(decode(orderstatus,0,orderquantity)))
from bi_order_condition
where merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-05-02'

支付方式 订单数量/订单金额
/dbs/api/payment?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select decode(paymenttype,10,'线上支付',20,'线下支付'),
sum(decode(orderprice,null,0,orderprice)) from bi_payment
where merchantCode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-05-02' 
group by decode(paymenttype,10,'线上支付',20,'线下支付')

select decode(paymenttype,10,'线上支付',20,'线下支付'),
sum(decode(ORDERQUANTITY,null,0,ORDERQUANTITY)) from bi_payment
where merchantCode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-05-02' 
group by decode(paymenttype,10,'线上支付',20,'线下支付')

线上支付
/dbs/api/onlinePayment?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select subpaytype,sum(ORDERQUANTITY) from bi_payment
where paymenttype=10
--and merchantCode='800202'
--and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
group by subpaytype

线下支付
/dbs/api/offlinePayment?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select subpaytype,sum(ORDERQUANTITY) from bi_payment
where paymenttype=20 
--and merchantCode='800202'
--and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
group by subpaytype

配送方式 订单数/金额
/dbs/api/order/logistics?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select logisticstype,
sum(orderquantity) 
--sum(orderprice)
from bi_logistics
where  merchantcode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01' 
group by logisticstype 

新老客户
/dbs/api/newAndOldStores?merchantCode=800202&start=2018-01-01&end=2018-04-01&newOrOld=1&isCooperate=1&startRow=0&length=10

select t1.customercode,decode(t4.customername,null,t1.customercode,t4.customername) column0,
decode(sign(t1.orderqty-1),0,'新客户','旧客户') column1,
decode(t4.iscooperate,2,'普通客户','合作会员'),
decode(t2.orderprice,null,0,t2.orderprice) column3,
decode(t2.aveorderprice,null,0,t2.aveorderprice),
decode(t2.orderqty,null,0,t2.orderqty),
decode(t3.categoryqty,null,0,t3.categoryqty)
from (select customercode,sum(orderquantity) orderqty from bi_order_condition
where merchantcode='800202' and orderstatus=40 group by customercode)  t1
left join 
(select customercode,
round(sum(orderprice),2) orderprice,
round(sum(orderprice)/sum(orderquantity),2) aveorderprice,
sum(orderquantity) orderqty
from bi_order_condition 
where statisticstime>='2018-01-01' and statisticstime<='2018-04-01' and merchantcode='800202'
and orderstatus=40 group by customercode)t2 on t1.customercode=t2.customercode
left join 
(select customercode,count(distinct thirdcategorycode) categoryqty from bi_trading_product
where statisticstime>='2018-01-01' and statisticstime<='2018-04-01' and merchantcode='800202'
and orderstatus=40 group by customercode)t3
on t1.customercode=t3.customercode
left join 
(select a.customercode,a.customername,a.iscooperate from 
(select b.customercode,b.customername,b.iscooperate,row_number() over (partition by b.customercode order by to_date(b.statisticstime,'yyyy-MM-dd') desc)rn
from bi_trading_store b where b.merchantcode='800202') a where rn=1 )t4
on t1.customercode=t4.customercode 
where t1.orderqty>1 and t4.iscooperate=1
order by column3 asc

下单地点
/dbs/api/order/location/?merchantCode=800202&start=2018-01-01&end=2018-04-01

select statisticstime,latitude,longitude,orderquantity from bi_placeorder_address 
where merchantcode='800202' 
and statisticstime>='2017-04-01' and statisticstime<='2018-04-01'