求用户连续购买天数以及与上次购买间隔天数

最新推荐文章于 2024-06-05 21:58:40 发布
最新推荐文章于 2024-06-05 21:58:40 发布
阅读量984 收藏 3
点赞数 2
文章标签： hive sql spark
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_49664495/article/details/119490743
版权
本文详细介绍了如何使用SQL查询用户连续购买的天数，并通过Hive的lead函数计算购买间隔天数。步骤包括数据去重、排序、计算连续天数以及利用lead函数找出购买中断的天数，对于理解用户购买行为和分析消费模式具有重要意义。
摘要由CSDN通过智能技术生成

SQL-求用户连续购买天数以及与上次购买间隔天数

Step 1:对数据进行聚合/去重

	根据需求不同,可能的名称也不尽相同,主要是理解思想.

第一步是对数据按天进行初步聚合(因为一个用户可能在某天有多次购买行为)

因为只是作为演示,所以只用单个用户进行

select member_id,order_date from TABLE_NAME where member_id='1690' group by member_id,order_date

结果如下:

member_idorder_date
16902021-01-01
16902021-01-02
16902021-01-03
16902021-01-04
16902021-01-06
16902021-01-07
16902021-01-08
16902021-01-09
16902021-01-10
16902021-01-11
16902021-01-12
16902021-01-13
16902021-01-15

结果就不展示完全了…

Step 2:对用户进行分组排序

我们把上一个结果表称为: TABLE_1

select
   member_id
  ,order_date 
  ,row_number() over(partition by member_id order by order_date) as rank_num
from
  TABLE_1

结果如下:

member_idorder_daterank_num
16902021-01-011
16902021-01-022
16902021-01-033
16902021-01-044
16902021-01-065
16902021-01-076
16902021-01-087
16902021-01-098
16902021-01-109
16902021-01-1110
16902021-01-1211
16902021-01-1312
16902021-01-1513

Step 3:日期与序号进行减法运算

	思路: 如果我的日期是连续的.这时日期减去序号天数,那么连续的日期会得到同一个日期,我们暂且称之为起始日期.

我们把上一个结果表称为: TABLE_2

select
    member_id
   ,order_date 
   ,rank_num
   ,date_sub(order_date,rank_num) as origin_date
from
  TABLE_2

结果如下:

member_idorder_daterank_numorigin_date
16902021-01-0112020-12-31
16902021-01-0222020-12-31
16902021-01-0332020-12-31
16902021-01-0442020-12-31
16902021-01-0652021-01-01
16902021-01-0762021-01-01
16902021-01-0872021-01-01
16902021-01-0982021-01-01
16902021-01-1092021-01-01
16902021-01-11102021-01-01
16902021-01-12112021-01-01
16902021-01-13122021-01-01
16902021-01-15132021-01-02

Step 4:获取开始连续的日期以及连续天数

	其实从上面的结果表中就已经能够统计出那些用户的连续天数满足需求了.为了保留更多信息,所以还是可以继续进行加工

我们把上一个结果表称为: TABLE_3

select
   table_a.member_id                 	  as member_id
  ,table_a.continuous_start_date    	  as continuous_start_date    
  ,count(table_a.continuous_start_date)   as continuous_date
from
  (
	select
		member_id
	   ,min(order_date) over(partition by member_id,origin_date) as continuous_start_date
	from
	  TABLE_3
  ) table_a
 group by table_a.member_id,table_a.continuous_start_date

结果如下:

member_idcontinuous_start_datecontinuous_date
16902021-01-014
16902021-01-068
16902021-01-159
16902021-01-256
16902021-02-051
16902021-02-071
16902021-02-093

Step 5:使用Hive中lead函数

	lead() over()   将下一行提到当前行来另起字段 
		lead(需要调整的字段名,下几行,空余部分用什么补充)


	lag() over()     将上一行提到当前行来另起字段
		lag(需要调整的字段名,上几行,空余部分用什么补充)

饱经沙场的都知道,产品的提数需求后面一般都会跟着无数的顺便、再来等等…

	思路: 当   (continuous_start_date + continuous_date) - 下一行的continuous_start_date   =  中断天数

其实算中断天数的方法很多.我这里就采用 lead函数来实现.我们把上面的结果表称为: TABLE_4

select
   a.member_id                as member_id                
  ,a.continuous_start_date    as continuous_start_date      
  ,a.continuous_next_date     as continuous_next_date 
  ,a.continuous_date       	  as continuous_date       	   
  ,datediff(a.continuous_next_date,date_add(a.continuous_start_date,a.continuous_date)) as discontinuity_date
from
(
	 select
	   member_id  as  member_id  
	  ,continuous_start_date  as  continuous_start_date  
	  ,lead(continuous_start_date,1,0) over(partition by member_id,continuous_start_date) as continuous_next_date
	  ,continuous_date	as  continuous_date	
	 from
	   TABLE_4
 )a

结果如下:

member_idcontinuous_start_datecontinuous_next_datecontinuous_datediscontinuity_date
16902021-01-012021-01-0641
16902021-01-062021-01-1581
16902021-01-152021-01-2591
16902021-01-252021-02-0565
16902021-02-052021-02-0711
16902021-02-072021-02-0911
16902021-02-092021-02-1331
明月夜深沉
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
统信系统UOS桌面版V20 用户手册
Reyn_观极
07-27 1万+
UOS桌面版V20用户手册。
Spark core 计算连续登录天数、连续使用次数、连续xxx的案例实操
Genebrother
05-04 558
1、给出以下数据，计算用户连续登录的天数，数据格式为：用户,登录日期 user001,2020-03-14 user002,2020-03-13 user001,2020-03-19 user001,2020-03-18 user002,2020-03-12 user001,2020-03-20 user002,2020-03-17 user001,2020-03-10 user002,2020-03-14 user001,2020-03-15 user002,2020-03-15 思路解析： 首
电商用户购买行为数据分析
alanmoneyman的博客
10-09 9871
本文使用SQL和SPSS工具，对超过100万条数据通过分组聚合、联结查询等方式进行清洗，针对用户购买数据从产品销售指标、用户价值指标进行分析，对数据进行可视化，利用RFM模型进行用户细分并提出具有针对性的业务策略。
Python数据分析案例—淘宝用户行为分析
m0_61848660的博客
10-31 1593
本文以淘宝app平台为数据集通过电商行业常见行业的指标对淘宝用户行为进行分析，从而探索淘宝用户行为进行分析，具体指标包括：日PV和日UV分析，付费率分析，转化率分析复购行为分析.
市场营销学5——消费者购买行为分析
热门推荐
changyanmanman的专栏
12-11 2万+
什么是消费者购买行为 　　消费者购买行为是指人们为满足需要和欲望而寻找、选择、购买、使用、评价及处置产品、服务时介入的过程活动，包括消费者的主观心理活动和客观物质活动两个方面。 消费者购买行为分析的环节 　　消费者购买行为研究包括以下几个环节： 　　购买行为环节模式描绘：通过座谈会、深访、观察等形式得到系统的、感性的消费者购买行为过程。由于不同类型的产品和服务的特点差异，使得购买行
【Hive SQL 每日一题】统计用户连续下单的日期区间
JIE的博客 --- moon_coder
09-06 1077
函数与行号标记进行运算，如果数据连续的话，那么运算后的日期必然是一样的。统计用户连续下单的日期区间，所以连续的下单日期必须。，显然该日期是连续的，利用这一特性完成该需求。进行分组，同天的下单日期只保留一条。的分组，最大值与最小值日期统计。运算后日期进行分组，过滤数量。窗口函数进行行号标记，然后和。窗口函数对行号进行标记。解决这题的关键是使用。运算后，日期结果都为。
SQL求会员每次消费时间间隔和平均消费间隔
lbship的博客
11-17 3980
有一个表记录了每个会员的消费时间，如下，有的会员消费了N次，有的会员只消费过一次，现在要求出每个会员两两之间消费时间间隔 select *,LEAD(purtime, 1, 0)over(partition by userid order by purtime) as lastpurtime from #test order by userid,purtime 看到 ...
Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析
击水三千里的专栏
12-09 975
窗口函数 SUM(...) OVER(...) 的工作方式是在指定的窗口内累加值。在这种情况下，窗口是由 PARTITION BY user_id ORDER BY dt 定义的，这意味着：累加是在每个 user_id 分区内独立进行的，所以不同用户的累加是隔离的。在每个分区内，累加是按照日期 dt 的顺序进行的
基于聚类与统计检验深度挖掘电商用户行为
最新发布
m0_53814833的博客
06-05 981
在当今竞争激烈的电商市场中，了解用户的行为和需求对于制定成功的市场策略至关重要，本项目通过建立RFM模型、K-Means聚类模型，将1000个用户进行划分，针对不同类的用户，提出不同的营销策略，最后通过统计检验来探究影响用户消费行为的因素和影响用户上网行为的因素，通过这些分析，商家能够更好地理解消费者，从而制定更有效的市场策略，满足用户期望，提升用户体验，最终推动业务发展。
累计连续签到 设计和实现
hdfg159的专栏
08-15 3209
累计连续签到 设计和实现 最近公司业务上需要实现一个累计连续打卡的功能，现在把打卡设计问题和思路整理一下发给大家 目前搜集到一些基于 Redis 位图 / 关系型数据库的一些方案，可以参考一下，做出最优方案的选择 玩转Redis-京东签到领京豆如何实现 基于Redis位图实现用户签到功能 如何利用 Redis 快速实现签到统计功能 由于需求的复杂，本文还是选择使用关系型数据库实现和存储，因为关系型数据库查询无所不能，哈哈哈哈 功能要求 签到 补签 统计某用户截至今天连续打卡天数 统计某
HIVE常见五种时间交叉及连续登录类问题
LHJ_548936的博客
09-27 1070
本文章旨在用于记录学习经验和便于交流 博主水平有限 如果文章中有任何错误 欢迎指出 博主不胜感激。（注：转载引用请注明出处） 连续问题： 如下数据为蚂蚁森林中用户领取的减少碳排放量，找出连续3天及以上减少碳排放量在100以上的用户。 id dt lowcarbon 1001 2021-12-12 123 1002 2021-12-12 45 1001 2021-12-13 43 1001 2021-12-13 45 1001 2021-12-13 23 1002 2021-12-14 4
【大数据分析】对网站用户购物行为进行数据分析
paney_li的博客
06-20 2846
【大数据分析】对网站用户购物行为进行数据分析。
网上商城数据库
m0_74062024的博客
05-15 235
3）**插入用户（用户编号,用户名:“张三”,密码:”123456”,性别:”男”,电话:”13682865928”,地址:”河南省洛阳市西苑路48号”)。由于用户编号是自增字段，通常不需要在INSERT语句中指定，数据库会自动为新插入的记录生成一个唯一的编号。VALUES ('张三', '123456', '男', '13682865928', '河南省洛阳市西苑路48号');2）**调整商品编号为"110000"的商品价格为原来价格的90%。1）**删除购物车中商品编号为"110000"的购物信息。
power bi | 计算每个订单的间隔时间
alottle的博客
01-07 7466
目标 常规的订单明细表下，已知客户和交易日期，求同一客户的订单间隔 思路 分解为第一步：求该客户上一次交易时间，第二步：和本次交易时间求差（绝对值） 求该客户上一次交易时间：把该客户所有早于本次的交易时间取出来，求最大值 主要代码 新建列1：客户上一次交易时间 客户上一次交易时间= VAR E_date ='表'[交易日期].[Date] VAR CUST ='表'[客户标识符] RETUR...
MapReduce用户行为数据分析之最多购买组合
weixin_44342332的博客
04-08 768
需求分析： 通过对购买组合的分析可以对商品进行捆绑销售或者特殊促销的手段来提高销量，利用Map统计所有用户的购买商品情况，在Reduce阶段将所有商品组合，最后在cleanup阶段统计各种组合商品的购买次数并输出Top5。 测试数据如下： 结果： 可视化：如下图4所示 部分代码分析： reduce阶段： map阶段use_ID和time为key,ite_ID为value。reduce阶段购...
SPARK计算用户不同时段的消费金额
斧王，不能再斩杀了吗的博客
10-23 2067
计算用户不同时段的消费金额 import com.alibaba.fastjson.JSON import org.apache.spark.streaming.dstream.DStream import org.apache.spark.streaming.{Seconds, StreamingContext} import org.apache.spark.streaming.kafka01...
Spark清洗中遇到时间计算问题(天数运算)
JIE的博客 --- moon_coder
12-02 1217
先来了解一下这个日历类Calendar，是用来对时间的一些操作，具体可以查看这篇文章，十分详细。 ​​​​​​JAVA常见类（十二）Calendar类_ytasdfg的博客-CSDN博客_calendar​​​​​​ 我这里的需求是：将末尾的日期与上映天数做运算，得到一个新的值添加到当前日期的末尾。 1.根据时间的格式创建一个模板 var pattern = "yyyy-MM-dd" 2.将字符串日期转换为Date类型 val current:Date = new Simple.
MySQL购买间隔时间计算
haiyouzhuceyun的博客
11-17 1531
题记 最早发布于 2020/08/27 版权声明：本文为xuchl的原创文章，修改或转载请附上原文出处链接或CSDN链接 原文链接：https://xuchunlei.top/2020/08/27/MySQL%E8%B4%AD%E4%B9%B0%E9%97%B4%E9%9A%94%E6%97%B6%E9%97%B4%E8%AE%A1%E7%AE%97/ 微信公众号链接 因为一些原因被要求按照客户消费金额分类披露客户购买间隔时间，并对其合理性进行补充分析。网上查出来的方法都是你抄我的我抄你的，于是乎自己开始思
电商用户行为分析大数据系统
chbxw
07-11 1万+
包含的模块 1、对用户访问session进行分析 2、JDBC辅助类封装 3、用户访问session聚合统计 4、按时间比例随机抽取session 5、获取点击、下单和支付次数排名前10的品类 6、获取top10品类的点击次数最多的10个session 7、复杂性能调优全套解决方案 8、十亿级数据troubleshooting经验总结 9、数据倾斜全套完美解决方案 10、模块功...
用sql实现连续购买天数最大的用户
03-23
可以使用以下 SQL 语句实现： SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM ( SELECT user_id, COUNT(*) AS consecutive_days FROM ( SELECT user_id, DATE(date_column) AS date FROM table_name ORDER BY user_id, date ) AS t1 GROUP BY user_id, DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) DAY) ) AS t2 GROUP BY user_id ORDER BY max_consecutive_days DESC LIMIT 1; 该语句会返回连续购买天数最多的用户的 ID 和连续购买天数。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值