9/16 每日一題(MYSQL 找出員工的直屬部門, 其中如果員工只有一個部門時,"N"會被視為"Y")

Table: Employee

+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
(employee_id, department_id) is the primary key (combination of columns with unique values) for this table.
employee_id is the id of the employee.
department_id is the id of the department to which the employee belongs.
primary_flag is an ENUM (category) of type ('Y', 'N'). If the flag is 'Y', the department is the primary department for the employee. If the flag is 'N', the department is not the primary.

 

Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is 'N'.

Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1           | 1             | N            |
| 2           | 1             | Y            |
| 2           | 2             | N            |
| 3           | 3             | N            |
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+
Output: 
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
Explanation: 
- The Primary department for employee 1 is 1.
- The Primary department for employee 2 is 1.
- The Primary department for employee 3 is 3.
- The Primary department for employee 4 is 3.

 # Write your MySQL query statement below

select employee_id ,department_id  from Employee

where primary_flag = "Y"

union

select employee_id ,department_id  from Employee

group by employee_id

having count(employee_id) = 1

#union運算服用於結合查詢的兩個部份結果，他從最終結果集中消除重複的紀錄

#也就是結合primary_flag='Y' 和 具有唯一employee_id值得員工

----------------------------------------------

select employee_id ,department_id

from

(select *, count(employee_id) OVER(PARTITION BY employee_id) AS EmployeeCount

from Employee) AS EmployeePtition

where EmployeeCount = 1 or primary_flag = 'Y'

#使用窗口函數進行內部查詢 COUNT() OVER()

#select *,count(employee_id) OVER(PARITION BY employee_id) AS EmployeeCount

#此查詢從表中獲取所有列並添加一個新的列EmployeeCount

#其中窗函數的部分, PARITION BY employee_id: 這會將數據分解為具有相同行的窗口或分區employee_id

#每個窗口本質是特定員工的數據的子集

#所以COUNT(employee_id) OVER(...): 這會計算每個員工在各自分區/窗口內的行數(即部門數)。

#結果是一個新列EmployeeCOunt

-----------------------------------------------

貓熊

方法一：條件過濾和基於聚合的並集

直覺

示例employee數據框：

employee_id	department_id	primary_flag
1	1	N
2	1	Y
2	2	N
3	3	N
4	2	N
4	3	Y
4	4	N

Step 1 - Filter by Flag:

filter_by_flag=employee[employee['primary_flag']=='Y'][['employee_id','department_id']]

employee_id	department_id
2	1
4	3

Step 2 - Unique Employees:

unique_employee=employee.groupby('employee_id').filter(lambda x:len(x)==1)[['employee_id','department_id']]

employee_id	department_id
1	1
3	3

#使用groupby() 將employee:DataFrame按employee_id分組

#使用filter()函數過濾長度為1的組

result=pd.concat([filter_by_flag,unique_employee]).drop_duplicates().reset_index(drop=True)

#使用pd.concat()組和兩個DataFrame

#調用drop_duplocates() 刪除重複的部分.確保資料乾淨

#最後 res_index(drop=True) 用於重置DataFrame 的索引, 確保舊索引不用成為DataFrame的column

----------------------------

import pandas as pd

def find_primary_department(employee: pd.DataFrame) -> pd.DataFrame:

    fliter=employee[employee['primary_flag']=='Y'][['employee_id','department_id']]

    uni=employee.groupby('employee_id').filter(lambda x:len(x)==1)[['employee_id','department_id']]

    res=pd.concat([fliter,uni]).drop_duplicates().reset_index(drop=True)

    print(res)

    return res