欧美精品久久久久久久,国产精品久久久久久久午夜,亚洲婷婷影院

主頁 > 知識庫 > Mysql縱表轉換為橫表的方法及優化教程

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 PageInfoAtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
    //1、獲取工模老化字段模板
    LambdaQueryWrapperAtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
    queryWrapper.eq(AtsItemFieldPo::getItemCode, AtsItemCodeConstant.GONGMO_AGING.getCode());
    ListAtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
    //2、組裝查詢條件
    ListString> 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());
    ListMapString, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = new PageInfo(dataList);
    //5、組裝返回結果
    ListAtsAgingItemRecordVo> recordVoList = null;
    if (!CollectionUtils.isEmpty(dataList)) {
        recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(recordVoList);
    return pageInfo;
}

優化后的Dao層，代碼如下：

public interface AtsItemRecordDao extends BaseMapperAtsItemRecordPo> {
 
    ListMapString, 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	更新時間