在google查询中选择最新日期的平均数

wkyowqbh 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(328)

我需要计算每个农业的最新日期的平均价格。我使用这个google查询从googlesheets加载表。

var queryString = encodeURIComponent("select max(A), D, avg(E) where B = 'cpt-novo' group by D");

但是我得到了错误的结果。此外，如果我更改了任何内容，我经常会出现错误，例如“add \u col \u to \u group \u by \u or \u agg”或“cannot \u group \u without \u agg”。
这是table：

date,       basis,    trader,   culture,       price
10.10.2017, cpt-novo, one,      wheat-5-class, 8000
31.10.2017, cpt-novo, one,      wheat-5-class, 8500
17.10.2017, cpt-novo, one,      wheat-11.5,    9100
31.10.2017, cpt-novo, one,      wheat-11.5,    9200
17.10.2017, cpt-novo, one,      wheat-12,      9300
31.10.2017, cpt-novo, one,      wheat-12,      9400
17.10.2017, cpt-novo, one,      wheat-12.5,    9500
31.10.2017, cpt-novo, one,      wheat-12.5,    9600
17.10.2017, cpt-novo, one,      wheat-13,      9750
31.10.2017, cpt-novo, one,      wheat-13,      9850
17.10.2017, cpt-novo, one,      wheat-13.5,    10000
31.10.2017, cpt-novo, one,      wheat-13.5,    10100
27.07.2017, cpt-novo, two,      barley,        8600
05.08.2017, cpt-novo, two,      barley,        9000
02.09.2017, cpt-novo, two,      wheat-11.5,    8300
10.10.2017, cpt-novo, two,      wheat-11.5,    9000
10.10.2017, cpt-novo, two,      wheat-12,      9300
01.12.2017, cpt-novo, two,      wheat-12,      9200
10.10.2017, cpt-novo, two,      wheat-12.5,    9600
01.12.2017, cpt-novo, two,      wheat-12.5,    9500
10.10.2017, cpt-novo, two,      wheat-13,      9800
01.12.2017, cpt-novo, two,      wheat-13,      9700
10.10.2017, cpt-novo, two,      wheat-13.5,    10000
10.10.2017, cpt-novo, two,      wheat-13.5,    10100
06.12.2017, cpt-novo, three,    wheat-13,      9800
06.12.2017, cpt-novo, three,    wheat-12,      9400
06.12.2017, cpt-novo, three,    wheat-11.5,    9200

结果应该是这些（我自己计算）：

wheat-5-class,  8500
wheat-11.5,     9133,333333
wheat-12,       9333,333333
wheat-12.5,     9550
wheat-13,       9783,333333
wheat-13.5,     10100
barley,         9000

sql mysql google-api google-docs-api google-query-language

来源：https://stackoverflow.com/questions/48186088/select-average-number-for-the-latest-date-in-google-query

1条答案

按热度按时间

watbbzwu1#

这个答案适用于mysql：
第一步：计算每个区域性的最大日期。。。

SELECT culture, MAX(date) max_date FROM my_table GROUP BY culture;
    +---------------+------------+
    | culture       | max_date   |
    +---------------+------------+
    | barley        | 2017-08-05 |
    | wheat-11.5    | 2017-10-31 |
    | wheat-12      | 2017-12-01 |
    | wheat-12.5    | 2017-12-01 |
    | wheat-13      | 2017-12-01 |
    | wheat-13.5    | 2017-10-31 |
    | wheat-5-class | 2017-10-31 |
    +---------------+------------+

第二步：找到满足上述条件的所有行。。。

SELECT a.* 
  FROM my_table a 
  JOIN
     ( SELECT culture
            , MAX(date) max_date 
         FROM my_table 
        GROUP 
           BY culture
     ) b
    ON b.culture = a.culture
   AND b.max_date = a.date;
+------------+----------+--------+---------------+-------+
| Date       | basis    | trader | culture       | price |
+------------+----------+--------+---------------+-------+
| 2017-08-05 | cpt-novo | two    | barley        |  9000 |
| 2017-10-31 | cpt-novo | one    | wheat-11.5    |  9200 |
| 2017-12-01 | cpt-novo | two    | wheat-12      |  9200 |
| 2017-12-01 | cpt-novo | two    | wheat-12.5    |  9500 |
| 2017-12-01 | cpt-novo | two    | wheat-13      |  9700 |
| 2017-10-31 | cpt-novo | one    | wheat-13.5    | 10100 |
| 2017-10-31 | cpt-novo | one    | wheat-5-class |  8500 |
+------------+----------+--------+---------------+-------+

第三步：找到上述产品的平均价格（按文化）。
作为练习留给读者。
编辑：我对gql一无所知。我怀疑你可以写下面这样的东西，但我不知道它是正确的还是有效的。。。

for b in db.GqlQuery("SELECT culture , MAX(date) max_date GROUP BY culture"):
for a in db.GqlQuery("SELECT * WHERE culture=:1 AND date=:2", b.culture,b.max_date):
print a.price

同样，在上述不太可能正确的情况下，我省略了步骤3。

赞(0）回复(0）举报 2021-06-25

我来回答

在google查询中选择最新日期的平均数

1条答案

相关问题

热门标签

最新问答