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

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:打折日期交叉问题

目录 问题&#xff1a;SQL解答&#xff1a;第一种方式&#xff1a;第二种方式&#xff1a; 问题&#xff1a; 如下为某平台的商品促销数据&#xff0c;字段含义分别为品牌名称、打折开始日期、打折结束日期&#xff0c;现在要计算每个品牌的打折销售天数&#xff08;注意其中的…...

三大主流前端框架介绍及选型

在前端项目中&#xff0c;可以借助某些框架&#xff08;如React、Vue、Angular等&#xff09;来实现组件化开发&#xff0c;使代码更容易复用。此时&#xff0c;一个网页不再是由一个个独立的HTML、CSS和JavaScript文件组成&#xff0c;而是按照组件的思想将网页划分成一个个组…...

云原生消息流系统 Apache Pulsar 在腾讯云的大规模生产实践

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

【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…...

数字化时代的智能支持:亚马逊云科技轻量应用服务器技术领先

轻量应用服务器是一种简化运维、门槛低的弹性服务器&#xff0c;它的"轻"主要体现在几个方面&#xff1a;开箱即用、应用优质、上手简洁、投入划算、运维简便以及稳定可靠。相较于普通的云服务器&#xff0c;轻量应用服务器简化了云服务的操作难度、使用和管理流程&a…...

【智慧之窗】AI驱动产品探索

一.初识 ChatGPT ChatGPT 是由 OpenAI 开发的自然语言处理&#xff08;NLP&#xff09;模型&#xff0c;基于 GPT&#xff08;Generative Pre-trained Transformer&#xff09;架构。GPT 系列的模型旨在理解和生成自然语言文本。ChatGPT 专注于支持对话性任务&#xff0c;即与…...

BBS项目--登录

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

Python---TCP服务端程序开发

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

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

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

听GPT 讲Rust源代码--src/tools(15)

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

python可以做小程序研发嘛,python能做微信小程序吗

大家好&#xff0c;给大家分享一下python可以做微信小程序开发吗&#xff0c;很多人还不知道这一点。下面详细解释一下。现在让我们来看看&#xff01; 大家好&#xff0c;给大家分享一下用python编写一个小程序&#xff0c;很多人还不知道这一点。下面详细解释一下用python代码…...

创建型模式 | 单例模式

一、单例模式 单例模式(Singleton Pattern)&#xff0c;使用最广泛的设计模式之一。其意图是保证一个类仅有一个实例被构造&#xff0c;并提供一个访问它的全局访问接口&#xff0c;该实例被程序的所有模块共享。 1、饿汉式 1.1、基础版本 在程序启动后立刻构造单例&#xff0…...

【无标题】欢迎使用Markdown编辑器

这里写自定义目录标题 欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题&#xff0c;有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants 创建一个自定义列表如何创建一个…...

Postgresql中PL/pgSQL的游标、自定义函数、存储过程的使用

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

【IDEA】Intellij IDEA相关配置

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

GD32移植STM32工程(因为懒,所以移植)

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

mt5和mt4交易软件有什么区别?

MetaTrader 4&#xff08;MT4&#xff09;和MetaTrader 5&#xff08;MT5&#xff09;是两种广泛使用的外汇和金融市场交易平台&#xff0c;由MetaQuotes公司开发。尽管它们都是外汇交易的常见选择&#xff0c;但在功能和特性上存在一些区别。以下是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 前言 &#x1f525; 优质竞赛项目系列&#xff0c;今天要分享的是 &#x1f6a9; **基于Django与深度学习的股票预测系统 ** 该项目较为新颖&#xff0c;适合作为竞赛课题方向&#xff…...

听GPT 讲Rust源代码--src/tools(16)

File: rust/src/tools/rust-analyzer/crates/ide-completion/src/completions/use_.rs rust-analyzer是一个基于Rust语言的IntelliSense引擎&#xff0c;用于提供IDE自动补全、代码导航和其他代码编辑功能。在rust-analyzer的源代码中&#xff0c;rust/src/tools/rust-analyzer…...

19c补丁后oracle属主变化,导致不能识别磁盘组

补丁后服务器重启&#xff0c;数据库再次无法启动 ORA01017: invalid username/password; logon denied Oracle 19c 在打上 19.23 或以上补丁版本后&#xff0c;存在与用户组权限相关的问题。具体表现为&#xff0c;Oracle 实例的运行用户&#xff08;oracle&#xff09;和集…...

conda相比python好处

Conda 作为 Python 的环境和包管理工具&#xff0c;相比原生 Python 生态&#xff08;如 pip 虚拟环境&#xff09;有许多独特优势&#xff0c;尤其在多项目管理、依赖处理和跨平台兼容性等方面表现更优。以下是 Conda 的核心好处&#xff1a; 一、一站式环境管理&#xff1a…...

linux之kylin系统nginx的安装

一、nginx的作用 1.可做高性能的web服务器 直接处理静态资源&#xff08;HTML/CSS/图片等&#xff09;&#xff0c;响应速度远超传统服务器类似apache支持高并发连接 2.反向代理服务器 隐藏后端服务器IP地址&#xff0c;提高安全性 3.负载均衡服务器 支持多种策略分发流量…...

Xshell远程连接Kali(默认 | 私钥)Note版

前言:xshell远程连接&#xff0c;私钥连接和常规默认连接 任务一 开启ssh服务 service ssh status //查看ssh服务状态 service ssh start //开启ssh服务 update-rc.d ssh enable //开启自启动ssh服务 任务二 修改配置文件 vi /etc/ssh/ssh_config //第一…...

DockerHub与私有镜像仓库在容器化中的应用与管理

哈喽&#xff0c;大家好&#xff0c;我是左手python&#xff01; Docker Hub的应用与管理 Docker Hub的基本概念与使用方法 Docker Hub是Docker官方提供的一个公共镜像仓库&#xff0c;用户可以在其中找到各种操作系统、软件和应用的镜像。开发者可以通过Docker Hub轻松获取所…...

阿里云ACP云计算备考笔记 (5)——弹性伸缩

目录 第一章 概述 第二章 弹性伸缩简介 1、弹性伸缩 2、垂直伸缩 3、优势 4、应用场景 ① 无规律的业务量波动 ② 有规律的业务量波动 ③ 无明显业务量波动 ④ 混合型业务 ⑤ 消息通知 ⑥ 生命周期挂钩 ⑦ 自定义方式 ⑧ 滚的升级 5、使用限制 第三章 主要定义 …...

【磁盘】每天掌握一个Linux命令 - iostat

目录 【磁盘】每天掌握一个Linux命令 - iostat工具概述安装方式核心功能基础用法进阶操作实战案例面试题场景生产场景 注意事项 【磁盘】每天掌握一个Linux命令 - iostat 工具概述 iostat&#xff08;I/O Statistics&#xff09;是Linux系统下用于监视系统输入输出设备和CPU使…...

《用户共鸣指数(E)驱动品牌大模型种草:如何抢占大模型搜索结果情感高地》

在注意力分散、内容高度同质化的时代&#xff0c;情感连接已成为品牌破圈的关键通道。我们在服务大量品牌客户的过程中发现&#xff0c;消费者对内容的“有感”程度&#xff0c;正日益成为影响品牌传播效率与转化率的核心变量。在生成式AI驱动的内容生成与推荐环境中&#xff0…...

【Oracle】分区表

个人主页&#xff1a;Guiat 归属专栏&#xff1a;Oracle 文章目录 1. 分区表基础概述1.1 分区表的概念与优势1.2 分区类型概览1.3 分区表的工作原理 2. 范围分区 (RANGE Partitioning)2.1 基础范围分区2.1.1 按日期范围分区2.1.2 按数值范围分区 2.2 间隔分区 (INTERVAL Partit…...

rnn判断string中第一次出现a的下标

# coding:utf8 import torch import torch.nn as nn import numpy as np import random import json""" 基于pytorch的网络编写 实现一个RNN网络完成多分类任务 判断字符 a 第一次出现在字符串中的位置 """class TorchModel(nn.Module):def __in…...