Mysql vertical table conversion to horizontal table method and optimization tutorial

1. Vertical table and horizontal table

Vertical table: The fields and field values ​​in the table are in key-value format, that is, two fields are defined in the table, one of which stores the field name, and the other field stores the value of the field represented by this field name.

For example, in the following ats_item_record table, field_code represents the field, and the following record_value represents the value of this field.

Pros and Cons:

Horizontal table: The table structure is clearer and some SQL statements for associated queries are easier, which is convenient for subsequent developers to take over. However, if there are not enough fields and new fields need to be added, the table structure will be changed.

Vertical table: It has higher scalability. If you want to add a field, you don’t need to change the table structure. However, some related queries will be more troublesome and inconvenient for maintenance and follow-up personnel.

In normal development, try to use horizontal tables instead of vertical tables. The maintenance cost is relatively high, and some related queries are also troublesome.

2. Convert a vertical table to a horizontal table

(1) The first step is to extract these field names and corresponding field values ​​from the vertical table.

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'

result:

Through the case statement, the field is successfully taken out from the vertical table, but it is still not a horizontal table at this time. The original_record_id here is the unique ID that records the same row of data. We can use this field to combine the above four rows into one row of records.

Note: Here you need to extract each field and do a case check. You need as many case statements as there are fields. Because a case statement, after encountering a when statement that meets the conditions, the subsequent ones will no longer be executed.

(2) Group, merge identical rows, and generate a horizontal table

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;

The results of the query:

Note: When using group by, you need to add the max function to the field. When using group by, it is usually used with aggregate functions. Common aggregate functions are:

• AVG() finds the average
• COUNT() finds the total number of columns
• MAX() Find the maximum value
• MIN() Find the minimum value
• SUM()
  

Please note that I put the common field r.original_record_id of the same record in the vertical table into the group by. This field is the same and unique for the same record in the vertical table and will never change (equivalent to the primary key ID of the previous horizontal table). Then I put the other fields into max (because the other fields are either the same, or the largest one can be taken, or only one vertical table record has a value and the other records are empty, so max can be used directly in these three cases). It is logically appropriate to take the largest update time of the four records as the update time of the same record. Then we perform the max() operation on the vertical table fields field_code and record_value. Because they are unique in the same record, there will not be two identical field_code records in the same data, so there is no problem with doing max() in this way.

Optimization points:

Finally, this SQL can be optimized. We can take out all the template fields (r.original_record_id, r.did, r.device_sn, r.mac_address, r.record_time, etc.) from the table that specifically stores template fields (all fields in the same logical vertical table are taken out), and then splice our max() part in the code as a parameter to execute. This way, we can make it universal. Every time we add a new template field, we don't need to change the SQL statement (this is how China Mobile stores mobile phone parameter data).

The optimized business layer (code for assembling SQL templates) is as follows:

@Override
public PageInfo<AtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
    //1. Get the template of the aging field of the model LambdaQueryWrapper<AtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
    queryWrapper.eq(AtsItemFieldPo::getItemCode, AtsItemCodeConstant.GONGMO_AGING.getCode());
    List<AtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
    //2. Assemble query conditions List<String> tplList = Lists.newArrayList(), conditionList = Lists.newArrayList(), validList = Lists.newArrayList();
    if (!CollectionUtils.isEmpty(fieldPoList)) {
        //3. Assemble dynamic max query field 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. Assemble dynamic where query conditions 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. Get the aging automation test item record PageHelper.startPage(qo.getPageNo(), qo.getPageSize());
    List<Map<String, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = new PageInfo(dataList);
    //5. Assemble the returned results List<AtsAgingItemRecordVo> recordVoList = null;
    if (!CollectionUtils.isEmpty(dataList)) {
        recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(recordVoList);
    return pageInfo;
}

The optimized Dao layer code is as follows:

public interface AtsItemRecordDao extends BaseMapper<AtsItemRecordPo> {
 
    List<Map<String, Object>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

The optimized SQL statement code is as follows:

<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
    )
    <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>

The template field table structure (ats_item_field table) is as follows:

The record table structure (ats_item_record table) is as follows:

Note: original_record_id is the unique ID of each record after the vertical table is converted to the horizontal table. It can be regarded as the same as the primary key ID of our ordinary horizontal table.

This concludes the introduction to converting a MySQL vertical table to a horizontal table.

Summarize

This is the end of this article about converting MySQL vertical table to horizontal table. For more information about converting MySQL vertical table to horizontal table, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!