mysql - 寻求统计新增合同数的方案

Question

场景

登陆用户可以随时查看某个时间段的新增合同数

部门成员关系

华东区总经理 
 |--- 华东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
     

合同表

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

计算规则

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

方案1

数据库是mysql，因为上级合同数，是所有下级的合同数之和，所以，我们递归得到了用户所有下级的用户id
然后再用count(*) from 表 where （created_at时间段条件） and created_by in (所有的下级用户id，包含当前登录用户id)

问题：数据是准确的，但是效率低下，对服务器性能也有影响

方案2

单独用一个表来记录每个用户每天的新增合同数，还是先得到所有下级用户的id，然后再用
select sum（inum） from user_count where （created_at时间段条件）
and created_by in (所有的下级用户id，包含当前登录用户id)

问题:每次 增加，删除，批量删除，都要修改这个字段，如果用户量增大，计数错误的可能性非常大，虽然统计方便了，但是数据不准确

求助

我们网站要统计今日新增，昨日新增，本周新增，本月新增，本季度新增，本年新增，用户还可以自己输入时间段查询
请问大家有没有什么好的方案，可以让数据既准确，统计起来效率又高的？

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! ! !