时间戳

首页 > 解决方案 > 自加入？前一周工作的员工是否在 3 周前活跃 - MYSQL

mysql - 自加入？前一周工作的员工是否在 3 周前活跃 - MYSQL

问题描述

我正在尝试向生产时间数据集添加一列，该列将告诉上周工作的供应商是否也在三周前工作。当前数据集如下所示：

RowID | ProviderID | ClientID |     DOS    |   DOS (Week)  | Hours 
  1   | 1111111111 | 22222222 | 11/2/2020  |   11/1/2020   | 2.5 
  2   | 1111111111 | 33333333 | 11/5/2020  |   11/1/2020   | 1 
  3   | 1111111111 | 44444444 | 10/13/2020 |   10/11/2020  | 3

我正在尝试使用 y/n 或 1/0 获取额外的列“活动前 3 周”作为值。对于上表，我们假设提供程序于 2020 年 10 月 13 日开始。理想情况下，新列将像这样填充：

RowID | ProviderID | ClientID |     DOS    |   DOS (Week)  | Hours | Active 3 weeks Prior 
  1   | 1111111111 | 22222222 | 11/2/2020  |   11/1/2020   | 2.5   |   Yes              
  2   | 1111111111 | 33333333 | 11/5/2020  |   11/1/2020   | 1     |   Yes
  3   | 1111111111 | 44444444 | 10/13/2020 |   10/11/2020  | 3     |   No

一些额外的花絮：我们的组织使用星期日作为一周的开始，因此 DOS（周）是服务日期之前的星期日。从我到目前为止所阅读的内容来看，这里的解决方案似乎是某种自我加入，其中基本生产记录汇总为每周小时数，并与 DOS (Week) - 21 的相同 providerID 记录进行比较。

我遇到的麻烦是：我是否首先通过自联接走在正确的轨道上，以及如何根据找到匹配值的成功或失败来生成 y/n 值。另外，我怀疑基于 ProviderID 和 DOS(Week) 连接的加入可能有缺陷？这是我迄今为止一直在玩的。

请让我知道我是否可以澄清这个问题或遗漏了一些非常明显的东西。我真的很感激任何帮助，因为几天来我一直在努力找出正确的搜索词来获得答案的线索。

标签： mysqlsqldatetimedate-arithmeticdomo

解决方案

如果您正在运行 MySQL 8.0，则可以使用窗口函数和range规范：

select t.*,
    (
        max(providerid) over(
            partition by providerid 
            order by dos
            range between interval 3 week preceding and interval 3 week preceding
        ) is not null
    ) as active_3_weeks_before
from mytable t

从您的解释和数据中并不清楚您的意思是三周前也有效。查询的作用是，对于每一行，检查是否存在具有相同供应商的另一行，dos并且恰好dos在当前行之前 3 周。这可以很容易地适应其他一些要求。

编辑：如果您想检查过去 3 周内的任何记录，您可以将窗口范围更改为：

range between interval 3 week preceding and interval 1 day preceding

如果您希望在 MySQL < 8.0 中使用此功能，其中窗口函数不可用，那么您将使用相关子查询：

select t.*,
    exists (
        select 1
        from mytable t1
        where 
            t1.providerid = t.provider_id
            and t1.dos >= t.dos - interval 3 week
            and t1.dos <  t.dos
    ) as active_3_weeks_before
from mytable t

推荐阅读