It is a common requirement that the statistical period is days. Weekly and monthly reports are more common. Long cycle projects, and even annual report requirements. I have mastered mysql by day, how to implement by year, by month, by week statistics?
1. Skills mastered: Statistics by day
Implementing a statistical period of days is simple. Specifically, the date() function returns the date of the time data, that is, only year, month and day, with no hour, minute and second information. Combined with Group BY, daily statistics can be realized.
There are many data indicators based on the statistical period of day, for example, the number of newly registered users per day.
Select date(created_at) as registration date, count(user_id) AS user number from users group by registration date order by registration dateCopy the code
2. Expand your skills by reasoning from what you know
Since the date() function is available, are there any corresponding year, month, week functions available? It’s purely my theory. Give it a shot.
And it worked. The fly in the ointment, however, is that the weeks and months are returned without years. When the amount of data crosses a year, it adds up the data for the same number of weeks or months each year. How to implement x, x month and x, x week? Already knowledge reserve goes reasoning, did not find an answer, that searches directly!
3. Search for answers
Column_name date_format(‘%Y-%m’) instead of month() in mysql
What if the keyword M for month is replaced by week? Give it a try. Try separately: date_format(column_name,’%Y-%w’) and date_format(column_name,’%Y-%w’).
Does the data return the wrong result? Not the week of the year as expected. The lowercase w returns the day of the week, and the uppercase W returns the day of the week. How to get the number of weeks this year, the realization of the statistical cycle of the weekly report?
Awareness of the lack of knowledge, leak
When I taught myself programming, I liked to reason from what I knew and expand my skills. Reasoning usually brings surprises, and when reasoning isn’t enough, search for the best. When you search for answers to specific questions, you can often find a piece of knowledge that is lacking. In my case, I realized I wasn’t familiar with keywords or common grammar for dates.
Just when the search encountered xiangdaome66 articles, there is a good collation.
MySQL date format (format) Value range.
| value | meaning | |
|---|---|---|
| seconds | % S, % S | Two digit seconds (00,01… , 59) |
| points | % % I, I | Two digit points (00,01… , 59) |
| hours | %H | 24-hour, two-digit hours (00,01… 23), |
| %h | 12-hour system, two-digit hours (00,01… 12), | |
| %k | 24-hour system, number of hours (0,1… 23), | |
| %l | 12 hour system, number of hours (0,1… 12), | |
| %T | 24-hour system, time format (HH:mm: SS) | |
| %r | 12-hour format (HH :mm: SS AM or PM) | |
| %p | AM in the morning or PM in the afternoon | |
| weeks | %W | Name of each day of the week (Sunday,Monday… , Saturday) |
| %a | Abbreviations for the names of each day of the week (Sun,Mon… , the Sat) | |
| %w | Identify the week in numeric format (0=Sunday,1=Monday,… , 6 = Saturday) | |
| %U | Numbers indicate the number of weeks. Sunday is the first day of the week | |
| %u | The number indicates the number of weeks. Monday is the first day of the week | |
| day | %d | A two-digit number indicates the number of days in the month (01,02… 31), |
| %e | Numbers represent days of the month (1,2… 31), | |
| %D | English suffixes indicate the number of days in a month (1st,2nd,3rd…). | |
| %j | Three digit number for the number of days in the year (001,002… , 366). | |
| month | %M | English month name (January,February,… Another awarding) |
| %b | English abbreviation Month name (Jan,Feb,… , Dec) | |
| %m | Two digits indicate the month (01,02… 12), | |
| %c | The numbers represent the months (1,2… 12), | |
| years | %Y | Four digit year (2015,2016…) |
| %y | Two-digit years (15,16…) | |
| The text output | The % character | Output text content directly |
A single knowledge point, a little to a certain knowledge point, can let their knowledge and skills on a step.
Help is also social, ask people with red envelopes
The table above is quite useful, but it still doesn’t solve the need for how to obtain the “week of a year.”
Although actively searching for answers is a good habit. But it takes a lot of time to find the answer, and there are people with the same goal to help each other, why not ask someone? Learning is essentially a social activity. When learning the same skill, I like to go to several study groups with a good atmosphere. I often share my thoughts and notes in the group, and I also take the initiative to help others, or put forward my own questions to trigger discussion. Asking for help is a good social act.
At this point I put the question to programming learning group, and with red envelopes for advice. After pointing out the answer quickly, using concat() function to concatenate.
For everyone to copy and learn, put the code:
Select concat(date_format(created_AT,'%Y-'),week(created_at)) as date_format(created_at,'%Y-') Count (user_id) As number of users from users group by week order by weekCopy the code6, summary
To sum up, date_format(), concat(), week() and other functions can be used in mysql to fulfill the requirements of monthly and weekly statistics commonly used in data analysis. Key statements are:
Date (column_name) as year month dayDate_format (column_name, 'Y - % m %) as yearsConcat (date_format (column_name, '% Y -'), week (column_name) as in weeks
In addition to knowledge points, I also put my own ideas to explore and expand skills in this note. Does it inspire you? If so, let me know in a comment or like and encourage me to share more.
Special disclaimer: the data in this note is a local database, only for my practice, not the official data of any product website.