flask-sqlalchemy查询，用于返回最新的和使用不同的列

Question

我真的很难写出正确的postgres查询，我想在flask-sqlalchemy。

下面是我的数据的样例表。我正试图为每个唯一的name获得xxx和yyy的最新(最新时间戳)。

所以理想情况下，对于下面的数据，我希望我的查询返回底部的4个条目。

+------------+-------+-----+--------------------------+
|    name    |  xxx  | yyy |           time           | ... (other columns)
+------------+-------+-----+--------------------------+
| aaa        |   2   |  12 | 2021-03-11 20:27:13+00   |
| bbbb       |   9   |  13 | 2021-03-11 20:27:13+00   |
| cccc       |   2   |  16 | 2021-03-11 20:27:13+00   |
| dddd       |   10  |  26 | 2021-03-11 20:27:13+00   |
| aaa        |   4   |  13 | 2021-03-11 20:27:23+00   |
| bbbb       |   8   |  12 | 2021-03-11 20:27:23+00   |
| cccc       |   1   |  15 | 2021-03-11 20:27:23+00   |
| dddd       |   12  |  26 | 2021-03-11 20:27:23+00   |
| aaa        |   3   |  12 | 2021-03-11 20:27:33+00   |
| bbbb       |   6   |  11 | 2021-03-11 20:27:33+00   |
| cccc       |   1   |  17 | 2021-03-11 20:27:33+00   |
| dddd       |   13  |  23 | 2021-03-11 20:27:33+00   |
+------------+-------+-----|--------------------------+

我最基本的返回单条目最新值的方法如下:

single_query = MyModel 
.query 
.filter_by(name = 'aaaa) 
.order_by(desc(MyModel.time)) 
.first()

使用我的模型，我试图得到我的预期结果查询如下(基于其他一些SO答案):

full_query = MyModel 
.query 
.with_entities(MyModel.name, MyModel.time, MyModel.xxx) 
.distinct(MyMmodel.name) 
.all()

这让我找到了大部分方法，但它返回的是随机条目(至少看起来是这样)。我以为我可以很容易地添加一个order_by(desc(MyModel.time))，但我不能使它与上面的查询工作。

有什么建议，我如何才能得到这个工作或一些指针，让我在正确的方向?我已经摸不着头脑好一阵了。:)

我已经做了很多搜索，但不知道如何扩展这样的答案(SQLalchemy distinct, order_by different column)到我的模型查询。

---

更新

如果我想一次查询两个表，例如通过MyModel和MyModel2查询sample_table_1和sample_table_2，我如何将postgres转换为flask-sqlalchemy?

实现我想要的原始查询如下:我能够基于@Oluwafemi Sule的有用答案。我可以扩展这个或解决方案查询以满足我的需求吗?)

SELECT
m.name,
m.xxx,
m.yyy
n.xxx,
n.yyy
FROM (
SELECT
name,
xxx,              
yyy,
ROW_NUMBER() OVER(PARTITION BY container_name ORDER BY time DESC) AS rn       
FROM                          
sample_table_1
) m, (
SELECT
name,
xxx,
yyy,
ROW_NUMBER() OVER(PARTITION BY container_name ORDER BY time DESC) AS rn 
FROM
sample_table_2
) n
WHERE m.container_name = n.container_name and m.rn = 1 and n.rn = 1;

Answer 1

获得预期结果的SQL查询如下:

SELECT 
name
,xxx
,yyy
,time 
FROM
( 
SELECT 
name
,xxx
,yyy
,time
-- Number rows after partitioning by name and reverse chronological ordering
,ROW_NUMBER () OVER (PARTITION BY name ORDER BY time DESC) AS rn
FROM sample_table
) subquery
WHERE rn = 1; 

现在，编写SQLAlchemy查询应该如下所示:

from sqlalchemy import func
subquery = (
MyModel
.query
.with_entities(
MyModel.name, 
MyModel.time, 
MyModel.xxx, 
MyModel.yyy,
func.row_number().over(
partition_by=MyModel.name,
order_by=MyModel.time.desc()
).label("rn")
)
.subquery()
)
full_query = (
MyModel.query.with_entities(
subquery.c.name,
subquery.c.time,
subquery.c.xxx,
subquery.c.yyy
)
.select_from(subquery)
.filter(subquery.c.rn == 1)
.all()
)

Answer 2

您可以通过为每个列选择不同的first_value(按名称分区并按时间排序)来获得所需的结果

使用sqlalchemy。核心语言

import sqlalchemy as sa
from sqlalchemy import func
stmt = sa.select([
func.first_value(c)
.over(partition_by=MyModel.name,
order_by=MyModel.time.desc())
.label(c.name)
for c in MyModel.__table__.c
]).distinct()

生成如下SQL:

select distinct 
first_value(my_model.name) OVER (PARTITION BY name ORDER BY time DESC) AS name, ...
from my_model