postgreSQL:获取有序表中组的最小值和最大值

Question

我有一个装有钻孔样品的表格。该表的每个深度单位都有一行，列出了该深度处的常用材料类型。从这个表中，我想得到每个层类型的顶部(最大(和底部(最小(深度。如何在 SQL 中完成此操作？如果我使用"分组依据"，深度排序就会丢失，如果我使用"排序依据"，我不能使用"最小(深度("和"最大(深度("？

示例输入表

|sampleid | layertype | depth|
+----------------------------+
| 1       | sand      |  0  | 
| 1       | sand      | -1  | 
| 1       | peat      | -2  | 
| 1       | sand      | -3  |
| 1       | sand      | -4  |
| 1       | sand      | -5  |
| 1       | clay      | -6  |
| 1       | clay      | -7  |
| 1       | clay      | -8  |
| 1       | sand      | -9  |
| 1       | sand      | -10 |
| 1       | sand      | -11 |
+----------------------------+

所需的输出表：

|sampleid | layertype | top | bottom |
+------------------------------------+
| 1       | sand      |  0  | -1     |
| 1       | peat      | -2  | -2     | 
| 1       | sand      | -3  | -5     |
| 1       | clay      | -6  | -8     |
| 1       | sand      | -9  | -11    |
+------------------------------------+

这种问题可能在StackOverflow上被问了很多次，但我找不到答案或重复，也许我没有使用正确的搜索词？

Answer 1

这是一个差距和孤岛问题。 我建议行号的差异：

select sample_id, layer_type, 
max(depth) as top, min(depth) as bottom
from  (select t.*,
row_number() over (partition by sample_id order by depth) as seqnum,
row_number() over (partition by sample_id, layer_type order by depth) as seqnum_lt
from sample_table t
) t
group by sample_id, layer_type, (seqnum - seqnum_lt)
order by sample_id, min(depth) desc;