In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)06/01 Report--
This article mainly introduces "how to use Mariadb aggregate function and grouping query". In daily operation, I believe many people have doubts about how to use Mariadb aggregate function and grouping query. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Mariadb aggregate function and grouping query". Next, please follow the editor to study!
MariaDB Server is one of the most popular open source relational databases. It was created by the original developers of MySQL and guaranteed to remain open source. It is part of most cloud products and is the default configuration for most Linux distributions. MariaDB is designed as a direct alternative to MySQL, with more features, a new storage engine, fewer errors and better performance.
Aggregate function
When we need to summarize the data in the table instead of using a row of data in the table, we can use the aggregate function provided by Mysql. In Mysql, there are five common aggregate functions:
AVG: take the average COUNT: take the statistical value MAX: take the maximum value MIN: take the minimum SUM: take the sum value
1. Get the sum of the population in the table
MariaDB [world] > SELECT SUM (Population) FROM city;+-+ | SUM (Population) | +-+ | 1429559884 | +-+ 1 row in set (0.01 sec)
two。 Get the total population of China in the data table
MariaDB [world] > SELECT SUM (Population) FROM city WHERE CountryCOde='CHN';+-+ | SUM (Population) | +-+ | 175953614 | +-+ 1 row in set (sec)
3. Find out how many cities there are in China.
MariaDB [world] > SELECT COUNT (ID) FROM city WHERE CountryCode = 'CHN';+-+ | COUNT (id) | +-+ | 363 | +-+ 1 row in set (0.00 sec)
With regard to COUNT, if you want to count how many rows there are in the table, do not use * as a parameter, because ~ affects performance, just choose a column.
4. Inquire about the population of the least populous city in China.
MariaDB [world] > SELECT MIN (Population) FROM city WHERE CountryCode = 'CHN';+-+ | MIN (Population) | +-+ | 89288 | +-+ 1 row in set (sec)
5. Inquire about the population of the most populous city in China
MariaDB [world] > SELECT MAX (Population) FROM city WHERE CountryCode = 'CHN';+-+ | MAX (Population) | +-+ | 9696300 | +-+ 1 row in set (sec)
6. Inquire about the average population of cities in Henan
MariaDB [world] > SELECT AVG (Population) FROM city WHERE District = 'Henan';+-+ | AVG (Population) | +-+ | 383278.3333 | +-+ 1 row in set (sec)
So, we see that aggregate functions are usually used for numerical calculations.
Grouping query
Above, when we use the aggregate function, we group the data of all SELECT. If we want to query the number of cities in all countries, we have to use WHERE to filter CountryCode many times.
MariaDB [world] > SELECT DISTINCT CountryCode FROM city;+-+ | CountryCode | +-+ | ABW | | AFG |. | ZWE | +-+ 232 rows in set (0.00 sec)
We see that there are 232 countries in our data sheet, so? Is it necessary for us to use COUNT (ID) in each of these 232 different countries to count the number of cities in each country? Actually this is not so.
We can use grouping query GROUP BY. What is grouping? A grouping query is a logical grouping of data using a specified column or columns (grouped into a group when the grouping is the same), assuming the following data:
MariaDB [world] > SELECT * FROM city LIMIT 5 +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +-+ 5 rows in set (0.00 sec)
We use GROUP BY CountryCode to specify CountryCode as the grouping basis, so they are divided into the same group and 5 in another group.
Grouping is usually used in conjunction with aggregate functions, which aggregate each individual logical grouping. Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in SELECT, you must specify the same expression in the GROUP BY clause and cannot use an alias. Except for the aggregate function, each column in the SELECT statement must be given in the GROUP BY clause. If the grouping contains rows with null values, NULL is returned as a grouping. The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.
1. Inquire about the number of cities in each country
MariaDB [world] > SELECT CountryCode,COUNT (ID) FROM city GROUP BY CountryCode +-+-+ | CountryCode | COUNT (ID) | +-+-+ | ABW | 1 | AFG | 4 |.. | ZMB | 7 | | ZWE | 6 | +-+-+ 232 rows in set (0.00 sec)
When the WHERE clause is used in a SELECT statement, the WHERE clause is always filtered before grouping.
two。 Inquire about the number of cities with a population of more than 1000000 in each country
MariaDB [world] > SELECT CountryCode,COUNT (ID) FROM city WHERE Population > = 1000000 GROUPP BY CountryCode +-+-+ | CountryCode | COUNT (ID) | +-+-+ | AFG | 1 | AGO | 1 | ARG | 3 | ARM | 1 | AUS | 4 | ... | YUG | 1 | ZAF | 1 | | ZMB | 1 | ZWE | 1 | +-+-+ 77 rows in set (0.01sec)
So in countries that do not appear in the group, there are no cities with a population of 1000000.
3. Inquire about countries with a total population of more than 1000000
The HAVING clause is used to filter the total value of the grouped data, while the operations supported by HAVING are the same as the WHERE clause.
For example:
MariaDB [world] > SELECT CountryCode,SUM (Population) AS Total_Population FROM city GROUP BY CountryCode HAVING Total_Population > 1000000 +-+-+ | CountryCode | Total_Population | +-+-+ | AFG | 2332100 | | AGO | 2561600 | ARE | 1728336 |. | | ZAF | 15196370 | | ZMB | 2473500 | | ZWE | 2730420 | +-+-+ 108 rows in set (0.00 sec) so far | The study on "Mariadb aggregate function and how to use grouping query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.