我有下面的表格,想要计算sum
true和group_by
按日期
| Date | Value1 | Value2 |
|:-----------|------------:|:------------:|
| 2019-12-05 | 5.5 | true|
| 2019-12-05 | 4.5 | true|
| 2019-12-05 | 6.5 | false|
| 2019-12-05 | 8.5 | false|
| 2019-12-05 | 2.5 | true|
| 2019-12-06 | 3.5 | true|
| 2019-12-06 | 5.5 | true|
| 2019-12-06 | 56.5 | true|
| 2019-12-06 | 8.5 | true|
| 2019-12-06 | 99.5 | false|
| ... | ... | ... |
我通过类方法对db.Model的查询:
return db.session.query(cls.Date, func.count(cast(cls.Value2, sqlalchemy.Integer)).filter(cls.Value2== True).label("Count True Value2")) \
.group_by(cls.Date)\
.order_by("Date")
结果应该是
| Date | Value2 |
|:-----------|------------:|
| 2019-12-05 | 3 |
| 2019-12-06 | 4 |
编辑也不起作用
return db.session.query(cls.Date, func.sum(case([(cls.Value2== True, 1)], else_=0).label('Value2'))) \
.filter(cls.Date== current_date.strftime("%Y-%m-%d")) \
.group_by(cls.Date)\
.order_by("Date")
Value2
到组条件。这是一个例子:
from datetime import date
from sqlalchemy import Integer
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Date, Boolean
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine('...creds...', echo=True)
class TestModel(Base):
__tablename__ = 'test_model'
id = Column(Integer, primary_key=True)
date = Column(Date)
value2 = Column(Boolean)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# a few test records
for date_ in (date(2020, 1, 1), date(2020, 1, 2)):
session.add(TestModel(date=date_, value2=True))
session.add(TestModel(date=date_, value2=True))
session.add(TestModel(date=date_, value2=True))
session.add(TestModel(date=date_, value2=False))
session.commit()
query = (
session.query(
TestModel.date,
TestModel.value2,
func.count('*').label('counter')
)
# uncomment if you need only True values
# .filter(TestModel.value2.is_(True))
.group_by(TestModel.date, TestModel.value2)
.order_by(TestModel.date)
)
for rec in query:
print('date {date}, value2 {value2}, counter {counter}'.format(
date=rec.date,
value2=rec.value2,
counter=rec.counter,
))
# date 2020-01-01, value2 True, counter 3
# date 2020-01-01, value2 False, counter 1
# date 2020-01-02, value2 False, counter 1
# date 2020-01-02, value2 True, counter 3
希望这可以帮助。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句