# Xiaobai needs to learn MySQL - incremental statistical SQL

2022-06-24 09:12:55

This article was launched in the open source community of aikesheng 《 Technology sharing | MySQL A cluster increment statistic in SQL The needs of 》.

My colleague raised a question MySQL In the database SQL The problem of incremental statistics , I use test data to simulate , The test table tt There are three fields ,code Is the identification name ,cdate Is the corresponding date ,ctotal It's a statistic ,

The original statistical statement , according to code and cdate Clustering , Count out each code Every month ctotal Accumulated records ,

But his need is to be able to follow code+cdate The statistical value of is displayed accumulatively , For example, the first line in the above figure total yes 10, Second elements total Show 10+9=19, In the third line total Show 10+9+11=30, And so on .

What he needs is total Line by line ,MySQL Can be achieved by defining variable accumulation , utilize @i Line by line ,

But there's a problem , Different code, No zeroing , For example, in the figure above ,code=BBB The first line of the record ,total Should be 5, Now it's 35, Obviously 30+5 Got .

There should be another logic , according to code While clustering sum Sum calculation , As shown below , Now you can see ,g_total according to code and cdate Add up , Different code, It will return to zero , Recalculate ,

In fact, this one can be optimized ,MySQL 8.0 Support with, Above SQL Chinese vs tt The table was read twice , With the help of with, It can be reduced to read only once tt surface ,

in addition , Combined with the actual retrieval scenario requirements , Consider adding indexes to related fields , Further improve the efficiency of data retrieval .

Of course , The above SQL There may be other alternatives or better solutions , This article only gives one of the solutions .

therefore , adopt SQL Achieve business needs , On the one hand, we need to fully understand the meaning of requirements , Can accurately map to specific SQL logically , On the other hand, you should know the functions supported by the database 、 function 、 Characteristics, etc. , Whether there is an implementation that can meet this requirement , besides , Non functional factors , It can't be ignored , The right index 、 Avoid reading data repeatedly 、 Avoiding unnecessary sorting and so on are the means we can use , This is it. " work out measures to suit local conditions , The water is impermanent ".

Xiaobai studies MySQL

Recently updated articles ：

Article classification and indexing ：

copyright notice
author[bisal(Chen Liu)],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/175/202206240740009953.html