Shulou(Shulou.com)05/31 Report--

This article introduces how to compile the SQL code for monthly statistics of data or weekly statistics of data. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

For all the requirements, when you don't know how to deal with them, you first use the simplest method, or to be clear, use the most primitive method to implement the business requirements first.

1. Make summary statistics on the withdrawal queue data table "ims_checkout_task", and summarize the total amount of withdrawals and the total number of withdrawals each month.

1. The operation of SQL is as follows:

SELECT id,SUM (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 11 MONTH),'% Ymi% m') AND `status` = 1 then money else 0 end) as'0 end) as' 0The sum (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 10 MONTH),'% Ymurf% m') AND `status` = 1 then money else 0 end) as'1) '% Ymuri% m') = date_format (DATE_SUB (curdate (), INTERVAL 9 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as'2) as'2 date_format (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 8 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as'3 '% Ymuri% m') = date_format (DATE_SUB (curdate (), INTERVAL 7 MONTH),'% Ymurk% m') AND `status` = 1 then money else 0 end) as'4 as'4 sum (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 6 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as'5 '% Ymuri% m') = date_format (DATE_SUB (curdate (), INTERVAL 5 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as' 6as'6) date_format (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 4 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as' 7mm (case when FROM_UNIXTIME (addTime)) '% Ymuri% m') = date_format (DATE_SUB (curdate (), INTERVAL 3 MONTH),'% Ymurk% m') AND `status` = 1 then money else 0 end) as' 8The date_format sum (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 2 MONTH),'% Ymurk% m') AND `status` = 1 then money else 0 end) as' 9status` (case when FROM_UNIXTIME (addTime) '% Ymuri% m') = date_format (DATE_SUB (curdate (), INTERVAL 1 MONTH),'% Ymuri% m') AND `status` = 1 then money else 0 end) as'10 as'10 sum (case when FROM_UNIXTIME (addTime,'%Y-%m') = date_format (DATE_SUB (curdate (), INTERVAL 0 MONTH),'% Ymurt% m') AND `status` = 1 then money else 0 end) as' 11'FROM ims_checkout_task

2. The database returns as follows:

3. Key words: case when

/ / process control statement case syntax, for example, if the sex field value is 1, the male is output; if the sex value is 2, the female is output; otherwise, the other CASE sexWHEN'1' THEN 'male' WHEN'2' THEN 'female' ELSE 'other END// is output, so the above SQL is, if the condition is true, the money field of the withdrawal amount is output, otherwise the output is 0.

Time processing

/ / A pair of timestamps are formatted into 2018-10FROM_UNIXTIME (addTime,'%Y-%m') / / SQL to obtain the current time format 2019-08. According to the expr value, the DATE _ FORMAT (DATE_SUB (CURDATE (), INTERVAL 0 MONTH),'% Ymuri% m') / / function is used to display date / time data in different formats. The DATE_FORMAT (date,format) / / function subtracts the specified interval from the date. The DATE_SUB (date,INTERVAL expr type) / / function returns the current date. CURDATE ()

For example

SELECT NOW (), CURDATE (), CURTIME ()

For example

SELECT DAYOFWEEK (NOW ()), WEEKDAY (now ()), DATE_FORMAT (NOW (), "% w"), NOW ()

Second, summarize the order quantity on a weekly basis for the credit order data table. For example, today is Tuesday, return the daily order summary data from Monday to Tuesday, and so on.

1. The operation of SQL is as follows:

SELECT id,SUM (case when DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') = 1 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (date_add (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day), 2) AND `pay_ status` = 1 then 1 else 0 end) as'0 else (case when DATE_FORMAT (FROM_UNIXTIME (pay_time)) '% Ymuri% mmi% d'),'% w') = 2 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (date_add (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day), 2) AND `pay_ status` = 1 then 1 else 0 end) as'1 else (pay_time,'%Y-%m-%d') '% w') = 3 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (date_add (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day), 2) AND `pay_ status` = 1 then 1 else 0 end) as' 2) case when DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') = 4 AND WEEK (date_add (curdate (), interval 6 day) 2) = WEEK (date_add (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day), 2) AND `pay_ status` = 1 then 1 else 0 end) as'3) sum (case when DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') = 5 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (FROM_UNIXTIME (pay_time) '% Ymuri% interval% d'), 2) AND `pay_ status` = 1 then 1 else 0 end) as' 4) sum (case when DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') = 6 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (date_add (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day) 2) AND `AND WEEK status` = 1 then 1 else 0 end) as' 5The pay_time,'%Y-%m-%d' sum (case when DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') = 0 AND WEEK (date_add (curdate (), interval 6 day), 2) = WEEK (FROM_UNIXTIME (pay_time,'%Y-%m-%d'), interval 6 day), 2) AND `pay_ status` = 1 then 1 else 0 end) as' 6'FROM ims_integral_order

2. The database returns as follows:

3. Key words

/ / format the timestamp and return the number of weeks. Note that the return value on Sunday is 0DATE_FORMAT (FROM_UNIXTIME (pay_time,'%Y-%m-%d'),'% w') / / returns WEEK (DATE_ADD (CURDATE (), interval 6 day), 2) of the current week of the year. / / the WEEK (date,mode) / / function adds the specified time interval to the date. DATE_ADD (date,INTERVAL expr type) / / so the above query condition is the week and the week at the same time to meet the SQL monthly statistics of the data or weekly statistics of the data how to write the code to share here, I hope the above content can be of some help to everyone, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.