Shulou(Shulou.com)06/01 Report--

Filtering timing of where condition and having condition when Mysql and aggregate function are together

Where filters before aggregation

When a query contains aggregate functions and where conditions, situations like this

Select max (cid) from t where t.id=5

Group first, then aggregate, and then filter the result set whose aggregate result is greater than or equal to 5

The difference between the two:

Where executes first and then executes the aggregate function. Having is executed after the aggregate function has been executed.

The following is a supplement

There is a requirement, a table, there is a status field (1: success, 2: failure, and so on). Now we want to use date grouping to count the number in different states.

Write a subquery first.

Select aa.logDate,aa.totalLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=1) pendingLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMY% d') and executeStatus=2) successLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=3) errorLogs (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=4) callbackErrorLogsfrom (select DATE_FORMAT (a.startTime,'% YMY% MMI% d') logDate, count (1) totalLogs from dxp.dxp_handlermodel a group by DATE_FORMAT (a.startTime,'% YMY% MMI% d') aa

The execution is quite slow. Thinking about whether conditions can be added in count, I looked for it as follows:

SelectDATE_FORMAT (startTime,'% Ymuri% mmurf% d') logDate, count (1) totalLogs, count (if (executeStatus=1,true,null)) pendingLogs, count (if (executeStatus=2,true,null)) successLogs, count (if (executeStatus=3,true,null)) errorLogs, count (if (executeStatus=4,true,null)) callbackErrorLogsfrom dxp.dxp_handlermodelgroup by DATE_FORMAT (startTime,'% YMY% MFE% d'))

Simple and easy to understand, and very efficient

Other aggregate functions can also be used, such as SUM

Practical examples:

Select count (if (create_date < '2017-01-01' and host_profile_id = '9294d2bf-f457-4fe5-9a36mure5f832310dc2) from profile_visit_log-equivalent to select count (create_date <' 2017-01-01) count from profile_visit_log where host_profile_id = '9294d2bf-f457-4fe5-9a36mure5f832310dc2'

All right, that's all for this article. I hope you will support me in the future.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.