When doing data analysis, we often hear the concepts of year-on-year and month on month. When publishing statistical data, enterprises and organizations usually like to compare the previous historical data with the year-on-year and month on month data to illustrate the changes of the data. For example, the Bureau of Statistics announced that in January 2022, CPI increased by 0.9% year-on-year and 0.6% month on month.
In fact, in the Database-based data analysis scenario, month on month and year-on-year are one of the typical complex calculation scenarios, especially before the analysis functions of commercial databases such as Oracle appear. Taking MySQL as an example, Lag and Lead functions were introduced in version 8.0. These two functions combined with windowing function effectively improve the implementation efficiency of year-on-year, month on month and other complex operations. In 5 This kind of query can only be implemented by self embedding in MySQL X series.
Let's take a simple example, MySQL 5 X and 8.0 are specifically calculated year-on-year and month on month.
See table for example data:
CREATE TABLE sales (
`product ID` varchar(20),
`sales volumes` int(20) ,
`Sales time` timestamp(6) NULL DEFAULT NULL
)
INSERT INTO sales VALUES ('C1001', 15, '2020-06-01 10:10:12');
INSERT INTO sales VALUES ('C1002',26, '2020-05-02 0:10:12');
INSERT INTO sales VALUES ('C1003', 21, '2020-04-03 0:10:12');
INSERT INTO sales VALUES ('C1003', 23, '2020-04-04 0:10:12');
INSERT INTO sales VALUES ('C1003', 0, '2020-03-05 0:10:12');
INSERT INTO sales VALUES ('C1001', 16, '2020-02-06 3:0:12');
INSERT INTO sales VALUES ('C1002', 32, '2020-01-07 0:10:12');
INSERT INTO sales VALUES ('C1001', 16, '2019-12-08 0:12:24');
INSERT INTO sales VALUES ('C1001', 32, '2019-06-09 0:12:24');
INSERT INTO sales VALUES ('C1002', 17, '2019-05-09 0:12:24');
1,MySQL 5.x: Year on year and proportion calculation is realized through sub query and association
Take the total sales value of different years counted by month and year, and calculate the month on month (total sales compared with the previous period) and year-on-year (total sales compared with the same period last year) as an example.
Sample table structure and data
Calculate month on month and year-on-year through SQL:
select year(c.Sales time) yy,month(c.Sales time) mm,
concat(ifnull(abs(round((sum(c.sales volumes)-ss1)/ss1*100,2)),0),'%') Year on year,
concat(ifnull(abs(round((sum(c.sales volumes)-ss2)/ss2*100,2)),0),'%') Month on month
from sales c
left join (select month(a.Sales time) mm1,
year(a.Sales time) yy1,
sum(a.sales volumes) ss1
from sales a
GROUP BY mm1,yy1) a
on month(c.Sales time) = a.mm1
and a.yy1 = year(c.Sales time)-1
left join (select month(a.Sales time) mm2,
year(a.Sales time) yy2,
sum(a.sales volumes) ss2
from sales a
GROUP BY mm2,yy2) b
on (b.yy2 = year(c.Sales time) and b.mm2+1 = month(c.Sales time) OR (yy2=year(c.Sales time)-1
AND b.mm2 = 12 AND month(c.Sales time) = 1))
group by yy, mm
order by yy,mm asc
Calculation results:
2. MySQL 8.0: year on year and proportion calculation through analysis function**
MySql8.0 supports Lead and Lag analysis functions. Although it can greatly improve the efficiency of the same month and month on month calculation, it still needs to write SQL statements for processing.
2. 1. Year on year calculation
select t2.particular year,t2.month,concat(round((t2.quantity-t1.quantity)/t1.quantity,2)*100,'%') as Year on year from ( SELECT year(Sales time) as particular year,month(Sales time) as month,sum(sales volumes) as quantity from sales group by year(Sales time),month(Sales time) order by year(Sales time) desc, month(Sales time) desc ) t1 ,( SELECT year(Sales time) as particular year,month(Sales time) as month,sum(sales volumes) as quantity from sales group by year(Sales time),month(Sales time) order by year(Sales time) desc, month(Sales time) desc ) t2 where t1.particular year=t2.particular year-1 and t1.month=t2.month
2. 2. Calculate month on month ratio
SELECT
mm,
CONCAT(
ROUND(
IFNULL(
(xl - first_xl) / first_xl * 100,
2
),
0
),
'%'
) AS Month on month
FROM
(
SELECT
mm,
xl,
lead (xl, 1) over (ORDER BY mm DESC) AS first_xl
FROM
(
SELECT
DATE_FORMAT(Sales time, '%Y-%m') AS mm,
sum(sales volumes) AS xl
FROM
sales
GROUP BY
DATE_FORMAT(Sales time, '%Y-%m')
) t
) a
After SqlServer2008R2 and Oracle10g, Lag and Lead analysis functions are provided. The specific calculation logic and usage are the same as mysql8 0 is similar.
3. Computing engine using BI tools
For such complex computing scenarios, Business Intelligence BI data analysis tools provide a more efficient solution. with Wyn Enterprise embedded business intelligence software For example, its built-in wax analysis expression and fast calculation engine provide the ability to directly realize complex calculations such as year-on-year and month on month, without writing complex and lengthy SQL.
3. 1. Use the built-in year-on-year and month on month fast calculation function**
Year on year, month on month and other calculations are generally the standard functions of BI tools, which can be directly realized by setting.
3. 2 using data analysis expressions
If the built-in fast calculation cannot meet the requirements, more complex calculation can be realized by analyzing the expression. Analysis expression is a more flexible and powerful data calculation method. Through rich functions, users can combine freely like Excel formula to realize more powerful analysis ability. The analysis expression carries out business calculation based on the data model, and completes a complex business logic calculation with some defined functions and correct syntax. This allows users to use data more flexibly and make maximum use of data.
Dear bosses, by comparing the differences between SQL and BI data analysis tools in dealing with complex calculations such as year-on-year and month on month, we can find that professional tools are more convenient in data calculation and processing capacity. In the future, if there are similar analysis and calculation requirements, select BI analysis tool It's perfectly appropriate to deal with it.