我在表中有这些数据
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”。
任何帮助将不胜感激
Thanks
如果您的 SQL 实现支持的话,您可以使用分析函数 - LAG、LEAD 和 COUNT() OVER 对您有利。 SQL小提琴here http://sqlfiddle.com/#!6/0c0fb/2.
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)