首先在数据库中的数据,价格的取值范围是1-999,我们将价格分为了五个区间,然后将支付人数和支付金额占比用百分号的形式表示。
下边的case语句,是第一次具体应用
SELECT
cost_name as 价值人群,
ROUND(sum(csmr_num) / (SELECT sum(csmr_num) FROM ads_oldguest_core_indicators_item_detail where cost_name = "V1"),2) as 交易人数占比,
ROUND(sum(csmr_amt) / (select sum(csmr_amt) from ads_oldguest_core_indicators_item_detail),2) as 交易金额占比,
case when item_sale_price< 200 and item_sale_price >=0 then '0-200'
when item_sale_price>= 200 and item_sale_price < 400 then '0-400'
when item_sale_price>= 400 and item_sale_price < 600 then '0-600'
when item_sale_price>= 600 and item_sale_price < 800 then '0-800'
else '800-1000'
end as '价格带'
FROM
ads_csmr_ogt_core_idr_item_dtl_df
where cost_name = "V1"
group by case when item_sale_price< 200 and item_sale_price >=0 then '0-200'
when item_sale_price>= 200 and item_sale_price < 400 then '0-400'
when item_sale_price>= 400 and item_sale_price < 600 then '0-600'
when item_sale_price>= 600 and item_sale_price < 800 then '0-800'
else '0-1000'
end;