聚合与分组

← 返回 Python3 SQLite 数据库

56.7 聚合与分组

COUNT 计数、SUM 求和、AVG 平均、MAX/MIN 最大最小。

GROUP BY 按列分组，配合 HAVING 过滤分组结果（类似 WHERE 但针对分组）。

全班统计

import sqlite3

with sqlite3.connect('school.db') as conn:
    row = conn.execute(
        'SELECT COUNT(*), AVG(score), MAX(score), MIN(score) FROM students'
    ).fetchone()
    print('人数', row[0], '平均分', round(row[1], 1))

按性别分组平均分

import sqlite3

with sqlite3.connect('school.db') as conn:
    cur = conn.execute(
        'SELECT gender, AVG(score) FROM students GROUP BY gender HAVING COUNT(*) >= 1'
    )
    for gender, avg_score in cur:
        print(gender, round(avg_score, 1))