英文:
Is there a way to get NTH_VALUE with a variable N? (SQL - BigQuery)
问题
Here's the translation for the content you provided:
使用以下格式的BigQuery表格：
{ "name": "parent_id", "type": "STRING", "mode": "REQUIRED"},
{ "name": "parent_x", "type": "INTEGER", "mode": "REQUIRED"},
{ "name": "thing_id", "type": "STRING", "mode": "REQUIRED"},
{ "name": "created_at", "type": "TIMESTAMP", "mode": "NULLABLE"}
假设每个“thing_id”都有一个关联的“created_at”时间戳。
我想要获取每个父级ID的第N个“created_at”，其中第N个“thing_id”在我的表中注册。
我可以使用NTH_VALUE()函数，但我希望我的N是基于parent_x的可变值，更具体地说，我希望MIN("字面整数", parent_x)等于N。问题在于NTH_VALUE()只接受字面值或查询参数。
我该如何解决这个问题？
我要获取的结果表格如下：
{ "name": "parent_id", "type": "STRING", "mode": "REQUIRED"},
{ "name": "nth_created_at", "type": "TIMESTAMP", "mode": "NULLABLE"}
英文:
With a Bigquery table as such:
{"name": "parent_id", "type": "STRING", "mode": "REQUIRED"},
{"name": "parent_x", "type": "INTEGER", "mode": "REQUIRED"},
{"name": "thing_id", "type": "STRING", "mode": "REQUIRED"},
{"name": "created_at", "type": "TIMESTAMP", "mode": "NULLABLE"}
Assuming we have one row per "thing_id" with an associated "created_at"
I'm trying to fetch, for each parent id, the Nth "created_at" where the Nth "thing_id" is registered in my table.
I can use NTH_VALUE() but I want my N to be variable based on parent_x, more specifically I want the MIN("literal integer", parent_x) to be N. The problem is that NTH_VALUE() only accepts a literal or query parameter.
How could I get around this?
The result table I'm looking for is:
{"name": "parent_id", "type": "STRING", "mode": "REQUIRED"},
{"name": "nth_created_at", "type": "TIMESTAMP", "mode": "NULLABLE"}
答案1
得分: 1
你可以使用 row_number() 和窗口函数 min()，如下所示：
select parent_id, created_at nth_created_at
from (
select
t.*,
row_number() over(partition by parent_id order by created_at) rn,
min(parent_x) over(partition by parent_id) tn
from mytable t
) t
where rn = tn
英文:
You could use row_number() and a window min() as follows:
select parent_id, created_at nth_created_at
from (
select
t.*,
row_number() over(partition by parent_id order by created_at) rn,
min(parent_x) over(partition by parent_id) tn
from mytable t
) t
where rn = tn
答案2
得分: 0
最简单的方法可能是使用 row_number() 函数：
select t.* except (seqnum)
from (select t.*, row_number() over (partition by parent_x order by timestamp) as seqnum
from t
) t
where seqnum = 1;
英文:
The simplest method is probably row_number():
select t.* except (seqnum)
from (select t.*, row_number() over (partition by parent_x order by timestamp) as seqnum
from t
) t
where seqnum = 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论