英文:
sqlalchemy - remove a label from a column
问题
我正在使用SQLAlchemy来查询BigQuery。
我有一个子查询，我正在尝试使用ArrayAgg对其进行去重。
基本上它应该看起来像这样：
SELECT
t.id,
ARRAY_AGG (t ORDER BY `time_col` DESC LIMIT 1)[OFFSET(0)].*
FROM (
SELECT ...
) t
GROUP BY t.id
我创建了一个编译器函数，以生成我所期望的ARRAY_AGG。
问题是，当我尝试执行查询时，数组agg字段会自动获得一个标签，这破坏了一切。
是否有一种方法只是让它使用*？
英文:
I am using sql alchemy to query BigQuery.
I have a subquery and I am trying to dedup it using ArrayAgg
basically it should look like this:
SELECT
t.id,
ARRAY_AGG (t ORDER BY `time_col` DESC LIMIT 1)[OFFSET(0)].*
from (
SELECT ...
) t
GROUP BY t.id
I created a compiler function that generates the ARRAY_AGG as I desire.
The problem is, when I try to execute the query, the array agg field gets an automatic label which ruins everything.
Is there a way to just make it use the *?
答案1
得分: 0
所以我找到了答案，在同事的帮助下，并希望把它放在这里，以便将来有人遇到这个问题。
基本上，我们生成了以下查询，没有使用*，所以我们得到了一个结构体，并用另一个select将其包装起来以提取字段。
我们创建了一个函数，用这种逻辑包装查询并返回类似这样的东西：
选择 deduped_record.field_1，...，deduped_record.field_n
从 (
选择 dedup_by_field，
ARRAY_AGG(source_table ORDER BY dedup_time_field DESC LIMIT 1)[OFFSET(0)] AS deduped_record
从 (
选择 field_1，...，field_n
从 表
)
按 dedup_by_field 分组
) 作为别名
我们只通过别名获取字段，而不是使用*。
英文:
So I found the answer with the help of my colleague, and wanted to put it here should anyone will ever face this too
We basically generated the following query without the * so we got a struct, and wrapped it with another select to extract the fields.
We made a function that wraps the query with this logic and returns something like this:
SELECT deduped_record.field_1, ..., deduped_record.field_n
FROM (
SELECT dedup_by_field,
ARRAY_AGG(source_table ORDER BY dedup_time_field DESC LIMIT 1)[OFFSET(0)] AS deduped_record
FROM (
SELECT field_1,...,field_n
FROM table
)
GROUP BY dedup_by_field
) AS alias
We just get the fields via the alias, instead of the *.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论