Tablestore：統計彙總

參數

樣本

統計年齡為 18 歲的人中得分的最低分數。

query = TermQuery('age', 18)
agg = Min('score', name='min')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

統計年齡為 18 歲的人中得分的最高分數。如果某人沒有分數，則對應分數的預設值為 0。

query = TermQuery('age', 18)
agg = Max('score', missing_value=0, name='max')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

統計年齡為 18 歲的所有人得分的總和。

query = TermQuery('age', 18)
agg = Sum('score', name='sum')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

統計年齡為 18 歲的所有人得分的平均分。

query = TermQuery('age', 18)
agg = Avg('score', name='avg')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

統計年齡為 18 歲的人中參加考試有分數的人數。

query = TermQuery('age', 18)
agg = Count('score', name='count')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

去重統計年齡為 18 歲的人中一共有多少種不同的姓名。

query = TermQuery('age', 18)
agg = DistinctCount('name', name='distinct_name')

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s, value: %s" % (agg_result.name, str(agg_result.value)))

參數

樣本

query = TermQuery('product', '10010')
agg = Percentiles('latency', percentiles_list=[50, 90, 95])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, aggs=[agg]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for agg_result in search_response.agg_results:
    print("name: %s" % agg_result.name)
    for item in agg_result.value:
        print("  percentile: %s, value: %s" % (str(item.key), str(item.value)))

參數

樣本 1

將年齡為 18 歲的人按分數分組，並擷取人數最多的 10 個分數值以及每個分數的人數。

query = TermQuery('age', 18)
group_by = GroupByField('score', size=10)

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  key: %s, count: %d" % (item.key, item.row_count))

樣本 2

將年齡為 18 歲的人按分數分組，並擷取人數最少的 2 個分數值以及每個分數的人數。

group_by = GroupByField('score', size=2, group_by_sort=[RowCountSort(sort_order=SortOrder.ASC)])

search_response = client.search(table_name, index_name,
    SearchQuery(TermQuery('age', 18), limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  key: %s, count: %d" % (item.key, item.row_count))

樣本 3

將年齡為 18 歲的人按分數分組，並擷取人數最多的 2 個分數值、每個分數的人數以及按主鍵排序前三的人的資訊。

sort = RowCountSort(sort_order=SortOrder.DESC)
sub_agg = [TopRows(limit=3, sort=Sort([PrimaryKeySort(sort_order=SortOrder.DESC)]), name='top_rows')]
group_by = GroupByField('score', size=2, group_by_sort=[sort], sub_aggs=sub_agg)

search_response = client.search(table_name, index_name,
    SearchQuery(TermQuery('age', 18), limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  key: %s, count: %d" % (item.key, item.row_count))
        for sub_agg in item.sub_aggs:
            print("    sub_agg: %s" % sub_agg.name)
            for entry in sub_agg.value:
                print("      value: %s" % str(entry))

樣本 4

將年齡為 18 歲的人按分數和性別分組。

sort = RowCountSort(sort_order=SortOrder.ASC)
sub_group = GroupByField('sex', size=10, group_by_sort=[sort])
group_by = GroupByField('score', size=10, group_by_sort=[sort], sub_group_bys=[sub_group])

search_response = client.search(table_name, index_name,
    SearchQuery(TermQuery('age', 18), limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  key: %s, count: %d" % (item.key, item.row_count))
        for sub_group in item.sub_group_bys:
            print("    sub_group: %s" % sub_group.name)
            for sub_item in sub_group.items:
                print("      key: %s, count: %s" % (str(sub_item.key), str(sub_item.row_count)))

參數

樣本

統計年齡為 18 歲的人中得分的分數在 [80, 90) 和 [90, 100) 兩個區間段的人數。

query = TermQuery('age', 18)
group_by = GroupByRange(field_name='score', ranges=[(80, 90), (90, 100)])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  range: %.1f~%.1f, count: %d" % (item.range_from, item.range_to, item.row_count))

參數

樣本

統計年齡為 18 歲的人中家庭住址在距離學校一公裡以內和一公裡到兩公裡內的人數。其中學校經緯度為(31,116)。

query = TermQuery('age', 18)
group_by = GroupByGeoDistance(field_name='address', origin=GeoPoint(31, 116), ranges=[(0, 1000), (1000, 2000)])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  range: %.1f~%.1f, count: %d" % (item.range_from, item.range_to, item.row_count))

參數

樣本

分別統計年齡為 18 歲的人中數學考了 100 分和語文考了 100 分的人數。

query = TermQuery('age', 18)
filter1 = TermQuery('math', 100)
filter2 = TermQuery('chinese', 100)
filters = [filter1, filter2]
group_by = GroupByFilter(filters)

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    i = 0
    for item in group_by_result.items:
        print("  filter: %s=%s, count: %d" % (str(filters[i].field_name), str(filters[i].column_value), item.row_count))
        i += 1

參數

樣本

query = TermQuery('product', '10010')
group_by = GroupByHistogram(field_name='latency', interval=100, field_range=FieldRange(0, 10000), missing_value=0)

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.ALL_FROM_INDEX))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  key: %s, value: %s" % (item.key, item.value))

參數

樣本 1

按欄位 score 對年齡為 18 歲的人進行分組，擷取每個分數值對應的行數。

query = TermQuery('age', 18)
# 作為資料來源的 GroupByField 只需指定 field_name 和 name，不能設定 size
source = GroupByField('score', name='group_by_score')
group_by = GroupByComposite(sources=[source])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.NONE))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    print("source_names: %s" % str(group_by_result.source_group_by_names))
    for item in group_by_result.items:
        print("  keys: %s, count: %d" % (str(item.keys), item.row_count))

樣本 2

按欄位 score 和 sex 對年齡為 18 歲的人進行組合分組，擷取每個分數和性別組合對應的行數。

query = TermQuery('age', 18)
source1 = GroupByField('score', name='group_by_score')
source2 = GroupByField('sex', name='group_by_sex')
group_by = GroupByComposite(sources=[source1, source2])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.NONE))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    print("source_names: %s" % str(group_by_result.source_group_by_names))
    for item in group_by_result.items:
        # keys 是字串列表，與 sources 順序一一對應；欄位值為空白時對應位置為 None
        print("  keys: %s, count: %d" % (str(item.keys), item.row_count))

樣本 3

使用 next_token 實現分頁擷取所有分組結果。按欄位 score 分組，每次擷取 2 個分組，通過翻頁擷取所有結果。

query = TermQuery('age', 18)
source = GroupByField('score')
group_by = GroupByComposite(sources=[source], size=2)

# 首次請求
search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.NONE))

group_by_result = search_response.group_by_results[0]
all_items = list(group_by_result.items)

# 翻頁擷取剩餘結果，next_token 為 None 時表示已擷取完畢
while group_by_result.next_token is not None:
    group_by = GroupByComposite(sources=[source], size=2, next_token=group_by_result.next_token)
    search_response = client.search(table_name, index_name,
        SearchQuery(query, limit=0, get_total_count=True, group_bys=[group_by]),
        ColumnsToGet(return_type=ColumnReturnType.NONE))

    group_by_result = search_response.group_by_results[0]
    all_items.extend(group_by_result.items)

# 輸出所有分組結果
for item in all_items:
    print("keys: %s, count: %d" % (str(item.keys), item.row_count))

樣本 4

使用子統計彙總。按欄位 score 分組後，統計每個分組中 score 的最大值。

query = TermQuery('age', 18)
source = GroupByField('score')
sub_agg = Max('score')
group_by = GroupByComposite(sources=[source], sub_aggs=[sub_agg])

search_response = client.search(table_name, index_name,
    SearchQuery(query, limit=0, get_total_count=True, group_bys=[group_by]),
    ColumnsToGet(return_type=ColumnReturnType.NONE))

for group_by_result in search_response.group_by_results:
    print("name: %s" % group_by_result.name)
    for item in group_by_result.items:
        print("  keys: %s, count: %d" % (str(item.keys), item.row_count))
        for sub_agg in item.sub_aggs:
            print("    sub_agg: %s, value: %s" % (sub_agg.name, str(sub_agg.value)))