我正在尝试将相当短的 SQL 转换为 sqlAlchemy ORM 查询。 SQL 使用 Postgres 的generate_series
制作一组日期,我的目标是制作一组按其中一列分类的时间序列数组。
表格(简化)非常简单:
counts:
-----------------
count (Integer)
day (Date)
placeID (foreign key related to places)
"counts_pkey" PRIMARY KEY (day, placeID)
places:
-----------------
id
name (varchar)
我想要的输出是每个位置的计数时间序列,包括一天未报告计数时的空值。例如,这对应于四天的系列:
array_agg | name
-----------------+-------------------
{NULL,0,7,NULL} | A Place
{NULL,1,NULL,2} | Some other place
{5,NULL,3,NULL} | Yet another
我可以通过采取相当容易地做到这一点CROSS JOIN
在日期范围和地点并将其与计数相结合:
SELECT array_agg(counts.count), places.name
FROM generate_series('2018-11-01', '2018-11-04', interval '1 days') as day
CROSS JOIN places
LEFT OUTER JOIN counts on counts.day = day.day AND counts.PlaceID = places.id
GROUP BY places.name;
我似乎不明白如何让 SQLAlchemy 做到这一点。经过大量挖掘,我发现了一个旧的谷歌群组线程这几乎可以导致这个:
date_list = select([column('generate_series')])\
.select_from(func.generate_series(backthen, today, '1 day'))\
.alias('date_list')
time_series = db.session.query(Place.name, func.array_agg(Count.count))\
.select_from(date_list)\
.outerjoin(Count, (Count.day == date_list.c.generate_series) & (Count.placeID == Place.id ))\
.group_by(Place.name)
这会为时间序列创建一个子选择,但会产生数据库错误:
表“places”有一个条目,但无法从查询的这一部分引用它。
所以我的问题是:你将如何在 sqlalchemy 中做到这一点。另外,我愿意承认这很困难,因为我使用 SQL 的方法很愚蠢。