《阿里云认证的解析与实战-数据仓库ACP认证》——云上数据仓库的架构方案——三、AnalyticDB高效分析实时人群画像

WITH tbase
AS 
  (SELECT vipid,
    rval,
    fval,
    maval,
    max(rval)over()rval_max,
    min(rval)over()rval_min,
    max(fval)over()fval_max,
    min(fval)over()fval_min,
    max(mval)over()mval_max,
    min(mval)over()mval_min,
  FROM(
    SELECT vipid,
        max(col mum_012)rval,
        count(1) fval,
        sum(col_mum_005)mval
        FROM vip_behavior256 a 
        WHERE a.col_num_001> 995
        GROUP BY vipid) a)

SELECT vipid,
CASE 
WHEN rvol > rval_vg AND fval > fval_avg 
AND mval > mval_avg THEN '重要价值客户' 
WHEN rval < rval_avg AND fval>fval_avg 
AND mval > mval_avg THEN ‘重要换回客户' 
WHEN rval > rval_avg AND fval < fval_avg
AND mval > mval_avg THEN '重要深耕客户'
WHEN rval < rval_avg AND fval>fwal_avg 
AND mval > mval_avg THEN'重要留客户’ 
WHEN rval > rval_avg AND fval>fval_avg 
AND mval < mval_avg THEN ‘潜力客户’
WHEN rval < rvall avg AND fual< fval_avg 
AND mval <mval_avg THEN‘新客户'
WHEN rval < rval_avg AND fval > fval_avg
AND mval < mval_avg THEN ‘一般维持客户'
WHEN rval <rval_avg AND fval < fval_avg 
AND mval< mval_avg THEN '流失客户'
end rfm_desc
FROM（
SELECT vipid,
rval*-100 + fval*100 + mval* 100+100 total_val,
rwal,tvall,mval,
avg(rval)over()rval_avg,avg(fval)over()fval_avg,avg(mval)over() mval_avg
FROM(
SELECT vipid,
((rval- rval min)/(rval mix-rvall min))rval,
((Fvll- Twill min)/(fval max-fvill min)fval,
((mval mval min)/(mvall max-mwal minl)mval
FROM tbase) a) a