hiveSql完成递归计算2.0

最新推荐文章于 2024-08-23 02:04:52 发布
最新推荐文章于 2024-08-23 02:04:52 发布
阅读量5.1k 收藏 32
点赞数 7
分类专栏： hive 文章标签： hive 数据仓库 数据库开发 big data
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/i_mycode/article/details/123839678
版权

hiveSql完成递归计算


最近遇到一个业务数据的坑，涉及到递归的计算思想，但是需要用hivesql完成计算。经过同事的提点完成了，这里记录一下过程。

基础数据

先说下基础数据：
有用户ID，下单时间，卡类型。想要按照有效期分别为年卡：365天，月卡：31天，两年卡：730天计算出每个用户每一单的会员开始结束时间。PS：表中没有一个用户在同一天买多单的情况

用户ID下单时间卡类型
A2022-01-24 21:47:00月卡
A2022-02-03 23:08:45月卡
A2022-02-11 07:22:43月卡
A2022-02-15 22:31:32年卡
B2022-02-14 16:03:12月卡

有会员数据，只记录了购买会员的下单时间，和会员卡的类型，下单用户。不知道会员的开始结束时间（会员有效时间），需要通过上述已知的条件计算出来。一开始以为只要算一下下单时间作为当前单的开始时间，再通过卡类型的有效期计算下结束时间就好（不同类型的卡有不同的有效期，例如月卡31天，年卡365天）。但是后来开始写才发现没那么简单~ 用户可以在自己还是上一单会员的有效期内提前购买会员。如果这种情况还是按照上述逻辑计算的话。上一单还没结束，当前单的下单时间就不是当前单的有效期开始时间了。例如：

用户第N次买会员卡类型下单时间计算开始时间计算结束时间
A1月卡2022-01-24 21:47:002022-01-24 21:47:002022-02-23 21:47:00
A2月卡2022-02-03 23:08:452022-02-03 23:08:452022-03-05 23:08:45

上表中用户A买了两单，但是第二单是在第一单还没结束买的。所以第二单的开始结束时间计算的就有问题了！
真实的第二单开始时间应该是第一单的结束时间，即2022-02-23号。结束日期是这个日期加上卡类型对应的有效期，即2022-03-25号。

分析数据

准对这种情况可以分析数据。
用户可以在自己还是会员有效期内再次购买，且可以不止购买一次。类似上述基础数据，用户A购买了4单，其中后三单都是在相对前一单没结束的情况下买的。按照上述的逻辑，这种情况，用户A的后3单都应该是相对前一单的结束时间。类似递归计算，每一单都需要先计算出前一单。

第N单用户ID下单时间卡类型计算开始时间计算结束时间
1A2022-01-24 21:47:00月卡当前单的下单时间当前单的下单时间+月卡有效天数31
2A2022-02-03 23:08:45月卡第1单的结束时间第1单的结束时间+月卡效天数31
3A2022-02-11 07:22:43月卡第2单的结束时间第2单的结束时间+月卡效天数31
4A2022-02-15 22:31:32年卡第3单的结束时间第3单的结束时间+年卡效天数365

利用hiveSQL实现，到这里可以先思考下怎么做

初步思路

将上述表按照用户id分组,下单时间升序row_number()开窗后进行自关联。左表序号 = 右表序号 + 1,会得到将每个用户的前后单拉平的数据。判断前一单会员的结束时间和当前单下单时间的大小，知道下一单购买时，是否在上一单的有效期内。从而判断当前单的开始时间用当前单的下单时间还是上一单的结束时间。见表：

a.用户IDa.下单时间a.结束时间a.卡类型a.序号b.下单时间b.卡类型b.序号
A2022-01-242022-02-24月卡1\N\N\N
A2022-02-032022-03-06月卡22022-01-24月卡1
A2022-02-112022-03-14月卡32022-02-03月卡2
A2022-02-152022-03-15年卡42022-02-11月卡3

可以看到上表中第二行数据是A用户的第二单（a.字段名）和第一单（b.字段名）拉平。其中a.结束时间是根据a.下单时间加上卡的对应有点天数计算的临时结束日期（目的是为了得到用户的第一单的结束日期），此时比较b.下单时间 和 a.结束时间即可知道用户的第二单是否是在第一单的有效期内下单。从而判断第二单的开始时间是第一单的结束时间 还是 第二单的下单时间。

仔细看发现此初步思路并不可行。因为是错一位关联的，可以看到上表中的第三行，应该需要用b.下单时间 和 用户第二单的真正会员结束时间做比较才对。此逻辑行不通。

最终解决方案

理一下思路，其实从一个用户角度出发，如果该用户购买了多单。每单可能是在上一单的有效期内购买（即续费），也有可能是在上一单有效期外购买（即复购）。
给用户的所有单按照时间升序排序后，如果是续费则为1，是复购则为0。有表如下:

第N单复购0/续费1
第一单-
第二单0
第三单1
第四单1
……
第N单0

即只需要找到除了第一单外每个连续续费前的第一个复购即可。按照这单复购连续递归计算出升序后的连续续费有效期。知道遇到下一个复购。即上表中的每个0管下面的连续一串的1。递归思路就在这里了。但是想了挺久没有想到怎么在已知用户的现有的所有订单数据的情况下计算出来（我感觉算不出来，如果有能算出来的思路课在下面的评论区 或者私信告诉我，我学习学习）。后来请教了大神同事。给了我一个思路。

先查询该表的所有数据最小下单时间作为第一天。因为表中没有一个用户在同一天买多单的情况。所以第一天出现的一定是用户的第一单。以此算下这些第一单的开始时间（即下单时间） 和 结束时间（下单时间 + 卡对应的有效天数）
写到表中，得结果表tableA，原表为tableB。再限制tableB中的下一天（即最小天的下一天，记为表tmp），tmp这些下单用户作为左表 左关联 tableA中每个用户的最大下单时间的那一单。判断tmp中的每个用户每一单的下单时间 和 该用户在今天之前的最后一单的结束时间。从而模拟出时间的流逝，每天用户下单 都和今天之前的最后一单做比较，判断是复购还是续费，从而对应的计算开始结束时间，再union all 今天之前的所有已经计算好的会员数据。就说这个思路真的挺牛，每单都能和自己的上一单已经算好的结束时间做关联！没明白的可以见下表，简单画一下一个用户的:

例如现在有用户A，下了很多单，有复购有续费的，最小下单时间取出来，最小下单则为该用户的第一单（时间是第一天）：

用户第N次买会员卡类型下单时间计算开始时间计算结束时间
A1月卡2022-01-24 21:47:002022-01-24 21:47:002022-02-23 21:47:00

此时时间流逝，来到该用户第二单的那天，取出该数据 作为左表A 左关联这个用户在今天之前的最后一单表b，即上表中的第一单：

a.用户a.第N次买会员a.卡类型a.下单时间b.用户b.第N次买会员b.卡类型b.下单时间b.开始时间b.结束时间
A2月卡2022-02-03 23:08:45A1月卡2022-01-24 21:47:002022-01-24 21:47:002022-02-23 21:47:00

此时第二单（当前单）就和已经算好了正确的开始 、 结束时间的前一单关联到一起了。判断当前单的下单时间和上一单的正确的结束时间的大小即可判断出当前单是续费还是复购。从而计算出当前单正确的开始时间结束时间。再和表b做union all操作，将该用户的两单写到结果表中。

用户第N次买会员卡类型下单时间计算开始时间计算结束时间
A1月卡2022-01-24 21:47:002022-01-24 21:47:002022-02-23 21:47:00
A2月卡2022-02-03 23:08:452022-02-23 21:47:002022-03-26 21:47:00

此时，时间继续流逝，来到该用户的第三单，同第二单一样。将第三单取出，左关联 前两单的最后一单（即第二单）。这样第三单右可以和已经计算好正确的开始结束时间的第二单关联上了。再计算第三单的开始结束时间，再union all以为第四单做准备。

代码

insert overwrite table 结果表
select
    a.order_sn, -- 订单号
    a.member_id, -- 用户ID
    cast(a.pay_amount as double) as pay_amount, -- 支付金额
    a.create_time, -- 订单创建时间
    case when a.total_amount = 98 then '月卡'
          when a.total_amount = 298 then '年卡'
    else '两年卡' end as card_type, -- 卡类型
	
	-- 会员开始时间
    case when b.member_id is null or cast(a.payment_time as string) > cast(b.end_time as string) then a.payment_time else b.end_time end as start_time,

	-- 会员结束时间
    case when b.member_id is null or cast(a.payment_time as string) > cast(b.end_time as string) then  
        case when a.total_amount = 98 then dateadd(a.payment_time,31,'dd')
             when a.total_amount = 298 then dateadd(a.payment_time,365,'dd')
        else dateadd(a.payment_time,730,'dd') end
    else 
        case when a.total_amount = 98 then dateadd(b.end_time,31,'dd')
             when a.total_amount = 298 then dateadd(b.end_time,365,'dd')
        else dateadd(b.end_time,730,'dd') end
    end as end_time
from 
    (select * from 原表 where substr(create_time,1,10) = '${tdate}') a
	
    left join

    (select
        *
    from
        (select 
            *,ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY create_time desc) AS irank 
        from 结果表 where substr(create_time,1,10) < '${tdate}'
        ) t where irank = 1
    ) b
    on a.member_id = b.member_id
union  all

select * from 结果表 where substr(create_time,1,10) < '${tdate}';

优缺点

  • 优点
    能准确的计算出每单的开始结束时间，思路很棒。

  • 缺点
    当数据量很大时，即会员表开始时间在很久以前，这种思路只能是单线程跑下来，不能并行计算的，所以跑的会很慢。

总结

涛神（同事），牛x！！！

@nanami
关注
专栏目录
Hive SQL操作与函数自定义（一）
梓纾的专栏
09-21 1万+
Hive SQl操作简介；关于UDF、UDAF、UDTF的自定义；以及其他一些整理资料（例如自定义函数jar包的放在那里，远程调试等）
大数据命令，一文在手，全部都有2.0
悦上心灵的博客
01-29 909
一文在手，全部都有
hive sql 时间递归写法
yisun123456的博客
08-16 6046
select a.p_day,0 fuv,0 vv,0 as fuv_sec,0 as vv_sec,count(distinct b.uid) fuv_sev,sum(b.vv) vv_sev from (select p_day,uid,sum(vv)vv from default.t_1 where p_day&gt;=20180706 and p_day&lt;=20180719 and ...
hiveSql完成类递归计算
i_mycode的博客
05-13 1万+
问题 每个月有阅读数据如下： 需要计算截止到每个月，前月距离当月月数乘前月阅读数累计之和。 听起来比较绕，举个栗子： 图中每个月的阅读量是a,b,c,d,e。计算结果result如图公式。
hive sql递归查询所有叶子结点
weixin_42497376的博客
08-20 254
为NULL的节点，然后通过自连接的方式将父节点和子节点进行关联，逐步递归查询。直到没有更多的子节点时，递归查询结束。在Hive SQL中，递归查询所有叶子节点可以使用WITH RECURSIVE关键字来实现。注意：Hive 2.3.2或更高版本才支持WITH RECURSIVE关键字。首先，你需要有一个表来表示节点之间的关系。在递归查询中，第一次递归查询的起始条件是。表示节点的父节点标识。上述查询会返回所有叶子节点的。表示节点的唯一标识，
hive往上递归查到所有数据
最新发布
weixin_41396262的博客
08-23 34
我整理的一些关于【数据】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/eDOcp1Hive 递归查询数据的实现 在数据分析和处理过程中，我们经常会遇到需要从某个记录开始，向上递归查找所有相关数据的情况。这篇文章将指导你如何实现 Hive 中的递归查询。尽管 Hive 本身并...
hive中的SQL
weixin_57551874的博客
05-06 966
第一章 查询语句基本语法 1.1 **Select***查询结构基本语法 下面是一个SQL查询语句的基本结构 1.2 sql****语句的执行顺序 1.3 查询注意事项 1.4 数据库建模 关系型数据库最难的地方，就是建模（model）。 错综复杂的数据，需要建立模型，才能储存在数据库。所谓"模型"就是两样东西：实体 （entity）+ 关系（relationship）ER图。实体指的是那些实际的对象，带有自己的属性，可以理解成一组相关属性的容器。关系就是实体之间的联系，通常可以分成"一对一"、
Hive 从 3.0 版本开始支持递归查询，这使得在 SQL 中可以更自然地处理递归逻辑。然而在某些情况下，比如在更老的 Hive 版本中，或者当递归查询导致性能问题时，你可能需要将递归逻辑替换成循环
靡不有初，鲜克有终
07-19 524
然而在某些情况下，比如在更老的 Hive 版本中，或者当递归查询导致性能问题时，你可能需要将递归逻辑替换成循环逻辑。将递归逻辑转换为循环逻辑是一个具有挑战性的任务，需要对数据的结构和递归逻辑有深入的理解。将递归查询转换为循环逻辑通常涉及到模拟递归的过程，比如使用存储过程或自定义函数来实现。- 首先需要理解递归查询中每一步是如何执行的，这包括递归的起始点和递归的终止条件。- 在实际应用中，你可能需要编写更复杂的逻辑来处理更复杂的递归关系。- 在循环中，逐步构建递归的结果，直到满足终止条件。
Hive/MySQL/Flink 中对树结构/递归计算的处理
lihuazaizheli的博客
07-25 1342
文章目录一.数据源:二.需要结果:三.hive sql四. 输出结果 一.数据源: id,city,parentId 1,北京市,0 2,山东省,0 3,昌平区,1 4,海淀区,1 5,沙河镇,3 6,马池口镇,3 7,中关村,4 8,上地,4 9,烟台市,2 10,青岛市,2 11,单平区,9 12,芝果区,9 13,即墨区,10 14,城阳,10 注意: 一个id 唯一对应一个ParentId 一个ParentId可以对应多个id 二.需要结果: 一级 二级 三级 0
如何在Hive中实现递归计算
热门推荐
张Sir的博客
10-10 2万+
Hive脚本中添加： set hive.mapred.supports.subdirectories=true; set mapred.input.dir.recursive=true;
部分普通sql查询在hive中的实现方式
11-18
部分普通sql查询在hive中的实现方式详细说明；
Hadoop和Hive
gailun66的博客
04-19 1880
Hadoop 框架是用于计算机集群大数据处理的框架，所以它必须是一个可以部署在多台计算机上的软件。部署了 Hadoop 软件的主机之间通过套接字 (网络) 进行通讯。Hadoop 主要包含 HDFS 和 MapReduce 两大组件，HDFS 负责分布储存数据，MapReduce 负责对数据进行映射、规约处理，并汇总处理结果。Hadoop 框架最根本的原理就是利用大量的计算机同时运算来加快大量数据的处理速度。
Spark SQL的自定义函数UDF
xiaohu21的博客
10-06 3599
Spark SQL的自定义函数UDF 1. 背景 在SQL使用时，会有内置函数，但如果业务比较复杂，但又希望可以有更加灵活的函数使用和复用，则需要自定义 UDF，就是user defined function，可以分为UDTF、UDAF UDTF，user defined table-generating function，就是将数据打散 UDAF，user defined aggregating function，就是将数据聚合。 2. 创建和使用UDF 下述会使用案例来展示如何使用自定义函数 2.1
从零开始大数据--Hadoop、HDFS、MapReduce、HBase、Hive
MOKE_SPACE
05-21 2993
文章目录概述HadoopHDFSHBase实现原理Regin服务器原理HBase安装与使用NoSQL数据库MapReduceHive 概述 IT领域每隔十五年就会迎来一次重大变革： 1980：个人计算机 1995：互联网 2010：物联网、云计算和大数据 信息科技为大数据时代提供技术支撑： 存储设备容量不断增加 CPU处理能力大幅提升 网络带宽不断增加 大数据是由结构化和非结构化数据组成的 10%的结构化数据，存储在数据库中 90%的非结构化数据，它们与人类信息密切相关 大数据技术的不同层面
SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
LMTX069的博客
03-20 4739
递归层次查询的orcale 和 hive实现，hive实现主要依赖于支持orcale语法
Hive 是否支持递归查询取决于你使用的 Hive 版本。如果你不确定你的版本是否支持，可以检查 Hive 的官方文档或更新到最新版本
靡不有初，鲜克有终
07-19 535
Hive 是一个基于 Hadoop 的数据仓库工具，它提供了类似于 SQL 的查询语言，用于在 Hadoop 上执行查询和管理数据。在传统的 SQL 数据库中，递归查询（也称为公用表表达式或 CTEs）可以通过 `WITH RECURSIVE` 语句实现。如果你使用的是较旧版本的 Hive，可能需要通过其他方法来模拟递归查询，比如使用存储过程或自定义的 UDF（用户定义的函数）。如果你使用的是较新版本的 Hive，可以使用 `WITH RECURSIVE` 来执行递归查询。
hive中递归_Hive使用来自嵌套子目录的输入创建表
weixin_35214204的博客
12-28 712
I have data in Avro format in HDFS in file paths like: /data/logs/[foldername]/[filename].avro. I want to create a Hive table over all these log files, i.e. all files of the form /data/logs/*/*. (They...
hive的UDF函数与hive实现WC以及开启文件递归支持
xwcj3的博客
02-02 1643
一、UDF函数 UDF函数 sum count … build-in 基础 线上业务，单单是用build-in是完成不了了 ==> 扩展我们自己所需要的函数 迁移：RDBMS ==> 云化（大数据上来Hive/Spark） 基于已有的业务使用Hive语法改造 开发一个函数，和RDBMS是同名的 DIFF UDF： User-Defined-Function UDF：one-to...
hivesql实现递归
03-25
HiveSQL 可以使用CTE（公共表表达式）来实现递归。公共表表达式是一种使用WITH关键字定义的命名查询块，它定义了一个可重用的查询块，可以在其他查询中使用。CTE可以递归地自引用，用于创建递归查询。 实现递归的一般步骤如下： 1. 定义递归的初始状态，也就是递归的基本查询语句。 2. 定义递归的终止条件，也就是递归结束的条件。 3. 定义递归的迭代部分，也就是在递归中执行的查询语句。 具体实现如下： WITH RECURSIVE cte AS ( -- 递归的初始状态 SELECT id, name, parent_id FROM table WHERE id = 1 UNION ALL -- 递归的迭代部分 SELECT t.id, t.name, t.parent_id FROM table t INNER JOIN cte ON t.parent_id = cte.id WHERE t.id != 1 -- 避免重复 ) -- 递归的终止条件 SELECT * FROM cte WHERE parent_id IS NULL;
评论 15
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

@nanami

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值