英文:
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 as2 rows * 8 columns
which you may change accordinglysort
is used to ascend both column_1A
& column_7G
答案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),"<="&row(A2:A))<=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 "<="
for a ">="
to account for either possibility.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论