使用DISTINCT ON结合ORDER BY查询返回错误结果求助

看起来你是想获取指定日期范围内，每个设备每天的最新一条追踪记录，但当前查询的逻辑有几个关键点没处理对，导致只有较早日期的结果正常。我帮你拆解问题并给出可行的解决方案：

核心问题点 #

1. 排序逻辑不完整：你只按device_id和日期降序排序，但没有针对time字段做降序——这意味着同一设备同一天有多条记录时，无法保证取到的是当天最新的那条，结果可能随机返回一条。
2. 多字段distinct的匹配问题：不同数据库对多字段distinct的处理逻辑不同（比如PostgreSQL的DISTINCT ON要求排序的前缀必须和distinct的字段完全匹配），原查询的排序顺序没有和distinct的字段形成正确对应，导致结果不符合预期。

方案一：窗口函数实现（推荐，跨数据库兼容） #

使用row_number()窗口函数可以清晰实现“分组取最新”的逻辑，适配MySQL、PostgreSQL、SQLite等多数数据库：

from sqlalchemy import func, over
from datetime import datetime, timedelta

# 处理日期边界，避免漏掉date_2当天的晚时段记录
start_date = datetime.strptime(date_1, '%d-%m-%Y')
end_date = datetime.strptime(date_2, '%d-%m-%Y') + timedelta(days=1) - timedelta(seconds=1)

# 定义窗口：按设备ID和日期分组，按时间降序排序，给每组记录打行号
row_num_window = over(
    func.row_number(),
    partition_by=[Tracking.device_id, func.date(Tracking.time)],
    order_by=Tracking.time.desc()
).label("row_num")

# 子查询获取所有符合日期范围的记录，并带上行号
subquery = Tracking.query. \
    filter(Tracking.time >= start_date). \
    filter(Tracking.time <= end_date). \
    add_columns(row_num_window). \
    subquery()

# 最终查询：筛选每个分组中行号为1的记录（即当天最新）
results = db.session.query(Tracking). \
    select_from(subquery). \
    where(subquery.c.row_num == 1). \
    order_by(subquery.c.device_id, subquery.c.time.desc()).all()

方案二：针对PostgreSQL优化（利用DISTINCT ON特性） #

如果你的数据库是PostgreSQL，可以直接调整排序顺序，让distinct的字段和排序前缀匹配，同时加上time降序确保取到最新记录：

from datetime import datetime, timedelta

start_date = datetime.strptime(date_1, '%d-%m-%Y')
end_date = datetime.strptime(date_2, '%d-%m-%Y') + timedelta(days=1) - timedelta(seconds=1)

results = Tracking.query. \
    filter(Tracking.time >= start_date). \
    filter(Tracking.time <= end_date). \
    # 排序顺序必须先和distinct的字段一致，再按time降序
    order_by(Tracking.device_id, func.date(Tracking.time), Tracking.time.desc()). \
    distinct(Tracking.device_id, func.date(Tracking.time)).all()

额外提示：日期边界简化处理 #

原查询中用Tracking.time <= datetime.strptime(date_2, '%d-%m-%Y')会只匹配到date_2当天0点的记录，漏掉当天其他时间的记录。你也可以直接用日期函数简化边界处理：

# 直接匹配日期部分，无需处理时间戳边界
results = Tracking.query. \
    filter(func.date(Tracking.time) >= datetime.strptime(date_1, '%d-%m-%Y').date()). \
    filter(func.date(Tracking.time) <= datetime.strptime(date_2, '%d-%m-%Y').date()). \
    # 后续逻辑...

内容的提问来源于stack exchange，提问作者Faisal