本月成交排名靠前的客户,他们采购产品有什么特征?
concat_ws ,collect_list ,regexp_replace,substring 等函数的用法。
temp t1=
select a.会员名,
a.商品名称,
a.商品编码,
a.单位,a.品牌,
a.月份,
if(b.销量 is null,0,b.销量) as 销量,
if(b.营收 is null,0,b.营收) as 营收
from [user_month_product] a left join [sku_uid_销量营收] b on a.会员名=b.会员名 and a.月份=b.月份 and a.商品编码=b.商品编码
temp t2=
select a.会员名,
a.商品名称,
a.商品编码,
a.单位,a.品牌,
count(a.[销量]) over (distribute by a.会员名,a.商品编码 order by a.月份 desc) as 累次,
concat_ws(',',collect_list(round(a.销量)) over (distribute by a.会员名,a.商品编码 order by a.月份 desc)) as [最近各月销量由本月至以前],
concat_ws(',',collect_list(round(a.营收,1)) over (distribute by a.会员名,a.商品编码 order by a.月份 desc)) as [最近各月营收由本月至以前]
from t1 a
temp t3=
select 会员名,
商品名称,
商品编码,
单位,品牌,
regexp_replace([最近各月销量由本月至以前],'\\.0',' ') as [最近各月销量由本月至以前],
regexp_replace([最近各月营收由本月至以前],'\\.0',' ') as [最近各月营收由本月至以前]
from t2
where 累次>3
output
select
会员名,
商品名称,
商品编码,
单位,品牌,
substr([最近各月销量由本月至以前],1,INSTRING([最近各月销量由本月至以前],',')-1) as 销量,
[最近各月销量由本月至以前],
substr([最近各月营收由本月至以前],1,INSTRING([最近各月营收由本月至以前],',')-1) as 营收,
[最近各月营收由本月至以前]
from t3