mysql - Looking for a solution to count the number of new contracts

Question

Scene

Logged in users can check the number of new contracts in a certain time period at any time

Department Member Relations

华东区总经理 
 |--- 华东1区经理 
 |      |---销售主管11 
 |      |    |---销售员111
 |      |    |---销售员112
 |      |---销售主管12
 |           |---销售员121
 |           |---销售员122
 |
 |
 |--- 华东2区经理 
        |---销售主管21 
        |    |---销售员211
        |    |---销售员212
        |---销售主管22
        |    |---销售员221
        |    |---销售员222
        |---销售主管23
        |    |---销售员231
        |    |---销售员232
华北区总经理 
 |--- 华北1区经理 
 |      |---销售主管31 
 |      |    |---销售员311
 |      |    |---销售员312
 |      |---销售主管32
 |           |---销售员321
 |           |---销售员322
 |
 |
 |--- 华北2区经理 
        |---销售主管41 
        |    |---销售员411
        |    |---销售员412
        |---销售主管42
        |    |---销售员421
        |    |---销售员422
        |---销售主管43
        |    |---销售员431
        |    |---销售员432
     

Contract Form

id          合同id
name        合同名称
created_at  创建时间
created_by  创建人
updated_at  修改时间
updated_by  修改人

Calculation rules

【新增合同数】 = 【所有下级的新增合同数】+【本人新增合同数】

Option 1

The database is mysql, because the number of superior contracts is the sum of the number of contracts of all subordinates, so we recursively get the user IDs of all subordinates of the user
and then use count(*) from table where (created_at time period Condition) and created_by in (all subordinate user IDs, including the current logged in user ID)

Problem: The data is accurate, but it is inefficient and has an impact on server performance

Option 2

Use a separate table to record the number of new contracts for each user every day, or first get the IDs of all subordinate users, and then use
select sum (inum) from user_count where (created_at time period condition)
and created_by in (all subordinate user IDs, including the current logged in user ID)

Problem: This field must be modified every time you add, delete, or batch delete. If the number of users increases, the possibility of counting errors is very high. Although statistics are convenient, the data is inaccurate

Help

Our website will count the new additions today, yesterday’s additions, this week’s additions, this month’s additions, this quarter’s additions, and this year’s additions. Users can also enter their own time period to query
Do you have any suggestions? A solution that can make the data both accurate and statistically efficient?

Answer 1

Consider putting a copy of the newly added contract in redis and clearing it once a day.

Answer 2

Option 1 is the preferred option. The only problem is that it is slow to recursively obtain all subordinate personnel. Then let’s solve this problem!

Assume that the personnel table has the following approximate structure, a typical tree-like data storage.
ID, Name, ParentId

Add a field Path to it, whose value is the access path to the person, such as -12-45-765-, where 765 is the current user ID, 45 is the superior of 765, and 12 is the superior of 45.

With this field, it is easy to filter out all his subordinates and himself based on a leader's ID. select id from employee where path like '%-45-%'

When updating personnel affiliation in the future, just remember to update this field.

Answer 3

If you have a large amount of data, you have to query and calculate it every time you check it. This is very, very stressful, no matter which solution you use.
For this kind of thing, you might as well do streaming calculation statistics directly. Calculate a piece of data once, and query it directly when using it.
In order not to affect the performance of normal processes, streaming computing statistics can be operated asynchronously
Our system has done similar statistics. You can see the last 90 days when looking at daily statistics, the last 3 years when looking at monthly statistics, and only the statistics before 3 years ago. It can be viewed by year, which seems to be similar to your needs. I wrote a small code for stream calculation statistics, which took less than a week.
github.com/panjjo/flysnow Of course, the code writing is still quite bad.

Answer 4

This is a common OLAP requirement in the database field, and materialized views can be considered.

For reference.

Love MongoDB! Have fun!

Answer 5

Writing a timer will solve the problem, that’s all! ! !