hiveSql完成类递归计算

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

和同事一起探讨了个问题挺有意思,记录一下!
问题
每个月有数据如下:
在这里插入图片描述
需要计算截止到每个月,前月距离当月月数乘前月阅读数累计之和。
类似递归操作:本月到第一个月的累计和,前一个月到第一个月的累计和…一直到第二个月到第一个月的和 再加上第一个月 最终所有累积和的合计。

听起来比较绕,举个栗子:
在这里插入图片描述
图中每个月的阅读量是a,b,c,d,e。计算结果result如图公式。

  1. 截止到2014-01月:只有a,距离本月为1个月,即a*1;
  2. 截止到2014-02月:1月距离本月为2个月,即1月的a为a*2,2月距离本月为1,即b,求和为a*2+b;
  3. 截止到2014-03月:1月距离本月为3个月,及1月的a为a*3,2月距离本月为2,即b*2,3月距离本月为1,即c,求和为a*3+b*2+c;
    以此类推计算截止到每个月的前月累计。

到这里可以先思考下怎么做。。。


最终同事用excel完成了计算。这里想用hiveSql看看能不能统计,想了下思路。

思路:

原本想的是横向拉平数据,在每个月的后面都计算好当月所需要的前月乘后的数据,再横向累加,如下图:
123
这个思路用sql实现有两个关键点:a.拉平数据 和 b.对应拉平数据后的乘数(前月距离本月的月数)

  1. 拉平数据
    思路:即多行转多列操作,步骤为多行转一行,再一行转多列即可。
    操作:按照createtime分组拼接每个月的数据为字符串,再拆分为多列
select
    createtime
    ,cast(split(str,',')[0] as bigint) as month1
    ,cast(split(str,',')[1] as bigint) as month2
    ,cast(split(str,',')[2] as bigint) as month3
    ,cast(split(str,',')[3] as bigint) as month4
    ,cast(split(str,',')[4] as bigint) as month5
from
    (select 
        createtime,wm_concat(avg_view,',') as str 
    from tablename 
    group by createtime
    ) tmp;
  1. 对应乘数
    思路:观察这里的月差值,小于等于当月的月数直接减,大于当月的月数直接赋0值即可。
    操作:利用row_number()产生自增序列,在利用leg() 操作自增序列的位置移动
select
    createtime,
    month1,lag(irank,1,0) over(order by irank) as lag1,
    month2,lag(irank,2,0) over(order by irank) as lag2,
    month2,lag(irank,3,0) over(order by irank) as lag3,
    month2,lag(irank,4,0) over(order by irank) as lag4,
    month2,lag(irank,5,0) over(order by irank) as lag5
from
    (select
        createtime
        ,cast(split(viewstr,',')[0] as bigint) as month1
        ,cast(split(viewstr,',')[1] as bigint) as month2
        ,cast(split(viewstr,',')[2] as bigint) as month3
        ,cast(split(viewstr,',')[3] as bigint) as month4
        ,cast(split(viewstr,',')[4] as bigint) as month5
        ,ROW_NUMBER() OVER(ORDER BY createtime) AS irank -- 多出一行自增序列
    from
        (select 
            createtime
            ,wm_concat(cast(avg_view as string),',') as viewstr
        from tablename
        group by createtime
        ) tmp
    ) tmp1

上述sql结果如图:
在这里插入图片描述
再将sql内的monthN*legN再横向累加即可

  1. 总结:这种思路可以实现但是缺点显而易见,太麻烦了。后来看到同事的数据有88个月,我裂开!要写多少行啊。放弃!

归结上述思路,发现繁琐的步骤就再横向拉平那块,88个月split要写88个,上面的leg又是88个。hive中对于这种繁琐的操作应该是有内置函数的。之前有写过内置函数的总结篇。HIVE窗口函数合集,发现果然有。

利用开窗函数的window子句其中的累加到当前行。只需要使用两次即可:

SELECT 
    createtime,avg_view,sample1,
    sum(sample1) over(order by createtime rows between UNBOUNDED PRECEDING and CURRENT ROW ) as sample2
FROM 
    (select 
        createtime,avg_view,
        sum(avg_view) over(order by createtime rows between UNBOUNDED PRECEDING and CURRENT ROW ) as sample1
    from tablename
    ) t;

第一次使用得到第一行累加到当前行的统计值sample1。再次将sample1累加一次第一行到当前行即得到最终结果。如下图所示:
在这里插入图片描述
总结:同事使用excel完成的思路也是这样,累加两次。excel真香!

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

相关推荐

Uds诊断自动化(CANoe.diva应用)

(7)其他配置选择可以保持默认(一般情况下,DTC以及31服务、下载服务需要另外测试)。(1)打开CANoe.Diva软件,并新建Diva工程。(2)设置工程名和保存位置;在"Specification file"(3)测试案例打开后的界面如下,可以对要进行测试的案例进行勾选,(可查看下角的进度条)后;(3)等待DIva工程的生成;(4)接下来是修改Diva工程选项,如下图点击。,如用例间的时间间隔、复位等待时间等。,即可打开导入的测试工程。,即可生成测试用例。(1)在CANoe中。

LIUJIAAAAA的博客 3446

hiveSql完成递归计算2.0

hiveSql实现递归思想,计算数据

i_mycode的博客 5532

联想拯救者r7000p在ubuntu20.04未找到wifi适配器,安装rtl8852ae网卡驱动问题解决方案

新买的拯救者r7000p 2021款,装完ubuntu20.04之后显示未找到wifi适配器, 更换N次源之后还是只有一个显卡驱动,在网上找了很多方法,再自己折腾了一段时间后,总算是解决了这个问题。 1.设置bios 打开主板系统,将security boot设置为disenable,因为设计到修改内核配置,必须得关掉安全启动。 2.查询网卡状态 使用命令查询网卡(hardware of Internet)状态 lshw -C network 因为是双系统,在Windows下控制面板-设备管理器中可以

MCZ777的博客 1万+

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

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

LMTX069的博客 5972

构建面向嵌入式Linux的ARM64交叉编译工具链:从sysroot配置到CMake实战

本文详细介绍了如何构建面向嵌入式Linux的ARM64交叉编译工具链,从sysroot配置到CMake实战。通过分步指导,帮助开发者解决架构不一致问题,提升编译效率,特别适用于树莓派等ARM64设备开发。文章涵盖sysroot机制、环境搭建、CMake配置及常见问题解决方案,是嵌入式开发的实用指南。

weixin_42537142的博客 200

【智驾与大模型】端到端与多模态大模型的精彩之旅

智驾和大模型的回顾与展望。开头的笔者自述也说了些心窝子话,边缘计算的监管刻不容缓!

仿生实验室:从0到1,制作人脸表情交互机器人,记录过程,软硬件全部开源,与君共襄盛举! 1321

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

运算放大器电路中的T型网络:如何用更小电阻实现高增益并降低噪声?

本文深入解析了运算放大器电路中T型反馈网络的设计与应用。该网络通过三个较小阻值电阻的组合,等效实现高增益,有效规避了传统大反馈电阻带来的显著热噪声问题。文章从数学原理、噪声定量分析到实际设计技巧,系统阐述了如何利用T型网络在实现高增益的同时,显著降低电路噪声并改善整体性能。

banana的博客 323

hive SQL实现占比、同比、环比计算(lag函数,lead函数)

前两天博文 《SQL实现占比(时间函数升级版)》, 现在用hive实现一下 SELECT month_order, year_order, number, concat( round( number / total * 100.00, 2 ), '%' ) percent FROM ( SELECT * FROM ( SELECT substr( order_time, 1,7...

清平乐的技术专栏 2万+

保姆级避坑指南:在Ubuntu 18.04上搞定速腾Helios雷达驱动与fast-LIO2的完整配置流程

本文提供了一份详细的速腾Helios雷达与fast-LIO2在Ubuntu 18.04上的配置避坑指南,涵盖驱动安装、数据格式转换到建图全流程。特别针对常见编译错误和数据转换问题提供了解决方案,帮助开发者高效完成环境搭建与建图任务。

weixin_27252001的博客 115

YOLOv11改进 | 缝合CCFM轻量检测模型实现高精度跨尺度特征融合【附参考代码】

YOLOv11作为一代优秀的端到端目标检测模型,已经在速度和精度之间取得了较好的平衡。但其默认的Neck结构(通常为FPN + PAN)在处理小目标与多尺度信息时,仍存在信息流冗余、跨尺度特征融合不充分等问题。 本文提出一种轻量化的跨尺度跨通道融合模块(CCFM, Cross-scale and Cross-channel Fusion Module),以替代或增强YOLOv11中的Neck部分,有效提升特征表达能力和检测性能,尤其是在小目标和复杂场景下取得显著涨点。

一键难忘的博客 1417

大数据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

[UVM]UVM TLM2.0简介及应用实例分析

本文介绍了TLM2.0在UVM中的实现与应用。TLM2.0作为SystemC模型间的核心传输机制,相比TLM1.0提供了更丰富的特性,包括双向阻塞/非阻塞接口、时间标记和统一数据包。文章详细解析了TLM2.0在UVM中的接口实现方式,包括socket类型定义、数据传输机制以及时间标记处理。通过一个具体实例,展示了如何在UVM组件中使用TLM2.0的initiator和target socket进行数据传输,并说明了标准数据包格式和精确时间标记的优势。最后指出TLM2.0在UVM中的实现主要是为了与Syste

元直的博客 5400

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

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

靡不有初,鲜克有终 2019

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

hive中的SQL

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

weixin_57551874的博客 1295

老版QQ原生表情gif动态

这是老版的QQ表情,不过全是动态的的,Windows照片查看器是不会动的,可以右击用浏览器打开查看,再说一遍是老版的,只是分享给宁愿用老版也要动态的人。

hive中递归_hive中常见问题

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

weixin_28721917的博客 5097

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

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

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>=20180706 and p_day<=20180719 and ...

yisun123456的博客 6184

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

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

靡不有初,鲜克有终 1272

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

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

weixin_42497376的博客 1272

hive的UDF函数与hive实现WC以及开启文件递归支持

一、UDF函数 UDF函数 sum count … build-in 基础 线上业务,单单是用build-in是完成不了了 ==> 扩展我们自己所需要的函数 迁移:RDBMS ==> 云化(大数据上来Hive/Spark) 基于已有的业务使用Hive语法改造 开发一个函数,和RDBMS是同名的 DIFF UDF: User-Defined-Function UDF:one-to...

xwcj3的博客 1832

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万+

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窗口函数合集 -- 持续更新
下一篇: 2021-05-24 每日一题 (数组中重复的数字)
@nanami
博客等级 码龄9年 4247粉丝 53原创
评论 4
成就一亿技术人!
拼手气红包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、付费专栏及课程。

余额充值