Learning link
mysql group statistics by date
Blogger-When the Camellia Blooms [Mysql Column Learning]
mysql group statistics by date
Everyone must have used Mysql’s date_format function. It is generally used for date and time conversion, as shown below
# It can be concluded that 2023-01-01 08:30:50
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d %H:%i:%s')
# Or as follows, you can get 2023-01-01
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d')
# Or as follows, you can get it, convert the string into a date/time value
#(The functions of the STR_TO_DATE function and the DATE_FORMAT function are opposite.
# If str cannot be parsed according to format, the STR_TO_DATE function will return NULL
# If any of the parameters is NULL, the STR_TO_DATE function will return NULL
# )
select str_to_date('2023-10-02','%Y-%m-%d') -- the format is consistent with date_format
However, many times are not limited to these most basic uses. When we need to have higher requirements for time data, we need to have a better understanding of this function, such as grouping and statistics of this period of time according to a certain time period. Data, in this issue we mainly talk about the operation of group statistics, let’s go directly to the topic.
Statistics by day
Statistics by day is a relatively basic statistical method, and most people should be able to write it out directly.
SELECT date_format( o.bill_date, '%Y-%m-%d' ) evertday, COUNT(*) orderNumFROM TABLE o GROUP BY everday ORDER BY everday DESC
Statistics by week
The first type
SELECT
subdate( date_format( o.bill_date, '%Y-%m-%d' ),
date_format( o.bill_date, '%w' )
) weekend,
COUNT(*) orderNum
FROM
test o
GROUP BY
weekend
ORDER BY
weekend DESC
LIMIT 9
It can be seen here that the dates have been sorted and grouped by week. The data from July 30 to August 5 has been counted in the weekend of July 30. Let’s briefly analyze it: First date_format(o.bill_date,’%Y- %m-%d’) everyone knows that the date will be changed into yyyy-MM-dd format
And date_format(o.bill_date,’%w’) will count the day of the week that this day is. I have compiled a table for this parameter at the end for everyone to view and use. So August 2nd happens to be Wednesday, and then pass Use the subdate function to subtract the date to calculate a corresponding time, or some friends like to use the DATE_SUB function, which has the same effect. In this way, the time of last weekend can be calculated, that is, there is a cycle from last weekend to this weekend.
Second type
SELECT weekday( bill_date ) AS weekday, date_format( date_add( bill_date, INTERVAL - ( weekday( bill_date ) + 1 ) DAY ), '%Y-%m-%d' ) AS monday, count(1) FROM t GROUP BY mondayorder BY monday DESC
The overall idea is similar. I calculated the time point of last Sunday for group by.
Monthly statistics
Monthly statistics are another way of thinking, because weekly statistics still have the concept of day, but monthly statistics can omit the date.
SELECT DATE_FORMAT ( bill_date, '%Y-%m' ) months, count(1) count FROM t GROUP BY monthsorder BY months DESC
Statistics by year
This is the same as counting by month. It only counts by year.
select DATE_FORMAT( bill_date, '%Y' ) months,count(1) count from t group by monthsorder by months desc
| Format | Description |
|---|---|
| %a | Three-character abbreviated working day name, such as: Mon, Tue, Wed, etc. |
| %b | Three-character abbreviated month name, for example: Jan, Feb, Mar, etc. |
| %c | Month value represented by numbers, for example: 1, 2, 3…12 |
| %D | Represents a day in the month as a number (can be understood as the number of days in the month), followed by an English suffix, for example: 1st, 2nd , 3rd, etc. |
| %d | Represents a day in the month as a numerical value, if it is less than 10 Number, the number needs to add leading 0, for example: 00,01,02, …31 |
| %e | Use a numerical value to represent a day in the month. Numbers less than 10 do not need to add leading 0, for example: 1, 2,… 31 |
| %f | Microseconds, range (000000 to 999999) |
| %H | Hour in 24-hour format, with leading 0 added, for example: 00,01…23 |
| %h | Hour in 12-hour format, with leading 0 added, for example: 00,01 … 12 |
%I |
Hour in the same 12-hour format as %h, with leading 0 added, for example: 00,01 … 12 |
| %i | Minutes, ranging from 00 to 59 |
| %j | The day of the year, add leading 0, for example: 001,002,… 366 |
| %k | Hour in 24-hour format, without leading 0, for example: 0,1,2 … 23 |
| %l | Hour in 12-hour format, without leading 0, for example: 1,2 … 12 |
| %M | Full name of the month, for example: January, February…December |
| %m | The month name displayed in numerical form, with leading 0 added, for example: 01,02,… 12 |
| %p | AM or PM, depending on other time specifiers |
| %r | Indicates time, 12-hour format hh:mm:ss AM/PM |
| %S | seconds, leading Add 0, for example: 00,01…59 |
| %s | Same as %S |
| %T | represents time, 24-hour format hh:mm:ss |
| %U | represents the number of weeks, Sunday is the first day of the week, for example: 00,01,02…53, leading 0 Week number |
| %u | represents the number of weeks, Monday is the first day of the week, for example: When 00,01,02 … 53, the number of weeks of leading 0 |
| %V | and %U Same as %X, it works with %X |
| %v | Same as %u, it works with %x Used together |
%W |
The full name of the working day, for example: Sunday , Monday,…, Saturday |
| %w | Working days, expressed as numbers (0 = Sunday , 6 = Saturday) |
| %X | The four-digit number of the week represents the year, and the first day is Sunday, often used with %V |
| %x | The four-digit number of the week represents the year, the first The day is Monday, often used with %v |
| %Y | represents the year, four digits Number, for example: 2000, 2001,…etc. |
| %y | represents the year, two digits, For example: 00, 01,…etc |
| %% | Add the percentage (%) character to the output |