postgresql高階應用之合併單元格

funnyZpC發表於2021-05-07
SQL

postgresql高階應用之合併單元格

轉載請註明出處https://www.cnblogs.com/funnyzpc/p/14732172.html

1.寫在前面✍

繼上一篇postgresql高階應用之行轉列&彙總求和之後想更進一步做點兒複雜的(圖表暫且不論哈?)，當然作為報表，出現最多的無非就是合併單元格了，是的，我已經迫不及待啦?~

2.思考

首先，我們的腦海中應該有一個對前端table有一個大致的瞭解， 當然這對非前端的同學十分的不友好，如果您嘗試閲讀以下內容存在困難的話(前端html、javascript) 可就此打住哈。。。
enn...,讓我先稍稍解釋下前端 html 的表格格式吧?

2.1 前端html->table基本結構

先給出一個十分base的html demo.html

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8" />
	<title>demo</title>
</head>
<body>
  <!-- 這裡定義了兩個屬性 border:定義表格邊框 cellpadding:定義單元格大小 -->
  <table border="3"  cellpadding="8">
	<thead>
		<tr>
			<th>表頭1</th><th>表頭2</th><th>表頭3</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>第一行第1個</td><td>第一行第2個</td><td>第一行第3個</td>
		</tr>
		<tr>
			<!-- 使用colspan屬性進行橫向合併，橫向被合併的單元格位置需要騰出來 -->
			<!-- 以下橫向合併兩個單元格，所以第二個td標簽就不要寫了，否則會溢位哦~ -->
			<td colspan="2">橫向合併了兩個單元格</td><td>第二行第3個</td>
		</tr>
		<tr>
			<td>第三行第1個</td><td>第三行第2個</td><td>第三行第3個</td>
		</tr>
		<tr>
			<!-- 使用rowspan屬性進行縱向合併，縱向合併的(跨越的)單元格位置需要騰出來 -->
			<!-- 以下縱向合併三個個單元格(在本行最後一個標簽)，所以下兩行的最後兩個td標簽就不要寫啦~，否則同樣會溢位哦~ -->
			<td>第四行第1個</td><td>第四行第2個</td><td rowspan="3">縱向合併了三個單元格</td>
		</tr>
		<tr>
			<td>第五行第1個</td><td>第五行第2個</td>
		</tr>
		<tr>
			<td>第六行第1個</td><td>第六行第2個</td>
		</tr>
	</tbody>
  </table>
</body>
</html>

瀏覽器渲染出來(使用瀏覽器開啟html檔案)的樣子是這樣的~

以上總結就是colspan實現橫向合併單元格，rowspan實現縱向合併單元格~

呃嗯，既然我們知道了html需要這兩個屬性值(也就是合併的行數或合併的列數),那麼就是要在sql中生成這兩個引數值然後提供給前端的同學使用哈，這是淺層意思，那麼深層意思是什麼呢？？？容我想想看。。。

  • 對於橫向合併單元格

    需要使用 case+when+then 語句判斷是否需要橫向合併(重要的是要給出橫向合併的數值)，這樣想是合理的，可能造成的困擾可能是這樣做會造成sql冗餘（當然也是不得已而為之），當然本節就不再講橫向合併單元格啦

  • 對於縱向合併單元格

    step1.? 如果使用聚合+視窗函式來計算需要合併的相同的列數，可能造成的問題是生成的rowspan對於相同列來説數值是一樣的（如下圖），這樣不可以欸~

    step2.? 。。。既然可以通過step1生成視窗內合併總數的數值，當然也可以通過視窗函式來生成一個倒排序列的列,哈哈?，你似乎發現了什麼~~~，對，將視窗合併總數的列與視窗內倒排序的列做等值判斷，相等的不就是第一個合併數字列了。。。bingo
    倒排序的視窗列

    求總的列+倒排序的列

    step3.? 既然我們能做一個資料列的合併，也能做兩個列的合併(也可以是一個二級列，注意 order by 對合併行的影響哦），這裡簡單各一個經過層層包裝後的合併數值列生成，注意下圖的綠色部分哦?

    光説不練假把式，通過一下測試指令碼試試囖?~

2.2表結構

drop table if EXISTS  report2 ;
CREATE TABLE report2 (
  "id" varchar(10) primary key,
  "name" varchar(50),
  "price" numeric,
  "level2" varchar(50) ,
  "level1" varchar(50)
);

2.3表欄位註釋

欄位 註釋
id 主鍵
name 商品名稱
price 價格
level2 二級分類
level1 一級分類

2.4表資料

INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0015', '洗髮露', '36', '洗護', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0008', '香皂', '17.5', '洗護', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0007', '薯條', '7.5', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0009', '方便麵', '3.5', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0004', '辣條', '5.6', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0006', 'iPhone X', '9600', '小電器', '電器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0003', '手錶', '1237.55', '小電器', '電器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0012', '電視', '3299', '大電器', '電器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0016', '洗衣機', '4999', '大電器', '電器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0013', '圍巾', '93', '配飾', '服裝配飾');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0017', '特步涼鞋', '499', '鞋子', '服裝配飾');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0001', 'NIKE新款鞋', '900', '鞋子', '服裝配飾');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0002', '外套', '110.9', '上衣', '服裝配飾');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0014', '作業本', '1', '紙張', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0005', '鉛筆', '7', '筆', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0010', '水杯', '27', '餐飲', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0011', '毛巾', '15', '洗護', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0018', '繪圖筆', '15', '筆', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0019', '汽水', '3.5', '其它', '零食');

3.?結果集最終求解

select 
  t1.*,
  case when  t_rank=t_count then t_count else null end as level1_row,
  case when  tu_rank=tu_count then tu_count else null end as level2_row
from 
(
    select 
      *,
      row_number() over(PARTITION by level1 order by level1 asc) t_rank,
      count(1) over (partition by level1) t_count,
      row_number() over(PARTITION by level1,level2 order by level1,level2 asc) tu_rank,
      count(1) over (partition by level1,level2) tu_count
    from report2 order by level1
) t1 order by t1.level1,t_rank desc,t_count desc,tu_rank desc,tu_count desc;

_紅色_部分即為前端童鞋需要的合併數值哈?~

如果你能看懂以上問題及求解的 sql ,恭喜你又升級啦?

總結下::對問題的分析✨以及對問題求解的思考?很重要嘛，當然還包含對postgresql所提供工具的靈活使用 ? 總會產生意想不到的驚喜，哈哈?~

相關文章