Select first object of group by in postgresql/sqlalchemy

Question

I have a request in sqlachemy with SQLite which return an object of the group by and a value (avg) :

result = session.query(
        obj, func.avg(obj.value).label("value_avg")
    ).group_by(
        func.strftime('%s', obj.date)
    ).all()

But now i need to use postgresql which is more restrictive (strict SQL) and i need to do the same thing but it need to replace query(obj) by something in the group by like func.avg() or something else. So i would like to know if exist any func which can be able to return the first obj of each group. If not possible maybe i can implement comparator for my obj and for instance call func.min(obj) like this :

result = session.query(
        func.min(obj), func.avg(obj.value).label("value_avg")
    ).group_by(
        func.date_part('second', obj.date)
    ).all()

And maybe implement cmp and eq in my obj model ? (What is the best practice)

EDIT :

I got a workaround but i'm not sure it's a good practice. First group by and next join :

sq = session.query(
        func.min(obj.date).label("date"), func.avg(obj.value).label("value_avg")
    ).group_by(
        func.cast(func.extract('second', obj.date) / 600, Integer)
    ).order_by(obj.date).subquery()
result = session.query(obj, sq.c.value_avg).join(sq,sq.c.date == obj.date).all()

What i want is the first obj of each group and value_avg of the group

Your solution (generally) could return multiple results. Unless the value in min() is unique (which I guess it is, because id sound like a primary key; but if it's only a foreign key, that might not be the case). -- This is a specialized case of greatest-n-per-group (where N=1). There are a plenty of SQL solutions already here on SO, but I don't know which is the most suitable for SQLAlchemy. — pozs
– pozs, Commented Apr 7, 2017 at 8:00
but that doesn't make much sense, because SELECT min(date) ... GROUP BY date and SELECT date ... GROUP BY date is the same (there is only one date in the date group anyway). -- Also, a single index doesn't guarantee its uniqueness. — pozs
– pozs, Commented Apr 7, 2017 at 8:11
Yes i agree but in my result i have access to other attribut which i need. With SQlite i didn't have this problem as you can this in the first case because of query(obj, func.avg(obj.value).label("value_avg")) which give me access to obj attributs — Timo
– Timo, Commented Apr 7, 2017 at 8:23
Why not use over() if you need to select both aggregated and non-aggregated value? — Łukasz Kamiński
– Łukasz Kamiński, Commented Apr 7, 2017 at 9:37

Łukasz Kamiński · Accepted Answer · 2017-04-07 12:45:25Z

3

You need to list all columns that you want to select and put them in group_by. Then you can select aggregated columns that are not part of the group by.

result = session.query(
    obj.column1,
    obj.column2,
    obj.column3,
    func.strftime('%s', obj.date),
    func.avg(obj.value).label("value_avg")
).group_by(
    obj.column1,
    obj.column2,
    obj.column3,
    obj.date
).all()

answered Apr 7, 2017 at 12:45

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Timo Over a year ago

Yes but it's not easy when you have to deal with foreign object like obj.obj2.obj3 with lazy query

Collectives™ on Stack Overflow

Select first object of group by in postgresql/sqlalchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related