SQL开窗函数之前后函数（LEAD、LAG）

羊羊猪 2024-05-29 原文

开窗函数

当我们需要进行一些比较复杂的子查询时，聚合函数就会非常的麻烦，因此可以使用开窗函数进行分组再运用函数查询。窗口函数既可以显示聚集前的数据，也可以显示聚集后的数据，可以在同一行中返回基础行的列值和聚合后的结果列

常见运用场景： 对班里同学成绩进行排序

常见的窗口函数

开窗函数基本形式

func_name(<parameter>) 
OVER(
[PARTITION BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC]
[rows between ?? And ??]
)

具体字段的解释看我的上一篇：SQL开窗函数之基本用法和聚合函数

前后函数

LEAD函数和 LAG函数主要用于查询当前字段的上一个值或下一个值，若向上取值或向下取值没有数据的时候显示为NULL

LEAD: 向后偏移
LAG: 向前偏移

LAG(<expression>,offset,default_value) 
OVER(
     PARTITION BY expr,
     ORDER BY expr [ASC|DESC]
	)

字段解释

Expression: 需要被偏移的字段
Offset: 偏移的量
default_value: 超出记录窗口时的默认值（可以设置为0，默认为null）

应用1：前后日期温度比较
weather表

获取前一天的温度和后一天的温度

select *,
lead(temperature, 1) over(order by recordDate) as lead_temp,
lag(temperature, 1) over(order by recordDate) as lag_temp
from weather

获取与当天比前一天温度更高的日期

with a as (       
select *,
lead(temperature, 1) over(order by recordDate) as lead_temp,
lag(temperature, 1) over(order by recordDate) as lag_temp
from weather
)
select * from a 
where lag_temp < temperature

应用2：求出连续登录5天的用户
LeetCode：1454.活跃用户

用户登录表

题解：

用户可能同一天登录了多次，而我们只需要一个登陆日期，因此需要 group by user_id, login_time 去重
用 lead() over() 窗口函数查找往下第4个登录日期
用datediff查找往下第4个登陆日期是否与当前日期相差4天，即连续5天

用lead找出天数差

select user_id, login_time, 
lead(login_time,4) over(partition by user_id order by login_time) as '5次后登录的时间', 
datediff(lead(login_time,4) over(partition by user_id order by login_time), login_time) as '天数差'
from user_login
group by user_id, date(login_time);

然后从上面找到连续登录了5天的用户，即 “天数差” 为4的用户

-- 完整代码
with a as(
select user_id, login_time, 
lead(login_time,4) over(partition by user_id order by login_time) as '5次后登录的时间', 
datediff(lead(login_time,4) over(partition by user_id order by login_time), login_time) as date_diff
from user_login
group by user_id, date(login_time)
)
select distinct user_id from a where date_diff = 4;

这里主要用到的两个例子，建表如下

-- weather表
drop table if exists weather;
create table weather(
    id int,
    recordDate date,
    temperature int
);
insert into weather
values (1,'2015-01-01',10),
       (2,'2015-01-02',25),
       (3,'2015-01-03',20),
       (4,'2015-01-04',30);

-- 用户登录表
drop table if exists user_login;
create table user_login
( 
user_id varchar(100), 
login_time datetime
); 

insert into user_login values 
(1,'2020-11-25 13:21:12'), 
(1,'2020-11-24 13:15:22'), 
(1,'2020-11-24 10:30:15'), 
(1,'2020-11-24 09:18:27'), 
(1,'2020-11-23 07:43:54'), 
(1,'2020-11-10 09:48:36'), 
(1,'2020-11-09 03:30:22'), 
(1,'2020-11-01 15:28:29'), 
(1,'2020-10-31 09:37:45'), 
(2,'2020-11-25 13:54:40'), 
(2,'2020-11-24 13:22:32'), 
(2,'2020-11-23 10:55:52'), 
(2,'2020-11-22 08:56:33'),
(2,'2020-11-22 06:30:09'), 
(2,'2020-11-21 08:33:15'), 
(2,'2020-11-20 05:38:18'), 
(2,'2020-11-19 09:21:42'), 
(2,'2020-11-02 00:19:38'), 
(2,'2020-11-01 09:03:11'), 
(2,'2020-10-31 07:44:55'), 
(2,'2020-10-30 08:56:33'), 
(2,'2020-10-29 09:30:28');

参考来源：
MySQL8中的开窗函数
 SQL练习题：连续登录5天的活跃用户

有关SQL开窗函数之前后函数（LEAD、LAG）的更多相关文章

ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2
我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re
ruby-on-rails - 在 ruby 中使用 gsub 函数替换单词 - 2
我正在尝试用ruby中的gsub函数替换字符串中的某些单词，但有时效果很好，在某些情况下会出现此错误？这种格式有什么问题吗NoMethodError(undefinedmethod`gsub!'fornil:NilClass):模型.rbclassTest"replacethisID1",WAY=>"replacethisID2andID3",DELTA=>"replacethisID4"}end另一个模型.rbclassCheck 最佳答案啊，我找到了!gsub!是一个非常奇怪的方法。首先，它替换了字符串，所以它实际上修改了
ruby - 在 Ruby 中有条件地定义函数 - 2
我有一些代码在几个不同的位置之一运行:作为具有调试输出的命令行工具，作为不接受任何输出的更大程序的一部分，以及在Rails环境中。有时我需要根据代码的位置对代码进行细微的更改，我意识到以下样式似乎可行:print"Testingnestedfunctionsdefined\n"CLI=trueifCLIdeftest_printprint"CommandLineVersion\n"endelsedeftest_printprint"ReleaseVersion\n"endendtest_print()这导致:TestingnestedfunctionsdefinedCommandLin
ruby - 在 Ruby 中按名称传递函数 - 2
如何在Ruby中按名称传递函数？(我使用Ruby才几个小时，所以我还在想办法。)nums=[1,2,3,4]#Thisworks,butismoreverbosethanI'dlikenums.eachdo|i|putsiend#InJS,Icouldjustdosomethinglike:#nums.forEach(console.log)#InF#,itwouldbesomethinglike:#List.iternums(printf"%A")#InRuby,IwishIcoulddosomethinglike:nums.eachputs在Ruby中能不能做到类似的简洁？我可以只
C51单片机——实现用独立按键控制LED亮灭（调用函数篇） - 2
说在前面这部分我本来是合为一篇来写的，因为目的是一样的，都是通过独立按键来控制LED闪灭本质上是起到开关的作用，即调用函数和中断函数。但是写一篇太累了，我还是决定分为两篇写，这篇是调用函数篇。在本篇中你主要看到这些东西！！！1.调用函数的方法（主要讲语法和格式）2.独立按键如何控制LED亮灭3.程序中的一些细节（软件消抖等）1.调用函数的方法思路还是比较清晰地，就是通过按下按键来控制LED闪灭，即每按下一次，LED取反一次。重要的是，把按键与LED联系在一起。我打算用K1来作为开关，看了一下开发板原理图，K1连接的是单片机的P31口，当按下K1时，P31是与GND相连的，也就是说，当我按下去时
Hive SQL 五大经典面试题 - 2
目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby-on-rails - 将字符串转换为 ruby-on-rails 中的函数 - 2
我需要一个通过输入字符串进行计算的方法，像这样function="(a/b)*100"a=25b=50function.something>>50有什么方法吗？最佳答案您可以使用instance_eval:function="(a/b)*100"a=25.0b=50instance_evalfunction#=>50.0请注意，使用eval本质上是不安全的，尤其是当您使用外部输入时，因为它可能包含注入(inject)的恶意代码。另请注意，a设置为25.0而不是25，因为如果它是整数a/b将导致0(整数)。
ruby - 在 ruby 中使用 .try 函数和 .map 函数 - 2
我需要从json记录中获取一些值并像下面这样提取curr_json_doc['title']['genre'].map{|s|s['name']}.join(',')但对于某些记录，curr_json_doc['title']['genre']可以为空。所以我想对map和join()使用try函数。我试过如下curr_json_doc['title']['genre'].try(:map,{|s|s['name']}).try(:join,(','))但是没用。最佳答案你没有正确传递block。block被传递给参数括号外的方法
ruby - 是否可以从也在该模块中的类内部调用模块函数 - 2
在这段Ruby代码中:ModuleMClassC当我尝试运行时出现“'M:Module'的未定义方法'helper'”错误c=M::C.new("world")c.work但直接从另一个类调用M::helper("world")工作正常。类不能调用在定义它们的同一模块中定义的模块函数吗？除了将类移出模块外，还有其他解决方法吗？最佳答案为了调用M::helper，你需要将它定义为defself.helper;结束为了进行比较，请查看以下修改后的代码段中的helper和helper2moduleMclassC

SQL开窗函数之前后函数（LEAD、LAG）

开窗函数

前后函数

有关SQL开窗函数之前后函数（LEAD、LAG）的更多相关文章

随机推荐