使用SQLAlchemy查询填充丢失的月度数据
使用SQLAlchemy查询填充丢失的月度数据
提问于 2021-03-14 12:24:21
我有一个数据库表，它列出了玩游戏的时间。现在我正在查询这张表，以收集数据以显示在图表中，以便很好地了解过去一年每月玩游戏的数量。
接下来的一切都很完美，直到新冠肺炎锁定，我们才开始看到几个月的零场比赛。*-/
data_db = list(
games.group_by("year")
.group_by("month")
.order_by(desc("year"))
.order_by(desc("month"))
.limit(12)
.values(
func.extract("year", Game.created).label("year"),
func.extract("month", Game.created).label("month"),
func.count().label("games"),
)
)哪里
games = Game.query.filter(
Game.started.isnot(None), Game.no_players.isnot(-1), Game.room == room
)和游戏SQLAlchemy模型。
这将导致表单[(2020, 2, 20), (2020, 3, 65), (2020, 5, 3), ...]的数据，在本例中没有2020年4月的数据。由此产生的条形图将显示所有非零月份的数据，当然，没有真实地显示现实。
我开始研究如何通过加入动态生成的日历表将SQLAlchemy查询扩展到包含零月，但并没有取得很大进展。顺便说一下，底层数据库是SQLite。
我现在用Python“解决”了以下情况：
data_raw = []
for date in rrule.rrule(
rrule.MONTHLY,
dtstart=datetime.now() - relativedelta.relativedelta(months=11),
until=datetime.now(),
):
for dbdata in data_db:
if dbdata[0:2] == (date.year, date.month):
data_raw.append(dbdata)
break
else:
data_raw.append((date.year, date.month, 0))这当然管用，但让我嘴里带着一点酸味。
由于数据集太小，这更像是一个镀金问题，而不是真正的性能问题，但我仍然想看看是否有一个SQLAlchemy解决方案。
(我想图形库(在本例中是Chart.js)也可能填补了空白，但我没有对此进行研究。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-03-15 15:24:55
您可以创建一个包含所有年份/月对(包括缺失的对)的临时表，然后将该表与聚合查询(作为.subquery())放在一起：
import datetime
from sqlalchemy import (
create_engine,
Table,
MetaData,
Column,
Integer,
DateTime,
desc,
func,
and_,
)
from sqlalchemy.orm import declarative_base, Session
engine = create_engine(
"sqlite:///:memory:",
future=True,
echo=True,
)
Base = declarative_base()
class Game(Base):
__tablename__ = "game"
id = Column(Integer, primary_key=True)
created = Column(DateTime)
room = Column(Integer)
no_players = Column(Integer)
started = Column(DateTime)
Base.metadata.create_all(engine)
# create test data to query
with Session(engine, future=True) as session:
session.add_all(
[
Game(
created=datetime.datetime.now(),
room=1,
no_players=2,
started=datetime.datetime.now(),
),
Game(
created=datetime.datetime(2021, 3, 4, 3, 2, 1),
room=1,
no_players=2,
started=datetime.datetime(2021, 3, 4, 5, 6, 7),
),
Game(
created=datetime.datetime(2021, 1, 1, 1, 1, 1, 1),
room=1,
no_players=3,
started=datetime.datetime(2021, 1, 1, 1, 1, 2, 3),
),
]
),
session.commit()
# define temporary table structure and data to insert
tmp_tbl_months = Table(
"tmp_tbl_months",
MetaData(),
Column("year", Integer, primary_key=True, autoincrement=False),
Column("month", Integer, primary_key=True, autoincrement=False),
prefixes=["TEMPORARY"],
)
current_date = datetime.date.today()
loop_year = current_date.year
loop_month = current_date.month
tmp_data = []
num_months_to_report = 4
for i in range(num_months_to_report):
tmp_data.append({"year": loop_year, "month": loop_month})
if loop_month == 1:
loop_month = 12
loop_year -= 1
else:
loop_month -= 1
print(tmp_data)
# [
# {'year': 2021, 'month': 3},
# {'year': 2021, 'month': 2},
# {'year': 2021, 'month': 1},
# {'year': 2020, 'month': 12}
# ]
with engine.begin() as conn:
tmp_tbl_months.create(conn)
conn.execute(tmp_tbl_months.insert(), tmp_data)
room = 1 # for testing
games = session.query(Game).filter(
Game.started.isnot(None), Game.no_players.isnot(-1), Game.room == room
)
aggregation = (
games.group_by("year")
.group_by("month")
.order_by(desc("year"))
.order_by(desc("month"))
.limit(12)
.with_entities(
func.extract("year", Game.created).label("year"),
func.extract("month", Game.created).label("month"),
func.count().label("games"),
)
.subquery()
)
# temp_tbl_months LEFT JOIN aggregation (subquery)
data_db = list(
session.query()
.select_from(tmp_tbl_months)
.outerjoin(
aggregation,
and_(
aggregation.c.year == tmp_tbl_months.c.year,
aggregation.c.month == tmp_tbl_months.c.month,
),
)
.with_entities(
tmp_tbl_months.c.year,
tmp_tbl_months.c.month,
func.coalesce(aggregation.c.games, 0),
)
)
print(data_db)
# [(2021, 3, 2), (2021, 2, 0), (2021, 1, 1), (2020, 12, 0)]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66624502
复制相关文章
相似问题
腾讯云开发者
