(二)《SQL進階教程》學習記錄--GROUP BY、PARTITION BY

冬先生發表於2021-11-08
SQL

(一) 《SQL進階教程》學習記錄--CASE
(二) 《SQL進階教程》學習記錄--GROUP BY、PARTITION BY

1、語法

GROUP BY <列名1>, <列名2>, <列名3>，根據列分組，通常配合聚合函式用於一些統計的需求。
例1：統計成績

-- 各科目最高分
select subject,max(fraction) from study group by subject
-- 各科目平均分
select subject,round(avg(fraction),2) from study group by subject

下面簡豐富一下表結構改名fraction，插入兩期成績例2：取兩期成績相同
配合HAVING，找兩期成績一樣的同學、科目。

select username,subject,fraction from fraction GROUP BY username,subject,fraction HAVING count(1)>1

2、關聯子查詢

通常例1無法滿我們的需求，還要知道最高分是誰，哪一期成績等，包括前三、前十等需求
例3：取各科目最高、前三

--各科最高
select * from fraction f1 where fraction = (select max(fraction) from fraction f2 where f1.subject = f2.subject) 
--各科前三
select * from fraction f where 3 > (select count(*) from fraction where subject = f.subject and fraction>f.fraction ) order by subject,fraction desc;

看到結果條數好像不太對，是因為同分的也查了出來，這是其一，還有另一個問題，寫起where條件也麻煩，比如只查第一期成績。

select * from fraction f where 3 > (select count(*) from fraction where subject = f.subject and fraction>f.fraction and createtime='2021-6-30' ) and createtime='2021-6-30'  order by subject,fraction desc;

實際業務、表結構都要比例子要複雜的多，sql語句會變得無比冗長，而且子查詢還有效能問題。

3、PARTITION BY

GROUP BY 和 PARTITION BY 都可以根據指定的列為表分組，區別在於 GROUP BY 在分組之後會把每個分組聚合成一行資料。partition by 通常會和 ROW_NUMBER()、RANK() 等視窗函式配合使用。
例4：各種分組、排名、排序
三個視窗函式，選擇合適的完成需求，分組、排序、where條件寫起來方便簡單，邏輯清晰。rank,dense_rank的區別是一個跳號，一個不跳號。

select *,ROW_NUMBER() OVER(PARTITION BY subject ORDER BY subject,fraction desc) rownumber,
	 RANK() OVER(PARTITION BY subject  ORDER BY subject,fraction desc) rank ,
	 DENSE_RANK() OVER(PARTITION BY subject ORDER BY subject,fraction desc) denserank from fraction where createtime='2021-6-30'

4、本文Get小技巧

（1）、欄位拼接

直接使用 || 合併， 例如 SELECT username || subject || '成績 : ' || fraction as info FROM study
或者函式concat_ws，例如 SELECT concat_ws('',username,subject,'成績 : ',fraction) as info FROM study where fraction=100
效果相同

（2）、迴圈

比如測試插入表資料，簡單的迴圈需求，可以用函式generate_series返回一個陣列，不用儲存過程和遊標。
例如：INSERT INTO study_test SELECT '體測', '張三', round(random()::numeric,2) * 100 FROM generate_series(1,10)

相關文章