在REST中使用SQLalchemy，如何将资源聚合到一天中的某个小时？

Question

我有一个MySql db表，如下所示：

time_slot            | sales
2022-08-026T01:00:00 | 100
2022-08-026T01:06:40 | 103
...

我正在通过api向客户端提供数据。FE工程师希望在查询期间内每天按小时汇总数据(atm，这是一周)。因此，他给出了from和to，并希望以嵌套数组的形式计算每天每小时内的销售额之和。因为这是一个星期，它是一个7元素数组，其中每个元素都是一个数组，其中包含了我们拥有数据的所有小时时隙。

[
 [
    "07:00": 567,
    "08:00": 657,
    ....
 ],
 [], [],  ...
]

api是在python中构建的。对于数据有一个ORM (sqlalchemy)，看起来如下：

class HourlyData(Base):
    hour: Column(Datetime)
    sales: Column(Float) 

我可以查询每小时的数据，然后在python内存中将其聚合到列表中。但是为了节省计算时间(和概念复杂性)，我想通过orm查询运行聚合。

实现这一目标的sqlalchemy语法是什么？

Answer 1

下面是您的起点，解决方案是使用现有工具的SQL和Python的混合，并且它应该与任何RDBMS一起工作。

1. 假设模型定义，并导入

from itertools import groupby
import json

class TimelyData(Base):
    __tablename__ = "timely_data"
    id = Column(Integer, primary_key=True)
    time_slot = Column(DateTime)
    sales = Column(Float)

1. 我们从数据库中获得的数据足够聚合，可以正确地分组

# below works for Posgresql (tested), and should work for MySQL as well
# see: https://mode.com/blog/date-trunc-sql-timestamp-function-count-on
col_hour = func.date_trunc("hour", TimelyData.time_slot)

q = (
    session.query(
        col_hour.label("hour"),  
        func.sum(TD.sales).label("total_sales"),
    )
    .group_by(col_hour)
    .order_by(col_hour)  # this is important for `groupby` function later on
)

再次使用python groupby按日期分组结果

groups = groupby(q.all(), key=lambda row: row.hour.date())

# truncate and format the final list as required
data = [
    [(f"{row.hour:%H}:00", int(row.total_sales)) for row in rows]
    for _, rows in groups
]

[[["01:00", 201], ["02:00", 102]], [["01:00", 103]], [["08:00", 104]]]示例结果：

我对MySQL并不熟悉，但由于广泛的JSON支持，Postgresql可以在DB级别实现所有这些功能。但是，我认为该实现的可读性不会得到改善，假设我们最多从168行获得的数据为7天x24小时，速度也不会提高。