Hive日常數(shù)據(jù)需求二

題目二：計算用戶的回購率和復購率

復購率: 當前月份購買2次及以上的客戶占所有客戶比例

回購率：當前月份購買且上個月份也購買的客戶占當月所有月份客戶比例

思路：

復購率

1急迂、對當月（2月份）的客戶分組滤馍，計數(shù)購買次數(shù)
2、篩選購買次數(shù)為2以上的疲吸，認為是復購群體
回購率

1富腊、篩選當月及上月部分
2、利用客戶id進行當月連上月淤袜，推薦左連
3、對同一條客戶id均有購買記錄的衰伯，認為是回購群體

操作：

復購率
1铡羡、對當月（2月份）的客戶分組，計數(shù)購買次數(shù)

select  customer_key, 
        count(customer_key) as ct
from    ods_sales_orders   
                
where   month(create_date) = 2
group by customer_key
limit 10;

image.png

PS：hive中g(shù)roupby 后的字段必須在select后出現(xiàn)意鲸，且groupby后沒有的字段不能在select出現(xiàn)烦周，除了聚合函數(shù)，否則報錯临扮。

示例：

image.png

2论矾、篩選購買次數(shù)為2以上的，認為是復購群體

select count(ct),
       count(if (ct>1,1,null)),
       count(if (ct>1,1,null))/count(ct) as fg_ratio
from  
(select customer_key,count(customer_key) as ct
from   ods_sales_orders                  
where  month(create_date) = 2
group by customer_key) 
as a ;

image.png

PS:注意在Hive中書寫sql時杆勇，這里每行需要頂格寫贪壳，否則會報錯.

例如以如下方式寫報錯：

select count(ct),
       count(if (ct>1,1,null)),
       count(if (ct>1,1,null))/count(ct) as fg_ratio
from  
      (select customer_key,count(customer_key) as ct  #這行開始沒有頂格寫
        from   ods_sales_orders                  
        where  month(create_date) = 2
        group by customer_key) as a ;

image.png

操作：

回購率
1、關(guān)聯(lián)本月與上月蚜退，找出回購客戶

SELECT *
FROM (
    SELECT customer_key, substr(create_date, 1, 7) AS umonth
    FROM ods_sales_orders
    GROUP BY customer_key, substr(create_date, 1, 7)
) a
    LEFT JOIN (
        SELECT customer_key, substr(create_date, 1, 7) AS umonth
        FROM ods_sales_orders
        GROUP BY customer_key, substr(create_date, 1, 7)
    ) b
    ON a.customer_key = b.customer_key
        AND substring(a.umonth, 6, 2) = substring(b.umonth, 6, 2) - 1
LIMIT 10;

image.png

2闰靴、統(tǒng)計每個月份的消費人數(shù)情況即可得到回購率

SELECT a.umonth, COUNT(a.customer_key) AS mcount, COUNT(b.customer_key) AS lcount
    , concat(round(COUNT(b.customer_key) / COUNT(a.customer_key) * 100, 2), '%') AS ratio
FROM (
    SELECT customer_key, substr(create_date, 1, 7) AS umonth
    FROM ods_sales_orders
    GROUP BY customer_key, substr(create_date, 1, 7)
) a
    LEFT JOIN (
        SELECT customer_key, substr(create_date, 1, 7) AS umonth
        FROM ods_sales_orders
        GROUP BY customer_key, substr(create_date, 1, 7)
    ) b
    ON a.customer_key = b.customer_key
        AND substring(a.umonth, 6, 2) = substring(b.umonth, 6, 2) - 1
GROUP BY a.umonth;

image.png

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者