sql - 使用分析函数时的分组
问题描述
假设我们有一个如下所示的表:
Level|Depth|Descrip|
0 | 0 | Base |
1 | 50 | Level_1 |
2 | 53 | Level_2 |
3 | 60 | Level_3 |
8 | 80 | Level_8 |
10 | 81 | Level_10|
15 | 101 | Level_15|
16 | 102 | Level_16|
17 | 102 | Level_16_bis|
18 | 103 | Level_17|
首先,我需要获得表示相对于先前的有意义的(超过 15 米)深度跳跃的行。我让那些行做这样的事情:
Select level,depth, descrip from(
Select level
, depth
,lag(depth) over (order by level asc) as prev_depth
, descrip
from ground_levels
)
Where abs(depth-prev_depth) > 15 and depth > 0
这给了我一张这样的桌子:
Level|Depth|Descrip|
1 | 50 | Level_1|
8 | 80 | Level_8|
15 | 101 | Level_15|
现在,我需要收集跳跃之间的关卡。所以,我需要这样的东西:
Level|Depth| Descrip | Equivalent_levels |
1 | 50 | Level_1 | 2,3 |
8 | 80 | Level_8 | 10 |
15 | 101 | Level_15| 16,17,18 |
我一直在搜索有关使用“listagg”、rank() 和其他分析函数的信息,但我被脚本卡住了:(
另外,如果我可以在满足这个条件时开始分组,那就太好了:abs(depth-prev_depth) > 15,所以我可以得到类似的东西:
Level|Depth|Descrip | Group_ID
1 | 50 | Level_1 | 1 |
2 | 53 | Level_2 | 1 |
3 | 60 | Level_3 | 1 |
8 | 80 | Level_8 | 2 |
10 | 81 | Level_10| 2 |
15 | 101 | Level_15| 3 |
16 | 102 | Level_16| 3 |
17 | 102 | Level_16_bis| 3 |
18 | 103 | Level_17| 3 |
有任何想法吗 ??
PS:对不起我的英语不好...
解决方案
您可以使用累积和来定义组。然后聚合:
Select min(level) as level,
min(depth) keep (dense_rank first order by level) as depth,
min(descrip) keep (dense_rank first order by level) as descrip,
list_agg(level, ',') within group (order by level) as levels
from (select gl.*,
sum(case when abs(prev_depth - depth) > 15 and depth > 0 then 1 else 0 end) over (order by level) as grp
from (select gl.*, lag(depth) over (order by level asc) as prev_depth
from ground_levels
) gl
) gl
group grp;
这实际上保留了列表中的起始级别。它可以被删除,但这需要更多的工作。
推荐阅读
- typescript - 让 typescript 知道来自 webpack `require.context` 的所有键
- python - 我可以在 Keras 中使用 2d 顺序 CNN 模型进行对象检测或定位吗?
- sql - 为数据集中存在的所有不同的 column2 值填充缺失的 column1 值
- javascript - Javascript:如何使 _.compact 忽略 0 作为虚假值?
- javascript - 动态解构道具?
- java - 如果 Java 中的 else 阻塞,如何在外部获取价值
- spring - 登录 Grails:使用 Log4j2 重新加载外部配置
- ios - 如何在 infoplist.strings 文件中使用用户定义的设置?
- sql-server - SQLSTATE[42000]:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]'FIELD' 不是可识别的内置函数名
- r - 将 week+day(39+3) 变量更改为 R 中的周或天