Group/Aggregation

Aggregating Queries

Sometimes you only need get aggregated results for a dataset. In such cases you must specify how to group and aggregate the results through the "groups" and "aggregates" features.

The groups are set with only one key:

Key	Description
column	The column the grouping should be applied on

An aggregation has the following required keys:

Key	Description
column	The column the aggregation should be applied to
type	The type of "aggregation" to do: "count", "average", "max", "min", "sum"

Not every column will support every aggregation type.

For example, to get a count of Apple stores by city and state, an example request is:

POST /dataset/store/query/ HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130

{
    "tickers": ["nasdaq:aapl"],
    "groups": [
        {
            "column": "city"
        },
        {
            "column": "state"
        }
    ],
    "aggregations": [
        {
            "column": "city",
            "type": "count"
        }
    ]
}

curl -X POST "https://data.thinknum.com/datasets/store/query/" \
-H "Accept: application/json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789" \
-d $'{
    "tickers": ["nasdaq:aapl"],
    "groups": [
        {
            "column": "city"
        },
        {
            "column": "state"
        }
    ],
    "aggregations": [
        {
            "column": "city",
            "type": "count"
        }
    ]
}'

response = requests.post(
    url='https://data.thinknum.com/datasets/store/query/', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/json'
    }, 
    data=json.dumps({
        "tickers": ["nasdaq:aapl"],
        "groups": [
            {
                "column": "city"
            },
            {
                "column": "state"
            }
        ],
        "aggregations": [
            {
                "column": "city",
                "type": "count"
            }
        ]
    })
)
results = json.loads(response.text)

A sample response follows:

{
    "state": "complete",
    "total": 6903,
    "id": "bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57",
    "formats": [
        "application/vnd.thinknum.table+json",
        "application/vnd.thinknum.chart+json"
    ]
}

To check if query is completed:

HEAD /datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57 HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130
Accept: application/vnd.thinknum.table+json

curl -I HEAD "https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57" \
-H "Accept: application/vnd.thinknum.table+json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789"

response = requests.head(
    url='https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/vnd.thinknum.table+json'
    }
)
results = dict(response.headers)

A sample response follows:

HTTP/1.1 200 OK
Server: nginx
Date: Wed, 28 Apr 2021 15:48:33 GMT
Content-Type: application/json
Connection: keep-alive
X-Truncated: false
X-Formats: application/vnd.thinknum.table+json, application/vnd.thinknum.chart+json, text/csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
X-Total: 6903
X-State: complete
Strict-Transport-Security: max-age=15768000

To retrieve data:

GET /datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57?limit=10&start=1 HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130
Accept: application/vnd.thinknum.table+json
Content-Type: application/json

curl -X GET "https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57?limit=10&start=1" \
-H "Accept: application/vnd.thinknum.table+json" \
-H "Content-Type: application/json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789"

response = requests.get(
    url='https://data.thinknum.com/datasets/store/query/bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/vnd.thinknum.table+json'
    }
)
results = json.loads(response.text)

A sample response follows:

{
    "group_fields": [
    ...
    ],
    "total": 6903,
    "id": "bacbe09cd67c9f3de1d90e85cab74446704e94836e4274d0fc29c440ee5f8b57",
    "count": 10,
    "rows": [
        [
            "CLINTON",
            "OK",
            1
        ],
        [
            "Woodbridge",
            "Virginia",
            1
        ],
        ...
    ],
    "fields": [
        {
            "display_name": "City",
            "format": null,
            "metric": false,
            "id": "city",
            "length": 64,
            "summary": "",
            "type": "string",
            "options": []
        },
        {
            "display_name": "State",
            "format": null,
            "metric": false,
            "id": "state",
            "length": 64,
            "summary": "",
            "type": "string",
            "options": []
        },
        {
            "display_name": "City (Count)",
            "format": null,
            "metric": true,
            "id": "city@count",
            "length": null,
            "summary": "",
            "type": "number",
            "options": []
        }
    ],
    "sort_fields": [],
    "start": 1,
    "state": "complete",
    "limit": 10,
    "last_date_updated": "2021-04-01T02:34:56Z"
}