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             |
+-------+-----------------+-----------------+

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/diannao/38715.shtml
繁體地址,請注明出處:http://hk.pswp.cn/diannao/38715.shtml
英文地址,請注明出處:http://en.pswp.cn/diannao/38715.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

網絡基礎-RIP協議

RIP(Routing Information Protocol)是一個基于距離矢量的動態路由協議,常用于小型到中型網絡。RIP是較早的路由協議之一,具有簡單易用的特點。以下是關于RIP協議的詳細介紹: RIP的主要特點 ①使用跳數(ho…

非標設備行業的數智化項目管理

近年來,中國制造快速發展,企業迫切需要加快轉型升級。與傳統制造業相比,高端制造業具有明顯的優勢:高技術、高附加值、低污染、低排放、競爭優勢強。一方面,企業對于生產效率和自動化水平的要求不斷提高,期…

開發個人OpenUI--1 項目介紹

開發個人OpenUI--1 項目介紹 開發個人OpenUI--1 項目介紹知識點大綱文章目錄項目地址 開發個人OpenUI–1 項目介紹 本文將以一個使用Ollama部署的ChatGPT為背景,主要還是介紹和學習使用 go-zero 框架,開發個人OpenUI的服務器后端,使用Docker…

武漢星起航:成功掛牌上股交,領航亞馬遜跨境電商,共創未來輝煌

在全球電商的競爭格局中,亞馬遜憑借其卓越的服務、豐富的商品種類和高效的物流體系,始終穩坐全球電商市場的頭把交椅。而在這股不可阻擋的電商浪潮中,武漢星起航電子商務有限公司憑借其前瞻性的戰略布局和強大的運營能力,成功在20…

名企面試必問30題(十二)——簡單介紹一下你的家庭情況

1.思路 對于面試官來說,他提出這個問題,只是為了深挖您的性格、穩定性、行事風格,包括未來定居規劃、生育規劃等基礎信息,這是正常情況。您不要過多圍繞其他家庭成員來講,否則面試官無法獲取他想要的,您也難…

【單片機畢業設計選題24040】-基于STM32的藍牙防丟器設計

系統功能: 系統上電后顯示“歡迎使用藍牙防丟系統請稍后”兩秒鐘顯示正常界面,如果藍牙正常連接OLED顯示Connected, 藍牙未連接則顯示DisConnected同時蜂鳴器報警 藍牙正常連接后在APP上每隔三秒顯示一個Connected 系統功能框圖: 主要功能模塊原理圖: 電源時鐘…

大數據之Zookeeper部署

文章目錄 集群規劃環境準備集群部署參考資料 集群規劃 確定使用Hadoop101、hadoop102和hadoop103三臺服務器來構建Zookeeper集群。 hadoop101hadoop102hadoop103zookeeperzookeeperzookeeper 環境準備 安裝zookeeper前需要確保下面的環境配置成功,具體可以參考大…

AI智能剪輯發展到哪種地步?來看看云微客就知道了!

不是短視頻團隊招不起,而是矩陣賬號更有性價比。企業做短視頻有反思過為什么干不過同行嗎?我們來看看大佬是怎么做的。云微客AI智能剪輯系統用幾百個賬號做矩陣布局,系統每天自動進行批量剪視頻、寫文案、一鍵自動化發布視頻,一個…

[漏洞復現] MetInfo5.0.4文件包含漏洞

[漏洞復現] MetInfo5.0.4文件包含漏洞 MetInfo5.0.4 漏洞代碼審計 漏洞出現在about/index.php中,因為利用了動態地址,所以存在漏洞。 漏洞檢查語句(!192.168.109.100是我的服務器ip,需要換成自己的)&…

華為倉頡語言體驗:一個簡單的socket服務端實現

前言 由于倉頡目前是內測狀態, 不能展示倉頡的詳細信息,但是華為倉頡官網的公共文檔的內容是可以公開的。 我相信有不少喜歡編程的朋友都申請了內測,但是一些編程初學者應該和我一樣,處于摸索階段。所以,我這里把我測…

排序題目:多數元素 II

文章目錄 題目標題和出處難度題目描述要求示例數據范圍進階 前言解法一思路和算法代碼復雜度分析 解法二思路和算法代碼復雜度分析 解法三思路和算法代碼復雜度分析 題目 標題和出處 標題:多數元素 II 出處:229. 多數元素 II 難度 3 級 題目描述 …

css高度0到高度auto,過渡的設置

1.css從高度0到高度auto,過渡設置 方法(vue代碼) 你可以通過設置transform: scale(0);到 transform: scale(1); 來實現,具體代碼 你也可以通過設置transform: scaleY(0);到 transform: scaleY(1); 這兩種展示的效果不一樣,你可以看看你喜歡那種 // css代碼// 原來的css類 .s…

港口危險貨物安全管理人員考試題庫(含答案)

一、單選題 1.化學品安全標簽內容中警示詞有( )種分別進行危害程度的警示。 A、3 B、4 C、5 參考答案:A 2.運輸放射性物品,應當使用( )的放射性物品運輸包裝容器(以下簡稱運輸容器)。 A、專業 B、專用 C、統一 D、定制 參考答案:B 3.庫區儀表及計算機監控管理系…

中電金信:金Gien樂道 | 6月熱門新聞盤點 回顧這一月的焦點事件

“以檢之力 e企守護”——上海市檢一分院與中電金信開展聯學聯建 6月24日,上海市人民檢察院第一分院與中電金信數字科技集團股份有限公司聯合開展“以檢之力 e企守護”聯學聯建活動。雙方共同參觀了全國檢察機關證券期貨犯罪辦案基地和重大職務犯罪案件辦理&#xf…

HTML5與3D打印:探索網頁內容的物理化可能

隨著科技的飛速發展,互聯網與物理世界的交匯點日益增多。HTML5作為當前網頁開發的主流標準,不僅推動了網頁內容的豐富性和互動性,還在與3D打印技術的結合中,展現出了將網頁內容物理化的巨大潛力。本文將探討HTML5與3D打印的結合點…

C++ 中的數據類型

C規定在創建一個變量或者常量時,必須要指定出相應的數據類型,否則無法給變量分配內存. 1 整型 作用:整型變量表示的是整數類型的數據 C中能夠表示整型的類型有以下幾種方式,區別在于所占內存空間不同: 數據類型占用…

python(6)numpy的使用詳細講解

在numpy中,最基本的數據結構是數組,因此我們首先需要了解如何創建一個數組。numpy提供了多種數組創建方法,包括從列表或元組創建、從文件中讀取數據、使用特定函數創建等。下面是一些常用的創建方法: 一、創建數組 1. 從列表或元…

【MySQL備份】Percona XtraBackup基礎篇

目錄 1.關于Percona XtraBackup 2. Percona XtraBackup有哪些特點? 3.安裝Percona XtraBackup 3.1.環境信息 3.2.安裝步驟 4. xtrabackup內部流程圖 5.Percona XtraBackup基礎語法 5.1.全量備份 5.2.增量備份 5.2.1.基于全量備份的增量備份 5.2.2.基于前…

[leetcode]max-consecutive-ones 最大連續1的個數

. - 力扣&#xff08;LeetCode&#xff09; class Solution { public:int findMaxConsecutiveOnes(vector<int>& nums) {int maxCount 0, count 0;int n nums.size();for (int i 0; i < n; i) {if (nums[i] 1) {count;maxCount max(maxCount, count);} else…

安裝和微調大模型(基于LLaMA-Factory)

打開終端&#xff08;在Unix或macOS上&#xff09;或命令提示符/Anaconda Prompt&#xff08;在Windows上&#xff09;。 創建一個名為lora的虛擬環境并指定Python版本為3.9。 conda create --name lora python3.9激活新創建的虛擬環境。 conda activate lora克隆項目。 git …