In our development process, we often encounter members or businesses to rank revenue, sorted according to the month, week or day; of course, you can first get the data used, and then according to the functions you want to process the data accordingly. Next, I want to show you how to operate MySQL directly, using some statistical functions of MySQL, it is very convenient to filter data.

Firstly, we introduce how to use php to get the time stamps of the day, week, month and yesterday.

• Get today's start and end timestamps

    $today_start=mktime(0,0,0,date('m'),date('d'),date('Y'));
    $today_end=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1;

• Get the start and end timestamps of yesterday

    $yesterday_start=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
    $yesterday_end=mktime(0,0,0,date('m'),date('d'),date('Y'))-1;

• Get last week's start and end timestamps

    $lastweek_start=mktime(0,0,0,date('m'),date('d')-date('w')+1-7,date('Y'));
    $lastweek_end=mktime(23,59,59,date('m'),date('d')-date('w')+7-7,date('Y'));

• Get the start and end timestamps of the week

    $thisweek_start=mktime(0,0,0,date('m'),date('d')-date('w'+1),date('Y'));
    $thisweek_end=mktime(23,59,59,date('m'),date('d')-date('w')+7,date('Y'));

• Get the start and end timestamps for this month

    $thismonth_start=mktime(0,0,0,date('m'),1,date('Y'));
    $thismonth_end=mktime(23,59,59,date('m'),date('t'),date('Y'));

 Below are some basic operations of MySQL data acquisition day, week, month and so on.

• Query the data of the day

    SELECT * FROM table name WHERE TO_DAYS (time field) = TO_DAYS(NOW());

• Query yesterday's data

    SELECT * FROM table name WHERE TO_DAYS (NOW () - TO_DAYS (time field) = 1;

• Query the data of the week

    SELECT * FROM table name WHERE YEARWEEK (DATE_FORMAT (time field,'%Y-%m-%d')= YEARWEEK (NOW ());

• Query last week's data

    SELECT * FROM table name WHERE YEARWEEK (DATE_FORMAT (time field,'%Y-%m-%d')= YEARWEEK (NOW ()-1;

• Query the data of that month

    SELECT * FROM table name WHERE DATE_FORMAT (time field,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');

• Query last month's data

    SELECT * FROM table name WHERE PERIOD_DIFF (DATE_FORMAT (NOW(),'%Y%m'), DATE_FORMAT (time field,'%Y%m')=1;

• Query the data of that year

    SELECT * FROM table name WHERE YEAR (time field) = YEAR(NOW());

• Query the data of the last 7 days

    SELECT * FROM table name WHERE DATE_SUB (CURDATE (), INTERVAL 7 DAY)<=DATE (time field);

Next, we will use some of the above query operations to process the revenue ranking function shown in the figure below.

• The data format is shown in the figure above, and the code is directly shown below.

switch ($style) {

            case 'total'://General list
                // $total = M('income_log')->group('userid')->sum($field);
                $total = M('')->query("SELECT userid,SUM($field) as sum FROM income_log GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10");
                break;
            case 'day'://Daily list
                $total = M('')->query("SELECT userid,SUM($field) as sum FROM income_log where TO_DAYS(date)=TO_DAYS(NOW()) GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10"); 
                break;
            case 'week'://Zhou Bang
                $total = M('')->query("SELECT userid,SUM($field) as sum FROM income_log where YEARWEEK(DATE_FORMAT(date,'%Y-%m-%d'))=YEARWEEK(NOW()) GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10"); 
                break;
            case 'month'://Monthly list
                $total = M('')->query("SELECT userid,SUM($field) as sum FROM income_log where DATE_FORMAT(date,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10"); 
                break;
            default: 
                break;
        }

The following is the results of the data obtained, only need to be processed; here, I use group by to group the data with user id to facilitate the acquisition of user information and ranking;

Array
(
    [0] => Array
        (
            [userid] => 2
            [sum] => 6.00
        )

    [1] => Array
        (
            [userid] => 1
            [sum] => 6.00
        )

    [2] => Array
        (
            [userid] => 3
            [sum] => 1.00
        )

    [3] => Array
        (
            [userid] => 4
            [sum] => 0.20
        )

)

OK, so much to share today. Because I know little about mysql query rate optimization, I will not elaborate - - and other knowledge enriched and then share this content with you.