python - 对 GROUP BY 查询进行排序

Question

我在用 python 编写的非常简单的程序中使用 sqlite3 数据库。我遇到问题的表包含三列:

id, software_version, place

现在我想计算特定软件版本的频率，所以我创建了这个查询:

SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh

结果我得到了:

program_version_1.1, 2
program_version_1.2, 2
program_version_1.3, 20
program_version_2.1, 7

不幸的是，输出没有按频率排序。如何修复查询以使其更好地工作？

Answer 1

在 COUNT 结果上添加 ORDER BY；给该结果一个别名:

SELECT
    software_ssh,
    COUNT (software_ssh) as freq
FROM simple_table
GROUP BY software_ssh 
ORDER BY freq

如果您需要从最频繁到最少的行排序，请使用ORDER BY freq DESC。

演示:

>>> from pprint import pprint
>>> c.execute('SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh')
<sqlite3.Cursor object at 0x102034490>
>>> pprint(list(c))
[(u'program_version_1.1', 2),
 (u'program_version_1.2', 2),
 (u'program_version_1.3', 20),
 (u'program_version_2.1', 7)]
>>> c.execute('''
... SELECT
...     software_ssh,
...     COUNT (software_ssh) as freq
... FROM simple_table
... GROUP BY software_ssh
... ORDER BY freq
... ''')
<sqlite3.Cursor object at 0x102034490>
>>> pprint(list(c))
[(u'program_version_1.1', 2),
 (u'program_version_1.2', 2),
 (u'program_version_2.1', 7),
 (u'program_version_1.3', 20)]