英文:
BigQuery - Extract last entry of each group
问题
我有一个表，每个产品组都插入了多条记录。现在，我想提取（SELECT）只有最新记录。更多信息，请参见截图。黄色高亮显示的记录应该在选择查询中返回。
英文:
I have one table where multiple records inserted for each group of product. Now, I want to extract (SELECT) only the last entries. For more, see the screenshot. The yellow highlighted records should be return with select query.
答案1
得分: 3
> HAVING MAX 和 HAVING MIN 子句现在支持 ANY_VALUE 函数，处于预览状态
HAVING MAX 和 HAVING MIN 刚刚被引入用于某些聚合函数 - https://cloud.google.com/bigquery/docs/release-notes#February_06_2023
使用它们，查询可以变得非常简单 - 请考虑以下方法
select any_value(t having max datetime).*
from your_table t
group by t.id, t.product
如果应用于你问题中的示例数据 - 输出为
英文:
> The HAVING MAX and HAVING MIN clause for the ANY_VALUE function is now in preview
HAVING MAX and HAVING MIN were just introduced for some aggregate functions - https://cloud.google.com/bigquery/docs/release-notes#February_06_2023
with them query can be very simple - consider below approach
select any_value(t having max datetime).*
from your_table t
group by t.id, t.product
if applied to sample data in your question - output is
答案2
得分: 1
如果你更熟悉聚合函数而不是窗口函数，下面可能是另一个选择。
英文:
You might consider below as well
SELECT *
FROM sample_table
QUALIFY DateTime = MAX(DateTime) OVER (PARTITION BY ID, Product);
If you're more familiar with an aggregate function than a window function, below might be an another option.
SELECT ARRAY_AGG(t ORDER BY DateTime DESC LIMIT 1)[SAFE_OFFSET(0)].*
FROM sample_table t
GROUP BY t.ID, t.Product
Query results
答案3
得分: 0
你可以使用窗口函数根据键进行分区，并根据定义的排序字段选择所需的内容。
例如：
select * from (
select *,
rank() over (partition by product, order by DateTime Desc) as rank
from `project.dataset.table`)
where rank = 1
英文:
You can use window function to do partition based on key and selecting required based on defining order by field.
For Example:
select * from (
select *,
rank() over (partition by product, order by DateTime Desc) as rank
from `project.dataset.table`)
where rank = 1
</details>
# 答案4
**得分**: 0
你可以使用此查询来选择每个组的最后一条记录：
选择 Top(1) * from Tablename group by ID order by DateTime Desc
<details>
<summary>英文:</summary>
You can use this query to select last record of each group:
Select Top(1) * from Tablename group by ID order by DateTime Desc
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论