hiveSql完成递归计算2.0

部分普通sql查询在hive中的实现方式 部分普通sql查询在hive中的实现方式详细说明; 立即下载


最近遇到一个业务数据的坑,涉及到递归的计算思想,但是需要用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!!!

SQL_WITH_AS实现父子递归查询 sql 2005中用CTE实现递归的很好很易懂很基础的例子讲解。 立即下载

相关推荐

【C++PTA】6-1 Point类的运算 (10 分)

定义Point类,有坐标x,y两个私有成员变量;对Point类重载“+”(相加)、“-”(相减)和“==”(相等)运算符,实现对坐标的改变,要求用友元函数和成员函数两种方法实现。对Point类重载<<运算符,以使得代码 Point p; cout<<p<<endl;可以输出该点对象的坐标。 函数接口定义: 实现Point类。 裁判测试程序样例: /* 请在这里填写答案 */ int main(int argc, char const *argv[]) { P

libertye的博客 2369

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH

递归层次查询的orcale 和 hive实现,hive实现主要依赖于支持orcale语法

LMTX069的博客 5972

常见消息队列:ActiveMQ、RabbitMQ、RocketMQ、Kafka的区别总结

常见消息队列:ActiveMQ、RabbitMQ、RocketMQ、Kafka的区别。

qq_30713721的博客 3474

hiveSql完成类递归计算

问题 每个月有阅读数据如下: 需要计算截止到每个月,前月距离当月月数乘前月阅读数累计之和。 听起来比较绕,举个栗子: 图中每个月的阅读量是a,b,c,d,e。计算结果result如图公式。

i_mycode的博客 1万+

DySAT+: Enhancing Dynamic Graph Representation Learning with Multi-Head Self-Attention and Temporal-

本文深入探讨了DySAT+模型在动态图表示学习中的创新应用,通过多头时空注意力机制和层级信息融合策略,有效解决了动态图数据中的时间维度信息捕捉和结构演变动态适应问题。该模型在社交网络异常检测和金融交易网络分析等场景中表现出色,显著提升了预测准确率和响应速度。

weixin_29035405的博客 338

STK12与Python联合仿真(一):环境搭建

STK仿真python接口,安装与测试

qq_42635142的博客 1万+

Hive/MySQL/Flink 中对树结构/递归计算的处理

文章目录一.数据源:二.需要结果:三.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

lihuazaizheli的博客 2155

Ubuntu系统全盘备份——TimeShift的安装与使用

所以如果有那天你不小心作了个大死,并且幸运到还能打开GNOME桌面和Timeshift(只要不是执行sudo rm -f /*这样的自杀程序)的时候,点击你要恢复的那个备份文件再点击Restore,经过短暂的重启后你的系统就成功恢复了。打开软件后,会有一些初始化设置,只需要一直点击 Next。根据自己电脑的内存大小决定是自动备份还是手动备份。当然,对于虚拟机用户来说,没有比在客户机直接拷贝虚拟磁盘文件更棒的方式了。点击左上角的 Create,就会执行备份操作。最下面的意思就是说不会自动备份需要手动操作。

琉璃轻纱的博客 4708

大数据Hive DDL其他语法

目录1 Database|schema(数据库) DDL操作1.1 Create database1.2 Describe database1.3 Use database1.4 Drop database1.5 Alter database2 Table(表)DDL操作2.1 Describe table2.2 Drop table2.3 Truncate table2.4 Alter table3 Partition(分区)DDL操作3.1 Add partition3.2 rename partiti

赵广陆 1304

TS808效果器制作教程

TS808效果器制作教程 可以在立创商城购买到大部分的电阻电容和IC,音频接口,踩钉,电位器和电源接口可以去某宝看看都买得到的。链接https//pan.baidu.com/s/1QId1eIE3fJ2u5NLBSISvGA。安装过程过于繁琐,把所有元器件按照图纸焊接完成就可以调试了,祝你们成功!这是TS808经典效果器制作过程的记录。IC是JRC4558。...

qq_18948149的博客 3432

ESP32-IDF开发实例-外部Flash与FatFs文件系统数据存取

FatFs是用于小型嵌入式系统的通用FAT / exFAT文件系统模块。FatFs模块是按照ANSI C(C89)编写的,并且与磁盘I/O层完全分开。因此,它独立于平台。它可以并入资源有限的小型微控制器中,例如8051,PIC,AVR,ARM,Z80,RX等。ESP32-IDF对FatFs提供了良好的支持。

视觉与物联智能 2920

Hive 从 3.0 版本开始支持递归查询,这使得在 SQL 中可以更自然地处理递归逻辑。然而在某些情况下,比如在更老的 Hive 版本中,或者当递归查询导致性能问题时,你可能需要将递归逻辑替换成循环

然而在某些情况下,比如在更老的 Hive 版本中,或者当递归查询导致性能问题时,你可能需要将递归逻辑替换成循环逻辑。将递归逻辑转换为循环逻辑是一个具有挑战性的任务,需要对数据的结构和递归逻辑有深入的理解。将递归查询转换为循环逻辑通常涉及到模拟递归的过程,比如使用存储过程或自定义函数来实现。- 首先需要理解递归查询中每一步是如何执行的,这包括递归的起始点和递归的终止条件。- 在实际应用中,你可能需要编写更复杂的逻辑来处理更复杂的递归关系。- 在循环中,逐步构建递归的结果,直到满足终止条件。

靡不有初,鲜克有终 2019

多模态大模型:技术原理与实战 全球多模态大模型介绍

关键词:多模态大模型、深度学习、计算机视觉、自然语言处理、跨模态学习、迁移学习、人工智能随着人工智能技术的飞速发展,多模态大模型已经成为了当前AI领域最热门的研究方向之一。多模态大模型能够同时处理和理解多种类型的数据,如文本、图像、音频和视频等,这为AI系统在复杂场景下的应用提供了更加强大和灵活的能力。本文将深入探讨多模态大模型的技术原理,介绍其实战应用,并对全球主要的多模态大模型进行全面的介绍和比较。

AI天才研究院 4271

hive中递归_hive中常见问题

1 limit语句优化eg.select *from table_name > limit 100在 Hive 中, 由于表的数据量往往较大, 以上语句都会被优化 (sethive.fetch.task.conversion = none 会被关闭这项优化, 强制起 MR 作业; 默认配置值为 more);这些语句的运行,都会通过过滤文件的处理方式查询结果并返回, 而不是起 MR 任务提...

weixin_28721917的博客 5097

怀旧版电脑GBC模拟器

再现原版GBC掌机的界面,电脑运行连游戏界面都一样大小,很怀旧,很真实,(英文版)强烈推荐,很小的文件,很难才找到的。

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

hive中的SQL

第一章 查询语句基本语法 1.1 **Select***查询结构基本语法 下面是一个SQL查询语句的基本结构 1.2 sql****语句的执行顺序 1.3 查询注意事项 1.4 数据库建模 关系型数据库最难的地方,就是建模(model)。 错综复杂的数据,需要建立模型,才能储存在数据库。所谓"模型"就是两样东西:实体 (entity)+ 关系(relationship)ER图。实体指的是那些实际的对象,带有自己的属性,可以理解成一组相关属性的容器。关系就是实体之间的联系,通常可以分成"一对一"、

weixin_57551874的博客 1295

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

Hive基础【系统内置函数,常用查询函数】

一 函数 1 系统内置函数 (1)查看系统内置函数 show functions; (2)查看系统内置日期相关函数 show functions like "*date*"; (3)查看内置函数用法 desc function 'current_date'; (4)使用内置函数 select current_date(); 2 常用查询函数 (1)空字段赋值 NVL:给值为NULL的数据赋值,它的格式是NVL( value,default_value)。它的功能是如果value为NULL,则NVL

weixin_43923463的博客 1854

visio安装包

Microsoft Office Visio 是一款专业的制图软件。利用经过更新的形状、协作工具和数据链接图表,创建专业级图表以简化复杂的信息。

Hive 是否支持递归查询取决于你使用的 Hive 版本。如果你不确定你的版本是否支持,可以检查 Hive 的官方文档或更新到最新版本

Hive 是一个基于 Hadoop 的数据仓库工具,它提供了类似于 SQL 的查询语言,用于在 Hadoop 上执行查询和管理数据。在传统的 SQL 数据库中,递归查询(也称为公用表表达式或 CTEs)可以通过 `WITH RECURSIVE` 语句实现。如果你使用的是较旧版本的 Hive,可能需要通过其他方法来模拟递归查询,比如使用存储过程或自定义的 UDF(用户定义的函数)。如果你使用的是较新版本的 Hive,可以使用 `WITH RECURSIVE` 来执行递归查询。

靡不有初,鲜克有终 1272

hive sql 时间递归写法

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 ...

yisun123456的博客 6184

hive sql递归查询所有叶子结点

为NULL的节点,然后通过自连接的方式将父节点和子节点进行关联,逐步递归查询。直到没有更多的子节点时,递归查询结束。在Hive SQL中,递归查询所有叶子节点可以使用WITH RECURSIVE关键字来实现。注意:Hive 2.3.2或更高版本才支持WITH RECURSIVE关键字。首先,你需要有一个表来表示节点之间的关系。在递归查询中,第一次递归查询的起始条件是。表示节点的父节点标识。上述查询会返回所有叶子节点的。表示节点的唯一标识,

weixin_42497376的博客 1272

Hive入门

1.1 什么是Hive Hive:由Facebook开源用于解决海量结构化日志的数据统计。 Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张表,并提供类SQL查询功能。 本质是:将HQL转化成MapReduce程序 1)Hive处理的数据存储在HDFS 2)Hive分析数据底层的实现是MapReduce 3)执行程序运行在Yarn上 1.2 Hive的优缺点 1.2.1 优点 1)操作接口采用类SQL语法,提供快速开发的能力(简单、容易上手)。 2)避免了去写MapReduce

如此生活丶的博客 261

如何在Hive中实现递归计算

Hive脚本中添加: set hive.mapred.supports.subdirectories=true; set mapred.input.dir.recursive=true;

张Sir的博客 2万+

hive递归尝试失败,使用python脚本解决的尝试

在一张hive表中 存储了自身关联自身两键值 即: 当前id(newid) 和 原先id (oldid); 每一个 newid 和 oldid 两id有可能 关联本表中 其他的id 现有需求找到 最后newid 和 最早oldid 使其一一对应 网上有sqlserver 等关系型数据库的递归说明大抵就是利用with as 建立子表 自身调用自身实现递归,但是hive中不支持这种方式, 后来想使用datax 提交代码到sqlserver运行,运行即结果直接传输到建立的hive表中,

select_where的博客 801

flink/hive sql 实现递归 查询人员组织架构

递归人员归属查询 解决的问题:flink sql无法使用递归查询的问题 场景 syc_loan_dpt(did, dname, dlevel, pdid)、syc_agent_usr(uid、uname、did) 组织架构:元事业部/城市中心/分中心/中心分部/归属阶段/归属小组 每个销售经归属,叶子节点可能为level in (6, 7, 8), 要求从原事业部开始到叶子节点 业务库(Oracle) selecet did, dname, dlevel, pdid from syc

qq_33256751的博客 4741

hive实现自定义公式解析

hive实现自定义公式解析 import org.apache.hadoop.hive.ql.exec.UDF; import javax.script.ScriptEngine; import javax.script.ScriptEngineManager; import javax.script.ScriptException; import java.util.Stack; import java.util.regex.Matcher; import java.util.regex.Pattern

qq_34352013的博客 732
上一篇: 11Python基础-字典Dict
下一篇: 12Python基础-集合Set
@nanami
博客等级 码龄9年 4247粉丝 53原创
评论 15
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

@nanami

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

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

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

打赏作者

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

抵扣说明:

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

余额充值