Konvertierung einer vertikalen MySQL-Tabelle in eine horizontale Tabelle und Tutorial zur Optimierung

1. Vertikaler Tisch und horizontaler Tisch

Vertikale Tabelle: Die Felder und Feldwerte in der Tabelle liegen im Schlüssel-Wert-Format vor, d. h. in der Tabelle sind zwei Felder definiert, von denen eines den Feldnamen speichert und das andere Feld den Wert des durch diesen Feldnamen dargestellten Feldes.

Beispielsweise stellt in der folgenden ats_item_record-Tabelle field_code das Feld dar und der folgende record_value den Wert dieses Felds.

Für und Wider:

Horizontale Tabelle: Die Tabellenstruktur ist übersichtlicher und einige SQL-Anweisungen für zugehörige Abfragen sind einfacher, was eine bequeme Übernahme durch nachfolgende Entwickler ermöglicht. Wenn jedoch nicht genügend Felder vorhanden sind und neue Felder hinzugefügt werden müssen, wird die Tabellenstruktur geändert.

Vertikale Tabelle: Sie ist besser skalierbar. Wenn Sie ein Feld hinzufügen möchten, müssen Sie die Tabellenstruktur nicht ändern. Einige damit verbundene Abfragen sind jedoch für das Wartungs- und Folgepersonal mühsamer und unbequemer.

Versuchen Sie bei der normalen Entwicklung, horizontale Tabellen anstelle von vertikalen Tabellen zu verwenden. Die Wartungskosten sind relativ hoch und einige zugehörige Abfragen sind ebenfalls problematisch.

2. Konvertieren Sie eine vertikale Tabelle in eine horizontale Tabelle

(1) Der erste Schritt besteht darin, diese Feldnamen und die entsprechenden Feldwerte aus der vertikalen Tabelle zu extrahieren.

Wählen Sie r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time, r.updated_time aktualisierte_Zeit,
(Fall r.Feldcode wenn 'akkumulierte Kochzeit' dann r.Datensatzwert sonst ''Ende) akkumulierte Kochzeit,
(Fall r.Feldcode wenn 'Datenversion' dann r.Datensatzwert sonst 'Ende') Datenversion,
(Fall r.Feldcode wenn 'loop_num' dann r.Datensatzwert sonst ''Ende) loop_num,
(Fall r.field_code wenn 'status' dann r.record_value sonst '' Ende) Status
von ats_item_record r 
wobei item_code = 'GONGMO_AGING'

Ergebnis:

Durch die Case-Anweisung wird das Feld erfolgreich aus der vertikalen Tabelle entfernt, aber es ist zu diesem Zeitpunkt noch keine horizontale Tabelle. Die original_record_id ist hier die eindeutige ID, die dieselbe Datenzeile aufzeichnet. Wir können dieses Feld verwenden, um die obigen vier Zeilen zu einer Datensatzzeile zu kombinieren.

Hinweis: Hier müssen Sie jedes Feld extrahieren und eine Fallprüfung durchführen. Sie benötigen so viele Fallanweisungen wie Felder vorhanden sind. Denn wenn eine Case-Anweisung auf eine When-Anweisung trifft, welche die Bedingungen erfüllt, werden nachfolgende Anweisungen nicht mehr ausgeführt.

(2) Gruppieren, Zusammenführen identischer Zeilen und Erstellen einer horizontalen Tabelle

Wählen Sie * aus (
	wählen Sie r.original_record_id,
    max(r.did) hat,
    max(r.Geräte-SN) Geräte-SN,
    max(r.mac_address) mac_address,
    max(r.Aufzeichnungszeit) Aufzeichnungszeit,
	max(r.Aktualisierungszeit) Aktualisierungszeit,
	max((Fall r.Feldcode wenn 'akkumulierte Kochzeit' dann r.Datensatzwert sonst '' Ende )) akkumulierte Kochzeit,
	max((Fall r.Feldcode wenn 'Datenversion' dann r.Datensatzwert sonst '' Ende)) Datenversion,
	max((Fall r.Feldcode wenn 'loop_num' dann r.Datensatzwert sonst '' Ende)) loop_num,
	max((Fall r.Feldcode wenn 'Status' dann r.Datensatzwert sonst '' Ende)) Status
	von ats_item_record r 
	wobei item_code = 'GONGMO_AGING'
	Gruppieren nach r.original_record_id
) m sortiert nach m.updated_time desc;

Die Ergebnisse der Abfrage:

Hinweis: Wenn Sie „Gruppieren nach“ verwenden, müssen Sie dem Feld die Max-Funktion hinzufügen. Bei Verwendung von „group by“ wird es normalerweise mit Aggregatfunktionen verwendet. Gängige Aggregatfunktionen sind:

• AVG() ermittelt den Durchschnitt
• COUNT() ermittelt die Gesamtzahl der Spalten
• MAX() Finde den Maximalwert
• MIN() Finde den Minimalwert
• SUMME()
  

Bitte beachten Sie, dass ich das gemeinsame Feld r.original_record_id desselben Datensatzes in der vertikalen Tabelle in die Gruppe nach eingefügt habe. Dieses Feld ist für denselben Datensatz in der vertikalen Tabelle gleich und eindeutig und wird sich nie ändern (entspricht der Primärschlüssel-ID der vorherigen horizontalen Tabelle). Dann habe ich die anderen Felder in max eingefügt (weil die anderen Felder entweder gleich sind oder das größte genommen werden kann oder nur ein Datensatz der vertikalen Tabelle einen Wert hat und die anderen Datensätze leer sind, sodass max in diesen drei Fällen direkt verwendet werden kann). Es ist logisch angemessen, die größte Aktualisierungszeit der vier Datensätze als Aktualisierungszeit desselben Datensatzes zu nehmen. Dann führen wir die max()-Operation für die vertikalen Tabellenfelder field_code und record_value aus. Da sie im selben Datensatz eindeutig sind, gibt es in denselben Daten keine zwei identischen field_code-Datensätze. Daher ist es kein Problem, max() auf diese Weise auszuführen.

Optimierungspunkte:

Schließlich kann dieses SQL optimiert werden. Wir können alle Vorlagenfelder (r.original_record_id, r.did, r.device_sn, r.mac_address, r.record_time usw.) aus der Tabelle entfernen, in der speziell Vorlagenfelder gespeichert sind (alle Felder in derselben logischen vertikalen Tabelle werden entfernt) und dann unseren max()-Teil als auszuführenden Parameter in den Code einfügen. Auf diese Weise können wir es universell machen. Jedes Mal, wenn wir ein neues Vorlagenfeld hinzufügen, müssen wir die SQL-Anweisung nicht ändern (so speichert China Mobile Parameterdaten für Mobiltelefone).

Die optimierte Business-Schicht (Code zum Zusammenstellen der SQL-Vorlagen) sieht wie folgt aus:

@Überschreiben
öffentliche PageInfo<AtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
    //1. Holen Sie sich die Vorlage des Alterungsfelds des Modells LambdaQueryWrapper<AtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
    queryWrapper.eq(AtsItemFieldPo::getItemCode, AtsItemCodeConstant.GONGMO_AGING.getCode());
    Liste<AtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
    //2. Abfragebedingungen zusammenstellen List<String> tplList = Lists.newArrayList(), conditionList = Lists.newArrayList(), validList = Lists.newArrayList();
    wenn (!CollectionUtils.isEmpty(fieldPoList)) {
        //3. Dynamisches Max-Abfragefeld zusammenstellen für (AtsItemFieldPo itemFieldPo : fieldPoList) {
            tplList.add("max((Fall r.field_code wenn '" + itemFieldPo.getFieldCode() + "' dann r.record_value sonst '' Ende )) " + itemFieldPo.getFieldCode());
            validList.add(itemFieldPo.getFieldCode());
        }
        qo.setTplList(tplList);
        //4. Stellen Sie dynamische Where-Abfragebedingungen zusammen, wenn (StringUtils.isNotBlank(qo.getDid())) {
            conditionList.add("UND hat wie CONCAT('%'," + qo.getDid() + ","'%')");
        }
        wenn (validList.contains("batch_code") und StringUtils.isNotBlank(qo.getBatchCode())) {
            conditionList.add("UND batch_code wie CONCAT('%'," + qo.getBatchCode() + ","'%')");
        }
        qo.setConditionList(Bedingungsliste);
    }
    qo.setItemCode(AtsItemCodeConstant.GONGMO_AGING.getCode());
    //4. Holen Sie sich den Datensatz des alternden Automatisierungstestelements PageHelper.startPage(qo.getPageNo(), qo.getPageSize());
    Liste<Map<String, Objekt>> Datenliste = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = neue PageInfo(Datenliste);
    //5. Stellen Sie die zurückgegebenen Ergebnisse zusammen: List<AtsAgingItemRecordVo> recordVoList = null;
    wenn (!CollectionUtils.isEmpty(dataList)) {
        DatensatzVoList = JSONUtils.copy(Datenliste, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(Datensatzliste);
    Seiteninfo zurückgeben;
}

Der optimierte Dao-Layer-Code lautet wie folgt:

öffentliche Schnittstelle AtsItemRecordDao erweitert BaseMapper<AtsItemRecordPo> {
 
    Liste<Map<String, Objekt>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

Der optimierte SQL-Anweisungscode lautet wie folgt:

<Auswahl id="selectItemRecordListByCondition" Ergebnistyp="java.util.HashMap"
        parameterType="com.galanz.iot.ops.restapi.model.qo.AtsItemRecordQo">
    Wählen Sie * von (
        Wählen Sie r.original_record_id id,
        max(r.did) hat,
        max(r.device_sn) Geräte-SN,
        max(r.aktualisiert_zeit) aktualisiert_zeit,
        max(r.Aufzeichnungszeit) Aufzeichnungszeit,
        <if test="tplList != null und tplList.size() > 0">
            <foreach-Sammlung="tplList" Element="tpl" Index="Index" Trennzeichen=",">
                ${tpl}
            </foreach>
        </if>
        VON ats_item_record r
        WO Artikelcode = #{Artikelcode}
        GRUPPE NACH r.original_record_id
    )
    <wo>
        <if test="conditionList != null und conditionList.size() > 0">
            <foreach-Sammlung="Zustandsliste" Element="Zustand" index="index">
                ${Bedingung}
            </foreach>
        </if>
    </wo>
    ORDER BY m.Aktualisierungszeit DESC
</Auswählen>

Die Struktur der Vorlagenfeldtabelle (ats_item_field-Tabelle) sieht wie folgt aus:

Die Datensatztabellenstruktur (ats_item_record-Tabelle) ist wie folgt:

Hinweis: original_record_id ist die eindeutige ID jedes Datensatzes, nachdem die vertikale Tabelle in die horizontale Tabelle konvertiert wurde. Sie kann als identisch mit der Primärschlüssel-ID unserer normalen horizontalen Tabelle angesehen werden.

Damit ist die Einführung zur Konvertierung einer vertikalen MySQL-Tabelle in eine horizontale Tabelle abgeschlossen.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Konvertierung einer vertikalen MySQL-Tabelle in eine horizontale Tabelle. Weitere Informationen zur Konvertierung einer vertikalen MySQL-Tabelle in eine horizontale Tabelle finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!