python - 如何在 flask-reSTLess 中构建 "group_by"SQL 查询？

Question

我正在使用 flask_sqlalchemy 和 flask_reSTLess 创建一个 API 端点来访问来自 PostgreSQL 数据库的数据。如何在 flask-reSTLess 中构造以下 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

对于group by，您需要按照以下格式指定feild。通过...分组形式的对象列表:

{"field": <fieldname>}

这里的fieldname是group by要使用的列。