当前位置: 首页 > news >正文

SQL进阶:如何跳过多个NULL值取第一个非NULL值?

NULL

  • 一、问题描述
  • 二、ORACLE
    • <一>、last_value () over ()
    • <二>、lag () over()
    • <三>、相关子查询
  • 三、MYSQL
    • <一>、全局变量
    • <二>、coalesce() + lag() over()
    • <三>、相关子查询
    • <四>、 recursive
    • <五>、lag() over() + min() over() / max() over()

一、问题描述

  • 假如某张表中某个列有多个null值,如何跳过这多个null值取第一个不为空的值去填充,如下述表格所示
productsale_daeamount
12024/01/01100
12024/01/02200
12024/01/03
12024/01/04
22024/01/01200
22024/01/02
22024/01/03300
22024/01/04

某产品当天sale_date为空的销售价格取上一天的填充,最终想展示的如下述表格所示

productsale_daeamount
12024/01/01100
12024/01/02200
12024/01/03200
12024/01/04200
22024/01/01200
22024/01/02200
22024/01/03300
22024/01/04300

二、ORACLE

<一>、last_value () over ()

  • last_value()是sql中的一个窗口函数,用于获取窗口中的最后一个值,默认计算范围是第一行到当前行,有窗口是窗口内的第一行到当前行
select product,sale_date,amount,last_value(amount ignore nulls)over(partition by product order by sale_date) as last_amountfrom test
  • 如上述代码所示,按照产品分组,销售日期排序,若是有值则自己就是组内的最后一个值,把自己填充,若为NULL,则用IGNORE NULLS语法忽略NULL值,则用上一个值填充

<二>、lag () over()

  • lag()也是sql中的窗口,用于获取当前行的前几条记录,默认为上一条记录
select product,sale_date,amount,coalsece(amount,lag(amount ignore nulls)over(partition by product order by sale_date)) as last_amountfrom test

如上述代码所示,按照产品分组,销售日期排序,用了coalesce()函数,表示若是amount为空,则取lag(),默认是取上一条,上一条若也为空,则用IGNORE NULLS语法忽略NULL值,再取上一个填充,直到找到非NULL值

<三>、相关子查询

select product,sale_date,amount,(select  amountfrom (select amount from test t1 where t1.product = t.productand t1.sale_date <= t.sale_dateand amount is not null order by t1.sale_date desc) where rownum <= 1) as last_amountfrom test t
  • 用一个内部的子查询找到product相同,日期<=当前日期,且amount不为空的第一条

三、MYSQL

  • MYSQL中虽然也有last_value()和lag()函数,但是不支持IGNORE NULLS选项,所以不能够直接用这两个函数实现

<一>、全局变量

  • mysql中@表示全局变量,可以用全局变量递归实现
set @last_non_null := NULL;with tmp as (selet product,sale_date,amount,if(amount is null, @last_non_null := coalesce(@last_non_null,amount),@last_non_null := amount) as last_amountfrom testorder by product,sale_date
)select product,sale_date,amount,cast(last_amount as float) as last_amountfrom tmp
  • 如上述代码所示,order by
    product,sale_date是保证同个产品是按照sale_date排序的,也就是按照产品分组,销售日期排序
  • set @last_non_null : NULL表示设置全局变量last_non_null为NULL,后面的sql会用到这个变量
  • amount不为空的时候,last_non_null直接赋值amount返回;当amount为空的时候,last_non_null此时没有被赋值,直接返回,返回的就是上一个非空值

<二>、coalesce() + lag() over()

  • 上面说到Mysql的lag() over()是不支持ignore nulls选项的,那如何实现忽略null值呢?可以用一种笨方法,就是前面几条都写出来
select product,sale_date,amount,coalesce(amount,lag(amount,1)over(partition by product order by sale_date),lag(amount,2)over(partition by product order by sale_date),lag(amount,3)over(partition by product order by sale_date),...)from test
  • 上述方法可以适用于连续NULL值比较少的情况,如果连续NULL值比较多,还是不建议的

<三>、相关子查询

  • Mysql的相关子查询和Oracle逻辑是一样的,只不过取第一条的时候稍有不同,mysql是用limit
select product,sale_date,amount,(select amountfrom test t1where t1.product = t.productand t1.sale_date <= t.sale_date order by t1.sale_date desclimit 1) as last_amountfrom test

<四>、 recursive

<五>、lag() over() + min() over() / max() over()

  • 这个方法比较特殊化,不是所有场景都适用,主要针对一些有序的场景,比如上述例子针对日期amount都是有序的,可以将它当做一种思路
with tmp as (select product,sale_date,amount,lag(amount) over (partition by product order by sale_date) as last_amountfrom test
)select product,sale_date,amount,coalesce(amount,max(amount)over(partition by product order by sale_date)) as last_amountfrom tmp

相关文章:

SQL进阶:如何跳过多个NULL值取第一个非NULL值?

NULL 一、问题描述二、ORACLE<一>、last_value () over ()<二>、lag () over()<三>、相关子查询 三、MYSQL<一>、全局变量<二>、coalesce() lag() over()<三>、相关子查询<四>、 recursive<五>、lag() over() min() over() …...

laravel 5.5 增加宏指令 joinSub, 省去->toSql() 和 addBinding($bindings);

laravel 5.5 增加宏指令 joinSub, 省去->toSql() 和 addBinding($bindings); 1. 在laravel5使用join 子查询时 $sub_query DB::table(table1)->select([table1.id, cate_id])->join(table2, table1.id, , table2.id)->where(table1.cate_id, 2)->orderBy(tabl…...

远程控制软件:探究云计算和人工智能的融合

在数字化时代&#xff0c;远程控制工具已成为我们工作与生活的重要部分。用户能够通过网络远程操作和管理另一台计算机&#xff0c;极大地提升了工作效率和便捷性。随着人工智能&#xff08;AI&#xff09;和云计算技术的飞速发展&#xff0c;远程控制工具也迎来了新的发展机遇…...

网络协议之DNS

一、DNS概述 域名系统&#xff08;Domain Name System&#xff0c;缩写&#xff1a;DNS&#xff09;是互联网的一项服务。它作为将域名和IP地址相互映射的一个分布式数据库&#xff0c;能够使人更方便地访问互联网。DNS使用TCP和UDP端口53&#xff0c;通过递归查询请求的方式来…...

.net6 使用 FreeSpire.XLS 实现 excel 转 pdf - docker 部署

FreeSpire.XLS && Aspose.Cells包都可以实现。实现过程中发现如下问题&#xff1a; 本地测试通过&#xff0c; docker部署服务器后报错&#xff1a; The type initializer for Spire.Xls.Core.Spreadsheet.XlsPageSetupBase threw an exception. 由于缺少依赖&#xf…...

QML学习 —— 28、3种等待指示控件(附源码)

效果如下 说明 BusyIndicator应用于指示在加载内容或UI被阻止等待资源可用时的活动。BusyIndicator类似于一个不确定的ProgressBar。两者都可以用来指示背景活动。主要区别在于视觉效果,ProgressBar还可以显示具体的进度(当可以确定时)。由于视觉差异,繁忙指示器和不确定的…...

flutter 专题十一 Fair原理篇Fair逻辑动态化架构设计与实现

数据逻辑处理布局中的逻辑处理Flutter类型数据处理 一、数据逻辑处理 我们接触的每一个Flutter界面&#xff0c;大多由布局和逻辑相关的代码组成。如Flutter初始工程的Counting Demo的代码&#xff1a; class _MyHomePageState extends State<MyHomePage> {// 变量 in…...

利用开源图床的技巧与实践

随着互联网的普及&#xff0c;图片的使用变得越来越广泛。无论是个人博客、社交媒体还是企业网站&#xff0c;都离不开图片的呈现。而图床作为图片存储和管理的工具&#xff0c;可以帮助开发者和内容创作者高效地管理图片资源。本文将探讨如何利用开源图床&#xff0c;并提供相…...

C++数据结构与算法

C数据结构与算法 1.顺序表代码模版 C顺序表模版 #include <iostream> using namespace std; // 可以根据需要灵活变更类型 #define EleType intstruct SeqList {EleType* elements;int size;int capacity; };// Init a SeqList void InitList(SeqList* list, int capa…...

Paddle Inference部署推理(三)

三&#xff1a;Paddle Inference推理 导出模型 Paddle Inference支持使用飞桨静态图模型进行推理&#xff0c;您可以通过以下两种方式获取静态图模型&#xff1a; &#xff08;1&#xff09;飞桨框架导出推理模型 飞桨框架在训练模型过程中&#xff0c;会在本地存储最终训练…...

python(四)os模块、sys模块

一、os模块 os 模块提供了很多程序与操作系统直接交互的功能 名称描述示例os.getcwd()得到当前工作目录&#xff0c;即当前Python脚本工作的目录路径‘D:\python’os.listdir()返回指定目录下的所有文件和目录名>>> os.listdir()os.remove()函数用来删除一个文件>…...

Oracle 数据库 IDENTITY 列

IDENTITY列是Oracle数据库12c推出的新特性。之所以叫IDENTITY列&#xff0c;是由于其支持ANSI SQL 关键字 IDENTITY&#xff0c;其内部实现还是使用SEQUENCE。 不过推出这个新语法也是应该的&#xff0c;毕竟MyQL已经有 AUTO_INCREMENT列&#xff0c;而SQL Server也已经有IDENT…...

【前端】js vue 屏蔽BackSpace键删除键导致页面后退的方法

【前端】js vue 屏蔽BackSpace键删除键导致页面后退的方法 方法一&#xff1a;通过全局事件监听阻止 Backspace 导致页面后退 在 main.js 或组件的 mounted 中添加以下代码&#xff1a; //【前端】js vue 屏蔽BackSpace键删除键导致页面后退的方法 document.addEventListener…...

深入解密 K 均值聚类:从理论基础到 Python 实践

1. 引言 在机器学习领域&#xff0c;聚类是一种无监督学习的技术&#xff0c;用于将数据集分组成若干个类别&#xff0c;使得同组数据之间具有更高的相似性。这种技术在各个领域都有广泛的应用&#xff0c;比如客户细分、图像压缩和市场分析等。聚类的目标是使得同类样本之间的…...

ArcGIS应用指南:ArcGIS制作局部放大地图

在地理信息系统&#xff08;GIS&#xff09;中&#xff0c;制作详细且美观的地图是一项重要的技能。地图制作不仅仅是简单地将地理数据可视化&#xff0c;还需要考虑地图的可读性和美观性。局部放大图是一种常见的地图设计技巧&#xff0c;用于展示特定区域的详细信息&#xff…...

非root用户安装CUDA

1.使用nvidia-smi查看当前驱动支持的最高CUDA版本&#xff1a; 表示当前驱动最多支持cuda12.1 2.进入cuda安装界面&#xff0c;https://developer.nvidia.com/cuda-toolkit-archive&#xff0c;选择想要安装的版本&#xff0c;例如想要安装CUDA11.4&#xff1a; 如果需要查看ub…...

单点修改,区间求和或区间询问最值(线段树)

【题目描述】 给定一个长度为n的非负整数序列&#xff0c;接下来有m次操作&#xff0c;操作共有3种&#xff1a;一是修改序列中某个元素的大小&#xff0c;二是求某个区间的所有元素的和&#xff0c;三是询问某个区间的最大值。整数序列下标从1开始。n<10^5, m<10^5。 …...

线性代数空间理解

学习线性代数已经很久&#xff0c;但是在使用过程中仍然还是不明所以&#xff0c;比如不知道特征向量和特征值的含义、矩阵的相乘是什么意思、如何理解矩阵的秩……。随着遇到的次数越来越多&#xff0c;因此我决定需要对线性代数的本质做一次深刻的探讨了。 本次主要是参考了3…...

Spring Boot教程之五:在 IntelliJ IDEA 中运行第一个 Spring Boot 应用程序

在 IntelliJ IDEA 中运行第一个 Spring Boot 应用程序 IntelliJ IDEA 是一个用 Java 编写的集成开发环境 (IDE)。它用于开发计算机软件。此 IDE 由 Jetbrains 开发&#xff0c;提供 Apache 2 许可社区版和商业版。它是一种智能的上下文感知 IDE&#xff0c;可用于在各种应用程序…...

C51相关实验

C51相关实验 LED //功能&#xff1a;1.让开发板的LED全亮&#xff0c;2,点亮某一个LED,3.让LED3以5Hz的频率闪动#include "reg52.h"#define LED P2 sbit led1 LED^1;void main(void) {LED 0xff;//LED全灭led1 0;while(1)//保持应用程序不退出{} }LED 输出端是高…...

k8s从入门到放弃之Ingress七层负载

k8s从入门到放弃之Ingress七层负载 在Kubernetes&#xff08;简称K8s&#xff09;中&#xff0c;Ingress是一个API对象&#xff0c;它允许你定义如何从集群外部访问集群内部的服务。Ingress可以提供负载均衡、SSL终结和基于名称的虚拟主机等功能。通过Ingress&#xff0c;你可…...

OpenLayers 分屏对比(地图联动)

注&#xff1a;当前使用的是 ol 5.3.0 版本&#xff0c;天地图使用的key请到天地图官网申请&#xff0c;并替换为自己的key 地图分屏对比在WebGIS开发中是很常见的功能&#xff0c;和卷帘图层不一样的是&#xff0c;分屏对比是在各个地图中添加相同或者不同的图层进行对比查看。…...

Android 之 kotlin 语言学习笔记三(Kotlin-Java 互操作)

参考官方文档&#xff1a;https://developer.android.google.cn/kotlin/interop?hlzh-cn 一、Java&#xff08;供 Kotlin 使用&#xff09; 1、不得使用硬关键字 不要使用 Kotlin 的任何硬关键字作为方法的名称 或字段。允许使用 Kotlin 的软关键字、修饰符关键字和特殊标识…...

10-Oracle 23 ai Vector Search 概述和参数

一、Oracle AI Vector Search 概述 企业和个人都在尝试各种AI&#xff0c;使用客户端或是内部自己搭建集成大模型的终端&#xff0c;加速与大型语言模型&#xff08;LLM&#xff09;的结合&#xff0c;同时使用检索增强生成&#xff08;Retrieval Augmented Generation &#…...

【Java学习笔记】BigInteger 和 BigDecimal 类

BigInteger 和 BigDecimal 类 二者共有的常见方法 方法功能add加subtract减multiply乘divide除 注意点&#xff1a;传参类型必须是类对象 一、BigInteger 1. 作用&#xff1a;适合保存比较大的整型数 2. 使用说明 创建BigInteger对象 传入字符串 3. 代码示例 import j…...

C++:多态机制详解

目录 一. 多态的概念 1.静态多态&#xff08;编译时多态&#xff09; 二.动态多态的定义及实现 1.多态的构成条件 2.虚函数 3.虚函数的重写/覆盖 4.虚函数重写的一些其他问题 1&#xff09;.协变 2&#xff09;.析构函数的重写 5.override 和 final关键字 1&#…...

七、数据库的完整性

七、数据库的完整性 主要内容 7.1 数据库的完整性概述 7.2 实体完整性 7.3 参照完整性 7.4 用户定义的完整性 7.5 触发器 7.6 SQL Server中数据库完整性的实现 7.7 小结 7.1 数据库的完整性概述 数据库完整性的含义 正确性 指数据的合法性 有效性 指数据是否属于所定…...

GitFlow 工作模式(详解)

今天再学项目的过程中遇到使用gitflow模式管理代码&#xff0c;因此进行学习并且发布关于gitflow的一些思考 Git与GitFlow模式 我们在写代码的时候通常会进行网上保存&#xff0c;无论是github还是gittee&#xff0c;都是一种基于git去保存代码的形式&#xff0c;这样保存代码…...

08. C#入门系列【类的基本概念】:开启编程世界的奇妙冒险

C#入门系列【类的基本概念】&#xff1a;开启编程世界的奇妙冒险 嘿&#xff0c;各位编程小白探险家&#xff01;欢迎来到 C# 的奇幻大陆&#xff01;今天咱们要深入探索这片大陆上至关重要的 “建筑”—— 类&#xff01;别害怕&#xff0c;跟着我&#xff0c;保准让你轻松搞…...

uniapp 实现腾讯云IM群文件上传下载功能

UniApp 集成腾讯云IM实现群文件上传下载功能全攻略 一、功能背景与技术选型 在团队协作场景中&#xff0c;群文件共享是核心需求之一。本文将介绍如何基于腾讯云IMCOS&#xff0c;在uniapp中实现&#xff1a; 群内文件上传/下载文件元数据管理下载进度追踪跨平台文件预览 二…...