Mysql纵表转换为横表的方法及优化教程

1、纵表与横表

纵表:表中字段与字段的值采用key—value形式,即表中定义两个字段,其中一个字段里存放的是字段名称,另一个字段中存放的是这个字段名称代表的字段的值。

例如,下面这张ats_item_record表,其中field_code表示字段,后面的record_value表示这个字段的值

优缺点:

横表:表结构更加的清晰明了,关联查询的一些sql语句也更容易,方便易于后续开发人员的接手,但是如果字段不够,需要新增字段,会改动表结构。

纵表:扩展性更高,如果要增加一个字段,不需要改变表结构,但是一些关联查询会更加麻烦,也不便于维护与后续人员接手。

平常开发,尽量能用横表就不要用纵表,维护成本比较高昂,而且一些关联查询也很麻烦。

2、纵表转换为横表

(1)第一步,我们先把这些字段名以及相应字段的值从纵表中取出来

select r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time, r.updated_time updated_time,
(case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end ) accumulated_cooking_time,
(case r.field_code when 'data_version' then r.record_value else '' end) data_version,
(case r.field_code when 'loop_num' then r.record_value else '' end) loop_num,
(case r.field_code when 'status' then r.record_value else '' end) status
from ats_item_record r
where item_code = 'GONGMO_AGING'

结果:

通过 case 语句,成功把字段从纵表中取出,但是此时仍算不上一个横表,我们这里的original_record_id 是记录同一行数据的唯一ID,我们这里可以通过这个字段把上面这四行合成一行记录。

注意:这里需要取出每一个字段,都要case一下,有多少个字段,就需要多少次case语句。因为一个case语句,遇到符合条件的when语句之后,后面的会不再执行。

(2)分组,合并相同行,生成横表

select * from (
	select r.original_record_id,
    max(r.did) did,
    max(r.device_sn) device_sn,
    max(r.mac_address) mac_address,
    max(r.record_time) record_time,
	max(r.updated_time) updated_time,
	max((case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end )) accumulated_cooking_time,
	max((case r.field_code when 'data_version' then r.record_value else '' end)) data_version,
	max((case r.field_code when 'loop_num' then r.record_value else '' end)) loop_num,
	max((case r.field_code when 'status' then r.record_value else '' end)) status
	from ats_item_record r
	where item_code = 'GONGMO_AGING'
	group by r.original_record_id
) m order by m.updated_time desc;

查询的结果:

注意:这里采用group by 分组的时候,需要给字段加上max函数。用group by 分组的时候,一般搭配聚合函数使用,常见的聚合函数:

  • AVG() 求平均数
  • COUNT() 求列的总数
  • MAX() 求最大值
  • MIN() 求最小值
  • SUM() 求和

大家注意一下,我把纵表同一条记录的公共字段 r.original_record_id 放到了group by里面,这个字段在纵表中同一条记录相同、唯一,且永远不会改变(相当于以前横表的主键ID),然后把其他字段放到 max 中(因为其他字段要么是相同的,要么是取最大的就可以,要么是只有一个纵表记录有数值其他记录为空,所以这三种情况都可以直接用max),四条记录取最大的更新时间作为同一条记录的更新时间,在逻辑上也是合适的。然后我们把纵表字段 field_code 和 record_value 做了 max() 操作,因为同一条记录里面他们都是唯一存在的,不会发生同一条数据有两个相同的 field_code 记录,所以这样做 max() 也是没有任何问题的。

优化点:

最后这个SQL是可以优化一下的,我们可以把模板字段(r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time 等),从专门存放模板字段表中全部取出来(同一个逻辑纵表的字段全部取出),然后再代码里面拼接好我们的 max() 部分,作为参数拼接进去执行,这样可以做到通用,每次如果新增加模板字段,我们不需要更改这个SQL语句了(中国移动他们存放手机的参数数据就是这么干的)。

优化后的业务层(组装 SQL 模板的代码),代码如下:

@Override
public PageInfo<AtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
    //1、获取工模老化字段模板
    LambdaQueryWrapper<AtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
    queryWrapper.eq(AtsItemFieldPo::getItemCode, AtsItemCodeConstant.GONGMO_AGING.getCode());
    List<AtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
    //2、组装查询条件
    List<String> tplList = Lists.newArrayList(), conditionList = Lists.newArrayList(), validList = Lists.newArrayList();
    if (!CollectionUtils.isEmpty(fieldPoList)) {
        //3、组装动态max查询字段
        for (AtsItemFieldPo itemFieldPo : fieldPoList) {
            tplList.add("max((case r.field_code when '" + itemFieldPo.getFieldCode() + "' then r.record_value else '' end )) " + itemFieldPo.getFieldCode());
            validList.add(itemFieldPo.getFieldCode());
        }
        qo.setTplList(tplList);
        //4、组装动态where查询条件
        if (StringUtils.isNotBlank(qo.getDid())) {
            conditionList.add("AND did like CONCAT('%'," + qo.getDid() + ",'%')");
        }
        if (validList.contains("batch_code") && StringUtils.isNotBlank(qo.getBatchCode())) {
            conditionList.add("AND batch_code like CONCAT('%'," + qo.getBatchCode() + ",'%')");
        }
        qo.setConditionList(conditionList);
    }
    qo.setItemCode(AtsItemCodeConstant.GONGMO_AGING.getCode());
    //4、获取老化自动化测试项记录
    PageHelper.startPage(qo.getPageNo(), qo.getPageSize());
    List<Map<String, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = new PageInfo(dataList);
    //5、组装返回结果
    List<AtsAgingItemRecordVo> recordVoList = null;
    if (!CollectionUtils.isEmpty(dataList)) {
        recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(recordVoList);
    return pageInfo;
}

优化后的Dao层,代码如下:

public interface AtsItemRecordDao extends BaseMapper<AtsItemRecordPo> {

    List<Map<String, Object>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

优化后的SQL语句,代码如下:

<select id="selectItemRecordListByCondition" resultType="java.util.HashMap"
        parameterType="com.galanz.iot.ops.restapi.model.qo.AtsItemRecordQo">
    SELECT * FROM (
        SELECT r.original_record_id id,
        max(r.did) did,
        max(r.device_sn) device_sn,
        max(r.updated_time) updated_time,
        max(r.record_time) record_time,
        <if test="tplList != null and tplList.size() > 0">
            <foreach collection="tplList" item="tpl" index="index" separator=",">
                ${tpl}
            </foreach>
        </if>
        FROM ats_item_record r
        WHERE item_code = #{itemCode}
        GROUP BY r.original_record_id
    ) m
    <where>
        <if test="conditionList != null and conditionList.size() > 0">
            <foreach collection="conditionList" item="condition" index="index">
                ${condition}
            </foreach>
        </if>
    </where>
    ORDER BY m.updated_time DESC
</select>

模板字段表结构(ats_item_field 表),如下所示:

字段名 类型 长度 注释
id bigint 20 主键ID
field_code varchar 32 字段编码
field_name varchar 32 字段名称
remark varchar 512 备注
created_by bigint 20 创建人ID
created_time datetime 0 创建时间
updated_by bigint 20 更新人ID
updated_time datetime 0 更新时间

记录表结构(ats_item_record 表),如下所示:

字段名 类型 长度 注释
id bigint 20 主键ID
did varchar 64 设备唯一ID
device_sn varchar 32 设备sn
mac_address varchar 32 设备Mac地址
field_code varchar 32 字段编码
original_record_id varchar 64 原始记录ID
record_value varchar 32 记录值
created_by bigint 20 创建人ID
created_time datetime 0 创建时间
updated_by bigint 20 更新人ID
updated_time datetime 0 更新时间

注:original_record_id 是纵转横表后,每条记录的唯一ID,可以看做我们普通横表的主键ID一样的东西

到此 Mysql 纵表转换为横表介绍完成。

总结

到此这篇关于Mysql纵表转换为横表的文章就介绍到这了,更多相关Mysql纵表转换为横表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL横纵表相互转化操作实现方法

    本文实例讲述了MySQL横纵表相互转化操作实现方法.分享给大家供大家参考,具体如下: 先创建一个成绩表(纵表) create table user_score ( name varchar(20), subjects varchar(20), score int ); insert into user_score(name,subjects,score) values('张三','语文',60); insert into user_score(name,subjects,score) value

  • Mysql纵表转换为横表的方法及优化教程

    1.纵表与横表 纵表:表中字段与字段的值采用key-value形式,即表中定义两个字段,其中一个字段里存放的是字段名称,另一个字段中存放的是这个字段名称代表的字段的值. 例如,下面这张ats_item_record表,其中field_code表示字段,后面的record_value表示这个字段的值 优缺点: 横表:表结构更加的清晰明了,关联查询的一些sql语句也更容易,方便易于后续开发人员的接手,但是如果字段不够,需要新增字段,会改动表结构. 纵表:扩展性更高,如果要增加一个字段,不需要改变表结

  • SQL Server纵表与横表相互转换的方法

    1,纵表转横表 纵表结构 Table_A: 转换后的结构: 纵表转横表的SQL示例: SELECT Name , SUM(CASE WHEN Course = N'语文' THEN Grade ELSE 0 END) AS Chinese , SUM(CASE WHEN Course = N'数学' THEN Grade ELSE 0 END) AS Mathematics , SUM(CASE WHEN Course = N'英语' THEN Grade ELSE 0 END) AS Engl

  • postgresql数据库 timescaledb 时序库 把大数据量表转换为超表的问题

    前言 这几天工作的时候发现在 timescaledb 时序库 中有部分大数据量的表不是超表,估计是当时建库的时候没有改 影响插入,查询效率 ,因此需要改成超表 因工作原因 部分内容做保密处理了 一 创建新表 首先因为在 timescaledb 时序库 中创建超表必须是要没有数据的表 因此第一步是 创建一张跟原表一模一样的表(表名后面加个_cs) 你可以直接copy建表语句,也可以直接用工具复制表结构 二 把新表改为超表 把新建的表改为超表,7天一分区 --我是7天一分区 SELECT creat

  • MySQL百万级数据量分页查询方法及其优化建议

    数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景: 适用于数据量较少的情况(元组百/千级) 原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余

  • SQL如何实现横表与纵表相互转换

    目录 一.横表简单概述 二.纵表简单概述 三.纵表转横表代码如下以及视图展示 四.横表转纵表代码如下以及视图展示 五.横表.纵表优点与缺点 六.总结 一.横表简单概述 横表是普通的建表方式.例如:表结构为主键.字段1.字段2.字段3…. 二.纵表简单概述 纵表的表结构为主键.字段代码.字段值,字段代码则为字段1.字段2.字段3…. 三.纵表转横表代码如下以及视图展示 1.纵表视图如下: 2.纵表转换成横表视图如下: 3.sql语句如下所示: select student_name, sum(ca

  • 利用Python的pandas数据处理包将宽表变成窄表

    目录 前言 1.引入包 3.关键操作,将宽表转换为窄表 4.对空值进行处理 5.导出存储到Excel中 前言 工作中经常会使用到将宽表变成窄表,例如这样的形式 编号 编码 单位1 单位2 单位3 单位4 ... ... ... ... ... ...       1 编码1... 数量... 数量... 数量... 数量... ... ... ... ... ... ...       2 编码2... 数量... 数量... 数量... 数量... ... ... ... ... ... ..

  • mysql表名忽略大小写配置方法详解

    linux下mysql默认是要区分表名大小写的.mysql是否区分大小写设置是由参数lower_case_table_names决定的,其中: 1)lower_case_table_names = 0  区分大小写(即对大小写不敏感),默认是这种设置.这样设置后,在mysql里创建的表名带不带大写字母都没有影响,都可以正常读出和被引用. 2)lower_case_table_names = 1  不区分大小写(即对大小写敏感).这样设置后,表名在硬盘上以小写保存,MySQL将所有表名转换为小写存

  • php列出mysql表所有行和列的方法

    本文实例讲述了php列出mysql表所有行和列的方法.分享给大家供大家参考.具体实现方法如下: 复制代码 代码如下: <html> <head> <title>Selecting Data</title> </head> <body> <?php $user = "root"; $pass = ""; $db = "mydatabase"; $link = mysql_

  • MySQL实现快速删除所有表而不删除数据库的方法

    本文实例讲述了MySQL实现快速删除所有表而不删除数据库的方法.分享给大家供大家参考,具体如下: 如果直接使用phpmyadmin操作的话肯定非常简单,勾选数据表->点击删除->点击确定,操作完毕! 这里介绍一下快速删除数据表的SQL命令操作方法. 删除表的命令: drop table 表名; 如果有200张表,执行200次,想想就不想动手了. 下面提供一个使用information_schema库的方案: 复制代码 代码如下: SELECT CONCAT('drop table ',tabl

  • MySQL快速复制数据库数据表的方法

    某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库.使用以下方法,可以非常简单地实现. 假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb.步骤如下: 1. 首先创建新的数据库newdb #mysql -u root -ppassword mysql>CREATE DATABASE `newdb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; 2. 使用mysqldump及mysql的

随机推荐