sql - 从一列定义的组中选择最小值/最大值作为另一列的子组 - SQL、HPVertica

Question

我试图在另一个组的子组中找到最小和最大日期。这是示例“数据”

ID   Type  Date
1     A    7/1/2015
1     B    1/1/2015
1     A    8/5/2014
22    B    3/1/2015
22    B    9/1/2014
333   A    8/1/2015
333   B    4/1/2015
333   B    3/29/2014
333   B    2/28/2013
333   C    1/1/2013

我想确定的是 - 在一个 ID 中，每个相似类型块的最小/最大日期是多少？所以对于 ID # 333，我想要以下信息:

A: min & max = 8/1/2015
B: min = 2/28/2013
   max = 4/1/2015
C: min & max = 1/1/2013

我无法弄清楚如何在 ID 分组中仅识别不间断的 Type 分组。对于 ID #1，我需要保留两个具有单独最小/最大日期的“A”类型，因为它们被类型“B”分割，所以我不能只为 ID #1 提取所有类型 A 的最小日期，它必须是两个单独的实例。

我尝试过的是类似于下面两行的内容，但是这两行都没有准确捕获上面提到的 ID #1 类型 B 中断类型 A 的情况。

Max(Date) OVER (Partition By ID, Type)

or this:
Row_Number() OVER (Partition By ID, Type ORDER BY Date DESC)
  ,then selecting Row #1 for max date, and date ASC w/ row #1 for min date

感谢您提供的任何见解!

Answer 1

如果我理解正确，您希望使用降序日期排序对 id/type 进行分组的最小/最大值，但问题是您希望它们基于 id 内按时间划分的集群。

你可以做的是使用 CONDITIONAL_CHANGE_EVENT 在更改类型时标记行，然后在您的 GROUP BY 中使用它在标准的最小/最大聚合上。

这将是达到您想要的目标的中间步骤:

select ID, Type, Date,
  CONDITIONAL_CHANGE_EVENT(Type) OVER( PARTITION BY ID ORDER BY Date desc) cce
from mytable
group by ID, Type, Date
order by ID, Date desc, Type

ID  Type Date                   cce
1   A    2015-07-01 00:00:00    0
1   B    2015-01-01 00:00:00    1
1   A    2014-08-05 00:00:00    2
22  B    2015-03-01 00:00:00    0
22  B    2014-09-01 00:00:00    0
333 A    2015-08-01 00:00:00    0
333 B    2015-04-01 00:00:00    1
333 B    2014-03-29 00:00:00    1
333 B    2013-02-28 00:00:00    1
333 C    2013-01-01 00:00:00    2

使用 CCE 将它们分组后，您可以对此进行汇总，以获得您在 cce 上寻找分组的最小值/最大值。您可以使用底部的顺序，这个顺序对我来说似乎最有意义。

select id, type, min(date), max(date)
from (
select ID, Type, Date,
  CONDITIONAL_CHANGE_EVENT(Type) OVER( PARTITION BY ID ORDER BY Date desc) cce
from mytable
group by ID, Type, Date
) x
group by id, type, cce
order by id, 3 desc, 4 desc;

id  type min                    max
1   A    2015-07-01 00:00:00    2015-07-01 00:00:00
1   B    2015-01-01 00:00:00    2015-01-01 00:00:00
1   A    2014-08-05 00:00:00    2014-08-05 00:00:00
22  B    2014-09-01 00:00:00    2015-03-01 00:00:00
333 A    2015-08-01 00:00:00    2015-08-01 00:00:00
333 B    2013-02-28 00:00:00    2015-04-01 00:00:00
333 C    2013-01-01 00:00:00    2013-01-01 00:00:00