如何在烧瓶不安中构造"group_by" SQL查询？

Question

我正在使用blask_sqlalchemy和blask_restless创建一个API端点，以从PostgreSQL数据库访问数据。如何在无烧瓶中构造以下SQL查询？

SELECT AVG("SpeedBand"), "Location"
FROM public.traffic_data
WHERE "RoadName"='ABC ROAD'
GROUP BY "Location"

这返回属于" ABC Road"的所有坐标对以及每对坐标的平均速度：

   avg   | Location
 ------------------------
1)'1.52' | 'X1 Y1 X2 Y2'
2)'1.85' | 'X3 Y3 X4 Y4'
 ------------------------

目前，此查询：

http://127.0.0.1:5000/api/traffic_data?q={"filters":[{"name":"RoadName","op":"==","val":"ABC ROAD"}]}

返回属于" ABC Road"的所有数据的JSON字符串以及不同时间的所有速度数据：

{
  "num_results": 26, 
  "objects": [
    {
      "DateTime": "2017-09-07T19:25:37.035325", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 3, 
      "id": 34094
    }, 
    {
      "DateTime": "2017-09-07T19:25:37.035325", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 38003
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 92874
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 96783
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 114755
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 114756
    }, 
    {
      "DateTime": "2017-09-13T10:08:17.727949", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 151654
    }, 
    {
      "DateTime": "2017-09-13T10:08:17.727949", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 155563
    }, 
    {
      "DateTime": "2017-09-13T10:19:39.040191", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 210434
    }, 
    {
      "DateTime": "2017-09-13T10:19:39.040191", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 214343
    }, 
    {
      "DateTime": "2017-09-13T10:27:51.307349", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 269214
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 327994
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 331903
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 349875
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 349876
    },
    . . .

  ], 
  "page": 1, 
  "total_pages": 1
}

Answer 1

for group by you需要以以下格式指定费尔德。通过...分组表格的对象列表：

{"field": <fieldname>}

此处fieldName是组由。