个人网站备案容易吗,重要新闻,网站建设哪种语言好,购物网站模板代码问题描述有一个用来记录每日客户消耗数据的表 t#xff0c;它的表结构如下#xff1a;字段类型描述created_dayDate消耗日期customer_idInteger客户IDamountInteger消耗金额要求#xff1a;统计出头部客户、腰部客户、尾部客户在上个月(2020-06-01 ~ 2020-06-30)的留存情况。…问题描述有一个用来记录每日客户消耗数据的表 t它的表结构如下字段类型描述created_dayDate消耗日期customer_idInteger客户IDamountInteger消耗金额要求统计出头部客户、腰部客户、尾部客户在上个月(2020-06-01 ~ 2020-06-30)的留存情况。输出结果的格式层级客户数量留存数量头部客户腰部客户尾部客户数据定义头部客户上个月消耗金额大于等于 30000 的客户腰部客户上个月消耗金额在 10000 ~ 30000(不包含 30000) 的客户尾部客户上个月消耗金额小于 10000 的客户留存最近两个月(上个月和本月)消耗金额大于 0 的客户时间上个月(2020-06-01 ~ 2020-06-30)、本月(2020-07-01 ~ 2020-07-31)解决方案今天这个问题和我上一篇文章里面的问题很相似只不过这里要求多统计一列因此解决的思路也差不多。下面我将用 CTE 来演示每个步骤。第一步计算出上个月每个客户的消耗金额。with t1 AS(SELECTcustomer_id,SUM(amount) AS amountFROMtWHERE created_day BETWEEN 2020-06-01AND 2020-06-30GROUP BY customer_id)SELECT * FROM t1第二步在第一步的基础上统计头部客户、腰部客户、尾部客户的数量。t2 AS(SELECTCASEWHEN amount 30000THEN 1WHEN amount 10000THEN 2ELSE 3END AS customer_level,COUNT(*) AS customter_cntFROMt1GROUP BY customer_level)SELECT * FROM t2在脚本中使用代码 1、2、3 分别表示头部客户、腰部客户、尾部客户。需要注意的是在 GROUP BY 子句中使用了 SELECT 子句中的字段别名 customer_level这种语法在其它数据中是编译不通过的。第三步计算留存。根据留存的定义只要客户在本月中有消耗就计入留存数。比如客户 AA 上个月的消耗金额是 40000那么 A 就是头部客户假如 A 在本月的消耗金额大于 0A 就为【留存】贡献了 1 。我们在 t1 之后插入表达式 t12t12 的脚本如下t12 AS(SELECTt1.customer_id,t1.amount,IF(tmp.amount 0, 1, 0) AS keep_stateFROMt1LEFT JOIN(SELECTcustomer_id,SUM(amount) AS amountFROMtWHERE created_day BETWEEN 2020-07-01AND 2020-07-31GROUP BY customer_id) tmpON tmp.customer_id t1.customer_id)SELECT * FROM t12t12 中的左连接也可以改造成标量子查询。完整的 SQL 实现with t1 AS(SELECTcustomer_id,SUM(amount) AS amountFROMtWHERE created_day BETWEEN 2020-06-01AND 2020-06-30GROUP BY customer_id),t12 AS(SELECTt1.customer_id,t1.amount,IF(tmp.amount 0, 1, 0) AS keep_stateFROMt1LEFT JOIN(SELECTcustomer_id,SUM(amount) AS amountFROMtWHERE created_day BETWEEN 2020-07-01AND 2020-07-31GROUP BY customer_id) tmpON tmp.customer_id t1.customer_id),t2 AS(SELECTCASEWHEN amount 30000THEN 1WHEN amount 10000THEN 2ELSE 3END AS customer_level,COUNT(*) AS customter_cnt,SUM(keep_state) AS keep_cntFROMt12GROUP BY customer_level)SELECTCASEcustomer_levelWHEN 1THEN 头部客户WHEN 2THEN 腰部客户ELSE 尾部客户END AS 层级,customter_cnt AS 客户数量,keep_cnt AS 留存数量FROMt2ORDER BY customer_level感兴趣的朋友可以尝试不使用左连接或者标量子查询的写法而是只查一次 t 表就能实现需求。本文分享自微信公众号 - SQL实现(gh_684ee9235a26)。如有侵权请联系 supportoschina.cn 删除。本文参与“OSC源创计划”欢迎正在阅读的你也加入一起分享。