mysql两个重要技巧：分列与合并、开窗与取值

mysql两个重要技巧：分列与合并、开窗与取值

1 分列与合并

1.1 场景1介绍

​ 在工作中，遇到生产表的记录字段信息合并于一个字段的情况，需要分字段，如图1所示；也会遇到相同生产表的字段分散在同一列不同行中，需要合并字段，如图2所示，对于这两种情况，这里记录操作记录。

图一

图二

1.2 操作记录

当在工作中，遇到生产表的记录字段信息合并于一个字段的情况，需要分字段时，实例表结构如下

CREATE TABLE `dz_sjjl_table` (
  `id` int NOT NULL COMMENT '主键',
  `table` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据记录表',
  `columns` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '记录项名称',
  `comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

​ 切分字段，操作如下，切分效果如图二所示。

SELECT 
	id, 
	`table`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tb.`columns`,',',b.help_topic_id + 1),',',- 1) AS `columns`
FROM
    (SELECT * FROM dz_sjjl_table) tb
    JOIN mysql.help_topic b ON b.help_topic_id <
    (LENGTH(tb.`columns`) - LENGTH(REPLACE(tb.`columns`, ',', '')) + 1)
 ;

合并字段，也会遇到相同生产表的字段分散在同一列不同行中，需要合并字段，如图2所示,合并结果如图一。

select 
	`table`,
	GROUP_CONCAT(`columns` order by auto_id separator ',') `columns`
from
	dz_sjjl_colunms
group by `table` ;

2 开窗与取值

2.1 场景2介绍

​ 在工作中，比如进行了数据治理，表字段有序号、部门、中文表名、英文表名、字段、指标、记录数，符合指标记录数、指标合格率等，实例表如下图所示；然后首次治理和再次治理的比较，要求：治理前检测数、最小合格率、最大合格率、平均合格率、治理后检测数、最小合格率、最大合格率、平均合格率。

2.2 操作记录

​ 这里在mysql 8.0中进行开窗计算，满足英文表名为’ltdgb’,‘ltxsb’,‘ltqqb’ 的数据，运行sql后，结果如下图所示

-- create view view_sheet3 as
SELECT 
    序号,
    部门,
    中文表名,
    英文表名,
    字段,
    指标,
    记录数,
    符合指标记录数,
    指标合格率,
    治理后记录数,
    治理后符合指标记录数,
	max(记录数) over w as 治理前记录数,
	min(指标合格率) over w as 最小合格率_b,
	max(指标合格率) over w as 最大合格率_b,
	avg(指标合格率) over w as 平均合格率_b,
	max(治理后记录数) over w as 治理后检测数,
	min( 治理后符合指标记录数/治理后记录数) over w as 最小合格率_a,
	max( 治理后符合指标记录数/治理后记录数) over w as 最大合格率_a,
	avg( 治理后符合指标记录数/治理后记录数) over w as 平均合格率_a
FROM `sheet3`
where 英文表名 in （ 'ltdgb','ltxsb','ltqqb' ）
WINDOW w as (partition by 英文表名 order by 序号 asc);

由以上结果统计，出表的数据则需要取最值，结果如下图

select 
	t1.* 
from view_sheet3  t1
left join (
		select * from view_sheet3 a
		where 序号 = (select max(序号) from view_sheet3 b where a.英文表名 = b.英文表名)
) t2
on t1.序号 = t2.序号 and t1.英文表名 = t2.英文表名
where t2.序号 is not null
;