sql语句按月统计与sqlchemy按月统计

一、SQL语句实现按年月统计数据

查询内容：在电梯数据表中查询，按月、按电梯分类统计新增的电梯数量，关联项目表，条件为项目表中的城市id=2282和省id=2281

select CONCAT(YEAR(a.createAt),'-',MONTH(a.createAt)),`type`,count(*) 
from ev_info a,projectinfo b 
where a.projectId=b.uuid
and b.city=2282
and b.province=2281
and a.createAt>'2018-03-01'
group by 1,2

结果如下：

二、sqlchemy中按月统计

from sqlalchemy import func, extract
from sqlalchemy.sql import and_

datas = db_session.query(
        func.concat(extract('year', EvInfo.createAt), "-", extract('month', EvInfo.createAt)).label('month'),
        EvInfo.type,
        func.count('*').label('count')) \
        .filter(EvInfo.createAt > year1ago) \
        .join(ProjectInfo,
              and_(ProjectInfo.uuid == EvInfo.projectId, and_(ProjectInfo.city == 2282, ProjectInfo.province == 2281))) \
        .group_by('month') \
        .group_by(EvInfo.type) \
        .all()

返回列表数据与上图一致