python - 在 SQLAlchemy 中，如何在 GROUP BY 之后对算术表达式进行 ORDER BY？

Question

我的表:

tb{
caller:string
callee:string
success:boolean
time: datetime
}

这个表是一个调用状态表，每次一个函数调用另一个函数后，调用者在我的数据库中存储自己的名字，调用的函数，调用状态(成功或失败)和调用时间。

现在我想用 SQL 或 Flask-SQLAlchemy 得到如下结果:

result(caller，callee, success_count, total_count, success_ratio)

我想获得按每个元组(调用者、被调用者)分组并按 success_ratio 排序的 success_count、total_count 和 success_ratio 的统计数据。

如何使用 SQL 语句或 Flask-SQLAlchemy 执行此操作？

Answer 1

让我们考虑以下示例 callstate 表。

+-----------+---------------+----------+---------------------+
| caller    | callee        | success  | time                |
+-----------+---------------+----------+---------------------+
| Bar::baz  | Dolor::sit    | 0        | 2016-01-07 00:00:00 |
| Bar::baz  | Dolor::sit    | 0        | 2016-01-05 00:00:00 |
| Bar::baz  | Lorem::ipsum  | 0        | 2016-01-01 00:00:00 |
| Bar::baz  | Lorem::ipsum  | 1        | 2016-01-04 00:00:00 |
| Bar::baz  | Lorem::ipsum  | 1        | 2016-01-09 00:00:00 |
| Bar::baz  | Lorem::ipsum  | 1        | 2016-01-08 00:00:00 |
| Bar::baz  | Lorem::ipsum  | 1        | 2016-01-04 00:00:00 |
| Bar::baz  | Qux::foo      | 0        | 2016-01-05 00:00:00 |
| Bar::baz  | Qux::foo      | 0        | 2016-01-01 00:00:00 |
| Bar::baz  | Qux::foo      | 1        | 2016-01-05 00:00:00 |
| Foo::bar  | Dolor::sit    | 0        | 2016-01-06 00:00:00 |
| Foo::bar  | Lorem::ipsum  | 0        | 2016-01-08 00:00:00 |
| Foo::bar  | Lorem::ipsum  | 1        | 2016-01-03 00:00:00 |
| Foo::bar  | Lorem::ipsum  | 1        | 2016-01-05 00:00:00 |
| Foo::bar  | Lorem::ipsum  | 1        | 2016-01-07 00:00:00 |
| Foo::bar  | Qux::foo      | 0        | 2016-01-07 00:00:00 |
| Foo::bar  | Qux::foo      | 0        | 2016-01-04 00:00:00 |
+-----------+---------------+----------+---------------------+

用于获取统计信息的简单 SQL 查询。

SELECT caller,
       callee,
       sum(success) AS 'success_count',
       count(*) AS 'total_count',
       sum(success) / count(*) AS 'success_ratio'
FROM callstate
GROUP BY caller, callee
ORDER BY success_ratio DESC

SQLAlchemy 中的类似查询。为了示例，这是纯 SQLAlchemy，但查询部分在 Flask-SQLAlchemy 的情况下应该非常相似。

#!/usr/bin/env python

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+mysqldb://johndoe@localhost:3306/so_callstate')
connection = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()

cs = Table('callstate', MetaData(),
    Column('caller', String()),
    Column('callee', String()),
    Column('success', Boolean())
)

result = session.query(
    cs.c.caller,
    cs.c.callee,
    func.sum(cast(cs.c.success, Integer).label('success_count'),
    func.count().label('total_count'),
    (func.sum(cast(cs.c.success, Integer)) / func.count()).label('success_ratio')
).group_by(cs.c.caller, cs.c.callee).order_by(desc('success_ratio'))

for row in result:
    print(row)

基于上述示例的示例输出。

('Bar::baz', 'Lorem::ipsum', Decimal('4'), 5L, Decimal('0.8000'))
('Foo::bar', 'Lorem::ipsum', Decimal('3'), 4L, Decimal('0.7500'))
('Bar::baz', 'Qux::foo', Decimal('1'), 3L, Decimal('0.3333'))
('Foo::bar', 'Qux::foo', Decimal('0'), 2L, Decimal('0.0000'))
('Bar::baz', 'Dolor::sit', Decimal('0'), 2L, Decimal('0.0000'))
('Foo::bar', 'Dolor::sit', Decimal('0'), 1L, Decimal('0.0000'))