商品统计

商品行为情况
/dbs/api/product/countByBehavior?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select t1.brand,t1.collecting,decode(t2.collecting,0,0,round(t1.collecting*100/t2.collecting,4)),
t1.cart,decode(t2.cart,0,0,round(t1.cart*100/t2.cart,4)),
t1.trading,decode(t2.trading,0,0,round(t1.trading*100/t2.trading,4)) from
(select decode(brand,null,'--',brand) brand,sum(decode(conditiontype,10,productqty,0)) collecting,
sum(decode(conditiontype,20,productqty,0)) cart,sum(decode(conditiontype,30,productqty,0)) trading
from bi_product_condition 
where merchantcode='800202' 
and statisticstime>='2018-02-01' and statisticstime<='2018-04-01'
group by decode(brand,null,'--',brand)) t1,
(select sum(decode(conditiontype,10,productqty,0)) collecting,
sum(decode(conditiontype,20,productqty,0)) cart,
sum(decode(conditiontype,30,productqty,0)) trading from bi_product_condition 
where merchantcode='800202' 
and statisticstime>='2018-02-01' and statisticstime<='2018-04-01') t2

select t3.parametercategoryname,t1.collecting,round(t1.collecting*100/t2.collecting,2),
t1.cart,round(t1.cart*100/t2.cart,2),
t1.trading,round(t1.trading*100/t2.trading,2) from
(select thirdcategorycode,sum(decode(conditiontype,10,productqty,0)) collecting,
sum(decode(conditiontype,20,productqty,0)) cart,sum(decode(conditiontype,30,productqty,0)) trading
from bi_product_condition where merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01'
group by thirdcategorycode) t1,
(select sum(decode(conditiontype,10,productqty,0)) collecting,
sum(decode(conditiontype,20,productqty,0)) cart,
sum(decode(conditiontype,30,productqty,0)) trading from bi_product_condition 
where merchantcode='800202' 
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01') t2
,lots.VIEW_PRODUCT_TYPE t3 where t1.thirdcategorycode=t3.parametercategorycode

品牌销售/成交金额
/dbs/api/brand/amount?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select decode(brand,null,'--',brand),sum(salesprice)
from bi_trading_product where orderstatus in (30,40)
and merchantcode='800202'
and statisticstime>='2018-01-01' and statisticstime<='2018-04-01'
group by decode(brand,null,'--',brand) order by sum(salesprice) desc

品牌商品交易情况
/dbs/api/brand/trading?merchantCode=800202&start=2018-01-01&end=2018-04-01

select decode(brand,null,'--',brand),sum(decode(orderstatus,40,salesprice,0)),
sum(decode(orderstatus,30,salesprice,40,salesprice,0)),
count(distinct productcode) from bi_trading_product
where statisticstime>='2018-01-01' and statisticstime<='2018-05-02'
and merchantcode='800202'
group by decode(brand,null,'--',brand)

商品分类销售、成交金额
/dbs/api/product/firstCategoryAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1

select t1.firstcategorycode,t2.firstcategoryname,sum(t1.salesprice) from bi_trading_product t1
left join (select distinct firstcategorycode,firstcategoryname from lots.VIEW_PRODUCT_TYPE)t2 
on t1.firstcategorycode=t2.firstcategorycode
where t1.orderstatus in (30,40) 
and t1.statisticstime>='2018-01-01' and t1.statisticstime<='2018-04-01'
and t1.merchantcode='800202'
group by t1.firstcategorycode,t2.firstcategoryname order by sum(t1.salesprice) desc

/dbs/api/product/secondCategoryAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1&firstCategoryCode=3800000000

select t1.firstcategorycode,t2.firstcategoryname,t1.secondcategorycode,t2.secondcategoryname,sum(t1.salesprice) from bi_trading_product t1
left join (select distinct firstcategorycode,firstcategoryname,secondcategorycode,secondcategoryname from lots.VIEW_PRODUCT_TYPE)t2 
on t1.secondcategorycode=t2.secondcategorycode
where t1.orderstatus in (30,40) 
and t1.statisticstime>='2018-05-01' and t1.statisticstime<='2018-05-02'
and t1.firstCategoryCode='8600000000'
and t1.merchantcode='800202'
group by t1.firstcategorycode,t2.firstcategoryname,t1.secondcategorycode,t2.secondcategoryname 
order by sum(t1.salesprice) desc

/dbs/api/product/thirdCategoryAmount?merchantCode=800202&start=2018-01-01&end=2018-04-01&type=1&secondCategoryCode=3801600000

select t1.firstcategorycode,t2.firstcategoryname,t1.secondcategorycode,t2.secondcategoryname,t1.thirdcategorycode,t2.parametercategoryname,sum(t1.salesprice) from bi_trading_product t1
left join (select distinct firstcategorycode,firstcategoryname,secondcategorycode,secondcategoryname,parametercategorycode,parametercategoryname from lots.VIEW_PRODUCT_TYPE)t2 
on t1.thirdcategorycode=t2.parametercategorycode
where t1.orderstatus in (30,40) 
and t1.statisticstime>='2018-01-01' and t1.statisticstime<='2018-04-01'
and t1.secondcategorycode='3801600000'
and t1.merchantcode='800202'
group by t1.firstcategorycode,t2.firstcategoryname,t1.secondcategorycode,t2.secondcategoryname,t1.thirdcategorycode,t2.parametercategoryname 
order by sum(t1.salesprice) desc