2025年以sql脚本计算电商零售中的每月新老客户占比情况

科技前沿 • 2025-01-29 07:07 • 阅读 36

大家好，我是讯享网，很高兴认识大家。

-- 数据预览： select * from fct_sales limit 10; -- 每月的新客户占比： select first_month as m_month ,count(dimMemberID) AS user_cnt ,sum(if(is_new = 1,1,0)) as new_user_num ,sum(if(is_new = 1,1,0)) / count(dimMemberID) as new_rate ,sum(if(is_new = 0,1,0)) as old_user_num ,sum(if(is_new = 0,1,0))/ count(dimMemberID) as old_rate from ( SELECT dimMemberID ,date_format(min(dimDateID),'%Y%m') AS first_month ,date_format(max(dimDateID),'%Y%m') AS last_month ,if(date_format(min(dimDateID),'%Y%m') = date_format(max(dimDateID),'%Y%m'),1,0 ) as is_new from fct_sales group by dimMemberID -- 已去重 ) t group by first_month -- 以首月作为每月标识分组月份 order by m_month; -- 验证：2017年8月的新客 select * from ( select dimMemberID ,min(dimDateID) as min_date ,max(dimDateID) as max_date from fct_sales group by dimMemberID ) t where DATE_FORMAT(min_date,'%Y%m') != DATE_FORMAT(max_date,'%Y%m') -- 第一次及最后一次购买都是在同一个月份 and min_date regexp ''; -- 验证完毕：2017年08月确实没有新客户

讯享网

在这里插入图片描述
讯享网

计算逻辑更正：

讯享网with temp1 as ( select distinct -- 添加distinct去重，主要是考虑到同一用户在激活当天可能可能下过两笔及以上订单 t2.user_id ,stimu_date ,active_date ,if (stimu_date = active_date,1,0) as flag from ( -- 计算得到每个用户的第一购买日期 SELECT dimMemberID as user_id ,min(dimDateID) as stimu_date FROM fct_sales where dimMemberID <> 0 -- 排除非会员 group by dimMemberID ) t1 right join ( -- 计算得到每个用户每月的第一次购买日期 SELECT dimMemberID as user_id ,dimDateID as active_date FROM fct_sales where dimMemberID <> 0 -- 排除非会员 ) t2 on t1.user_id = t2.user_id ) select month(active_date) as mmonth ,sum(if (flag = 1,1,0 )) as new_cnt ,count(distinct user_id) as user_cnt # 此处不能用count(1) ,sum(if (flag = 1,1,0 ))/ count(distinct user_id) as rate from temp1 group by month(active_date) with rollup;

最后统计结果应该是：
在这里插入图片描述

2025年以sql脚本计算电商零售中的每月新老客户占比情况

相关推荐