How can I get/query data from a table in Google Spreadsheets and limit to the first X rows for each "set" of rows?

huangapple
go评论70阅读模式
英文:

How can I get/query data from a table in Google Spreadsheets and limit to the first X rows for each "set" of rows?

问题

以下是翻译好的部分：

我有一组数据（如下所示），代表了多组数据。我想获取所有这些数据集，但限制每个数据集的行数不超过X行。

我知道如何使用query的limit函数，但似乎它会限制所有的行。

示例数据

应用ID 应用名称 L1经理 目标分数 降分金额 维度名称 维度值 权重
1 一个 a 49 10 1 35 3.50
1 一个 a 49 10 2 9 0.90
1 一个 a 49 10 3 15 1.50
2 两个 a 32 70 1 50 0.71
2 两个 a 32 70 2 22 0.31
2 两个 a 32 70 3 30 0.43
3 三个 a 46 67 1 45 0.67
3 三个 a 46 67 2 18 0.27
3 三个 a 46 67 3 50 0.75
4 四个 a 43 30 1 19 0.63
4 四个 a 43 30 2 41 1.37
4 四个 a 43 30 3 13 0.43
5 五个 b 38 17 1 1 0.06
5 五个 b 38 17 2 38 2.24
5 五个 b 38 17 3 16 0.94
6 六个 b 37 73 1 48 0.66
6 六个 b 37 73 2 38 0.52
6 六个 b 37 73 3 24 0.33
7 七个 b 36 8 1 5 0.63
7 七个 b 36 8 2 3 0.38
7 七个 b 36 8 3 36 4.50
8 八个 b 45 56 1 16 0.29
8 八个 b 45 56 2 44 0.79
8 八个 b 45 56 3 41 0.73
9 九个 c 44 55 1 6 0.11
9 九个 c 44 55 2 50 0.91
9 九个 c 44 55 3 43 0.78
10 十个 c 37 30 1 13 0.43
10 十个 c 37 30 2 29 0.97
10 十个 c 37 30 3 25 0.83

每个应用ID代表一个数据集。

我可以按应用ID和维度值进行排序，如下所示：=QUERY(A14:H44, "SELECT * ORDER BY A, G")。

在这个示例中，我只想返回每个应用ID/数据集的前2行。所以期望的输出如下：

应用ID 应用名称 L1经理 目标分数 降分金额 维度名称 维度值 权重
1 一个 a 49 10 2 9 0.90
1 一个 a 49 10 3 15 1.50
2 两个 a 32 70 2 22 0.31
2 两个 a 32 70 3 30 0.43
3 三个 a 46 67 2 18 0.27
英文:

I have data (example below) that represents multiple sets of data. I want to get all the sets but limit each set to the first X rows.

I know how to use the limit function of query but that seems to limit for all the rows.

Example Data

app ID app name l1 manager target score amount to drop by score dimension name dimension value weight
1 one a 49 10 1 35 3.50
1 one a 49 10 2 9 0.90
1 one a 49 10 3 15 1.50
2 two a 32 70 1 50 0.71
2 two a 32 70 2 22 0.31
2 two a 32 70 3 30 0.43
3 three a 46 67 1 45 0.67
3 three a 46 67 2 18 0.27
3 three a 46 67 3 50 0.75
4 four a 43 30 1 19 0.63
4 four a 43 30 2 41 1.37
4 four a 43 30 3 13 0.43
5 five b 38 17 1 1 0.06
5 five b 38 17 2 38 2.24
5 five b 38 17 3 16 0.94
6 six b 37 73 1 48 0.66
6 six b 37 73 2 38 0.52
6 six b 37 73 3 24 0.33
7 seven b 36 8 1 5 0.63
7 seven b 36 8 2 3 0.38
7 seven b 36 8 3 36 4.50
8 eight b 45 56 1 16 0.29
8 eight b 45 56 2 44 0.79
8 eight b 45 56 3 41 0.73
9 nine c 44 55 1 6 0.11
9 nine c 44 55 2 50 0.91
9 nine c 44 55 3 43 0.78
10 ten c 37 30 1 13 0.43
10 ten c 37 30 2 29 0.97
10 ten c 37 30 3 25 0.83

Each app ID is one set.

I can sort on app ID and dimension value like so: =QUERY(A14:H44, "SELECT * ORDER BY A, G").

In this example, I only want it to return the first 2 rows for each app ID/set. So the expected output would be like:

app ID app name l1 manager target score amount to drop by score dimension name dimension value weight
1 one a 49 10 2 9 0.90
1 one a 49 10 3 15 1.50
2 two a 32 70 2 22 0.31
2 two a 32 70 3 30 0.43
3 three a 46 67 2 18 0.27
3 three a 46 67 1 45 0.67
4 four a 43 30 3 13 0.43
4 four a 43 30 1 19 0.63
5 five b 38 17 1 1 0.06
5 five b 38 17 3 16 0.94
6 six b 37 73 3 24 0.33
6 six b 37 73 2 38 0.52
7 seven b 36 8 2 3 0.38
7 seven b 36 8 1 5 0.63
8 eight b 45 56 1 16 0.29
8 eight b 45 56 3 41 0.73
9 nine c 44 55 1 6 0.11
9 nine c 44 55 3 43 0.78
10 ten c 37 30 1 13 0.43
10 ten c 37 30 3 25 0.83

答案1

得分: 1

*这里有一种方法（非基于查询）可以尝试：*

=reduce(A1:H1,sort(unique(tocol(A2:A,1))),lambda(a,c,iferror(vstack(a,array_constrain(sort(filter(A:H,A:A=c),7,1),2,8)))))

 - 在这里使用`array_constrain`来设置限制为`2行 * 8列`，您可以根据需要进行更改
 - 使用`sort`来升序排列第1列`A`和第7列`G`
英文:

<!-- language-all: js -->

Here's one approach (not query-based) you may test out:

=reduce(A1:H1,sort(unique(tocol(A2:A,1))),lambda(a,c,iferror(vstack(a,array_constrain(sort(filter(A:H,A:A=c),7,1),2,8)))))
  • array_constrain is used here to set the limit as 2 rows * 8 columns which you may change accordingly
  • sort is used to ascend both column_1 A & column_7 G

How can I get/query data from a table in Google Spreadsheets and limit to the first X rows for each "set" of rows?

答案2

得分: 1

老派方法，使用按行条件计数'App ID'：

={A1:H1;filter(A2:H,countifs(A2:A,A2:A,row(A2:A),"<="&row(A2:A))<=2)}

注意：您的问题说您想要每个应用程序ID的前两行，但您的示例答案似乎有最后两行。您可以将"<="翻转为">="以考虑任一可能性。

英文:

Old-school approach using a conditional count of 'App ID' by row:

={A1:H1;filter(A2:H,countifs(A2:A,A2:A,row(A2:A),&quot;&lt;=&quot;&amp;row(A2:A))&lt;=2)}

N.B. Your question says you want the first two rows per App ID, but your example answer appears to have the last two rows. You can flip the &quot;&lt;=&quot; for a &quot;&gt;=&quot; to account for either possibility.

huangapple
  • 本文由 发表于 2023年7月17日 21:47:09
  • 转载请务必保留本文链接：https://go.coder-hub.com/76705099.html
  • google-sheets
  • google-sheets-formula
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定