查找一个字段更改时的开始和结束日期

Question

我在表中有此数据

FIELD_A   FIELD_B     FIELD_D
249052903   10/15/2011 N
249052903   11/15/2011 P ------------- VALUE CHANGED
249052903   12/15/2011 P
249052903   1/15/2012   N ------------- VALUE CHANGED
249052903   2/15/2012   N
249052903   3/15/2012   N
249052903   4/15/2012   N
249052903   5/15/2012   N
249052903   6/15/2012   N
249052903   7/15/2012   N
249052903   8/15/2012   N
249052903   9/15/2012   N

每当FIELD_D中的值更改时，它就会组成一个组，而我需要该组中的最小和最大日期。查询应该返回

FIELD_A   GROUP_START   GROUP_END
249052903   10/15/2011  10/15/2011
249052903   11/15/2011  12/15/2011
249052903   1/15/2012              9/15/2012

到目前为止，我看到的示例在Field_D中的数据是唯一的。这里的数据可以重复显示，首先是“ N”，然后变为“ P”，然后又回到“ N”。

任何帮助将不胜感激

谢谢

Answer 1

如果SQL实现支持分析功能，则可以使用LAG，LEAD和COUNT（）OVER这样的解析函数，以发挥自己的优势。SQL
Fiddle在这里。

WITH EndsMarked AS (
  SELECT
    FIELD_A,
    FIELD_B,
    CASE WHEN FIELD_D = LAG(FIELD_D,1) OVER (ORDER BY FIELD_B)
         THEN 0 ELSE 1 END AS IS_START,
    CASE WHEN FIELD_D = LEAD(FIELD_D,1) OVER (ORDER BY FIELD_B)
         THEN 0 ELSE 1 END AS IS_END
  FROM T
), GroupsNumbered AS (
  SELECT
    FIELD_A,
    FIELD_B,
    IS_START,
    IS_END,
    COUNT(CASE WHEN IS_START = 1 THEN 1 END)
      OVER (ORDER BY FIELD_B) AS GroupNum
  FROM EndsMarked
  WHERE IS_START=1 OR IS_END=1
)
  SELECT
    FIELD_A,
    MIN(FIELD_B) AS GROUP_START,
    MAX(FIELD_B) AS GROUP_END
    FROM GroupsNumbered
    GROUP BY FIELD_A, GroupNum;