current position:Home>Xiaobai needs to learn MySQL - incremental statistical SQL

Xiaobai needs to learn MySQL - incremental statistical SQL

2022-06-24 09:12:55bisal(Chen Liu)

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 ,

f0e6bd0d49e74bf0b51cc3af6698e4d9.png

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

3d3fe01381393e10c7508d953d16c479.png

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 ,

37fa746b36e72dfcffcb8570309c60ad.png

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 ,

ae051262eea83551ef8e87b67385fa77.png

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 ,

f389fc445ebc7f8c14429acdc707765d.png

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

Xiaobai studies MySQL - You've encountered this kind of scenario where you can't log in ?

Xiaobai studies MySQL - There are some differences between users created by different versions

Xiaobai studies MySQL - A tool for randomly inserting test data

Xiaobai studies MySQL - varchar Why are type fields often defined as 255?

Xiaobai studies MySQL - A case of flexible index creation

Xiaobai studies MySQL - “ Be opportunistic ” The number of records in a statistical table

Xiaobai studies MySQL - Once slow SQL The positioning of

Xiaobai studies MySQL - TIMESTAMP The type field is not empty and the default value property is affected

Xiaobai studies MySQL - Talk about the importance of data backup

Xiaobai studies MySQL - InnoDB Support optimize table?

Xiaobai studies MySQL - table_open_cache The role of

Xiaobai studies MySQL - Table space defragmentation method

Xiaobai studies MySQL - Case sensitive problem solving

Xiaobai studies MySQL - only_full_group_by Validation rules for

Xiaobai studies MySQL - max_allowed_packet

Xiaobai studies MySQL - mysqldump Parameter differences to ensure data consistency

Xiaobai studies MySQL - The query will lock the table ?

Xiaobai studies MySQL - The problem of index key length limitation

Xiaobai studies MySQL - MySQL Will be affected by “ High water level ” Influence ?

Xiaobai studies MySQL - Database software and initialization installation

Xiaobai studies MySQL - Chat

Recently updated articles :

Several guesses about the design of Tencent conference number

Mystery of power bank

China Super League and Guoan in the new season , Through the thorns

Oracle Of CTAS Can I bring constraints and other attributes to the new table ?

Fault tree analysis (FTA)

Article classification and indexing :

official account 1000 Article classification and index

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

Random recommended