case / if / filter /？在google sheets查询中
我有一个登记簿，其中有他们的帐户id的所有交易。该寄存器有一个"Type“字段，我想在该字段上拉取摘要数据。
交易：
| A | B | C | D |
|---------|---------|-------------|------------|
| Account | Amount | Type | Date |
|---------|---------|-------------|------------|
| 1b6f | 44.21 | Charge | 2016-09-01 |
| 5g0p | 101.57 | Charge | 2016-09-01 |
| 5g0p | 21.53 | Upgrade Fee | 2016-09-01 |
| 5g0p | -123.10 | Payment | 2016-09-07 |
| 1b6f | 4.43 | Late Fee | 2016-10-01 |
| 1b6f | 4.87 | Late Fee | 2016-11-01 |我想要一个单一的查询，将允许我拉出所有以下摘要信息
Account
Current Balance
Total Charges
Last Charge Date
First Charge Date
Total Fees
Last Fee Date
Total Payments
Last Payment Date如下所示：
=QUERY(
A:D,
"SELECT A,
SUM(B),
SUM(FILTER(D:D,C:C='Charge')),
MAX(FILTER(D:D,C:C='Charge')),
MIN(FILTER(D:D,C:C='Charge')),
SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))),
MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))),
SUM(FILTER(B:B,C:C='Payment')),
MAX(FILTER(D:D,C:C='Payment'))
GROUP BY A
label
A 'Account',
SUM(B) 'Current Balance',
SUM(FILTER(D:D,C:C='Charge')) 'Total Charges',
MAX(FILTER(D:D,C:C='Charge')) 'Last Charge Date',
MIN(FILTER(D:D,C:C='Charge')) 'First Charge Date',
SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))) 'Total Fees',
MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))) 'Last Fee Date',
SUM(FILTER(B:B,C:C='Payment')) 'Total Payments',
MAX(FILTER(D:D,C:C='Payment')) 'Last Payment Date'
"
)有了这些结果
| Account | Current Balance | Total Charges | Last Charge Date | First Charge Date | Total Fees | Last Fee Date | Total Payments | Last Payment Date |
|---------|-----------------|---------------|------------------|-------------------|------------|---------------|----------------|-------------------|
| 1b6f | 53.51 | 44.21 | 2016-09-01 | 2016-09-01 | 9.30 | 2016-11-01 | 0 | |
| 5g0p | 121.55 | 223.12 | 2016-10-01 | 2016-09-01 | 21.53 | 2016-09-01 | -123.10 | 2016-09-07 |不幸的是，MAX requires input of of a column identifier似乎根本不能使用任何非标量函数(如FILTER)，甚至不能使用任何未列出的聚合函数(如JOIN)。
我目前正在使用一组具有不同WHERE参数的独立查询，但是它非常非常慢。
回答 3
Stack Overflow用户
发布于 2016-10-10 14:52:50
@trex005我为你创建了表格
的答案在这里：https://docs.google.com/spreadsheets/d/1oC9RYfZD4ITnVfksIVFbC0KkadtFma-JUVk2PdiXqIA/edit?usp=sharing
主公式的示例如下：
=SUMPRODUCT(FILTER(Sheet1!$B$2:$B,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))它会自动将Sheet1中的新ID添加到Sheet2中，并对其进行计数。我使用与谷歌表单相同的方式来计算我的工资。这样使用google forms是非常有效的
“收费”的最后日期公式：
=MAX(FILTER(Sheet1!$D$2:$D,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))其中A2:A是
=UNIQUE(Sheet1!A2:A)Stack Overflow用户
发布于 2016-10-10 15:31:05
Pivot是一个选项：
=QUERY(A:D,"select A, max(D), min(D), sum(B) where B is not null group by A pivot C",0)不过，它不会给出总数。
Stack Overflow用户
发布于 2016-10-10 19:14:04
简短的回答
在Google Sheets中，不可能使用唯一查询来获得所需的结果。一种解决方案是优化一组单独的查询
解释
正如您已经提到的，Google Query Language不支持将非标量值作为聚合函数的参数。
优化查询的一种方法是使用过滤器从没有WHERE子句的查询的源数组中删除空行。
示例
=QUERY(
FILTER(A:D,LEN(A:A),
"SELECT A, SUM(B) LABEL A 'Current Balance' SUM(B)' Total Charges'"
)然后，您可以使用数组sintax连接所有查询的结果
={query1,query2}参考文献
https://stackoverflow.com/questions/39951972
复制相似问题
腾讯云开发者
