SQLAlchemy: Get the maximum value of grouped values
P粉738346380
2023-09-01 12:07:40
<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>
This is a bad (inefficient) approach:
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().