How to use mysql to complete the data generation in excel

Excel is the most commonly used tool for data analysis. This article compares the functions of MySQL and Excel to introduce how to use MySQL to complete data generation, data cleaning, preprocessing, and the most common data classification, data filtering, classification summary, and data pivot operations in Excel. In this article, we introduce the contents of parts 5, 6, and 7, data extraction, data screening, and data aggregation and perspective.

5. Data Extraction

The fifth part is data extraction, which is also the most common task in data analysis. The following describes how to use each function.

Extract data by column

#按列提取SELECT city FROM data1;

Extract data by row

#Extract by rowSELECT * FROM data1 WHERE city='beijing';

Extract data by location

#Extract by position SELECT * FROM data1 LIMIT 2,5;

Extract data by condition

#Extract and calculate by condition SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;

6. Data screening

The sixth part is data filtering, which uses the three conditions of AND, OR, NOT and greater than, less than and equal to filter the data, and count and sum them. Similar to the filtering function and countifs and sumifs functions in Excel.

Filter by condition (and, or, not)

The Excel data directory provides a "Filter" function, which is used to filter the data table according to different conditions. In MySQL, WHERE is used to complete the filtering operation, and the sum and count functions can also realize the functions of sumif and countif functions in Excel.

#Data filtering AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;

#Data filtering IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');

#Data filtering OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

#Data filtering (not equal)
SELECT * FROM data1 WHERE city !='beijing';

#Data filtering like (fuzzy filtering)
SELECT * FROM data1 WHERE city LIKE 'bei%';

#Count after filtering countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;

#Sum after filtering sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age<30;

#Find the average after filtering averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

7. Data classification, summary and perspective

The seventh part is to classify and summarize the data. In Excel, you can use classification and summary and pivot data to summarize data according to specific dimensions. The main functions used in MySQL are GROUP BY and CASE WHEN. The following describes how to use these two functions.

Classification

Excel provides a "Subtotal" function under the data directory, which can summarize the data table according to the specified fields and summary methods. In MySQL, the corresponding operations are completed through GROUP BY, and multi-level classification aggregation can be supported.

GROUP BY is a function for classification and aggregation. It is very easy to use. You can specify the column name to be grouped, or you can specify multiple column names at the same time. GROUP BY groups in the order in which the column names appear. At the same time, a summary method should be formulated after grouping. The most common ones are counting and summing.

#Single column classification summary SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

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

Pivot

The "Pivot Table" function is provided under the Insert directory in Excel to summarize the data table according to specific dimensions. There is no direct pivot table functionality in MySQL. But the same effect can be achieved through the CASE WHEN function.

Pivot table is also a commonly used way to classify and summarize data, and its function is more powerful than GROUP BY. In the following code, city is set as the row field, colour is set as the column field, price is set as the value field, and the price amount is calculated.

#View the original data table SELECT * FROM data1;

#Use CASE WHEN to pivot data CREATE VIEW data_Items AS (
 SELECT
 data1.city,
 CASE WHEN colour = "A" THEN price END AS A,
 CASE WHEN colour = "B" THEN price END AS B,
 CASE WHEN colour = "C" THEN price END AS C,
 CASE WHEN colour = "F" THEN price END AS F
 FROM data1
);

#View the results SELECT * FROM data_Items;

#Summarize the fields CREATE VIEW data1_Extended_Pivot AS (
 SELECT
 city,
 SUM(A) AS A,
 SUM(B) AS B,
 SUM(C) AS C,
 SUM(F) AS F
 FROM data_Items
 GROUP BY city
);

#View the results SELECT * FROM data1_Extended_Pivot;

#Handle null values ​​CREATE VIEW data1_Extended_Pivot_Pretty AS (
 SELECT 
 city, 
 COALESCE(A, 0) AS A, 
 COALESCE(B, 0) AS B, 
 COALESCE(C, 0) AS C,
 COALESCE(F, 0) AS F
 FROM data1_Extended_Pivot
);

#View the pivot data results SELECT * FROM data1_Extended_Pivot_Pretty;