hiveSql 构建连续日期

使用HiveSQL实现开始到结束日期之间的所有日期 查询1: set hive.exec.mode.local.auto=true; --开启本地模式 set hive.cli.print.header=true; --打印表.列名 select tmp.*, t.*, date_add(start_date, pos) as mid_date from( select '1' as uid, '2020-01-01' as start_date, '2020-01-05' as end_date 阅读详情

hiveSql 构建连续日期

需求

有如下数据

iddateval
1012022-12-0110
1012022-12-0320
1012022-12-0640
1022022-12-0220
1022022-12-0430
1022022-12-0760

期望构建如下数据:

idfinal_datevalfinal_value
1012022-12-011010
1012022-12-02010
1012022-12-032030
1012022-12-04030
1012022-12-05030
1012022-12-064070
1012022-12-07070
1022022-12-0100
1022022-12-022020
1022022-12-03020
1022022-12-043050
1022022-12-05050
1022022-12-06050
1022022-12-0760110

期望数据是将已有数据补齐2022-12-01到2022-12-07号日期,没有的日期value为0,然后计算每组id内的累加value值。

分析

从期望数据角度看,咱们如果有每组id的2022-12-01到2022-12-07的数据,作为主表left join已有数据,再一个sum()over() 就很简单了,关键在于如何构造连续日期。
构造连续日期,且分成多行很容易想到利用repeat()函数,和posexplode()拆分重复的字符串,构建多行带索引的数据。再利用行索引与2022-12-01做日期计算,就能得到每组组内的01-07号多行数据

实现

1、先构建所有分组的2022-12-01、2022-12-07


with tmp as (
    select '101' as id, '2022-12-01' as idate, 10 as value
    union all 
    select '101' as id, '2022-12-03' as idate, 20 as value
    union all 
    select '101' as id, '2022-12-06' as idate, 40 as value
    union all  
    select '102' as id, '2022-12-02' as idate, 20 as value
    union all 
    select '102' as id, '2022-12-04' as idate, 30 as value
    union all 
    select '102' as id, '2022-12-07' as idate, 60 as value
)
select id,'2022-12-07' as end_time,'2022-12-01' as start_time from tmp group by id

在这里插入图片描述
2、再将步骤1中的2022-12-07与2022-12-01做差+1,作为repeat()函数的参数,重复多少次。得到重复字符串后,利用posexplode()列转行,将重复字符串拆分出有索引(ind)的多行,再将start_time与索引做日期计算,得到最终主表数据结果,见sql:


with tmp as (
    select '101' as id, '2022-12-01' as idate, 10 as value
    union all 
    select '101' as id, '2022-12-03' as idate, 20 as value
    union all 
    select '101' as id, '2022-12-06' as idate, 40 as value
    union all  
    select '102' as id, '2022-12-02' as idate, 20 as value
    union all 
    select '102' as id, '2022-12-04' as idate, 30 as value
    union all 
    select '102' as id, '2022-12-07' as idate, 60 as value
),
during_date as (
    select id,'2022-12-07' as end_time,'2022-12-01' as start_time from tmp group by id
)
select 
    a.id,a.start_time,b.ind
    ,substr(dateadd(to_date(a.start_time,'yyyy-mm-dd'),b.ind,'dd'),1,10) as final_date
from during_date a
lateral view posexplode( split(repeat('a,',datediff(end_time,start_time)+1),',') ) b as ind,val

结果如下:
在这里插入图片描述
3、步骤2中的数据,咱们只需要id和final_date列即可,作为主表数据 去left join基础数据,见sql:

with tmp as (
    select '101' as id, '2022-12-01' as idate, 10 as value
    union all 
    select '101' as id, '2022-12-03' as idate, 20 as value
    union all 
    select '101' as id, '2022-12-06' as idate, 40 as value
    union all  
    select '102' as id, '2022-12-02' as idate, 20 as value
    union all 
    select '102' as id, '2022-12-04' as idate, 30 as value
    union all 
    select '102' as id, '2022-12-07' as idate, 60 as value
),
during_date as (
    select id,'2022-12-07' as end_time,'2022-12-01' as start_time from tmp group by id
)
select 
    a.id,
    a.final_date,
    nvl(b.value,0) as value,
    sum(nvl(b.value,0)) over(partition by a.id order by a.final_date ) as final_value
from
    (select 
        a.id
        ,substr(dateadd(to_date(a.start_time,'yyyy-mm-dd'),b.ind,'dd'),1,10) as final_date
    from during_date a
    lateral view posexplode( split(repeat('a,',datediff(end_time,start_time)+1),',') ) b as ind,val
    ) a 
left join 
    tmp b 
on a.id = b.id and a.final_date = b.idate

结果如下:
在这里插入图片描述

最后

喜欢的点赞、关注、收藏吧~ 你的支持是最大的创作动力~~

HiveSQL题——用户连续登陆 阅读详情

相关推荐

HiveSQL实现日期缺失值,取最近一条记录补充

hiveSQL 将缺失日期补全,取最近上一日期的数值填充。

weixin_42385450的博客 3019

hiveSQL面试题20__时间序列--补全数据

目录0- 描述1- 问题一 0- 描述 描述:时间序列–补全数据 表名:t20 表字段及内容: date_id a b c 2014 AB 12 bc 2015 23 2016 d 2017 BC 1- 问题一 描述:如何使用最新数据补全表格 输出结果如下所示: date_id a b c 2014 AB 12 bc 2015 AB 23 bc 2016 AB

CLKTOY的博客 747

Sql进阶技巧:稀疏字段累计求和问题

稀疏字段累计求和问题,如何构造连续日期?

石榴姐yyds 2891

sql:生成连续时间(pgsql、hivesql)

pgsql 代码 select generate_series(now()-interval '1 days','2022-01-14','1 days'); 结果 hive 代码 with subquery as ( select split(space(datediff('2020-11-04','2020-11-01')), ' ') as x ) select date_add('2020-11-01', idx) as new_date from sub

weixin_43815750的博客 1002

Hive获取连续时间用 posexplode

获取连续的日期 假如我们需要获取2020-07-15至2020-07-21间所有的日期,可以像这样写 SELECT pos, date_add( start_date, pos ) dd FROM ( SELECT '2020-07-15' AS start_date, '2020-07-21' AS end_date ) temp lateral VIEW posexplode ( split ( space( datediff( end_date, start_date ) ), '

someInNeed的博客 3441

SQL基础:HiveSql常用的时间维度计算方法(月初、月末、周几)及时间维度 表生成

本文总结了关于Hive中时间函数的使用及时间维度表的生成方法,时间维度表及时间函数在数据开发中经常被用到,这块需要切实掌 目前格式支持:MM(月)YYYY(年)Hive3.0后支持Q表示季度 select trunc(current_date,'MM') --月初 select trunc(current_date,'YY') --年初 last_day() last_day(string date) — 返回该月最后一天的日期 select last_day(current_d.

石榴姐yyds 2万+

从学生成绩到用户行为:HiveSQL数据分析7大经典案例详解

本文详细解析了HiveSQL数据分析的7大经典案例,涵盖学生成绩统计、用户行为分析、电商场景等多个业务领域。通过实战代码演示,帮助读者掌握HiveSQL的核心技巧,特别适合准备面试的数据从业者提升实战能力。案例包括多维度筛选、连续登录识别、商品TopN排名等典型场景,展现HiveSQL处理海量数据的强大能力。

weixin_29251567的博客 216

SQL高级技巧:如何精准计算非连续日期累计值【闪电快车面试题】

本文给出了一种非连续日期准确求解累计值的通用方法。通过本文可以学习到: (1)连续日期的构造方法 (2)非连续日期准确求解累计值的方法

石榴姐yyds 3099

hive中获取连续的日期或者月份

获取连续的日期 假如我们需要获取2020-07-15至2020-07-21间所有的日期,可以像这样写 SELECT pos, date_add( start_date, pos ) dd FROM ( SELECT '2020-07-15' AS start_date, '2020-07-21' AS end_date ) temp lateral VIEW posexplode ( split ( space( datediff( end_date, start_date ) ), '' )

zjh19961213的博客 7684

超声信号处理——硬件电路中的混频应用

本文介绍了超声多普勒系统的信号处理原理。系统通过波形振荡器产生高频信号,经换能器转换为超声波发射,接收反射信号后与原始信号混频,利用三角函数的积化和差特性产生包含多普勒频移的低频差频信号和高频和频信号。通过低通滤波器滤除高频成分后,即可获得纯净的多普勒信号。这种处理方法将MHz级高频信号转换为可听低频信号,降低了系统复杂度与成本。数学推导表明,差频信号频率正好

fcxgfdjy的博客 1125

HBM PHY 架构解析:从主控模块到DWORD设计的深度探索

本文深入解析了HBM PHY的完整架构,从作为系统大脑的PUB主控模块出发,详细阐述了其CSR控制、初始化与训练引擎的核心功能。进而剖析了负责命令传输的AWORDX2模块与作为数据高速公路的DWORD模块的设计奥秘,包括其双向数据流、精密时钟对齐与延迟线校准机制。最后,探讨了顶层MASTER PHY的整合作用及从DFI到DRAM的完整数据通路,为理解这一高性能内存接口的关键物理层设计提供了全面视角。

weixin_29306011的博客 298

hiveSQL面试题18__时间序列--构造连续日期

目录0- 描述1- 问题一 0- 描述 描述:时间序列–构造连续日期 表名:t18 表字段及内容: a b c 101 2018-01-01 10 101 2018-01-03 20 101 2018-01-06 40 102 2018-01-02 20 102 2018-01-04 30 102 2018-01-07 60 1- 问题

CLKTOY的博客 410

多机器人编队人工势场法协同避障算法原理及实现

ROS及SLAM进阶教程(十一)多机器人编队人工势场法协同避障算法原理及实现避障算法原理避障算法仿真 多机器人协同编队需要将理论和实践紧密地结合起来,其应用包括编队队形生成、保持、变换和路径规划与避障等等都是基于图论的理论基础完成的。 详细请参考ROS及SLAM进阶教程(八)多机器人协同编队算法原理及实现 自主避障功能是机器人编队在各种环境中保持自身安全的重要功能,在编队的基础上加入避障的功能,机器人可扫描到一定范围内的障碍物(包括其他机器人),在即将与之发生冲突时提前规避冲突,以保证自身的安全性,同时

Redglass的博客 1万+

hive sql练习3:连续日期问题-posexplod函数使用

hive sql练习3:连续日期问题-posexplod函数使用

qq_45124566的博客 624

STM32CubeMX | HAL库实战:ADC多通道采集模式深度解析(轮询、DMA、TIM触发)

本文深度解析了基于STM32CubeMX与HAL库实现ADC多通道数据采集的三种核心模式:轮询、DMA以及TIM触发DMA。文章通过对比分析,详细阐述了各模式的配置要点、适用场景与潜在陷阱,旨在帮助开发者根据实时性、CPU占用及采样精度等需求,选择并实现高效稳定的多通道数据采集方案。

postgres8guard的博客 973

可微编程:当深度学习遇见物理定律,重塑科学计算新范式

本文探讨了可微编程如何将深度学习与物理定律相结合,重塑科学计算范式。通过自动微分技术,它将物理方程融入可微计算图,使模型在数据驱动的同时遵守物理约束。文章以神经常微分方程为例,阐述了其在连续系统建模、科学模拟器优化等领域的应用,并分析了当前面临的挑战与最佳实践路径。

weixin_29056145的博客 136

HiveSQL如何生成连续日期剖析

情景假设:有一结果表,表中有start_dt和end_dt两个字段,,想要根据开始和结束时间生成连续日期的多条数据,应该怎么做?直接上结果sql。(为了便于演示和测试这里通过模拟一个结果表数据)如果对涉及到的函数和语法不是特别了解,直接看到上述结果可能有点懵,接下来换个形式理解下,即如下sql如上sql结构比较简单,即t1表和t2表进行笛卡尔集,t1是原始表只有1行数据,但是结果是6行数据,因此关键点是t2的结果。

初阳 1667

栅格数据做相关性分析前的预处理(批量定义投影、栅格投影、重采样)

最近在做两个栅格数据的相关性分析,这个最主要的是两个栅格数的行列号是一致的(也就是说坐标系、分辨率一致),那么标准的栅格数据的分辨率是以米的形式存在的,但是有一些栅格数据是以度为单位存在的,所以在做分析前,一定要先把其转换为米,否则在重采样的时候就会出错。(相关文章——栅格数据以度为单位变为以米为单位的简单做法)当然,在变其分辨率的单位前,一定要将其坐标系转换为一致。所以就有以下的步骤(如何将两个栅格数据的坐标系、分辨率的单位和分辨率一致) 1.首先,要看两个栅格数据的坐标系是否一致,如果不一致,要先统一到

qq_41828818的博客 8351

HiveSQL找出连续日期及连续的天数

参考:https://www.cnblogs.com/Joetao/p/3842242.html 参考关键代码: select 本期起始日期=min(rq),本期终止日期=max(rq), 持续天数=max(id1)-min(id1)+1, 距上一期天数=case a.id1-a.id2 when -1 then 0 else max(datediff(d,rq2...

卡卡大叔 8710

37、基于视觉的轻量级面部呼吸和心率测量技术

本文提出了一种基于视觉的轻量级面部呼吸和心率测量技术,通过远程光电容积脉搏波描记法(RPPG)和面部振动信息实现非接触式生理信号采集。创新性地结合框选和特征点选择多个感兴趣区域(ROI),并采用光流跟踪、主成分分析(PCA)和集成经验模态分解(EEMD)等方法优化信号处理流程。实验结果表明,该方法在测量精度和稳定性方面优于传统方法,并可在树莓派4等低功耗平台上实现实时运行,具有广泛的应用前景。

r7s8t的博客 307

NSX-T 4.1部署搭建

NSX-T4.1 部署搭建

weixin_58250887的博客 564

hivesql 根据开始日期和结束日期生产连续日期

hive sql 生成连续日期

wertd_fgegg的博客 1244

SAP B1中文培訓教材_SDK

公司内部培训资料,对开发工具 ,DIAPI, UIAPI,Add-ON有详细解说

hiveSql 实现计算用户的最大连续登录天数 & 创建断连后的连续序号

这里写目录标题1.需求一:1.1.已有数据1.2.期望结果1.3.分析1.4.实现2.需求二2.1.已有数据2.2.期望结果2.3.分析2.4.实现方式一2.5.实现方式二 1.需求一: 计算每个用户在一段时间内的最大连续登录天数 1.1.已有数据 username logintime A 2021-10-01 A 2021-10-02 A 2021-10-03 A 2021-10-05 A 2021-10-06 A 2021-10-09 B 2021-10-01

i_mycode的博客 3912

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

HiveSQL基础

文章目录HiveSQL一、HiveSQL与传统SQL的区别二、MapReduce的工作流程基础语法一、基础select(与SQL一样)+分区二、group by三、order by四、执行顺序常用函数一、如何把时间戳zhuan HiveSQL 一、HiveSQL与传统SQL的区别 存储位置上:hivesql存储在hdfs上,传统SQL存储在块设备或者本地文件中 数据格式:hiveSQL的数据格式...

liuluTL的博客 1616

ET2012新版免狗免安装绿色软件

ET2012新版免狗免安祖昂绿色软件。,新界面,新视觉,

HiveSQL——连续增长问题

HiveSQL——连续增长问题

爱吃辣条的博客 3042

HiveSQL找出连续日期及连续的天数(方法二)

经常遇到统计连续行为日期及连续天数的问题,之前的解决方案相对复杂,本次给出最简单的解决方案。 1、测试数据: use xxx; drop table test_serialdate; create table if not exists test_serialdate ( user_id string comment '用户ID', sign_date string comment '签到日期' ) stored as orcfile ; insert into table test

卡卡大叔 3658
上一篇: hiveSql 重分组聚合问题
下一篇: hiveSql 京东面试题-有效值问题
@nanami
博客等级 码龄9年 4247粉丝 53原创
评论 2
成就一亿技术人!
拼手气红包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、付费专栏及课程。

余额充值