PostgreSQL的GROUP BY如何查看日期范围？

Question

我有一个包含"Code"值变化历史记录的表格。每个月，这个表格都会新增一条记录，记录了指定月份的新"Code"值。

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/07/2016 | 31/07/2016 |    4 |
| Employee | 01/06/2016 | 30/06/2016 |    2 |
| Employee | 01/05/2016 | 31/05/2016 |    2 |
| Employee | 01/04/2016 | 30/04/2016 |    3 |
| Employee | 01/03/2016 | 31/03/2016 |    3 |
| Employee | 01/02/2016 | 29/02/2016 |    4 |
| Employee | 01/01/2016 | 31/01/2016 |    4 |
+----------+------------+------------+------+

我需要根据"Code"变化分组数据，每次分组得到一条新的记录，并且取"From date"的最小值和"To date"的最大值。数据必须按照"FromDate"降序排序。通过我的查询，我得到了以下结果：

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/05/2016 | 30/06/2016 |    2 |
| Employee | 01/03/2016 | 30/04/2016 |    3 |
| Employee | 01/01/2016 | 31/07/2016 |    4 |
+----------+------------+------------+------+

它的工作很好，但是如果相同的“代码”有多个日期范围（请参见第一个表中的4个代码），我会得到每个代码的单行。我希望能够以2个记录获得这4个代码的结果，因为它的时间段不连续，而是被其他代码（3和2）打断：

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/07/2016 | 31/07/2016 |    4 |
| Employee | 01/05/2016 | 30/06/2016 |    2 |
| Employee | 01/03/2016 | 30/04/2016 |    3 |
| Employee | 01/01/2016 | 29/02/2016 |    4 |
+----------+------------+------------+------+

我使用以下查询语句：

SELECT
    d."Employee",
    MIN (d."FromDate") AS "FromDate",
    MAX (d."ToDate") AS "ToDate",
    d."Code"
FROM
    (
        SELECT
            "Employees"."FromDate",
            "Employees"."ToDate",
            "Employees"."Code",
            "Employees"."Employee"
        FROM
            schema_estelspa."Employees"
        ORDER BY
            "Employees"."FromDate" DESC
    ) d
GROUP BY
    d."Code",
    d."Employee"
ORDER BY
    (MIN(d."FromDate")) DESC

有没有什么诀窍可以得到我想要的结果？

日期格式为：dd/MM/yyyy。

- Darion Badlydone

---

无关，但是：你应该_真的_尽量避免那些可怕的带引号的标识符。它们带来的麻烦远大于它们的价值。 - user330315

或者只是按员工分组 - devasia2112

@B4NZ41 我需要对日期（起始和结束）进行分组，因此我不能删除 group by，也不能按员工分组。 - Darion Badlydone

可能是在SQL中分组连续时间间隔的重复问题。 - Laurenz Albe

看起来像Packing Intervals。它是针对SQL Server编写的，但是最新版本的Postgres具有与SQL Server相同的分析函数。 - Vladimir Baranov

Answer 1

在这里，您需要制定日期范围，并将from_date作为分组列的一部分。您还需要进行自我连接以实现此结果。我已经在Teradata中准备了以下SQL语句。请根据您的数据库进行必要的更改（coalesc用作if null表达式，您也可以使用nvl或case语句）。

查询：

SELECT E.EMPLOYEE, E.CODE,COALESCE(ET1.FROMdATE,E.FROMDATE)FROM_DATE ,MAX(E.TODATE)TO_D
FROM EMP_TEST E
LEFT OUTER JOIN EMP_TEST ET1
ON E.EMPLOYEE=ET1.EMPLOYEE
AND E.CODE=ET1.CODE
AND E.FromDate=ET1.ToDate+1
GROUP BY 1,2,3
ORDER BY FROM_DATE

输出：

    Employee    Code    FROM_DATE   TO_D
1   Employee    4   1/1/2016    2/29/2016
2   Employee    2   5/1/2016    6/30/2016
3   Employee    4   7/1/2016    7/31/2016
4   Employee    3   3/1/2016    4/30/2016

- Bhavesh Ghodasara

Answer 2

连接点的标准递归解决方案

• 实际应用中，半开区间（lower_limit <= X < upper_limit）更易处理
• 递归从没有下限邻居的任何线段开始
• 相邻线段粘贴在右侧，构建更长的链
• 最终查询抑制部分结果

注意：下面的代码不处理重叠区间。

---

        -- Table
CREATE TABLE ecode
        ( employee varchar NOT NULL
        , code INTEGER NOT NULL
        , fromdate DATE NOT NULL
        , uptodate DATE NOT NULL
        );
SET datestyle = 'DMY' ;

        -- Data
INSERT INTO ecode(employee, fromdate, uptodate, code) VALUES
 ('Employee','01/07/2016','31/07/2016', 4)
, ('Employee','01/06/2016','30/06/2016', 2)
, ('Employee','01/05/2016','31/05/2016', 2)
, ('Employee','01/04/2016','30/04/2016', 3)
, ('Employee','01/03/2016','31/03/2016', 3)
, ('Employee','01/02/2016','29/02/2016', 4)
, ('Employee','01/01/2016','31/01/2016', 4)
        ;

        -- Convert to half-open interval
UPDATE ecode SET uptodate = uptodate + '1 day'::interval;
-- SELECT * FROM ecode;

WITH RECURSIVE zzz AS (
        SELECT employee, code, fromdate, uptodate
        FROM ecode e0
        WHERE NOT EXISTS ( -- first one in series
                SELECT * FROM ecode nx
                WHERE nx.employee = e0.employee
                AND nx.code = e0.code
                AND nx.uptodate = e0.fromdate
                )
        UNION ALL -- append consecutive intervals
        SELECT e1.employee, e1.code, zzz.fromdate, e1.uptodate
        FROM ecode e1
        JOIN zzz ON zzz.employee = e1.employee
        AND zzz.code = e1.code
        AND zzz.uptodate = e1.fromdate
        )
SELECT * FROM zzz
        -- suppress the partial results
WHERE NOT EXISTS (SELECT * FROM ecode nx
                WHERE nx.employee = zzz.employee
                AND nx.code = zzz.code
                AND nx.fromdate = zzz.uptodate
                )
ORDER BY employee, code, fromdate
        ;

---

结果：

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
SET
INSERT 0 7
UPDATE 7
 employee | code |  fromdate  |  uptodate  
----------+------+------------+------------
 Employee |    2 | 2016-05-01 | 2016-07-01
 Employee |    3 | 2016-03-01 | 2016-05-01
 Employee |    4 | 2016-01-01 | 2016-03-01
 Employee |    4 | 2016-07-01 | 2016-08-01
(4 rows)

- joop

---

我认为你会发现Packing Intervals很有趣。它展示了如何处理重叠的时间间隔。虽然它是针对SQL Server编写的，但Postgres中也有查询中使用到的所有分析函数。 - Vladimir Baranov

在我看来，处理重叠区间的最佳方法是边缘检测并对其结果进行求和。 - joop