SQL面试题挑战01:打折日期交叉问题
目录
- 问题:
- SQL解答:
- 第一种方式:
- 第二种方式:
问题:
如下为某平台的商品促销数据,字段含义分别为品牌名称、打折开始日期、打折结束日期,现在要计算每个品牌的打折销售天数(注意其中的交叉日期)。比如vivo的打折销售天数就为17天。
brand start_date end_date
xiaomi 2021-06-05 2021-06-09
xiaomi 2021-06-11 2021-06-21
vivo 2021-06-05 2021-06-15
vivo 2021-06-09 2021-06-21
honor 2021-06-05 2021-06-21
honor 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26
huawei 2021-06-05 2021-06-26
huawei 2021-06-09 2021-06-15
huawei 2021-06-17 2021-06-21
SQL解答:
第一种方式:
根据每个品牌的促销开始时间和结束时间可以得到品牌每天促销的明细数据,然后,按品牌分组,日期去重就可以得到每个品牌打折销售天数。但此种方式适合数据量不大的情况,因为该方法会让数据膨胀的很厉害。
with temp as (select 'xiaomi' as brand ,'2021-06-05' as start_date,'2021-06-09' as end_dateunion allselect 'xiaomi' as brand ,'2021-06-11' as start_date,'2021-06-21' as end_dateunion allselect 'vivo' as brand ,'2021-06-05' as start_date,'2021-06-15' as end_dateunion allselect 'vivo' as brand ,'2021-06-09' as start_date,'2021-06-21' as end_dateunion all select 'honor' as brand ,'2021-06-05' as start_date,'2021-06-21' as end_dateunion all select 'honor' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_dateunion allselect 'honor' as brand ,'2021-06-17' as start_date,'2021-06-26' as end_dateunion allselect 'huawei' as brand ,'2021-06-05' as start_date,'2021-06-26' as end_dateunion allselect 'huawei' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_dateunion allselect 'huawei' as brand ,'2021-06-17' as start_date,'2021-06-21' as end_date
)select
brand
,count(distinct dt) as dts
from (
selectbrand,start_date,end_date,date_add(start_date,tmp.col_idx) as dt
from temp
lateral VIEW posexplode(split(repeat("#,",datediff(date(end_date), date(start_date))),'#')) tmp AS col_idx,col_val
) tt
group by brand
;
备注:补充repeat函数
select repeat("#,",datediff('2023-12-18','2023-12-01'))
#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,select split(repeat("#,",datediff('2023-12-18','2023-12-01')),'#')
["",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",","]
第二种方式:
第二种方式规避数据膨胀的情况,经过适当的处理,消除日期交叉的情况
with temp as (select 'xiaomi' as brand ,'2021-06-05' as start_date,'2021-06-09' as end_dateunion allselect 'xiaomi' as brand ,'2021-06-11' as start_date,'2021-06-21' as end_dateunion allselect 'vivo' as brand ,'2021-06-05' as start_date,'2021-06-15' as end_dateunion allselect 'vivo' as brand ,'2021-06-09' as start_date,'2021-06-21' as end_dateunion all select 'honor' as brand ,'2021-06-05' as start_date,'2021-06-21' as end_dateunion all select 'honor' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_dateunion allselect 'honor' as brand ,'2021-06-17' as start_date,'2021-06-26' as end_dateunion allselect 'huawei' as brand ,'2021-06-05' as start_date,'2021-06-26' as end_dateunion allselect 'huawei' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_dateunion allselect 'huawei' as brand ,'2021-06-17' as start_date,'2021-06-21' as end_date
)select
brand
,sum(datediff(date(end_date),date(start_date))+1)
from
(
selectbrand,casewhen start_date<=max_date then date_add(date(max_date),1)else start_date endas start_date,end_datefrom(selectbrand,start_date,end_date,max(end_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and 1 PRECEDING ) as max_date --获取同一品牌内按开始日期排序后,取第一行到前一行的最大结束时间from temp)t1)t1
where end_date>=start_date
group by brand
;
补充:rows 和range的区别
在 SQL 中,rows 和 range 是两种不同的窗口帧(window frame)类型,它们定义了窗口函数的计算范围。
rows 窗口帧是基于行的,它使用一组相对于当前行的行号来定义窗口函数的计算范围。rows 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。
range 窗口帧是基于值的,它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。range 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。
注释:
PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点
UNBOUNDED PRECEDING 表示从前面的起点
UNBOUNDED FOLLOWING:表示到后面的终点
一般来说,rows 和 range 窗口帧都可以用于定义窗口函数的计算范围,但是它们有一些不同的特点:rows 窗口帧是基于行的,它使用一组相对于当前行的行号来定义窗口函数的计算范围。因此,rows 窗口帧适用于基于行号的计算,例如计算排名、移动平均等。range 窗口帧是基于值的,它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。因此,range 窗口帧适用于基于数值范围的计算,例如计算累计和、百分比等。
一般情况下,rows 窗口帧比 range 窗口帧更常用,因为基于行号的计算更加常见。但是在某些特殊情况下,range 窗口帧也可以使用。
例如:当窗口函数的计算范围基于连续的数值范围时,可以使用 range 窗口帧。例如,计算累计和、计算百分比等。当窗口函数的计算范围包含重复的值时,可以使用 range 窗口帧来避免重复计算。例如,计算连续相同值的最大长度、计算某个值在窗口中的出现次数等。
需要注意的是,对于一些特殊的窗口函数,可能只能使用 rows 窗口帧,例如计算排名、计算移动平均等。因此,在使用 range 窗口帧时,需要根据具体的需求和窗口函数的特性选择合适的窗口帧类型。
相关文章:

SQL面试题挑战01:打折日期交叉问题
目录 问题:SQL解答:第一种方式:第二种方式: 问题: 如下为某平台的商品促销数据,字段含义分别为品牌名称、打折开始日期、打折结束日期,现在要计算每个品牌的打折销售天数(注意其中的…...

三大主流前端框架介绍及选型
在前端项目中,可以借助某些框架(如React、Vue、Angular等)来实现组件化开发,使代码更容易复用。此时,一个网页不再是由一个个独立的HTML、CSS和JavaScript文件组成,而是按照组件的思想将网页划分成一个个组…...

云原生消息流系统 Apache Pulsar 在腾讯云的大规模生产实践
导语 由 InfoQ 主办的 Qcon 全球软件开发者大会北京站上周已精彩落幕,腾讯云中间件团队的冉小龙参与了《云原生机构设计与音视频技术应用》专题,带来了以《云原生消息流系统 Apache Pulsar 在腾讯云的大规模生产实践》为主题的精彩演讲,在本…...

【LeetCode刷题】--245.最短单词距离III
245.最短单词距离III class Solution {public int shortestWordDistance(String[] wordsDict, String word1, String word2) {int len wordsDict.length;int ans len;if(word1.equals(word2)){int prev -1;for(int i 0;i<len;i){String word wordsDict[i];if(word.equa…...

数字化时代的智能支持:亚马逊云科技轻量应用服务器技术领先
轻量应用服务器是一种简化运维、门槛低的弹性服务器,它的"轻"主要体现在几个方面:开箱即用、应用优质、上手简洁、投入划算、运维简便以及稳定可靠。相较于普通的云服务器,轻量应用服务器简化了云服务的操作难度、使用和管理流程&a…...

【智慧之窗】AI驱动产品探索
一.初识 ChatGPT ChatGPT 是由 OpenAI 开发的自然语言处理(NLP)模型,基于 GPT(Generative Pre-trained Transformer)架构。GPT 系列的模型旨在理解和生成自然语言文本。ChatGPT 专注于支持对话性任务,即与…...

BBS项目--登录
BBS阶段性测试总要求 django登录报错 Error: [WinError 10013] 以一种访问权限不允许的方式做了一个访问套接字的尝试。 原因分析:出现这种情况在Windows中很常见,就是端口被占用 解决措施:这时我们只需改一下端口便可以了 登录前端页面(HTML…...

Python---TCP服务端程序开发
1. 开发 TCP 服务端程序开发步骤回顾 创建服务端端套接字对象绑定端口号设置监听等待接受客户端的连接请求接收数据发送数据关闭套接字 2. socket 类的介绍 导入 socket 模块import socket 创建服务端 socket 对象socket.socket(AddressFamily, Type) 参数说明: AddressF…...

回归预测 | MATLAB实现GWO-DHKELM基于灰狼算法优化深度混合核极限学习机的数据回归预测 (多指标,多图)
回归预测 | MATLAB实现GWO-DHKELM基于灰狼算法优化深度混合核极限学习机的数据回归预测 (多指标,多图) 目录 回归预测 | MATLAB实现GWO-DHKELM基于灰狼算法优化深度混合核极限学习机的数据回归预测 (多指标,多图&#…...

听GPT 讲Rust源代码--src/tools(15)
File: rust/src/tools/rust-analyzer/crates/mbe/src/token_map.rs 在Rust源代码中,rust/src/tools/rust-analyzer/crates/mbe/src/token_map.rs文件的作用是实现了一个能够将输入的文本映射为标记的结构。具体来说,它定义和实现了几个结构体(…...

python可以做小程序研发嘛,python能做微信小程序吗
大家好,给大家分享一下python可以做微信小程序开发吗,很多人还不知道这一点。下面详细解释一下。现在让我们来看看! 大家好,给大家分享一下用python编写一个小程序,很多人还不知道这一点。下面详细解释一下用python代码…...
创建型模式 | 单例模式
一、单例模式 单例模式(Singleton Pattern),使用最广泛的设计模式之一。其意图是保证一个类仅有一个实例被构造,并提供一个访问它的全局访问接口,该实例被程序的所有模块共享。 1、饿汉式 1.1、基础版本 在程序启动后立刻构造单例࿰…...
【无标题】欢迎使用Markdown编辑器
这里写自定义目录标题 欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants 创建一个自定义列表如何创建一个…...

Postgresql中PL/pgSQL的游标、自定义函数、存储过程的使用
场景 Postgresql中PL/pgSQL代码块的语法与使用-声明与赋值、IF语句、CASE语句、循环语句: Postgresql中PL/pgSQL代码块的语法与使用-声明与赋值、IF语句、CASE语句、循环语句-CSDN博客 上面讲了基本语法,下面记录游标、自定义函数、存储过程的使用。 …...

【IDEA】Intellij IDEA相关配置
IDEA 全称 IntelliJ IDEA,是java编程语言的集成开发环境。IntelliJ在业界被公认为最好的Java开发工具,尤其在智能代码助手、代码自动提示、重构、JavaEE支持、各类版本工具(git、svn等)、JUnit、CVS整合、代码分析、 创新的GUI设计等方面的功能可以说是超…...

GD32移植STM32工程(因为懒,所以移植)
文章目录 一、前言二、差异性三、软件移植部分1.前期准备1.1 安装GD32固件库1.2 选择所用芯片 2.修改程序2.1 启动时间(内部时钟可不改)2.2 主频2.2.1 系统时钟配置2.2.2 108MHz宏定义第一处第二处第三处第四处第五处 2.2.3 串口2.2.4 FLASH 四、总结 一…...

mt5和mt4交易软件有什么区别?
MetaTrader 4(MT4)和MetaTrader 5(MT5)是两种广泛使用的外汇和金融市场交易平台,由MetaQuotes公司开发。尽管它们都是外汇交易的常见选择,但在功能和特性上存在一些区别。以下是MT4和MT5之间的主要区别&…...

零刻EQ12 N100 双2.5G网口 All In One新手教程
零刻EQ12 N100 双2.5G网口 All In One新手教程 前言1.硬件配置2.准备工作2.1. ESXI8.0U2镜像2.2. Rufus磁盘工具下载2.3. ikuai镜像下载2.4. StarWindConverter虚拟磁盘格式转换工具下载2.5. OpenWrt镜像下载2.6. 黑群晖RR引导镜像下载(DSM7.2)2.7. 需要准备的硬件2.8. 格式化需…...

竞赛保研 基于Django与深度学习的股票预测系统
文章目录 0 前言1 课题背景2 实现效果3 Django框架4 数据整理5 模型准备和训练6 最后 0 前言 🔥 优质竞赛项目系列,今天要分享的是 🚩 **基于Django与深度学习的股票预测系统 ** 该项目较为新颖,适合作为竞赛课题方向ÿ…...

听GPT 讲Rust源代码--src/tools(16)
File: rust/src/tools/rust-analyzer/crates/ide-completion/src/completions/use_.rs rust-analyzer是一个基于Rust语言的IntelliSense引擎,用于提供IDE自动补全、代码导航和其他代码编辑功能。在rust-analyzer的源代码中,rust/src/tools/rust-analyzer…...
在软件开发中正确使用MySQL日期时间类型的深度解析
在日常软件开发场景中,时间信息的存储是底层且核心的需求。从金融交易的精确记账时间、用户操作的行为日志,到供应链系统的物流节点时间戳,时间数据的准确性直接决定业务逻辑的可靠性。MySQL作为主流关系型数据库,其日期时间类型的…...
设计模式和设计原则回顾
设计模式和设计原则回顾 23种设计模式是设计原则的完美体现,设计原则设计原则是设计模式的理论基石, 设计模式 在经典的设计模式分类中(如《设计模式:可复用面向对象软件的基础》一书中),总共有23种设计模式,分为三大类: 一、创建型模式(5种) 1. 单例模式(Sing…...

无法与IP建立连接,未能下载VSCode服务器
如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...

Vue2 第一节_Vue2上手_插值表达式{{}}_访问数据和修改数据_Vue开发者工具
文章目录 1.Vue2上手-如何创建一个Vue实例,进行初始化渲染2. 插值表达式{{}}3. 访问数据和修改数据4. vue响应式5. Vue开发者工具--方便调试 1.Vue2上手-如何创建一个Vue实例,进行初始化渲染 准备容器引包创建Vue实例 new Vue()指定配置项 ->渲染数据 准备一个容器,例如: …...
macOS多出来了:Google云端硬盘、YouTube、表格、幻灯片、Gmail、Google文档等应用
文章目录 问题现象问题原因解决办法 问题现象 macOS启动台(Launchpad)多出来了:Google云端硬盘、YouTube、表格、幻灯片、Gmail、Google文档等应用。 问题原因 很明显,都是Google家的办公全家桶。这些应用并不是通过独立安装的…...

云原生安全实战:API网关Kong的鉴权与限流详解
🔥「炎码工坊」技术弹药已装填! 点击关注 → 解锁工业级干货【工具实测|项目避坑|源码燃烧指南】 一、基础概念 1. API网关(API Gateway) API网关是微服务架构中的核心组件,负责统一管理所有API的流量入口。它像一座…...
C++课设:简易日历程序(支持传统节假日 + 二十四节气 + 个人纪念日管理)
名人说:路漫漫其修远兮,吾将上下而求索。—— 屈原《离骚》 创作者:Code_流苏(CSDN)(一个喜欢古诗词和编程的Coder😊) 专栏介绍:《编程项目实战》 目录 一、为什么要开发一个日历程序?1. 深入理解时间算法2. 练习面向对象设计3. 学习数据结构应用二、核心算法深度解析…...

Chrome 浏览器前端与客户端双向通信实战
Chrome 前端(即页面 JS / Web UI)与客户端(C 后端)的交互机制,是 Chromium 架构中非常核心的一环。下面我将按常见场景,从通道、流程、技术栈几个角度做一套完整的分析,特别适合你这种在分析和改…...

DeepSeek源码深度解析 × 华为仓颉语言编程精粹——从MoE架构到全场景开发生态
前言 在人工智能技术飞速发展的今天,深度学习与大模型技术已成为推动行业变革的核心驱动力,而高效、灵活的开发工具与编程语言则为技术创新提供了重要支撑。本书以两大前沿技术领域为核心,系统性地呈现了两部深度技术著作的精华:…...

[论文阅读]TrustRAG: Enhancing Robustness and Trustworthiness in RAG
TrustRAG: Enhancing Robustness and Trustworthiness in RAG [2501.00879] TrustRAG: Enhancing Robustness and Trustworthiness in Retrieval-Augmented Generation 代码:HuichiZhou/TrustRAG: Code for "TrustRAG: Enhancing Robustness and Trustworthin…...