使用mysql 8窗口函数

Question

我的薪水表是这样的，

employeeId  Salary     salaryEffectiveFrom       
    19966   10000.00    2022-07-01
    19966   20000.00    2022-07-15

我的角色/等级表是这样的，

employeeId       grade      roleEffectiveFrom   
    19966       grade 3         2022-07-01  
    19966       grade 2         2022-07-10         

我正试图通过考虑两个表中的生效日期来获得一个职等所支付的工资。

三年级自2022年7月1日起生效。2年级自7月10日起生效，意味着3年级至7月9日即9天生效。二年级由七月十日至二零二二年七月十日起生效。

10000的薪金由7月1日至2022年7月14日至2022年7月14日至2022年7月14日至2022年，20000的薪金自15日起生效。因此，3年级的工资为10000英镑，为期9天，2年级的工资为10000英镑，为期4天，2年级的工资为20000英镑，从10日后开始。生效日期的角色优先于生效日期的薪资。

这个问题，

SELECT  er.employeeId,
        es.salary,
        `grade`,
        date(er.effectiveFrom) roleEffectiveFrom,
        date(es.effectiveFrom) salaryEffectiveFrom,
        DATEDIFF(LEAST(COALESCE(LEAD(er.effectiveFrom) 
OVER (PARTITION BY er.employeeId ORDER By er.effectiveFrom),
        DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
        DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
        er.effectiveFrom) as '#Days'  ,
        ROUND((salary * 12) / 365, 2) dailyRate
FROM EmployeeRole  er 
     join EmployeeSalary es ON (es.employeeId = er.employeeId)
      and er.employeeId = 19966
;

给我下面的结果集，

employeeId  Salary      grade      roleEffectiveFrom    salaryEffectiveFrom    Days   dailyRate
    19966   10000.00    grade 3         2022-07-01          2022-07-01         0         328.77
    19966   20000.00    grade 3         2022-07-01          2022-07-15         9         657.53
    19966   10000.00    grade 2         2022-07-10          2022-07-01         0         328.77
    19966   20000.00    grade 2         2022-07-10          2022-07-15         22       657.53

grade3在7月份生效9天，所以我想使用每日薪酬栏，328.77 *9= 2985.93作为一个单独的列，以获得这9天的总工资，但是我无法做到，因为我得到了错误的行的天数，也就是说9应该是第一行的结果。

[医]小提琴

Answer 1

我想如果是我的话，我会生成一个列表，其中包含每天的有效级别和薪水，然后在最后汇总。看看这把小提琴：

https://dbfiddle.uk/4t2RW2M2

我从聚合查询开始，这样我们就可以看到输出，然后我将查询的片段分解出来，以显示中间输出。下面是最终输出的图像和生成该输出的查询：

​

​

SELECT grade, gradeEffective, salary, salaryEffective,
  min(dt) as startsOn, max(dt) as endsOn, count(*) as days,
  dailyRate, 
  sum(dailyRate) as pay
FROM (
  SELECT DISTINCT dt, grade, gradeEffective, salary, salaryEffective, 
    ROUND((salary * 12) / 365, 2) as dailyRate
  FROM (
    SELECT dts.dt, 
      first_value(r.grade)         OVER w as grade,
      first_value(r.effectiveFrom) OVER w as gradeEffective,
      first_value(s.salary)        OVER w as salary,
      first_value(s.effectiveFrom) OVER w as salaryEffective
    FROM (
      WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
      SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
    ) dts
    LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
    LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
    WINDOW w AS (
      PARTITION BY dts.dt 
      ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
      ROWS UNBOUNDED PRECEDING
    )
  ) z
) a GROUP BY grade, gradeEffective, salary, salaryEffective, dailyRate
ORDER BY min(dt);

现在，我做的第一件事是使用递归的CTE创建一个日期列表：

WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates

从7月1日到7月31日的日期列表。

取下日期列表，然后将两个表连接到其中，如下所示：

SELECT *
FROM (
  WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
  SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
) dts
LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom

具有大于或等于生效日期的dt。注意，在第9天之后，您开始获得每个日期的重复行。

​

​

我们将创建一个窗口来获取每个日期的薪等和薪资的第一个值，我们将首先按角色effectiveFrom，然后按effectiveFrom进行排序，以满足您的优先级条件。

SELECT dts.dt, 
  first_value(r.grade)         OVER w as grade,
  first_value(r.effectiveFrom) OVER w as gradeEffective,
  first_value(s.salary)        OVER w as salary,
  first_value(s.effectiveFrom) OVER w as salaryEffective
FROM (
  WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
  SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
) dts
LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
WINDOW w AS (
  PARTITION BY dts.dt 
  ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
  ROWS UNBOUNDED PRECEDING
);

​

​

这仍然会在某些日期留下多个条目，尽管它们是重复的，所以让我们在一个新的查询中使用这个输出，使用DISTINCT只留下一行的一个副本，并利用这个机会添加每日费率字段：

SELECT DISTINCT dt, grade, gradeEffective, salary, salaryEffective, 
  ROUND((salary * 12) / 365, 2) as dailyRate
FROM (
  SELECT dts.dt, 
    first_value(r.grade)         OVER w as grade,
    first_value(r.effectiveFrom) OVER w as gradeEffective,
    first_value(s.salary)        OVER w as salary,
    first_value(s.effectiveFrom) OVER w as salaryEffective
  FROM (
    WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
    SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
  ) dts
  LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
  LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
  WINDOW w AS (
    PARTITION BY dts.dt 
    ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
    ROWS UNBOUNDED PRECEDING
  )
) z;

这将产生重复的每日数据。

​

​

现在我们所要做的就是使用聚合来提取每个级别和薪水的总和，这是我开始使用的查询。

如果这是你想要的，或者有什么不清楚的话，请告诉我。

由于问题中没有详细说明开始和结束条件，我只是随意地创建了日期列表。根据两个表中的第一个effectiveFrom生成列表并不困难，下面是一个从该开始日期一直运行到当前的示例：

WITH RECURSIVE dates(n) AS (
  SELECT min(effectiveFrom) FROM (
    select effectiveFrom from EmployeeRole UNION
    select effectiveFrom from EmployeeSalary
  ) z
  UNION SELECT n + INTERVAL 1 DAY FROM dates WHERE n <= now()
)
SELECT n as dt FROM dates

我也没有处理多名员工，因为只有一个给出，我只是猜测实际数据的形状。

Answer 2

合并两个表日期，然后使用相关子查询

with cte as
(
SELECT employeeid,effectivefrom from EMPLOYEEROLE
union  
select employeeid,effectivefrom from employeesalary
)
,cte1 as
(select employeeid,effectivefrom,
          coalesce(
            date_sub(lead(effectivefrom) over (partition by employeeid order by effectivefrom),interval 1 day) ,
            now())  nexteff
from cte
)
select *,
        datediff(nexteff,effectivefrom) + 1 diff, 
      (select grade from employeerole e where e.effectivefrom <= cte1.effectivefrom order by e.effectivefrom desc limit 1) grade,
      (select salary from employeesalary e where e.effectivefrom <= cte1.nexteff order by e.effectivefrom desc limit 1) salary
from cte1;


+------------+---------------------+---------------------+------+---------+--------+
| employeeid | effectivefrom       | nexteff             | diff | grade   | salary |
+------------+---------------------+---------------------+------+---------+--------+
|      19966 | 2022-07-01 00:00:00 | 2022-07-09 00:00:00 |    9 | grade 3 |  10000 |
|      19966 | 2022-07-10 00:00:00 | 2022-07-14 00:00:00 |    5 | grade 2 |  10000 |
|      19966 | 2022-07-15 00:00:00 | 2022-10-08 08:51:49 |   86 | grade 2 |  20000 |
+------------+---------------------+---------------------+------+---------+--------+
3 rows in set (0.003 sec)


with cte as
(
SELECT employeeid,effectivefrom from EMPLOYEEROLE
union  
select employeeid,effectivefrom from employeesalary
)
,cte1 as
(select cte.employeeid,effectivefrom,
          coalesce(
            date_sub(lead(effectivefrom) over (partition by employeeid order by effectivefrom),interval 1 day) ,
            last_day(maxdt))  nexteff
from cte
JOIN (select cte.employeeid,max(effectivefrom) maxdt from cte group by employeeid) c1
      on c1.employeeid = cte.employeeid
)
select *,
        datediff(nexteff,effectivefrom) + 1 diff, 
      (select grade from employeerole e where e.effectivefrom <= cte1.effectivefrom order by e.effectivefrom desc limit 1) grade,
      (select salary from employeesalary e where e.effectivefrom <= cte1.nexteff order by e.effectivefrom desc limit 1) salary
from cte1;

+------------+---------------------+---------------------+------+---------+--------+
| employeeid | effectivefrom       | nexteff             | diff | grade   | salary |
+------------+---------------------+---------------------+------+---------+--------+
|      19966 | 2022-07-01 00:00:00 | 2022-07-09 00:00:00 |    9 | grade 3 |  10000 |
|      19966 | 2022-07-10 00:00:00 | 2022-07-14 00:00:00 |    5 | grade 2 |  10000 |
|      19966 | 2022-07-15 00:00:00 | 2022-07-31 00:00:00 |   17 | grade 2 |  20000 |
+------------+---------------------+---------------------+------+---------+--------+
3 rows in set (0.004 sec)

Answer 3

您可以开始添加两个新列(即tmpFrom和tmpTo)，它们应该给出计算9天所需的正确日期。

SELECT
    er.employeeId,
    es.salary,
    `grade`,
    date(er.effectiveFrom) roleEffectiveFrom,
    date(es.effectiveFrom) salaryEffectiveFrom,
    DATEDIFF(LEAST(COALESCE(LEAD(er.effectiveFrom) 
                                 OVER (PARTITION BY er.employeeId ORDER By er.effectiveFrom),
                         DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
                         DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
                er.effectiveFrom) as '#Days'  ,
    ROUND((salary * 12) / 365, 2) dailyRate,
    date(er.effectiveFrom) tmpFrom,
    (select e2.effectiveFrom 
           from EmployeeRole e2
           where e2.employeeId = er.employeeId and e2.effectiveFrom > er.effectiveFrom
           order by e2.effectiveFrom
           limit 1) as tmpTo
FROM EmployeeRole  er 
join EmployeeSalary es ON (es.employeeId = er.employeeId)
      and er.employeeId = 19966
order by er.effectiveFrom
;

在上面的查询中，我使用了一个子选择，这可能会影响性能。您可以学习窗口函数，并检查是否有比这个子查询更适合您需要的函数。

这取决于您计算这两列之间的天数，但是您也应该解决NULL值，这个值应该是月底(但我不确定我是否正确地记得您的问题……)

请参阅：DBFIDDLE