MySQL Classic programming problem

Question of the week number

1 calculation date is week

This seems like a simple question, and you can use MySQL built-in functions to calculate

(1) weekday (date) whose return value is 0-6, 0 for Monday, 6 for Sunday;

(2) DayOfWeek (date) whose return value is 1-7, but it is more difficult to understand, 1 represents the Sunday, 6 represents the Saturday;

(3) Dayname (date) whose return value is directly a specific noun, Monday, Tuesday ...

The above three methods can be calculated according to the date of the week, but there are certain limitations, relative weekday () Most in line with the Chinese habit.

We also have another convenient method of calculation, such as we know the exact day of the week, such as: 2000-01-03 is Monday, then I can calculate a date in the following way belongs to the weeks

Select (DateDiff (date, ' 2000-01-03 ') +1)%7, the result is 1 is Monday, the result is 2 is Tuesday, the result is 7 is Sunday

Select (DateDiff (' 2017-12-22 ', ' 2000-01-03 ') +1)%7 = 5 2017-12-22 is Friday, we can take any of the Monday date as the reference base to calculate, very consistent with the Chinese thinking habits.

2 GROUP BY week

This problem if you use the System function week (date), when a Zhouquanin week will split a week into two weeks of statistics, as follows:

Number of tests established:

CREATE TABLE ' Sales ' (
' id ' int (one) not NULL auto_increment,
' Date ' datetime is not NULL,
' Cost ' int (ten) unsigned not NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=9 DEFAULT Charset=utf8;

-- ----------------------------
--Records of sales
-- ----------------------------

INSERT into ' Sales ' VALUES (' 8 ', ' 2017-01-01 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 1 ', ' 2017-11-30 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 2 ', ' 2017-12-01 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 3 ', ' 2017-12-02 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 4 ', ' 2017-12-31 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 5 ', ' 2018-01-01 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 6 ', ' 2018-01-02 00:00:00 ', ' 100 ');
INSERT into ' Sales ' VALUES (' 7 ', ' 2018-01-10 00:00:00 ', ' 100 ');

Use the week function to get weekly cost per week

Select Week (date), sum (cost)
From sales
GroupBy Week (date);

　　

The result of week function calculation is to count 2017-12-31,2018-01-01,2018-01-02 as a week, and it is counted as the last week of 2017, in practical application we treat 2018-01-01 ~ 2018-01-07 as a week, 2017-12-25 ~ 2017-12-31 is regarded as a week, so the week function clearly does not meet our actual use requirements.

We introduce a method similar to the calculation of the day of the week, the same use Date reference method, 2017-01-02 for Monday, convenient to calculate we use it as a reference

Select Floor (DateDiff (date, ' 2017-01-02 ')/7) as Week_number,
Date_add (' 2017-01-02 ', Interval Floor ((DateDiff (date, ' 2017-01-02 ')/7) *7 Day) as Begin_dt,
Date_add (' 2017-01-02 ', Interval Floor ((DateDiff (date, ' 2017-01-02 ')/7) *7+6 Day) as End_dt,
SUM (cost) as Total
From sales
Group BY Floor ((DateDiff (date, ' 2017-01-02 ')/7);

　　

This will not only calculate the week, but also calculate the start and end times of the week.

MySQL Classic programming problem