在SqlAlchemy上使用(Postgres)上的distinct的正确方法是什么? [英] What is the correct way to use distinct on (Postgres) with SqlAlchemy?

问题描述

我想获取具有max(timestamp)和名称分组的表的所有列.

I want to get all the columns of a table with max(timestamp) and group by name.

到目前为止，我尝试过的是:normal_query =从表中选择max(timestamp)作为时间"

What i have tried so far is: normal_query ="Select max(timestamp) as time from table"

event_list = normal_query \
            .distinct(Table.name)\
            .filter_by(**filter_by_query) \
            .filter(*queries) \
            .group_by(*group_by_fields) \
            .order_by('').all()

我得到的查询:

SELECT  DISTINCT ON (schema.table.name) , max(timestamp)....

此查询基本上返回带有名称和时间戳的两列.

this query basically returns two columns with name and timestamp.

而我要查询:

SELECT DISTINCT ON (schema.table.name) * from table order by ....

返回该表中的所有列.这是预期的行为，并且我能够获取所有列，我如何在python中纠正它以得到此语句?.基本上星号丢失了.有人可以帮我吗?

which returns all the columns in that table.Which is the expected behavior and i am able to get all the columns, how could i right it down in python to get to this statement?.Basically the asterisk is missing. Can somebody help me?

推荐答案

您似乎想要的是

What you seem to be after is the DISTINCT ON ... ORDER BY idiom in Postgresql for selecting greatest-n-per-group results (N = 1). So instead of grouping and aggregating just

event_list = Table.query.\
    distinct(Table.name).\
    filter_by(**filter_by_query).\
    filter(*queries).\
    order_by(Table.name, Table.timestamp.desc()).\
    all()

这将最终选择按名称分组"的行，这些行具有最大的时间戳记值.

This will end up selecting rows "grouped" by name, having the greatest timestamp value.

除非您正在执行手动即席查询，否则您通常不想在应用程序代码中始终使用星号.星号基本上是" FROM 表/关系中的所有列"，如果您添加列，对其进行重新排序等等，那么稍后可能会打破您的假设.

You do not want to use the asterisk most of the time, not in your application code anyway, unless you're doing manual ad-hoc queries. The asterisk is basically "all columns from the FROM table/relation", which might then break your assumptions later, if you add columns, reorder them, and such.

如果您想根据时间戳在最终结果中对结果行进行排序，则可以使用例如

In case you'd like to order the resulting rows based on timestamp in the final result, you can use for example Query.from_self() to turn the query to a subquery, and order in the enclosing query:

event_list = Table.query.\
    distinct(Table.name).\
    filter_by(**filter_by_query).\
    filter(*queries).\
    order_by(Table.name, Table.timestamp.desc()).\
    from_self().\
    order_by(Table.timestamp.desc()).\
    all()

这篇关于在SqlAlchemy上使用(Postgres)上的distinct的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！