当前位置： 文江博客话题详情

SQL等级计数事件

发布于 2025-01-28 12:58:43 字数 4313 浏览 2 评论 0原文

在我的表中，我有类似员工合同的数据：

我想在时间段获得工作职位，类似此类

登录录音从有效到Jobposition
BCDE BCDE2019-07-012019-09-09-09-30项目经理
BCDE BCDE2020-01-09 2020-092020-06-16-16卓越中心主任
BCDE2020-06-172021-07-31团队负责人
BCDE BCDE2021-08-012099-12-31卓越中心主任卓越中心主任，

所以我写了查询：

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, login

但是在这种情况下它不起作用（如果在这种情况下都不起作用（如果 所以我决定使用dense_rank，这样：

select login, ValidFrom, ValidTo, JobPosition
,dense_rank() OVER (Partition BY JobPosition ORDER BY login, ValidFrom, ValidTo, JobPosition) as no
from employeeContracts

问题

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, no, login

无法正常工作

，相同的工作意见） ，有人具有dense_rank此后
是； -012019-09-30经理1
BCDE2020-01-092020-06-16卓越中心
​​​负责项目
​​​-31团队负责人3
BCDE2021-04-012021-06-30团队负责人3
BCDE2021-07-012021-07-31团队领导者3
BCDE2021-08-012021-2021-2021-12-31卓越中心4
BCDE2022-01-012022-05-09卓越中心负责人4
BCDE2022-02-012022-05-09主管4
BCDE2022-09209999999999999-12-31卓越中心4

之后使用查询获得最终结果：

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, no, login

示例数据方案

CREATE TABLE employeeContracts (
  login text,
  ValidFrom datetime,
  ValidTo datetime,
  JobPosition text
);

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2019-07-01', '2019-09-30', 'Project Manager');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-01-09', '2020-06-16', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-06-17', '2020-07-31', 'Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-08-01', '2021-03-31', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-04-01', '2021-06-30', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-07-01', '2021-07-31', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-08-01', '2021-12-31', '   Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-01-01', ' 2022-05-09', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-02-01', '2022-05-09', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-05-09', '2099-12-31', 'Head of Center of Excellence');

可以测试在这里

原文

In my table I have data from employee contracts like this:
enter image description here

And I want to get job position in time periods, something like this

loginValidFromValidToJobPosition
bcde2019-07-012019-09-30Project Manager
bcde2020-01-092020-06-16Head of Center of Excellence
bcde2020-06-172021-07-31Team Leader
bcde2021-08-012099-12-31Head of Center of Excellence

So I write query:

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, login

But It doesn't work in this case (if someone has the same JobPosition), so I decided to use dense_rank, like this:

select login, ValidFrom, ValidTo, JobPosition
,dense_rank() OVER (Partition BY JobPosition ORDER BY login, ValidFrom, ValidTo, JobPosition) as no
from employeeContracts

and after that

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, no, login

but problem is dense_rank doesn't work as I need ;) I want to get something like this:

loginValidFromValidToJobPositionno
bcde2019-07-012019-09-30Project Manager1
bcde2020-01-092020-06-16Head of Center of Excellence2
bcde2020-06-172020-07-31Team Leader3
bcde2020-08-012021-03-31Team Leader3
bcde2021-04-012021-06-30Team Leader3
bcde2021-07-012021-07-31Team Leader3
bcde2021-08-012021-12-31Head of Center of Excellence4
bcde2022-01-012022-05-09Head of Center of Excellence4
bcde2022-02-012022-05-09Head of Center of Excellence4
bcde2022-05-092099-12-31Head of Center of Excellence4

and after that use query to get final result:

select DimEmployeeId, JobPosition, login, min(ValidFrom), max(ValidTo)
from employeeContracts
group by DimEmployeeId, JobPosition, no, login

Example Data Scheme

CREATE TABLE employeeContracts (
  login text,
  ValidFrom datetime,
  ValidTo datetime,
  JobPosition text
);

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2019-07-01', '2019-09-30', 'Project Manager');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-01-09', '2020-06-16', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-06-17', '2020-07-31', 'Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2020-08-01', '2021-03-31', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-04-01', '2021-06-30', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-07-01', '2021-07-31', '   Team Leader');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2021-08-01', '2021-12-31', '   Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-01-01', ' 2022-05-09', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-02-01', '2022-05-09', 'Head of Center of Excellence');

INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition) 
VALUES ('bcde', '2022-05-09', '2099-12-31', 'Head of Center of Excellence');

This can be tested Here

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

極樂鬼 2025-02-04 12:58:43

这听起来像是一个“差距和岛屿”问题。接近它的一种方法是将排名识别并分配给每个“岛”（或每个工作人员的日期范围）。

登录有效从有效的工作求职prev位jobpositiongroup
bcde2019-07-01 00:00:002019-09-09-09-09-09-30 00:00:00项目经理null null null null bcde
009 00: 002020-01-09:00:00 2020 2020 -06-16中心经理负责
00：00:00卓越项目​​​
​​-01 00：00：002021-03-31 00：00：00团队负责人团队负责人3
BCDE2021-04-01 00:00：00：00 2021-06-06-06-06-30 00:00:00团队负责人团队领导者3
BCDE2021 -07-01 00：00：002021-07-31 00：00:00团队负责人团队负责人BCDE
2021-08-0100:00：002021-12-12-31 00:00:003团队负责人4
BCDE2022-01-01 00:00:0000:00:00负责人卓越中心负责
2022-05-09卓越中心卓越中心 09 00:00:00卓越中心卓越中心主任卓越中心负责人4
BCDE2022-05-09 00:00:002099-12-31 00:00:00卓越中心卓越中心主任中心负责人4

卓越 应用您的最小/最大逻辑，按排名编号进行分组：

 SELECT grp.JobPositionGroup
        , grp.JobPosition
        , grp.Login
        , MIN(grp.ValidFrom) AS ValidFrom
        , MAX(grp.ValidTo) AS ValidTo
 FROM (
         SELECT  cron.*
                 , SUM( IF( JobPosition = PrevPosition, 0, 1) ) OVER(
                            ORDER BY ValidFrom, ValidTo
                 )  AS JobPositionGroup
         FROM   (
                    SELECT ec.*
                          , LAG(JobPosition, 1) OVER (
                               ORDER BY ValidFrom, ValidTo
                          ) AS PrevPosition
                    FROM   employeeContracts ec
                 )
                 cron
         ) grp
 GROUP BY grp.JobPositionGroup
          , grp.JobPosition
          , grp.Login

结果：

jobpositiongroupjobpositionloginlogin00:00
lo vall vall vall to to 1项目经理BCDE2019-07-01 00:00:2019-09-09-09-09-30 00:00：00:00： 00
2卓越中心主管BCDE2020-01-09 00:00:002020-06-16-16-16 00:00:00
3团队负责人BCDE BCDE2020-06-17 00:00：002021-07-07-31 00:00:00 ：00
4卓越中心负责人BCDE BCDE2021-08-01 00:00:002099-12-31 00:00:00

db＆lt;＆gt;＆gt;＆gt; gt; gt; a href

This sounds like a "Gaps and Islands" problem. One way of approaching it is to identify and assign rankings to each "island" (or date ranges for each JobPosition).

loginValidFromValidToJobPositionPrevPositionJobPositionGroup
bcde2019-07-01 00:00:002019-09-30 00:00:00Project Managernull1
bcde2020-01-09 00:00:002020-06-16 00:00:00Head of Center of ExcellenceProject Manager2
bcde2020-06-17 00:00:002020-07-31 00:00:00Team LeaderHead of Center of Excellence3
bcde2020-08-01 00:00:002021-03-31 00:00:00Team LeaderTeam Leader3
bcde2021-04-01 00:00:002021-06-30 00:00:00Team LeaderTeam Leader3
bcde2021-07-01 00:00:002021-07-31 00:00:00Team LeaderTeam Leader3
bcde2021-08-01 00:00:002021-12-31 00:00:00Head of Center of ExcellenceTeam Leader4
bcde2022-01-01 00:00:002022-05-09 00:00:00Head of Center of ExcellenceHead of Center of Excellence4
bcde2022-02-01 00:00:002022-05-09 00:00:00Head of Center of ExcellenceHead of Center of Excellence4
bcde2022-05-09 00:00:002099-12-31 00:00:00Head of Center of ExcellenceHead of Center of Excellence4

Then apply your min/max logic, grouping by the ranking number:

 SELECT grp.JobPositionGroup
        , grp.JobPosition
        , grp.Login
        , MIN(grp.ValidFrom) AS ValidFrom
        , MAX(grp.ValidTo) AS ValidTo
 FROM (
         SELECT  cron.*
                 , SUM( IF( JobPosition = PrevPosition, 0, 1) ) OVER(
                            ORDER BY ValidFrom, ValidTo
                 )  AS JobPositionGroup
         FROM   (
                    SELECT ec.*
                          , LAG(JobPosition, 1) OVER (
                               ORDER BY ValidFrom, ValidTo
                          ) AS PrevPosition
                    FROM   employeeContracts ec
                 )
                 cron
         ) grp
 GROUP BY grp.JobPositionGroup
          , grp.JobPosition
          , grp.Login

Results:

JobPositionGroupJobPositionLoginValidFromValidTo
1Project Managerbcde2019-07-01 00:00:002019-09-30 00:00:00
2Head of Center of Excellencebcde2020-01-09 00:00:002020-06-16 00:00:00
3Team Leaderbcde2020-06-17 00:00:002021-07-31 00:00:00
4Head of Center of Excellencebcde2021-08-01 00:00:002099-12-31 00:00:00

db<>fiddle here

回复 原文
~没有更多了~

关于作者

墨洒年华

暂无简介

文章
评论
544 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文