SQLAlchemy: Get the maximum value of grouped values
P粉738346380
P粉738346380 2023-09-01 12:07:40
0
1
471
<p>I have this table in my Flask app: </p> <pre class="brush:php;toolbar:false;">class Events(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) timestamp_event = db.Column(db.DateTime, nullable=False)</pre> <p>I need to get the day with the most records, including the value and the date itself in a printable format (e.g. d/m/y). I tried the following code: </p> <pre class="brush:php;toolbar:false;">daily_max = db.func.max(db.session.query(db.func.count(Events.id),\ db.func.date_format(Events.timestamp_event,'%d/%m/%y'))\ .group_by(db.func.date_format(Events.timestamp_event,'%Y%M%D')))</pre> <p>Then we hope that daily_max[0] has a value and daily_max[1] does not have the date of hh:mm:ss. </p> <p>When I print daily_max after the query, the value is: </p> <pre class="brush:php;toolbar:false;">daily_max = max((SELECT count(events.id) AS count_1, date_format(events.timestamp_event, :date_format_2) AS date_format_1 FROM events GROUP BY date_format(events.timestamp_event, :date_format_3)))</pre> <p>This is a sample of the data. The answer (the value of <code>daily_max</code>) should be <strong>(3, "21/01/2022")</strong>: </p> <pre class="brush:php;toolbar:false;">id timestamp_event ---- --------------- 1 2022-01-15 12:34 2 2022-01-21 01:23 3 2022-01-21 05:33 4 2022-01-21 11:11 5 2022-01-23 00:01 6 2022-01-23 23:29</pre> <p>Any clues as to what I'm doing wrong? Can't find the answer in the documentation. </p>
P粉738346380
P粉738346380

reply all(1)
P粉541565322

This is a bad (inefficient) approach:

daily_max = db.session.query(db.func.count(Events.id),
            db.func.date_format(Events.timestamp_event,'%d/%m/%y'))\
            .group_by(db.func.date_format(Events.timestamp_event,'%Y%M%D'))\
            .order_by(db.func.count(Events.id).desc()).first()

It groups all (id count, date) pairs, sorts the pairs in reverse order of count, and gets the first pair.

It gets the job done, but I'd really like to know an efficient, elegant way to use func.max().

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template