postgresql - 获取一个月内出现一次以上的 ID

Question

我需要创建一个查询来选择“employee_id, project_id,start_date and end_date when [for a employee_id has more than one project_id in the same month.]我的想法是，我不知道如何查看 employee_id 在同一个月内是否有多个 project_id。我知道一种方法是在“2010-01-01”和“2010-02-01”之间使用，但这不是正确的方法，因为我不知道从哪个日期开始。所以它需要动态的。

Employee_id Project_id  Project_ Employee_id    Start_date  End_date    occupied    status  Role_id key

"47"    "96"    "1440"  "2019-04-07"    "2019-04-30"    100 "ON_PROJECT"    "3" "NO"
"47"    "96"    "1441"  "2019-04-05"    "2019-04-20"    100 "ON_PROJECT"    "250"   "NO"
"47"    "131"   "1442"  "2019-04-07"    "2019-04-15"    100 "ON_PROJECT"    "3" "NO"

我需要那些，当 Start_date End_date 在同一个月，例如对于相同的 Employee_id 和 Project_id，其中 Start_date 和 End_date 在同一个月。我需要结果像这个例子
前任(47,96,1440,'2019-04-07','2019-04-30'), (47,96,1441,'2019-04-06','2019-04-20'), 只为一个月，我知道有很多行这可能是相同的结果，但我只需要为一个工作，同一个月不会有超过 2 个 Employee_id 和 Project_id。

Answer 1

demo:db<>fiddle

(不是为了进行更多测试，我将 1439 end_date 行更改为几个月后，并将 project_id 更改为 132 并在最后一行添加了不应显示的 employee_id = 48)

SELECT
   employee_id,
   project_id,
   MIN(start_date),
   MAX(end_date)
FROM
   emp, generate_series(date_trunc('month', start_date), date_trunc('month', end_date) , interval '1 month') gs
GROUP BY employee_id, project_id
HAVING COUNT(gs) >= 2

1. date_trunc 取一个月的第一天。这是一种规范化，因为我只对月份感兴趣，而不是实际的日子。
2. generate_series 为项目条目存在的每个月生成行。 (参见项目 132，它出现在从 4 月到 6 月的三个月中。)。这种扩展允许按项目/员工分组并计算不同的月份
3. GROUP employee_id 和 project_ids 组并计算它们出现的月份。
4. 使用 HAVING 子句过滤超过 1 个月的组。

---

需求发生一些变化后:

使用 window function COUNT允许计算某个组的行而不需要聚合。因此 COUNT(*) OVER() 将组的计数添加为新列，之后可以对其进行过滤。

demo:db<>fiddle

如果 start_date 和 end_date 在同一个月:

SELECT 
    employee_id,
    project_id,
    start_date,
    end_date
FROM (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY employee_id, project_id, date_trunc('month', start_date))
    FROM
       emp_same
) s
WHERE count >= 2

否则，您将不得不展开项目出现的所有月份(如上所述):

SELECT 
    employee_id,
    project_id,
    start_date,
    end_date
FROM (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY employee_id, project_id, gs)
    FROM
       emp_diff, generate_series(date_trunc('month', start_date), date_trunc('month', end_date) , interval '1 month') gs
) s
WHERE count >= 2