So verwenden Sie MySQL, um die Datengenerierung in Excel abzuschließen

Excel ist das am häufigsten verwendete Tool zur Datenanalyse. Dieser Artikel vergleicht die Funktionen von MySQL und Excel und stellt vor, wie MySQL zur Datengenerierung, Datenbereinigung, Vorverarbeitung und den gängigsten Datenklassifizierungs-, Datenfilterungs-, Klassifizierungszusammenfassungs- und Daten-Pivot-Operationen in Excel verwendet wird. In diesem Artikel stellen wir die Inhalte der Teile 5, 6 und 7 vor: Datenextraktion, Datenscreening sowie Datenaggregation und -perspektive.

5. Datenextraktion

Der fünfte Teil ist die Datenextraktion, die zugleich die häufigste Aufgabe bei der Datenanalyse ist. Nachfolgend wird die Verwendung der einzelnen Funktionen beschrieben.

Daten nach Spalte extrahieren

#按列提取SELECT city FROM data1;

Daten zeilenweise extrahieren

#Extrahieren nach rowSELECT * FROM data1 WHERE city='beijing';

Daten nach Standort extrahieren

#Nach Position extrahieren SELECT * FROM data1 LIMIT 2,5;

Daten nach Bedingung extrahieren

#Extrahieren und berechnen nach Bedingung SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;

6. Datenscreening

Der sechste Teil ist die Datenfilterung, bei der die drei Bedingungen UND, ODER, NICHT und größer als, kleiner als und gleich zum Filtern, Zählen und Summieren der Daten verwendet werden. Ähnlich der Filterfunktion und den Funktionen „Zählenwenn“ und „Summewenn“ in Excel.

Filtern nach Bedingung (und, oder, nicht)

Das Excel-Datenverzeichnis bietet eine „Filter“-Funktion, mit der die Datentabelle nach verschiedenen Bedingungen gefiltert werden kann. In MySQL wird WHERE verwendet, um den Filtervorgang abzuschließen, und die Funktionen „Summe“ und „Zähle“ können auch die Funktionen „Summewenn“ und „Zählewenn“ in Excel realisieren.

#Datenfilterung UND
Wählen Sie * aus Daten1, wobei Stadt = "Shanghai" und Alter > 30 ist.

#Datenfilterung IN
SELECT * FROM data1 WHERE city IN (,Shanghai‘,,Peking‘);

#Datenfilterung ODER
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

#Datenfilterung (ungleich)
SELECT * FROM data1 WHERE Stadt !='Peking';

#Datenfilterung wie (Fuzzy-Filterung)
SELECT * FROM data1 WHERE city LIKE 'bei%';

#Anzahl nach dem Filtern countif
Wählen Sie ANZAHL(id) als ID-Anzahl aus Daten1, wobei Stadt = 'Shanghai' und Alter > 30 ist.

#Summe nach dem Filtern von sumtif
SELECT SUM(Preis) AS Preis FROM Daten1 WHERE Stadt='Peking' AND Alter<30;

#Finden Sie den Durchschnitt nach dem Filtern von Averageif
SELECT AVG(Preis) AS Durchschnittspreis FROM Daten1 WHERE Stadt !='Peking';

7. Datenklassifizierung, Zusammenfassung und Perspektive

Der siebte Teil besteht darin, die Daten zu klassifizieren und zusammenzufassen. In Excel können Sie Klassifizierung und Zusammenfassung sowie Pivot-Daten verwenden, um Daten nach bestimmten Dimensionen zusammenzufassen. Die in MySQL verwendeten Hauptfunktionen sind GROUP BY und CASE WHEN. Nachfolgend wird die Verwendung dieser beiden Funktionen beschrieben.

Einstufung

Excel bietet im Datenverzeichnis eine Funktion „Zwischensumme“, mit der die Datentabelle entsprechend den angegebenen Feldern und Zusammenfassungsmethoden zusammengefasst werden kann. In MySQL werden die entsprechenden Vorgänge durch GROUP BY abgeschlossen und eine mehrstufige Klassifizierungsaggregation kann unterstützt werden.

GROUP BY ist eine Funktion zur Klassifizierung und Aggregation. Sie ist sehr einfach zu verwenden. Sie können den zu gruppierenden Spaltennamen angeben oder mehrere Spaltennamen gleichzeitig angeben. GROUP BY gruppiert in der Reihenfolge, in der die Spaltennamen erscheinen. Gleichzeitig sollte nach der Gruppierung eine Zusammenfassungsmethode formuliert werden. Die gebräuchlichsten sind Zählen und Summieren.

#Zusammenfassung der Klassifizierung für eine Spalte SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

#Mehrere Spalten SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

Drehpunkt

Um die Datentabelle nach bestimmten Dimensionen zusammenzufassen, steht im Excel-Verzeichnis „Einfügen“ die Funktion „PivotTabelle“ zur Verfügung. In MySQL gibt es keine direkte PivotTable-Funktionalität. Derselbe Effekt kann jedoch durch die Funktion CASE WHEN erzielt werden.

Pivot-Tabellen werden ebenfalls häufig zum Klassifizieren und Zusammenfassen von Daten verwendet und ihre Funktion ist leistungsfähiger als die von GROUP BY. Im folgenden Code wird „Stadt“ als Zeilenfeld, „Farbe“ als Spaltenfeld, „Preis“ als Wertefeld festgelegt und der Preisbetrag berechnet.

#Ursprüngliche Datentabelle anzeigen SELECT * FROM data1;

#Verwenden Sie CASE WHEN, um Daten zu pivotieren CREATE VIEW data_Items AS (
 WÄHLEN
 data1.city,
 FALL WENN Farbe = "A" DANN Preis ENDE ALS A,
 Fall, wenn Farbe = "B", dann Preis, Ende als B,
 Fall, wenn Farbe = "C", dann Preis, Ende als C,
 FALL, WENN Farbe = "F" DANN Preis ENDE ALS F
 VON Daten1
);

#Ergebnisse anzeigen SELECT * FROM data_Items;

#Fassen Sie die Felder zusammen CREATE VIEW data1_Extended_Pivot AS (
 WÄHLEN
 Stadt,
 SUMME(A) ALS A,
 SUMME(B) ALS B,
 SUMME(C) ALS C,
 SUMME(F) ALS F
 VON data_Items
 GRUPPE NACH Stadt
);

#Ergebnisse anzeigen SELECT * FROM data1_Extended_Pivot;

#Nullwerte verarbeiten CREATE VIEW data1_Extended_Pivot_Pretty AS (
 WÄHLEN 
 Stadt, 
 KOALESCE(A, 0) ALS A, 
 COALESCE(B, 0) AS B, 
 COALESCE(C, 0) AS C,
 KOALESCE(F, 0) AS F
 VON data1_Extended_Pivot
);

#Pivot-Datenergebnisse anzeigen SELECT * FROM data1_Extended_Pivot_Pretty;