CODEWITHSUNDEEP
pythonsqlflasksqlalchemyflask-sqlalchemy
张元亮
张元亮

Reputation: 61

How do I ORDER BY an arithmetic expression after a GROUP BY in SQLAlchemy?

My Table:

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

This table is a call state table, every time after a function calls another one, the caller stores its own name, the function it called, the call state (success or failure) and the call time in my database.

Now I want to get results with SQL or Flask-SQLAlchemy as follows:

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

I want to get the statistics of success_count, total_count and success_ratio grouped by every tuple (caller, callee) and ordered by success_ratio.

How can I do this with an SQL statement or Flask-SQLAlchemy?

Upvotes: 3

Views: 2157

Answers (1)

zsltg
zsltg

Reputation: 773

Let's consider the following example callstate table.

+-----------+---------------+----------+---------------------+
| 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 |
+-----------+---------------+----------+---------------------+

A simple SQL query for getting the statistics.

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

A similar query in SQLAlchemy. For the sake of example, this is pure SQLAlchemy, but the query part should be very similar in case of 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)

A sample output based on the example above.

('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'))

Upvotes: 3

Related Questions