如何在bigquery中查找重复结构中的最后一个项目 [英] How to find last item in a repeated structure in bigquery

问题描述

我有一个嵌套的重复结构，重复的结构是可变长度的。例如，它可能是一个具有重复结构的个人对象，该对象拥有该人居住的城市。我希望找到该列表中最后一项说要找到当前城市人居住的地方。是否有简单的方法可以做这一点，我试着环顾jsonpath的功能，但我不知道如何与内使用它。您可以使用 / query-referencerel =noreferrer> LAST 和 WITHIN

  SELECT 
记录中的第一个（cell.value），
 LAST（cell.value）记录
 FROM [publicdata：samples.trigrams] 
其中ngram =！！那个
  

---

<2>）或者如果您想要更高级的内容，可以使用 POSITION

POSITION（field） - 返回基于one，在一组重复字段中的字段的顺序位置。

您可以从trigrams中检查样本（点击Details以查看未展开的模式）
https://bigquery.cloud.google.com/table/publicdata:samples.trigrams?pli=1

当你运行POSITION时，你会得到顺序该领域。

  SELECT 
 ngram，
 cell.value，
 position（cell.volume_count）as pos，
 FROM [publicdata：samples.trigrams] 
 where ngram =！！那个
  

现在您已经拥有该位置，您可以查询最后一个。

I have a nested repeated structure, the repeated structure is of variable length. For example, it could be a person object with a repeated structure that holds cities the person has lived in. I'd like to find the last item in that list say to find current city person lives in. Is there an easy way to do this, I tried looking around jsonpath functions but I'm not sure how to use it with "within". Any help please?

解决方案

1) You can use LAST and WITHIN

SELECT  
  FIRST(cell.value) within record ,
  LAST(cell.value) within record 
FROM [publicdata:samples.trigrams] 
where ngram = "! ! That"

---

2) or if you want something more advanced you can use POSITION

POSITION(field) - Returns the one-based, sequential position of field within a set of repeated fields.

You can check the samples from trigrams (click on Details to see the unflatten schema) https://bigquery.cloud.google.com/table/publicdata:samples.trigrams?pli=1

And when you run POSITION, you get the ordering of that field.

SELECT  
  ngram,
  cell.value,
  position(cell.volume_count) as pos,
FROM [publicdata:samples.trigrams] 
where ngram = "! ! That"

Now that you have the position, you can query for last one.

这篇关于如何在bigquery中查找重复结构中的最后一个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！