Efficiency in data statistics

I have been changing a statistical part of the project for the last two days. Some Opinions on the statistical efficiency are written here as a record.

 

1. Statistics sometimes encounter different query conditions in the same table, so you have to write many statistical statements to get the results. First of all: to ensure data correctness, we must not sacrifice accuracy for efficiency. My solution is to check the content that needs to be queried, use the content of this Part as a temporary table, and then sort it out through chained query.

2. Minimize the number of link queries in the statistics module. Link queries in the verified module reduce the efficiency of some queries.

For example, select T. sid, T. qymc, t1.dl _ count, t2.qy _ ybyh_count from (select Sid, qymc from base_qyxx where (isdeleted = 0 or isdeletedis null) and ID = '2013') tleft join (select userid, count (1) dl_count from sys_log_login lwhere L. logindate> = to_date ('20140901', 'yyyymmdd') and L. logindate <= to_date ('20140901', 'yyyymmdd') group by userid) t1on T. SID = t1.useridleft join (select qyid, count (1) qy_ybyh_countfrom hidden_danger_ybyhxx ybyh where (isdeletedis null or isdeleted = 0) and (zfjcid is null) and ybyh. cjsj> = to_date ('20140901', 'yyyymmdd') and ybyh. cjsj <= to_date ('20170101', 'yyyymmdd') group by qyid) t2on T. SID = t2.qyid

 

Efficiency in data statistics