SQL面试题练习 —— 查询前2大和前2小用户并有序拼接
目录
- 1 题目
- 2 建表语句
- 3 题解
1 题目
有用户账户表,包含年份,用户id和值,请按照年份分组,取出值前两小和前两大对应的用户id,需要保持值最小和最大的用户id排首位。
样例数据
+-------+----------+--------+
| year | user_id | value |
+-------+----------+--------+
| 2022 | A | 30 |
| 2022 | B | 10 |
| 2022 | C | 20 |
| 2023 | A | 40 |
| 2023 | B | 50 |
| 2023 | C | 20 |
| 2023 | D | 30 |
+-------+----------+--------+
期望结果
+-------+-----------------+-----------------+
| year | max2_user_list | min2_user_list |
+-------+-----------------+-----------------+
| 2022 | A,C | B,C |
| 2023 | B,A | C,D |
+-------+-----------------+-----------------+
2 建表语句
--建表语句
create table if not exists t_amount
(year string,user_id string,value bigint
)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS orc;--插入数据insert into t_amount(year, user_id, value)
values ('2022', 'A', 30),('2022', 'B', 10),('2022', 'C', 20),('2023', 'A', 40),('2023', 'B', 50),('2023', 'C', 20),('2023', 'D', 30)
3 题解
(1)row_number函数根据年份分组,value正排和倒排得到两个序列
select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rn
from t_amount
执行结果
+----------+-------+--------+----------+-----+
| user_id | year | value | desc_rn | rn |
+----------+-------+--------+----------+-----+
| B | 2022 | 10 | 3 | 1 |
| C | 2022 | 20 | 2 | 2 |
| A | 2022 | 30 | 1 | 3 |
| C | 2023 | 20 | 4 | 1 |
| D | 2023 | 30 | 3 | 2 |
| A | 2023 | 40 | 2 | 3 |
| B | 2023 | 50 | 1 | 4 |
+----------+-------+--------+----------+-----+
(2)根据年份分组,取出value最大user_id,第二大user_id,最小user_id,第二小user_id
根据年份分组,取出每年最大、第二大,最小、第二小用户ID。使用 if 对desc_rn,rn进行判断,对符合条件的数据取出 user_id,其他去null,然后使用聚合函数取出结果。
select year,max(if(desc_rn = 1, user_id, null)) as max1_user_id,max(if(desc_rn = 2, user_id, null)) as max2_user_id,max(if(rn = 1, user_id, null)) as min1_user_id,max(if(rn = 2, user_id, null)) as min2_user_id
from (select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rnfrom t_amount) t1
group by year
执行结果
+-------+---------------+---------------+---------------+---------------+
| year | max1_user_id | max2_user_id | min1_user_id | min2_user_id |
+-------+---------------+---------------+---------------+---------------+
| 2022 | A | C | B | C |
| 2023 | B | A | C | D |
+-------+---------------+---------------+---------------+---------------+
(3)按照顺序拼接,得到最终结果
按照题目要求,进行字符拼接
- 拼接max1_user_id、max2_user_id为max2_list;
- 拼接min1_user_id、min2_user_id为min2_list;
select year,concat(max(if(desc_rn = 1, user_id, null)), ',',max(if(desc_rn = 2, user_id, null))) as max2_user_list,concat(max(if(rn = 1, user_id, null)), ',',max(if(rn = 2, user_id, null))) as min2_user_list
from (select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rnfrom t_amount) t1
group by year
执行结果
+-------+-----------------+-----------------+
| year | max2_user_list | min2_user_list |
+-------+-----------------+-----------------+
| 2022 | A,C | B,C |
| 2023 | B,A | C,D |
+-------+-----------------+-----------------+
相关文章:
SQL面试题练习 —— 查询前2大和前2小用户并有序拼接
目录 1 题目2 建表语句3 题解 1 题目 有用户账户表,包含年份,用户id和值,请按照年份分组,取出值前两小和前两大对应的用户id,需要保持值最小和最大的用户id排首位。 样例数据 ------------------------- | year | user_id | v…...
Arthas常见使用姿势
文章目录 Arthas常见使用姿势官网基本命令通用参数解释表达式核心变量说明常用命令一些常用特殊案例举例其他技巧关于OGNLOGNL的常见使用OGNL的一些特殊用法与说明OGNL内置的虚拟属性OGNL的个人思考OGNL的杂碎,收集未做验证 Arthas常见使用姿势 官网 https://arth…...
Apache Kylin的入门学习
Apache Kylin的入门学习可以从以下几个方面进行: 1. 了解Kylin的基本概念 定义:Apache Kylin是一个开源的分布式分析引擎,它基于Hadoop和HBase构建,提供Hadoop/Spark之上的SQL查询接口及多维分析(OLAP)能…...
React@16.x(46)路由v5.x(11)源码(3)- 实现 Router
目录 1,Router 的结构2,实现2.1,react-router1,matchPath.js2,Router.js3,RouterContext.jsx4,index.jsx 2.2,react-router-domBrowserRouter.jsxindex.jsx 1,Router 的结…...
openGauss真的比PostgreSQL差了10年?
前不久写了MogDB针对PostgreSQL的兼容性文章,我在文中提到针对PostgreSQL而言,MogDB兼容性还是不错的,其中也给出了其中一个能源客户之前POC的迁移报告数据。 But很快我发现总有人回留言喷我,而且我发现每次喷的这帮人是根本不看文…...
【国产开源可视化引擎Meta2d.js】快速上手
提示 初始化引擎后,会生成一个 meta2d 全局对象,可直接使用。 调用meta2d前,需要确保meta2d所在的父容器element元素位置大小已经渲染完成。如果样式或css(特别是css动画)没有初始化完成,可能会报错&…...
c#与倍福Plc通信
bcdedit /set hypervisorlaunchtype off...
【OceanBase诊断调优】—— 如何通过trace_id找到对应的执行节点IP
1. 前言 OceanBase作为分布式数据库,查问题找对节点很关键。好在OceanBase执行的每一条SQL都能通过trace_id来关联起来,知道trace_id怎么知道是在哪个节点发起的呢,请看本文。 2. trace_id生成规则 ob内部trace_id的生成函数如下࿰…...
鸿蒙开发Ability Kit(程序访问控制):【使用粘贴控件】
使用粘贴控件 粘贴控件是一种特殊的系统安全控件,它允许应用在用户的授权下无提示地读取剪贴板数据。 在应用集成粘贴控件后,用户点击该控件,应用读取剪贴板数据时不会弹窗提示。可以用于任何应用需要读取剪贴板的场景,避免弹窗…...
PL/SQL入门到实践
一、什么是PL/SQL PL/SQL是Procedural Language/Structured Query Language的缩写。PL/SQL是一种过程化编程语言,运行于服务器端的编程语言。PL/SQL是对SQL语言的扩展。PL/SQL结合了SQL语句和过程性编程语言的特性,可以用于编写存储过程、触发器、函数等…...
双非本 985 硕,我马上要入职上海AI实验室大模型算法岗
暑期实习基本结束了,校招即将开启。 不同以往的是,当前职场环境已不再是那个双向奔赴时代了。求职者在变多,HC 在变少,岗位要求还更高了。 最近,我们又陆续整理了很多大厂的面试题,帮助一些球友解惑答疑&…...
C盘清理和管理
本篇是C盘一些常用的管理方法,以及定期清理C盘的方法,大部分情况下都能避免C盘爆红。 C盘清理和管理 C盘存储管理查看存储情况清理存储存储感知清理临时文件清理不需要的 迁移存储 磁盘清理桌面存储管理应用存储管理浏览器微信 工具清理 C盘存储管理 查…...
晚上睡觉要不要关路由器?一语中的
前言 前几天小白去了一个朋友家,有朋友说:路由器不关机的话会影响睡眠吗? 这个影响睡眠嘛,确实是会的。毕竟一时冲浪一时爽,一直冲浪一直爽……刷剧刷抖音刷到根本停不下来,肯定影响睡眠。 所以晚上睡觉要…...
ardupilot开发 --- 坐标变换 篇
Good Morning, and in case I dont see you, good afternoon, good evening, and good night! 0. 一些概念1. 坐标系的旋转1.1 轴角法1.2 四元素1.3 基于欧拉角的旋转矩阵1.3.1 单轴旋转矩阵1.3.2 多轴旋转矩阵1.3.3 其他 2. 齐次变换矩阵3. visp实践 0. 一些概念 相关概念&am…...
git clone 别人项目后正确的修改和同步操作
简介 git clone主要是克隆别人的开源项目。但更高端的操作是实现本地修改的同时,能同步别人的在线修改,并且不相互干扰: 克隆原始项目:从远程仓库克隆项目到本地。添加上游仓库:将原始项目的远程仓库添加为上游仓库。…...
JAVA连接FastGPT实现流式请求SSE效果
FastGPT 是一个基于 LLM 大语言模型的知识库问答系统,提供开箱即用的数据处理、模型调用等能力。同时可以通过 Flow 可视化进行工作流编排,从而实现复杂的问答场景! 一、先看效果 真正实流式请求,SSE效果,SSE解释&am…...
二分查找1
1. 二分查找(704) 题目描述: 算法原理: 暴力解法就是遍历数组来找到相应的元素,使用二分查找的解法就是每次在数组中选定一个元素来将数组划分为两部分,然后因为数组有序,所以通过大小关系舍弃…...
什么美业门店管理系统好用?2024美业收银系统软件排名分享
美业SAAS系统在美容、美发、美甲等行业中十分重要,这种系统为美业提供了一种数字化解决方案,帮助企业更高效地管理业务和客户关系。 美业门店管理系统通常提供预约管理、客户管理、库存管理、报表生成等一系列功能,以满足美容院、美发沙龙等…...
【文件上传】
文件上传漏洞 FileUpload 0x01 定义 服务端未对客户端上传文件进行严格的 验证和过滤造成可上传任意文件情况;0x02 攻击满足条件: 1. 上传文件能够被Web容器解释执行 2. 找到文件位置 3.上传文件未被改变内容。(躲避安全检查&#…...
Golang 单引号、双引号和反引号的概念、用法以及区别
在 Golang(Go 语言)中,单引号 ()、双引号 (") 和反引号 () 用于不同类型的字符串和字符表示。以下是它们的概念、用法和区别: 1. 单引号 () 概念 单引号用于表示 字符(rune 类型)。一个字符表示一个…...
linux和mysql基础指令
Linux中nano和vim读可以打开记事文件。 ifdown ens33 ifup ens33 关闭,开启网络 rm -r lesson1 gcc -o code1 code1.c 编译c语言代码 ./code1 执行c语言代码 rm -r dir 删除文件夹 mysql> show databases-> ^C mysql> show databases; -------…...
JDK 为什么需要配置环境变量
前言 首先,我们要知道 Java 程序的执行过程。首先将 xxx.java 文件(使用 javac 编译指令)编译成 xxx.class 文件(字节码文件),再将字节码文件(使用 java 执行指令)解释成电脑所能认识…...
ViewBinding的使用(因为kotlin-android-extensions插件的淘汰)
书籍: 《第一行代码 Android》第三版 开发环境: Android Studio Jellyfish | 2023.3.1 问题: 3.2.4在Activity中使用Toast章节中使用到了kotlin-android-extensions插件,但是该插件已经淘汰,根据网上了解,目前使用了新的技术VewBinding替…...
IOS Swift 从入门到精通:ios 连接数据库 安装 Firebase 和 Firestore
创建 Firebase 项目 导航到Firebase 控制台并创建一个新项目。为项目指定任意名称。 在这里插入图片描述 下一步,启用 Google Analytics,因为我们稍后会用到它来发送推送通知。 在这里插入图片描述 在下一个屏幕上,选择您的 Google Analytics 帐户(如果已创建)。如果没…...
QT4-QT5(6)-const char* QString 乱码转换
我简单粗暴的给出个结论: QString GBK编码正常,可以转UTF-8编码,但会有少量乱码。 const char* 编码就不要转编码,转哪个都是乱码。 UTF-8.cpp 下 1.QString GBK->UTF-8 2.const char * GBK->UTF-8 const char *…...
报错:RuntimeError_ cuDNN error_ CUDNN_STATUS_EXECUTION_FAILED
原因:pytorch与cuda版本不对 也有可能是内存空间不足,可以更改虚拟空间大小,参考:解决电脑内存不足问题:Win10虚拟内存设置指南...
黑马点评项目总结1-使用Session发送验证码和登录login和 使用Redis存储验证码和Redis的token登录
黑马先是总结了从session实现登录,然后是因为如果使用了集群方式的服务器的话,存在集群共享session互相拷贝效率低下的问题,接着引出了速度更快的内存型的kv数据库Redis, 使用Session发送验证码和登录login 举个例子:…...
【大模型】Vllm基础学习
前言:vllm是一个大语言模型高速推理框架,旨在提高大模型的服务效率。优势是内存管理,实现的核心是pageattetion算法。仅在gpu上加速,不在cpu加速。 目录 1. PageAttention2. 实践2.1 安装2.2 离线推理2.3 适配OpenAI的api 1. Page…...
使用vue动态给同一个a标签添加内容 并给a标签设置hover,悬浮文字变色,结果鼠标悬浮有的字上面不变色
如果Vue的虚拟DOM更新机制导致样式更新不及时,你可以尝试以下几种方法来解决这个问题: 确保使用响应式数据: 确保你使用的数据是响应式的,并且任何对这些数据的更改都会触发视图的更新。在Vue中,你应该使用data对象中的…...
【ajax实战06】进行文章发布
本文章目标:收集文章内容,并提交服务器保存 一:基于form-serialize插件收集表单数据 form-serialize插件仅能收集到表单数据,除此之外的数据无法收集到 二:基于axios提交到服务器保存 三:调用alert警告…...
Codeforces Round 954 (Div. 3)(A~E)
目录 A. X Axis B. Matrix Stabilization C. Update Queries D. Mathematical Problem A. X Axis Problem - A - Codeforces 直接找到第二大的数,答案就是这个数与其他两个数的差值的和。 void solve() {vector<ll>a;for (int i 1; i < 3; i){int x;…...
基于Java微信小程序同城家政服务系统设计和实现(源码+LW+调试文档+讲解等)
💗博主介绍:✌全网粉丝10W,CSDN作者、博客专家、全栈领域优质创作者,博客之星、平台优质作者、专注于Java、小程序技术领域和毕业项目实战✌💗 🌟文末获取源码数据库🌟感兴趣的可以先收藏起来,还…...
[21] Opencv_CUDA应用之使用Haar级联的对象检测
Opencv_CUDA应用之使用Haar级联的对象检测 Haar级联使用矩形特征来检测对象,它使用不同大小的矩形来计算不同的线和边缘特征。矩形包含一些黑色和白色区域,如下图所示,它们在图像的不同位置居中 类Haar特征检测算法的思想是计算矩形内白色像素和黑色像素之间的差异这个方法的…...
CXL:拯救NVMe SSD缓存不足设计难题-2
LMB提出了基于CXL协议的内存扩展框架和内核模块。该方案利用CXL内存扩展器作为物理DRAM源,旨在提供一个统一的内存分配接口,使PCIe和CXL设备都能方便地访问扩展的内存资源。通过这个接口,NVMe驱动和CUDA的统一内存内核驱动可以直接高效地访问…...
Opencv学习项目6——pyzbar
在之前我们学习了解码图片中的二维码,这次我们开启摄像头来解码视频中二维码 开启摄像头 # 打开摄像头 cap cv2.VideoCapture(0) cap.set(3, 640) # 设置摄像头画面宽度 cap.set(4, 480) # 设置摄像头画面高度 我使用的是笔记本上的摄像头来进行的,…...
Switch 刷安卓11 (LineageOS 18.1) 大气层双系统图文教程
很多朋友手上已经拥有了完成硬破的 Switch ,但又不甘心仅仅使用 Switch 本身的地平线系统,Switch 刷安卓 (Android 11) 会是一个好的选择,虽然 Switch 的 CPU 性能拉跨,但和桌面平台同一设计思路的TegraX1 GPU 可谓是先于时代&…...
Spring Boot与Spring Batch的深度集成
Spring Boot与Spring Batch的深度集成 大家好,我是免费搭建查券返利机器人省钱赚佣金就用微赚淘客系统3.0的小编,也是冬天不穿秋裤,天冷也要风度的程序猿!今天我们将深入探讨在Spring Boot应用中如何实现与Spring Batch的深度集成…...
RTSP协议在视频监控系统中的典型应用、以及视频监控设备的rtsp地址格式介绍
目录 一、协议概述 1、定义 2、提交者 3、位置 二、主要特点 1、实时性 2、可扩展性 3、控制功能 4、回放支持 5、网络适应性 三、RTSP的工作原理 1、会话准备 2、会话建立 3、媒体流控制 4、会话终止 5、媒体数据传输 四、协议功能 1、双向性 2、带外协议 …...
Kotlin基础——异步和并发
同步和异步 同步指的是一种行为:当执行IO操作的时候,在代码层面上我们需要主动去等待结果,直到结果返回阻塞指的是一种状态:当执行IO操作的时候,线程处于挂起状态,就是该线程没有执行了 故同步不是阻塞&a…...
消防认证-防火卷帘
一、消防认证 消防认证是指消防产品符合国家相关技术要求和标准,且通过了国家认证认可监督管理委员会审批,获得消防认证资质的认证机构颁发的证书,消防产品具有完好的防火功能,是住房和城乡建设领域验收的重要指标。 二、认证依据…...
SpringBoot3.3集成knif4j-swagger文档方式和使用案例
springboot3 集成 knif4j : 访问地址: swagger 接口文档默认地址:http://localhost:8080/swagger-ui.html# Knife4j 接口文档默认地址:http://127.0.0.1:8080/doc.html Maven: <dependency><groupId>com.github.x…...
老年服务与管理实训室:制定教学模式
随着我国人口老龄化程度的加深,如何为老年人提供优质的养老服务成为社会关注的重点。作为培养老年服务人才的重要阵地,老年服务与管理实训室应制定科学合理的教学模式,满足行业发展需求,培养出高素质的老年服务专业人才。本文针对老年服务与管理实训室的教学模式展开探讨,提出相…...
4、DDD、中台和微服务的关系
DDD、中台和微服务的关系 1 DDD和中台的本质 领域驱动设计(DDD)和中台在企业架构中有着密切的关系。DDD的本质在于通过对业务领域的深入分析和建模,构建高内聚、低耦合的系统。而中台则是对企业核心业务能力的抽象和封装,以实现…...
【ACM出版,马来西亚-吉隆坡举行】第四届互联网技术与教育信息化国际会议 (ITEI 2024)
作为全球科技创新大趋势的引领者,中国不断营造更加开放的科技创新环境,不断提升学术合作的深度和广度,构建惠及各方的创新共同体。这是对全球化的新贡献,是构建人类命运共同体的新贡献。 第四届互联网技术与教育信息化国际学术会议…...
走进IT的世界
引言 随着高考的结束,对于即将踏入IT(信息技术)领域的新生而言,这个假期不仅是放松身心的时间,更是提前规划、深化专业知识、为大学生活奠定坚实基础的宝贵机会。以下是一份详尽的高考假期预习与规划指南,…...
Linux 时区文件编译器 zic【man 8 zic】
1. NAME(名) zic - 时区编译器 2. SYNOPSIS(概要) zic [-v] [-d directory] [-l localtime] [-p posixrules] [-L leapsecondfilename] [-s] [-y command] [filename ...]3. DESCRIPTION(函数描述) zic…...
Springboot下使用Redis管道(pipeline)进行批量操作
之前有业务场景需要批量插入数据到Redis中,做的过程中也有一些感悟,因此记录下来,以防忘记。下面的内容会涉及到 分别使用for、管道处理批量操作,比较其所花费时间。 分别使用RedisCallback、SessionCallback进行Redis pipeline …...
Vue技巧大揭秘:自定义指令的力量与应用
引言 自定义指令就像是给予开发者的一把魔法钥匙,它能够打开DOM操作的新世界,按我的理解就是把对DOM操作的逻辑进行封装 全局注册与局部注册 全局注册 定义: 全局注册意味着自定义指令在Vue实例创建之前通过Vue.directive()方法注册&…...
HR人才测评,如何考察想象力?
什么是想象力? 想象力是指,人们通过在已有物质的基础上,通过大脑想象、加工、创造出新事物的能力,举一个非常简单的例子,在提到鸟这种生活的时候,大家会联想到各种各样不同鸟的品种。 在企业招聘中常常应…...
Git命令远程分支的合并和本地分支的同步
天行健,君子以自强不息;地势坤,君子以厚德载物。 每个人都有惰性,但不断学习是好好生活的根本,共勉! 文章均为学习整理笔记,分享记录为主,如有错误请指正,共同学习进步。…...