-- 4.6 各分類用戶數(shù)量和占比, 銷售貢獻(xiàn)額和貢獻(xiàn)額占比,以及累計銷售貢獻(xiàn)額和累計貢獻(xiàn)額占比

#方法一

select 用戶價值分類, count(user_id) 人數(shù) ,count(user_id)/ (select count(user_id) 總?cè)藬?shù) from RFM1) 人數(shù)占比,

sum(M) 銷售貢獻(xiàn)額, sum(M)/ (select sum(M) 總銷售額 from RFM1) 銷售貢獻(xiàn)額占比,

SUM(sum(M)) OVER( ORDER BY sum(M) DESC) AS 累計銷售貢獻(xiàn)額,

SUM(sum(M)) OVER( ORDER BY sum(M) DESC) /(select sum(M) 總銷售額 from RFM1) AS 累計銷售貢獻(xiàn)額占比

from RFM1

group by 用戶價值分類

order by sum(M) DESC;

#以下為錯誤方法, 加上了Partition by后就是分區(qū)內(nèi)的累計求和,這里要各分區(qū)總體的求和,不用加partition by,要會從第一個分區(qū)向當(dāng)前分區(qū)累計求和

#剛開始我寫錯了,看了半天沒發(fā)現(xiàn)這個錯誤

select 用戶價值分類, count(user_id) 人數(shù) ,count(user_id)/ (select count(user_id) 總?cè)藬?shù) from RFM1) 人數(shù)占比,

sum(M) 銷售貢獻(xiàn)額, sum(M)/ (select sum(M) 總銷售額 from RFM1) 銷售貢獻(xiàn)額占比,

SUM(sum(M)) OVER(PARTITION BY 用戶價值分類 ORDER BY sum(M)) AS 累計銷售貢獻(xiàn)額,

SUM(sum(M)) OVER(PARTITION BY 用戶價值分類 ORDER BY sum(M)) /(select sum(M) 總銷售額 from RFM1) AS 累計銷售貢獻(xiàn)額占比

from RFM1

group by 用戶價值分類

order by sum(M) DESC;

#方法二

select *,sum(銷售貢獻(xiàn)額) OVER( ORDER BY 銷售貢獻(xiàn)額 DESC) AS 累計銷售貢獻(xiàn)額,

SUM(銷售貢獻(xiàn)額) OVER(ORDER BY 銷售貢獻(xiàn)額 DESC) /(select sum(M) 總銷售額 from RFM1) AS 累計銷售貢獻(xiàn)額占比

from

(select 用戶價值分類, count(user_id) 人數(shù) ,count(user_id)/ (select count(user_id) 總?cè)藬?shù) from RFM1) 人數(shù)占比,

sum(M) 銷售貢獻(xiàn)額, sum(M)/ (select sum(M) 總銷售額 from RFM1) 銷售貢獻(xiàn)額占比

from RFM1

group by 用戶價值分類

order by sum(M) DESC )t1;