时间戳

首页 > 解决方案 > 计算给定日期范围内每个月的平均值

mysql - 计算给定日期范围内每个月的平均值

问题描述

我有员工表，其中每个员工都有相关的start_date、end_date和薪水。

注意：在底部您可以找到用于导入结构和数据的 SQL 代码。

+----+-------+------------+------------+---------+
| id | name  | start_date | end_date   | salary  |
+----+-------+------------+------------+---------+
|  1 | Mark  | 2017-05-01 | 2020-01-31 | 2000.00 |
|  2 | Tania | 2018-02-01 | 2019-08-31 | 5000.00 |
|  3 | Leo   | 2018-02-01 | 2018-09-30 | 3000.00 |
|  4 | Elsa  | 2018-12-01 | 2020-05-31 | 4000.00 |
+----+-------+------------+------------+---------+

问题

对于给定的日期范围，我想提取给定日期范围内每个月的平均工资。

更新：我希望有 MySQL 5.6 的解决方案，但如果有 MySQL 8+ 的解决方案（仅用于个人知识），那就太好了。

例子

如果日期范围是2018-08-01 - 2019-01-31，则 SQL 语句应该从 2018 年 8 月到 2019 年 1 月循环，它必须计算每个月的平均工资：

以下您可以看到日期范围2018-08-01 - 2019-01-31的预期结果

+------+-------+------------+
| year | month | avg_salary |
+------+-------+------------+
| 2018 | 08    | 3333.33    |
| 2018 | 09    | 3333.33    |
| 2018 | 10    | 3500.00    |
| 2018 | 11    | 3500.00    |
| 2018 | 12    | 3666.67    |
| 2019 | 01    | 3666.67    |
+------+-------+------------+

注意：我解决了这个混合 MySQL 和 PHP 代码的问题，但是对于大的日期范围，它必须执行太多的查询（每个月一个）。所以我想有一个只使用 MySQL的解决方案。

SQL 导入结构和数据

CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `salary` decimal(10,2) DEFAULT NULL
);

INSERT INTO `employees` (`id`, `name`, `start_date`, `end_date`, `salary`) VALUES
(1, 'Mark', '2017-05-01', '2020-01-31', '2000.00'),
(2, 'Tania', '2018-02-01', '2019-08-31', '5000.00'),
(3, 'Leo', '2018-02-01', '2018-09-30', '3000.00'),
(4, 'Elsa', '2018-12-01', '2020-05-31', '4000.00');

标签： mysqlsqldategroup-byaggregate-functions

解决方案

这是执行此操作的 MySQL 8.0 递归 CTE 方式。CTE 创建表中year, month最小值start_date和最大值之间所有组合end_date的employees列表，然后将其LEFT JOIN编入employees表中以获取在该特定年份和月份工作的所有员工的平均工资：

WITH RECURSIVE months (year, month) AS
(
  SELECT YEAR(MIN(start_date)) AS year, MONTH(MIN(start_date)) AS month FROM employees
  UNION ALL
  SELECT year + (month = 12), (month % 12) + 1 FROM months
  WHERE STR_TO_DATE(CONCAT_WS('-', year, month, '01'), '%Y-%m-%d') <= (SELECT MAX(end_date) FROM employees)
)
SELECT m.year, m.month, ROUND(AVG(e.salary), 2) AS avg_salary
FROM months m
LEFT JOIN employees e ON STR_TO_DATE(CONCAT_WS('-', m.year, m.month, '01'), '%Y-%m-%d') BETWEEN e.start_date AND e.end_date
WHERE STR_TO_DATE(CONCAT_WS('-', m.year, m.month, '01'), '%Y-%m-%d') BETWEEN '2018-08-01' AND '2019-01-31'
GROUP BY m.year, m.month

输出：

year    month   avg_salary
2018    8       3333.33
2018    9       3333.33
2018    10      3500.00
2018    11      3500.00
2018    12      3666.67
2019    1       3666.67

dbfiddle 上的演示

推荐阅读