英文:
Count Number Of Changes Per Group
问题
我想要这个结果：
| Manager_Change_Counter | Employee |
|---|---|
| 5 | 1 |
| 4 | 2 |
我需要统计每位员工在项目经理变更方面的次数，不考虑项目数量。
英文:
Given this data:
| Project Manager | Project Num | Employee |
|---|---|---|
| A | 1 | 1 |
| A | 2 | 1 |
| B | 3 | 1 |
| A | 4 | 1 |
| B | 5 | 1 |
| B | 6 | 1 |
| A | 7 | 1 |
| A | 8 | 1 |
| B | 9 | 1 |
| A | 1 | 2 |
| B | 2 | 2 |
| B | 3 | 2 |
| A | 4 | 2 |
| A | 5 | 2 |
| B | 6 | 2 |
| A | 7 | 2 |
I want This Result :
| Manager_Change_Counter | Employee |
|---|---|
| 5 | 1 |
| 4 | 2 |
I need to count per employee how many project manger changes made regardless of number of projects
答案1
得分: 0
以下是已翻译的内容：
可以通过使用窗口函数 lag() 来实现这一目标，以检索上一行并确定是否已更改项目经理，然后计算更改的总时间：
WITH CTE AS (
SELECT Employee,
Project_Manager,
lag(Project_Manager, 1, Project_Manager) OVER (PARTITION BY Employee ORDER BY Project_Num) AS PREV_Project_Manager
FROM mytable
)
SELECT Employee, SUM(CASE WHEN Project_Manager = PREV_Project_Manager THEN 0 ELSE 1 END) AS Manager_Change_Counter
FROM CTE
GROUP BY Employee
结果：
Employee Manager_Change_Counter
1 5
2 4
英文:
This can be accomplished by using the window function lag() to retrieve the previous row and determine whether or not Project Manager has been changed, then calculating the total time of changes :
WITH CTE AS (
select Employee,
Project_Manager,
lag(Project_Manager, 1, Project_Manager) over (partition by Employee order by Project_Num) as PREV_Project_Manager
from mytable
)
SELECT Employee, SUM(case when Project_Manager = PREV_Project_Manager then 0 else 1 end) as Manager_Change_Counter
from CTE
GROUP BY Employee
Result :
Employee Manager_Change_Counter
1 5
2 4
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论