HiveSQL题——窗口函数(lag/lead)

目录

一、窗口函数的知识点

1.1 窗户函数的定义

1.2 窗户函数的语法

1.3 窗口函数分类

1.4 前后函数:lag/lead

二、实际案例

2.1 股票的波峰波谷

0 问题描述

1 数据准备

2 数据分析

3 小结

2.2 前后列转换（面试题）

0 问题描述

1 数据准备

2 数据分析

3 小结

一、窗口函数的知识点

1.1 窗户函数的定义

         窗口函数可以拆分为【窗口+函数】。窗口函数官网指路：LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationicon-default.png?t=N7T8https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

  • 窗口：定义函数计算范围（窗口函数：针对分组后的数据，从逻辑角度指定计算的范围，并没有从物理上真正的切分，只有group by 是物理分组，真正意义上的分组）
  • 函数：定义函数计算逻辑
  •  窗口函数的位置：跟sql里面聚合函数的位置一样，from -> join -> on -> where -> group by->select 后面的普通字段，窗口函数 -> having -> order by  -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
  • sql 执行顺序：from -> join -> on -> where -> group by->select 后面的普通字段，聚合函数-> having -> order by -> limit

1.2 窗户函数的语法

       <窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

  • window_name：给窗口指定一个别名。
  • over：用来指定函数执行的窗口范围，如果后面括号中什么都不写，即over() ，意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算。
  • 符号[] 代表：可选项；  | ： 代表二选一
  •  partition by 子句： 窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。分组间互相独立。
  • order by 子句  :每个partition内部按照哪些字段进行排序，如果没有partition ，那就直接按照最大的窗口排序，且默认是按照升序（asc）排列。
  • 窗口子句：显示声明范围（不写窗口子句的话，会有默认值）。常用的窗口子句如下：
    rows between unbounded preceding and  unbounded following; -- 上无边界到下无边界（一般用于求 总和）rows between unbounded preceding and current row;  --上无边界到当前记录(累计值)rows between 1 preceding and current row; --从上一行到当前行rows between 1 preceding and 1 following; --从上一行到下一行rows between current row and 1 following; --从当前行到下一行

     ps: over()里面有order by子句，但没有窗口子句时 ，即： <窗口函数> over ( partition by 字段... order by 字段... )，此时窗口子句是有默认值的->  rows between unbounded preceding and current row (上无边界到当前行)。

      此时窗口函数语法：<窗口函数> over ( partition by 字段... order by 字段... ) 等价于

     <窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
     需要注意有个特殊情况：当order by 后面跟的某个字段是有重复行的时候， <窗口函数> over ( partition by 字段... order by 字段... )  不写窗口子句的情况下，窗口子句的默认值是：range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。

    因此，遇到order by 后面跟的某个字段出现重复行，且需要计算【上无边界到当前行】，那就需要手动指定 窗口子句 rows between unbounded preceding and current row ，偷懒省略窗口子句会出问题~

      ps: 窗口函数的执行顺序是在where之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。

     【例如】求出登录记录出现间断的用户Id

selectid
from (selectid,login_date,lead(login_date, 1, '9999-12-31')over (partition by id order by login_date) next_login_date--窗口函数 lead(向后取n行)--lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据，如果没有就为nullfrom (--用户在同一天可能登录多次，需要去重selectid,date_format(`date`, 'yyyy-MM-dd') as login_datefrom user_loggroup by id, date_format(`date`, 'yyyy-MM-dd')) tmp1) tmp2
where  datediff(next_login_date, login_date) >=2
group by id;

  • 窗口函数本身也有执行顺序： <窗口函数>over ( partition by  order by   窗口子句 )的执行顺序：over -> partition by -> order by -> 窗口子句 -> 函数

1.3 窗口函数分类

      哪些函数可以是窗口函数呢？(放在over关键字前面的)

  • 聚合函数

sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over  (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);

    ps : 高级聚合函数：

         collect_list 收集并形成list集合，结果不去重;

         collect_set 收集并形成set集合，结果去重; 

      举例：

--每个月的入职人数以及姓名select 
month(replace(hiredate,'/','-')),count(*) as cnt,collect_list(name) as name_list
from employee
group by month(replace(hiredate,'/','-'));/*
输出结果
month  cn  name_list
4	    2	["宋青书","周芷若"]
6	    1	["黄蓉"]
7	    1	["郭靖"]
8	    2	["张无忌","杨过"]
9	    2	["赵敏","小龙女"]*/

  • 排序函数

      rank() 、row_number() 、dense_rank() 函数不支持自定义窗口子句。

--  顺序排序——1、2、3
row_number() over(partition by .. order by .. )--  并列排序，跳过重复序号——1、1、3（横向加）
rank() over(partition by .. order by .. )-- 并列排序，不跳过重复序号——1、1、2（纵向加）
dense_rank()  over(partition by .. order by .. )

  • 前后函数 

-- 取得column列的前n行，如果存在则返回，如果不存在，返回默认值default
lag(column,n,default) over(partition by order by) as lag_test
-- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值default
lead(column,n,default) over(partition by order by) as lead_test

  • 头尾函数

---当前窗口column列的第一个数值，如果有null值，则跳过
first_value(column,true) over (partition by ..order by.. 窗口子句) ---当前窗口column列的第一个数值，如果有null值，不跳过
first_value(column,false) over (partition by ..order by.. 窗口子句)--- 当前窗口column列的最后一个数值，如果有null值，则跳过
last_value(column,true) over (partition by ..order by.. 窗口子句) --- 当前窗口column列的最后一个数值，如果有null值,不跳过
last_value(column,false) over (partition by ..order by.. 窗口子句)

1.4 前后函数:lag/lead

       lead和lag函数，这两个函数一般用于计算差值，上面已介绍其语法。lag和lead函数不支持自定义窗口子句。

-- 取得column列的前n行，如果存在则返回，如果不存在，返回默认值default
lag(column,n,default) over(partition by order by) as lag_test
-- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值default
lead(column,n,default) over(partition by order by) as lead_test

二、实际案例

2.1 股票的波峰波谷

0 问题描述

    求股票的波峰Crest 和 波谷trough

波峰：当天的股票价格大于前一天和后一天
波谷：当天的股票价格小于前一天和后一天

1 数据准备

create table if not exists table2
(id     int comment '股票id',dt     string comment '日期',price  int comment '价格'
)comment '股票价格波动信息';insert overwrite table table2 values
(1,'2019-01-01',10001),
(1,'2019-01-03',1001),
(1,'2019-01-02',1001),
(1,'2019-01-04',1000),
(1,'2019-01-05',1002),
(1,'2019-01-06',1003),
(1,'2019-01-07',1004),
(1,'2019-01-08',998),
(1,'2019-01-09',997),
(2,'2019-01-01',1002),
(2,'2019-01-02',1003),
(2,'2019-01-03',1004),
(2,'2019-01-04',998),
(2,'2019-01-05',999),
(2,'2019-01-06',997),
(2,'2019-01-07',996);

2 数据分析

  此题容易理解，利用lag()和lead()函数便可以解决。

selectid,dt,price,casewhen price > lag_price and price > lead_price then 'crest'when price < lag_price and price < lead_price then 'trough'end as price_type
from (selectid,dt,price,lag(price, 1) over (partition by id order by dt)  as lag_price,lead(price, 1) over (partition by id order by dt) as lead_pricefrom table2) tmp1;

3 小结

    lead和lag函数一般用于计算当前行与上一行，或者当前行与下一行之间的差值。在用户间断登陆问题中也遇到过此函数。指路：HiveSQL题——用户连续登陆-CSDN博客文章浏览阅读220次，点赞4次，收藏3次。HiveSQL题——用户连续登陆https://blog.csdn.net/SHWAITME/article/details/135900251?spm=1001.2014.3001.5501

2.2 前后列转换（面试题）

0 问题描述

    表temp包含A,B 两列，使用SQL对该B列进行处理，形成C列。按照A列顺序，B列值不变，C列累计技术 B列值变化，则C列重新开始计数，如图所示

   

1 数据准备

with table4 as (select 2010 as A,1 as Bunion allselect 2011 as A,1 as Bunion allselect 2012 as A,1 as Bunion allselect 2013 as A,0 as Bunion allselect 2014 as A,0 as Bunion allselect 2015 as A,1 as Bunion allselect 2016 as A,1 as Bunion allselect 2017 as A,1 as Bunion allselect 2018 as A,0 as Bunion allselect 2019 as A,0 as B
)

2 数据分析

with table4 as (select 2010 as A,1 as Bunion allselect 2011 as A,1 as Bunion allselect 2012 as A,1 as Bunion allselect 2013 as A,0 as Bunion allselect 2014 as A,0 as Bunion allselect 2015 as A,1 as Bunion allselect 2016 as A,1 as Bunion allselect 2017 as A,1 as Bunion allselect 2018 as A,0 as Bunion allselect 2019 as A,0 as B
)selectA,B,row_number() over (partition by T order by A) as C
from (selectA,B,--over (order by A) 本质是 ：over(order by rows between unbounded preceding and current row )--省略的是：上无边界到当前行sum(change) over (order by A) Tfrom (selectA,B,-- 向上取一行，取不到的记为0lag(B, 1, 0) over (order by A) as Lag,casewhen B <> lag(B, 1, 0) over (order by A) then 1else 0end   as changefrom table4) tmp1) tmp2;

3 小结

    lead /lag函数常用于差值计算。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/247997.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

JavaWeb中的Filter（过滤器）和 Listener（监听器）

JavaWeb中的Filter（过滤器）和 Listener（监听器）

提示&#xff1a;这两个东西听起来似乎很难&#xff0c;实际上是非常简单的&#xff0c;按照要求写就行了&#xff0c;一定不要被新名词给吓到了。 JavaWeb中的Filter&#xff08;过滤器&#xff09; 一、Filter&#xff08;过滤器&#xff09;1.如何编写 Filter2.Filter 中的细…
阅读更多...
1002. HarmonyOS 开发问题：鸿蒙 OS 技术特性是什么?

1002. HarmonyOS 开发问题：鸿蒙 OS 技术特性是什么?

1002. HarmonyOS 开发问题&#xff1a;鸿蒙 OS 技术特性是什么? 硬件互助&#xff0c;资源共享 分布式软总线 分布式软总线是多种终端设备的统一基座&#xff0c;为设备之间的互联互通提供了统一的分布式通信能力&#xff0c;能够快速发现并连接设备&#xff0c;高效地分发…
阅读更多...
方案：将vue项目放在SpringMVC中，并用tomcat访问

方案：将vue项目放在SpringMVC中，并用tomcat访问

需要先将项目生成一次war包才能访问项目的webapp文件夹下的资源&#xff0c;否则tomcat的webapp文件夹下面不会生成对应资源文件夹就无法访问。 问题&#xff1a;目录如下&#xff1a; 今天我测试了一下将vue打包后&#xff0c;放入webapp下面访问&#xff0c;却发现vue项目无…
阅读更多...
关于Spring Boot和MyBatis常见的十道面试题

关于Spring Boot和MyBatis常见的十道面试题

拦截器和过滤器有什么区别&#xff1f; 拦截器&#xff08;Interceptor&#xff09;和过滤器&#xff08;Filter&#xff09;都是用于在请求道道目标资源的之前或之后进行处理的组件。主要区别有以下几点&#xff1a; 依赖对象不同&#xff1a;过滤器是来时Servlet&#xff0…
阅读更多...
备战蓝桥杯--数据结构及STL应用（基础）

备战蓝桥杯--数据结构及STL应用（基础）

今天轻松一点&#xff0c;讲一讲stl的基本操作吧&#xff01; 首先&#xff0c;让我们一起创建一个vector容器吧&#xff01; #include<bits/stdc.h> using namespace std; struct cocoack{ int coco,ck; } void solve(){vector<cocoack> x;for(int i0;i<5;i){…
阅读更多...
私有化部署pdf工具箱

私有化部署pdf工具箱

功能简介 用于合并/拆分/旋转/移动PDF及其页面的完全交互式GUI。 将多个 PDF 合并到一个生成的文件中。 将 PDF 拆分为多个文件&#xff0c;并按指定的页码或将所有页面提取为单个文件。 将 PDF 页面重新组织为不同的顺序。 以 90 度为增量旋转 PDF。 删除页面。 多页布局…
阅读更多...
《HTML 简易速速上手小册》第7章：HTML 多媒体与嵌入内容（2024 最新版）

《HTML 简易速速上手小册》第7章：HTML 多媒体与嵌入内容（2024 最新版）

文章目录 7.1 在HTML中嵌入视频和音频7.1.1 基础知识7.1.2 案例 1&#xff1a;嵌入视频文件7.1.3 案例 2&#xff1a;嵌入音频文件7.1.4 案例 3&#xff1a;创建一个视频和音频混合的播放列表 7.2 使用 <iframe> 嵌入外部内容7.2.1 基础知识7.2.2 案例 1&#xff1a;嵌入…
阅读更多...
超越传统—Clean架构打造现代Android架构指南

超越传统—Clean架构打造现代Android架构指南

超越传统—Clean架构打造现代Android架构指南 1. 引言 在过去几年里&#xff0c;Android应用开发经历了巨大的变革和发展。随着移动设备的普及和用户对应用的期望不断提高&#xff0c;开发人员面临着更多的挑战和需求。传统的Android架构在应对这些挑战和需求时显得有些力不从…
阅读更多...
除了Adobe之外，还有什么方法可以将Excel转为PDF？

除了Adobe之外，还有什么方法可以将Excel转为PDF？

前言 Java是一种广泛使用的编程语言&#xff0c;它在企业级应用开发中发挥着重要作用。而在实际的开发过程中&#xff0c;我们常常需要处理各种数据格式转换的需求。今天小编为大家介绍下如何使用葡萄城公司的的Java API 组件GrapeCity Documents for Excel&#xff08;以下简…
阅读更多...
Docker 安装篇（CentOS）

Docker 安装篇（CentOS）

Docker社区版 Docker从1.13版本之后采用时间线的方式作为版本号&#xff0c;分为社区版CE和企业版EE。 社区版是免费提供给个人开发者和小型团体使用的&#xff0c;企业版会提供额外的收费服务&#xff0c;比如经过官方测试认证过的基础设施、容器、插件等。 1、Docker 要求 C…
阅读更多...
研发日记，Matlab/Simulink避坑指南(五)——CAN解包 DLC Bug

研发日记，Matlab/Simulink避坑指南(五)——CAN解包 DLC Bug

文章目录 前言 背景介绍 问题描述 分析排查 解决方案 总结 前言 见《研发日记&#xff0c;Matlab/Simulink避坑指南&#xff08;一&#xff09;——Data Store Memory模块执行时序Bug》 见《研发日记&#xff0c;Matlab/Simulink避坑指南(二)——非对称数据溢出Bug》 见《…
阅读更多...
Activity创建与跳转

Activity创建与跳转

快捷&#xff0c;一下创建三个 跳转
阅读更多...
《HTML 简易速速上手小册》第1章：HTML 入门（2024 最新版）

《HTML 简易速速上手小册》第1章：HTML 入门（2024 最新版）

文章目录 1.1 HTML 简介与历史&#xff08;&#x1f609;&#x1f310;&#x1f47d;踏上神奇的网页编程之旅&#xff09;1.1.1 从过去到现在的华丽蜕变1.1.2 市场需求 —— HTML的黄金时代1.1.3 企业中的实际应用 —— 不只是个网页1.1.4 职业前景 —— 未来属于你 1.2 基本 H…
阅读更多...
文献速递：人工智能医学影像分割--- 深度学习分割骨盆骨骼：大规模CT数据集和基线模型

文献速递：人工智能医学影像分割--- 深度学习分割骨盆骨骼：大规模CT数据集和基线模型

文献速递&#xff1a;人工智能医学影像分割— 深度学习分割骨盆骨骼&#xff1a;大规模CT数据集和基线模型 我们为大家带来人工智能技术在医学影像分割上的应用文献。 人工智能在医学影像分析中发挥着至关重要的作用&#xff0c;尤其体现在图像分割技术上。这项技术的目的是准…
阅读更多...
Django配置websocket时的错误解决

Django配置websocket时的错误解决

基于移动群智感知的网络图谱构建系统需要手机app不断上传数据到服务器并把数据推到前端标记在百度地图上&#xff0c;由于众多手机向同一服务器发送数据&#xff0c;如果使用长轮询&#xff0c;则实时性差、延迟高且服务器的负载过大&#xff0c;而使用websocket则有更好的性能…
阅读更多...
iOS 17.4 苹果公司正在加倍投入人工智能

iOS 17.4 苹果公司正在加倍投入人工智能

每周跟踪AI热点新闻动向和震撼发展 想要探索生成式人工智能的前沿进展吗&#xff1f;订阅我们的简报&#xff0c;深入解析最新的技术突破、实际应用案例和未来的趋势。与全球数同行一同&#xff0c;从行业内部的深度分析和实用指南中受益。不要错过这个机会&#xff0c;成为AI领…
阅读更多...
组件冲突、data函数、组件通信

组件冲突、data函数、组件通信

文章目录 1.组件的三大组成部分 - 注意点说明2.组件的样式冲突&#xff08;用 scoped 解决&#xff09;3.data是一个函数4.组件通信1.什么是组件通信&#xff1f;2.不同的组件关系 和 组件通信方案分类 5.prop详解prop 校验①类型校验②完整写法&#xff08;类型&#xff0c;非…
阅读更多...
Windows系统安装OpenSSH+VS Code结合内网穿透实现远程开发

Windows系统安装OpenSSH+VS Code结合内网穿透实现远程开发

文章目录 前言1、安装OpenSSH2、vscode配置ssh3. 局域网测试连接远程服务器4. 公网远程连接4.1 ubuntu安装cpolar内网穿透4.2 创建隧道映射4.3 测试公网远程连接 5. 配置固定TCP端口地址5.1 保留一个固定TCP端口地址5.2 配置固定TCP端口地址5.3 测试固定公网地址远程 前言 远程…
阅读更多...
计算机毕业设计 | SpringBoot 求职招聘管理系统(附源码)

计算机毕业设计 | SpringBoot 求职招聘管理系统(附源码)

1&#xff0c;绪论 1.1 开发背景 高学历人群是网络求职者的主体&#xff0c;且结构趋向固定。而在疫情肆虐的今日&#xff0c;线上招聘成了越来越多企业和个人选择的方式。在疫情期间线下招聘转为线上招聘&#xff0c;是疫情防控的需要。不能否定的是新的招聘模式的出现一定会…
阅读更多...
IS-IS：04 DIS

IS-IS：04 DIS

IS-IS 协议只支持两种网络类型&#xff0c;即广播网络和点到点网络。与 OSPF 协议相同&#xff0c; IS-IS 协议在广播网络中会将网络视为一个伪节点 &#xff08; Pesudonde&#xff0c;简称 PSN&#xff09;&#xff0c;并选举出一台DIS &#xff08;Designated IS&#xff09…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023