Schritte der MySQL-Methode zum Bestimmen, ob es sich um eine Teilmenge handelt

Schritte der MySQL-Methode zum Bestimmen, ob es sich um eine Teilmenge handelt

1. Problem

Die Geschichte entstand aus einem Bericht, der die Fehler- und Auslassungsrate abfragt: Es gibt zwei Abfrageergebnisse, eines für die Elemente, die dem Bericht hinzugefügt wurden, und das andere für die Elemente, die dem Bericht hinzugefügt werden sollten.

Was bedeutet es, ohne Auslassungen zu sein? Das heißt, alle Elemente, die hinzugefügt werden sollten, wurden hinzugefügt

Der Vollständigkeitsgrad der Meldungen errechnet sich aus dem Verhältnis der Anzahl der vollständigen Meldungen zur Gesamtzahl der Meldungen.

Hier sind zwei Beispielberichte (einer mit allen hinzugefügten und einer mit fehlenden Teilen)

Suchen Sie zunächst das erste Ergebnis - die Elemente, die dem Bericht hinzugefügt werden sollen

WÄHLEN 
     r.id ALS Berichts-ID, m.project_id sollte zum Projekt-FROM hinzugefügt werden 
  Bericht 
  INNER JOIN-Anwendung a ON r.app_id=a.id
  INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
  RIGHT JOIN application_sample_item si ON s.id=si.sample_id       
  RECHTS JOIN set_project_mapping m ON si.set_id=m.set_id
WO r.id IN ('44930','44927')
ORDER BY r.id,m.projekt_id;

Suchen Sie dann das zweite Ergebnis - melden Sie die hinzugefügten Elemente

Wählen Sie r.id AS Berichts-ID, i.Projekt-ID AS hinzugefügtes_Projekt FROM Bericht r 
RIGHT JOIN report_item i ON r.id=i.report_id
WO r.id IN ('44930','44927');

Oben sehen Sie den Ergebnissatz, den wir vergleichen möchten. Es ist nicht schwer zu erkennen, dass Bericht 44927 vollständig ist, während Bericht 44930 die gleiche Anzahl von Elementen enthält, aber tatsächlich 758 Elemente hinzufügt und 112 Elemente fehlen, sodass es sich um einen fehlenden Bericht handelt.

2. Lösung

Der Frage nach zu urteilen handelt es sich offensichtlich um eine Frage der Beurteilung, ob es sich um eine Teilmenge handelt. Sie können die hinzugefügten Elemente und die Elemente, die hinzugefügt werden sollen, separat durchlaufen. Wenn die Elemente, die hinzugefügt werden sollen, mit den hinzugefügten Elementen übereinstimmen, bedeutet dies, dass die Elemente, die hinzugefügt werden sollen, eine Teilmenge der hinzugefügten Elemente sind, d. h. es gibt keine Auslassungen.

Dieses Problem kann zwar durch Schleifen und Vergleichen gelöst werden, aber der Cross Join von kartesischen Produkten in SQL bedeutet oft einen enormen Overhead und eine langsame Abfragegeschwindigkeit. Gibt es also eine Möglichkeit, dieses Problem zu vermeiden?

Option 1:

Mit Hilfe der Funktionen FIND_IN_SET und GROUP_CONCAT verstehen Sie zunächst die folgenden beiden Funktionen

FIND_IN_SET(Zeichenfolge,Zeichenfolgeliste)

  • str: der abzufragende String
  • strlist: Parameter werden durch englische "," getrennt, beispielsweise (1,2,6,8,10,22)

Die Funktion FIND_IN_SET gibt die Position des abzufragenden Strings im Zielstring zurück.

GROUP_CONCAT( [distinct] Zu verbindendes Feld [order by sort field asc/desc ] [separator 'separator'] )

Die Funktion GROUP_CONCAT() kann die Werte desselben Felds mehrerer Datensätze zu einem Datensatz zusammenfassen und zurückgeben. Das Standardtrennzeichen ist das englische „,“ .

Die Standardlänge von GROUP_CONCAT() beträgt jedoch 1024

Wenn die Länge des Spleißens 1024 überschreiten muss, führt dies daher zu einer unvollständigen Kürzung und die Länge muss geändert werden.

GLOBAL festlegen group_concat_max_len=102400;
Setzen Sie die Sitzung auf group_concat_max_len=102400;

Aus den beiden obigen Funktionseinführungen geht hervor, dass FIND_IN_SET und GROUP_CONCAT durch englische ',' (fett gedruckt) getrennt sind.

Daher können wir GROUP_CONCAT verwenden, um die zu einem String hinzugefügten Elemente zu verketten und dann FIND_IN_SET verwenden, um nacheinander abzufragen, ob die hinzuzufügenden Elemente im String vorhanden sind.

1. Ändern Sie das SQL in der Beschreibung des Problems und verwenden Sie GROUP_CONCAT, um die Elemente der hinzugefügten Elemente zu einer Zeichenfolge zu verketten

SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS hinzugefügte Projektliste FROM Bericht r 
LINKS JOIN report_item i ON r.id=i.report_id
WO r.id IN ('44930','44927')
GRUPPE NACH r.id;

2. Verwenden Sie FIND_IN_SET, um nacheinander zu überprüfen, ob die hinzuzufügenden Elemente in der Zeichenfolge vorhanden sind

SELECT Q.id,FIND_IN_SET(W.Liste der hinzuzufügenden Elemente, Q.Liste der bereits hinzugefügten Elemente) AS Is FROM fehlt 
   (
   -- Bericht der hinzugefügten Projekte SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS hinzugefügte Projektliste FROM Bericht r 
      LINKS JOIN report_item i ON r.id=i.report_id
      WO r.id IN ('44930','44927')
      GRUPPE NACH r.id
   )Q,
   (
   -- Elemente melden, die hinzugefügt werden sollenAUSWÄHLEN 
         r.id,s.app_id,m.project_id sollten die Projektliste hinzufügen VON 
         Bericht 
         INNER JOIN-Anwendung a ON r.app_id=a.id
         INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
         INNER JOIN application_sample_item si ON s.id=si.sample_id       
         INNER JOIN set_project_mapping m ON si.set_id=m.set_id
      WO r.id IN ('44930','44927')
      ORDER BY r.id,m.projekt_id
   )W
   WO Q.id=W.id;

3. Verpasste Meldungen herausfiltern

 SELECT Q.id,CASE WHEN FIND_IN_SET(W.Liste der hinzuzufügenden Elemente, Q.Liste der bereits hinzugefügten Elemente)>0 THEN 1 ELSE 0 END AS Fehlt FROM 
   (
   -- Bericht der hinzugefügten Projekte SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS hinzugefügte Projektliste FROM Bericht r 
      LINKS JOIN report_item i ON r.id=i.report_id
      WO r.id IN ('44930','44927')
      GRUPPE NACH r.id
   )Q,
   (
   -- Elemente melden, die hinzugefügt werden sollenAUSWÄHLEN 
         r.id,s.app_id,m.project_id sollten die Projektliste hinzufügen VON 
         Bericht 
         INNER JOIN-Anwendung a ON r.app_id=a.id
         INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
         INNER JOIN application_sample_item si ON s.id=si.sample_id       
         INNER JOIN set_project_mapping m ON si.set_id=m.set_id
      WO r.id IN ('44930','44927')
      ORDER BY r.id,m.projekt_id
   )W
   WO Q.id=W.id
   GRUPPE NACH Q.id
   HAVING COUNT(`Fehlt es`)=SUM(`Fehlt es`);

4. Unser oberstes Ziel ist die Ermittlung der Null-Auslassungsrate

 SELECT COUNT(X.id) Anzahl der Berichte ohne fehlende Elemente, Y.total Gesamtzahl der Berichte, CONCAT(FORMAT(COUNT(X.id)/Y.total*100,2),'%') AS Projekt ohne Rate fehlender Elemente FROM 
(
  SELECT Q.id,CASE WHEN FIND_IN_SET(W.Liste der hinzuzufügenden Elemente, Q.Liste der bereits hinzugefügten Elemente)>0 THEN 1 ELSE 0 END AS Fehlt FROM 
   (
   -- Bericht der hinzugefügten Projekte SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS hinzugefügte Projektliste FROM Bericht r 
      LINKS JOIN report_item i ON r.id=i.report_id
      WO r.id IN ('44930','44927')
      GRUPPE NACH r.id
   )Q,
   (
   -- Elemente melden, die hinzugefügt werden sollenAUSWÄHLEN 
         r.id,s.app_id,m.project_id sollten die Projektliste hinzufügen VON 
         Bericht 
         INNER JOIN-Anwendung a ON r.app_id=a.id
         INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
         INNER JOIN application_sample_item si ON s.id=si.sample_id       
         INNER JOIN set_project_mapping m ON si.set_id=m.set_id
       WO r.id IN ('44930','44927')
    ORDER BY r.id,m.projekt_id
   )W
   WO Q.id=W.id
   GRUPPE NACH Q.id
   HAVING COUNT(`Fehlt es`)=SUM(`Fehlt es`)
 )X,
 (
    -- Gesamtzahl der Berichte SELECT COUNT(E.nums) AS total FROM
    (
      SELECT COUNT(r.id) AS nums FROM Bericht r 
      WO r.id IN ('44930','44927')
      GRUPPE NACH r.id
    )E    
 )Y 
 ;

Option 2:

Obwohl die obige Lösung 1 das zeilenweise Durchlaufen und Vergleichen vermeidet, handelt es sich im Wesentlichen um einen Einzelvergleich der Elemente. Gibt es also eine Möglichkeit, den Vergleich zu vermeiden?

Die Antwort ist natürlich ja. Ob die Erfassung vollständig ist, können wir anhand der statistischen Menge feststellen.

1. Verwenden Sie „union all“, um die hinzugefügten Elemente mit den hinzuzufügenden Elementen zu verbinden, ohne Duplikate zu entfernen.

 (
 -- Elemente, die hinzugefügt werden sollenAUSWÄHLEN 
  r.id,m.projekt_id
AUS 
   Bericht 
INNER JOIN-Anwendung a ON r.app_id=a.id
INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
INNER JOIN application_sample_item si ON s.id=si.sample_id       
INNER JOIN set_project_mapping m ON si.set_id=m.set_id
WO r.id IN ('44930','44927')
ORDER BY r.id,m.projekt_id
)
UNION ALLE
(
 -- Projektauswahl r.id,i.project_id aus Bericht r,report_item i hinzugefügt 
wobei r.id = i.report_id und r.id IN ('44930','44927')
Gruppieren nach r.app_id,i.project_id
 )

Aus den Ergebnissen können wir ersehen, dass es im selben Bericht doppelte Elemente gibt. Dabei handelt es sich um die Elemente, die hinzugefügt werden sollten, und die Elemente, die hinzugefügt wurden.

2. Nach den Ergebnissen der gemeinsamen Tabelle die Anzahl der überlappenden Elemente im statistischen Bericht

# Soll die Anzahl der Elemente hinzufügen, die sich mit den bereits hinzugefügten überschneiden select tt.id,count(*) count from 
(
   wähle t.id,t.project_id,count(*) aus 
   (
      (
        -- Elemente, die hinzugefügt werden sollenAUSWÄHLEN 
          r.id,m.projekt_id
        AUS 
          Bericht 
          INNER JOIN-Anwendung a ON r.app_id=a.id
          INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
          INNER JOIN application_sample_item si ON s.id=si.sample_id       
          INNER JOIN set_project_mapping m ON si.set_id=m.set_id
        WO r.id IN ('44930','44927')
        ORDER BY r.id,m.projekt_id
      )
      UNION ALLE
      (
        -- Projektauswahl r.id,i.project_id aus Bericht r,report_item i hinzugefügt 
        wobei r.id = i.report_id und r.id IN ('44930','44927')
        Gruppieren nach r.app_id,i.project_id
      )
      
   )
   GRUPPE NACH t.id,t.projekt_id
   HAVING count(*) >1 
) tt-Gruppe nach tt.id

3. Vergleichen Sie den Betrag im zweiten Schritt mit dem Betrag, der hinzugefügt werden soll. Wenn sie gleich sind, bedeutet dies, dass keine Auslassung vorliegt.

wähle bb.id, aa.count wurde hinzugefügt, bb.count muss hinzugefügt werden,
    FALL, WENN aa.count/bb.count=1, DANN 1
    SONST 0
    END AS 'Fehlt es?' 
aus 
(
# Soll die Anzahl der Elemente hinzufügen, die sich mit den bereits hinzugefügten überschneiden select tt.id,count(*) count from 
(
   wähle t.id,t.project_id,count(*) aus 
   (
      (
        -- Elemente, die hinzugefügt werden sollenAUSWÄHLEN 
          r.id,m.projekt_id
        AUS 
          Bericht 
          INNER JOIN-Anwendung a ON r.app_id=a.id
          INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
          INNER JOIN application_sample_item si ON s.id=si.sample_id       
          INNER JOIN set_project_mapping m ON si.set_id=m.set_id
        WO r.id IN ('44930','44927')
        ORDER BY r.id,m.projekt_id
      )
      UNION ALLE
      (
        -- Projektauswahl r.id,i.project_id aus Bericht r,report_item i hinzugefügt 
        wobei r.id = i.report_id und r.id IN ('44930','44927')
        Gruppieren nach r.app_id,i.project_id
      )
      
   )
   GRUPPE NACH t.id,t.projekt_id
   HAVING count(*) >1 
) tt-Gruppe nach tt.id 
) aa RECHTSVERBINDUNG
(
  -- Die Anzahl der Elemente, die hinzugefügt werden sollen. AUSWÄHLEN 
    r.id,s.app_id,COUNT(m.project_id) Anzahl
  AUS 
    Bericht 
    INNER JOIN-Anwendung a ON r.app_id=a.id
    INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
    INNER JOIN application_sample_item si ON s.id=si.sample_id       
    INNER JOIN set_project_mapping m ON si.set_id=m.set_id
  WO r.id IN ('44930','44927')
  GRUPPE NACH r.id
  ORDER BY r.id,m.projekt_id
) bb EIN aa.id = bb.id 
BESTELLEN NACH aa.id

4. Finden Sie die No-Missing-Rate

wählen 
    SUM(asr.`Fehlt es?) AS Keine fehlende Zahl, COUNT(asr.id) AS Gesamtzahl, CONCAT(FORMAT(SUM(asr.`Fehlt es?)/COUNT(asr.id)*100,5),'%') AS Keine fehlende Rate melden von 
(
  wähle bb.id, aa.count wurde hinzugefügt, bb.count muss hinzugefügt werden,
      FALL, WENN aa.count/bb.count=1, DANN 1
      SONST 0
      END AS 'Fehlt es?' 
  aus 
  (
  # Soll die Anzahl der Elemente hinzufügen, die sich mit den bereits hinzugefügten überschneiden select tt.id,count(*) count from 
  (
     wähle t.id,t.project_id,count(*) aus 
     (
        (
          -- Elemente, die hinzugefügt werden sollenAUSWÄHLEN 
            r.id,m.projekt_id
          AUS 
            Bericht 
            INNER JOIN-Anwendung a ON r.app_id=a.id
            INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
            INNER JOIN application_sample_item si ON s.id=si.sample_id       
            INNER JOIN set_project_mapping m ON si.set_id=m.set_id
          WO r.id IN ('44930','44927')
          ORDER BY r.id,m.projekt_id
        )
        UNION ALLE
        (
          -- Projektauswahl r.id,i.project_id aus Bericht r,report_item i hinzugefügt 
          wobei r.id = i.report_id und r.id IN ('44930','44927')
          Gruppieren nach r.app_id,i.project_id
        )
        
     )
     GRUPPE NACH t.id,t.projekt_id
     HAVING count(*) >1 
  ) tt-Gruppe nach tt.id 
  ) aa RECHTSVERBINDUNG
  (
    -- Die Anzahl der Elemente, die hinzugefügt werden sollen. AUSWÄHLEN 
      r.id,s.app_id,COUNT(m.project_id) Anzahl
    AUS 
      Bericht 
      INNER JOIN-Anwendung a ON r.app_id=a.id
      INNER JOIN Anwendungsbeispiel s ON a.id=s.app_id
      INNER JOIN application_sample_item si ON s.id=si.sample_id       
      INNER JOIN set_project_mapping m ON si.set_id=m.set_id
    WO r.id IN ('44930','44927')
    GRUPPE NACH r.id
    ORDER BY r.id,m.projekt_id
  ) bb EIN aa.id = bb.id 
  BESTELLEN NACH aa.id
) asr;

Dies ist das Ende dieses Artikels über die Schritte zum Bestimmen, ob MySQL eine Teilmenge ist. Weitere Informationen zum Bestimmen, ob MySQL eine Teilmenge ist, finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!

Das könnte Sie auch interessieren:
  • MySql8 WITH RECURSIVE rekursive Abfrage Eltern-Kind-Sammlungsmethode

<<:  Detaillierte Erklärung zur Verwendung von Router-View-Komponenten in Vue

>>:  Was ist Nginx-Lastausgleich und wie wird er konfiguriert?

Artikel empfehlen

MySQL wählt den passenden Datentyp für die ID

Inhaltsverzeichnis Zusammenfassung der Distribute...

Details zur Verwendung von aktiviertem Vue in untergeordneten Komponenten

Seite: Basis: <Vorlage> <div Klasse=&quo...

Das Konzept von MTR in MySQL

MTR steht für Mini-Transaktion. Wie der Name scho...

So implementieren Sie die Zuordnung zwischen Rahmenfenstern und die Verwendung des Zielattributs von Hyperlinks

Der Schlüssel zum Erreichen der Zuordnung des Rahm...

Sieben Prinzipien eines guten Designers (2): Farbgebrauch

<br />Vorheriger Artikel: Sieben Prinzipien ...

Detaillierte Erklärung der dynamischen Komponenten von vue.js

:ist eine dynamische Komponente Verwenden Sie v-b...

Die endgültige Lösung für Chromes Mindestschriftgrößenbeschränkung von 12px

Ich glaube, dass viele Benutzer, die Websites ers...

So beheben Sie den 2002-Fehler bei der Installation einer MySQL-Datenbank in der Alibaba Cloud

Beim Installieren der Datenbank ist folgender Feh...

So ändern Sie das Root-Passwort von Mysql5.7.10 auf dem MAC

Starten Sie MySQL zunächst im Skip-Grant-Tables-M...

Zusammenfassung der Fallstricke, die Ihnen bei der Entwicklung von WeChat-Applets möglicherweise noch nicht begegnet sind

Inhaltsverzeichnis getApp() Definieren Sie Variab...

Beispielcode für HTML-Layout links und rechts

CSS: Code kopieren Der Code lautet wie folgt: html...

Implementierungsbeispiel für die Message Board-Funktion von Node.js+Express

Inhaltsverzeichnis Nachrichtenbrett Erforderliche...

Der Unterschied zwischen GB2312, GBK und UTF-8 in der Webseitenkodierung

Zunächst müssen wir verstehen, dass GB2312, GBK u...

MySQL-Datenbankgrundlagen - Prinzip der Join-Operation

Join verwendet den Nested-Loop-Join-Algorithmus. ...

So implementieren Sie die Kopierschutzfunktion für Webseiten (mit der Cracking-Methode)

Durch einen Rechtsklick auf die Quelldatei wird fo...