Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to realize the grouping according to conditional changes in the MySQL library". In the daily operation, I believe that many people have doubts about how to realize the grouping according to conditional changes in the MySQL library. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to realize the grouping according to conditional changes in the MySQL library". Next, please follow the editor to study!

This grouping is based on comparing the values of the previous line of fields and generating a new group when a certain change occurs (such as getting larger than 10). SQL only supports equivalent grouping. In order to realize this kind of ordered conditional grouping, it has to be transformed into equivalent grouping after several data transformations. Take the new version of MySQL, which supports window functions, as an example, it goes through three steps:

1. Get the change flag field flag. The field value of the previous line is obtained through the window function lag, which satisfies the change condition (such as this line-previous line > 10) flag is set to 1, otherwise it is 0

2. Accumulate flag fields to get acc fields. Add up from the first line to the current line, you still have to use the window function: sum (flag)... BETWEEN ROWS UNBOUNDED AND CURRENT ROW

3. Make regular equivalent grouping according to the acc field.

In the early days, MySQL without window functions can also be realized in theory, but it is more complex, so we won't go into details.

This kind of ordered grouping is very simple if you use the SPL language. You can do it in one sentence with the @ I option of the group operation:

= connect ("mysqlDB") .query ("select * from t") .group @ I (f > ~ [- 1] .f + 10)

After completing the grouping action, the sequence table B of the two-tier structure is obtained. For the second layer grouping subset, it is easy to do any calculation:

= B.max (~ .len ()) to get the maximum number of rows of the group

= B. (~ .new (frecom avg (f2)). Conj () to get the average value of f2 for each group.

= B. (~ .sort @ z (f2) .m (to (3)). Conj () take out the three largest lines of f2 in each group

At this point, the study on "how to achieve conditional change grouping in the MySQL library" 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.