英文:
Rank over lateral flatten
问题
我好奇如何在lateral flatten上对一组值进行排名。
假设我有一个如下所示的列：
id tree
1 AB -> BC -> CD
2 A -> Z
3 B
我想要得到一个如下所示的结果：
id tree flattened ranker
1 AB -> BC -> CD AB 1
1 AB -> BC -> CD BC 2
1 AB -> BC -> CD CD 3
2 A -> Z A 1
2 A -> Z Z 2
3 B B 1
我已经实现了lateral flatten，但不知道如何对它进行排名。希望能得到帮助，谢谢。
英文:
I'm curious on how to do a rank over group of values on lateral flatten.
So let's say I have a column that looks like:
id tree
1 AB -> BC -> CD
2 A -> Z
3 B
I would like to get a result looking like this:
id tree flattened ranker
1 AB -> BC -> CD AB 1
1 AB -> BC -> CD BC 2
1 AB -> BC -> CD CD 3
2 A -> Z A 1
2 A -> Z Z 2
3 B B 1
I have implemented the lateral flatten but I don't know how to rank it.
Any help would be highly appreciated. Thank you
答案1
得分: 1
当执行侧向展开操作时，返回的列之一是INDEX。这相当于你正在寻找的排名，不过值从0开始。
链接：https://docs.snowflake.com/en/sql-reference/functions/flatten.html
英文:
When you execute a lateral flatten, one of the returned columns is the INDEX. That is the equivalent of the rank that you are looking for, except the values start at 0.
https://docs.snowflake.com/en/sql-reference/functions/flatten.html
答案2
得分: 0
对于任何有兴趣的人，
代码看起来像这样：
select table.*, c.value as flattened, rank() over (partition by id order by c.index) as ranker from table, lateral flatten(input => split(tree, ' -> ')) c;
<details>
<summary>英文:</summary>
For anyone interested,
the code would look like:
select table.*, c.value as flattened, rank() over (partition by id order by c.index) as ranker from table, lateral flatten(input => split(tree, ' -> ')) c;
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论