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 类型)。一个字符表示一个…...
未来机器人的大脑:如何用神经网络模拟器实现更智能的决策?
编辑:陈萍萍的公主一点人工一点智能 未来机器人的大脑:如何用神经网络模拟器实现更智能的决策?RWM通过双自回归机制有效解决了复合误差、部分可观测性和随机动力学等关键挑战,在不依赖领域特定归纳偏见的条件下实现了卓越的预测准…...
eNSP-Cloud(实现本地电脑与eNSP内设备之间通信)
说明: 想象一下,你正在用eNSP搭建一个虚拟的网络世界,里面有虚拟的路由器、交换机、电脑(PC)等等。这些设备都在你的电脑里面“运行”,它们之间可以互相通信,就像一个封闭的小王国。 但是&#…...
智慧工地云平台源码,基于微服务架构+Java+Spring Cloud +UniApp +MySql
智慧工地管理云平台系统,智慧工地全套源码,java版智慧工地源码,支持PC端、大屏端、移动端。 智慧工地聚焦建筑行业的市场需求,提供“平台网络终端”的整体解决方案,提供劳务管理、视频管理、智能监测、绿色施工、安全管…...
3.3.1_1 检错编码(奇偶校验码)
从这节课开始,我们会探讨数据链路层的差错控制功能,差错控制功能的主要目标是要发现并且解决一个帧内部的位错误,我们需要使用特殊的编码技术去发现帧内部的位错误,当我们发现位错误之后,通常来说有两种解决方案。第一…...
抖音增长新引擎:品融电商,一站式全案代运营领跑者
抖音增长新引擎:品融电商,一站式全案代运营领跑者 在抖音这个日活超7亿的流量汪洋中,品牌如何破浪前行?自建团队成本高、效果难控;碎片化运营又难成合力——这正是许多企业面临的增长困局。品融电商以「抖音全案代运营…...
镜像里切换为普通用户
如果你登录远程虚拟机默认就是 root 用户,但你不希望用 root 权限运行 ns-3(这是对的,ns3 工具会拒绝 root),你可以按以下方法创建一个 非 root 用户账号 并切换到它运行 ns-3。 一次性解决方案:创建非 roo…...
PL0语法,分析器实现!
简介 PL/0 是一种简单的编程语言,通常用于教学编译原理。它的语法结构清晰,功能包括常量定义、变量声明、过程(子程序)定义以及基本的控制结构(如条件语句和循环语句)。 PL/0 语法规范 PL/0 是一种教学用的小型编程语言,由 Niklaus Wirth 设计,用于展示编译原理的核…...
【HTTP三个基础问题】
面试官您好!HTTP是超文本传输协议,是互联网上客户端和服务器之间传输超文本数据(比如文字、图片、音频、视频等)的核心协议,当前互联网应用最广泛的版本是HTTP1.1,它基于经典的C/S模型,也就是客…...
Web 架构之 CDN 加速原理与落地实践
文章目录 一、思维导图二、正文内容(一)CDN 基础概念1. 定义2. 组成部分 (二)CDN 加速原理1. 请求路由2. 内容缓存3. 内容更新 (三)CDN 落地实践1. 选择 CDN 服务商2. 配置 CDN3. 集成到 Web 架构 …...
在web-view 加载的本地及远程HTML中调用uniapp的API及网页和vue页面是如何通讯的?
uni-app 中 Web-view 与 Vue 页面的通讯机制详解 一、Web-view 简介 Web-view 是 uni-app 提供的一个重要组件,用于在原生应用中加载 HTML 页面: 支持加载本地 HTML 文件支持加载远程 HTML 页面实现 Web 与原生的双向通讯可用于嵌入第三方网页或 H5 应…...
