postgresql高级应用之合并单元格
- 2021 年 5 月 7 日
- 笔记
- DB技术及相关, PostgreSQL
postgresql高级应用之合并单元格
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所提供工具的灵活使用 👉 总会产生意想不到的惊喜，哈哈😘~
