如何在SQLAlchemy中实现资产类别区间状态统计的简洁查询

资产表结构 #

目标统计结果 #

期望生成如下格式的统计字典：

{
 auto: {not_ok: 1, to_watch: 0, ok: 2},
 bike: {not_ok: 0, to_watch: 1, ok: 1},
 plane:{not_ok: 0, to_watch: 0, ok: 1}
}

区间划分规则 #

统计时，每个资产类别的区间通过np.linspace(0, max_status_by_category, 4).tolist()生成，例如auto类别的区间为[0.0,20.0,40.0,60.0]。区间与状态类别的对应逻辑为：

interval[0]<= not_ok <= interval[1]
interval[1]< to_watch <= interval[2]
interval[2]<   ok <= interval[3]

当前实现方式 #

当前采用分两步的实现：

1. 先查询每个类别的最大状态值：

q_c = session.query(Assets.asset_category, func.count(Assets.asset_category),
                    func.max(Assets.asset_state)
                    ).group_by(Assets.asset_category).all()

2. 循环生成每个类别的区间，再执行统计查询：

q_status = session.query(Assets.asset_category, func.count(Assets.asset_category),
          func.count(func.nullif(and_(Assets.asset_state >= interval[0], Assets.asset_state <= interval[1]), False)),
          func.count(func.nullif(and_(Assets.asset_state > interval[1], Assets.asset_state <= interval[2]), False)),
          func.count(func.nullif(and_(Assets.asset_state > interval[2], Assets.asset_state <= interval[3]), False)),
           ) \
 .group_by(Assets.asset_category).all()

优化疑问 #

• 是否可以在单次查询中动态生成区间并完成统计？
• 若已提前生成如下区间字典，能否在SQLAlchemy查询中直接引用该字典中的区间值进行统计？
  提前生成的区间字典：

interval = {
auto: [0.0, 20.0, 40.0, 60.0],
bike: [0.0, 10.0, 20.0, 30.0],
plane: [0.0, 5.333333333333333, 10.666666666666666, 16.0]
}

希望得到更简洁且性能更优的实现思路。

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