(一) 《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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/353223.html
標籤：PostgreSQL
上一篇：pgpool-II 入門教程
下一篇：oracle總結