def getScore(username=None):
counts = db.session.query(User.name, func.sum(DailyCompletion.score).label("score")).join(
User.points).group_by(User.name).order_by(func.sum(DailyCompletion.score).desc())
if username:
counts = counts.filter(User.slackHandle == username)
if not counts.count():
return None
elif username:
return counts.first()
else:
return counts.all()
python类sum()的实例源码
def _performance(bot: Bot, update: Update) -> None:
"""
Handler for /performance.
Shows a performance statistic from finished trades
:param bot: telegram bot
:param update: message update
:return: None
"""
if get_state() != State.RUNNING:
send_msg('`trader is not running`', bot=bot)
return
pair_rates = Trade.session.query(Trade.pair, func.sum(Trade.close_profit).label('profit_sum')) \
.filter(Trade.is_open.is_(False)) \
.group_by(Trade.pair) \
.order_by(text('profit_sum DESC')) \
.all()
stats = '\n'.join('{index}.\t<code>{pair}\t{profit:.2f}%</code>'.format(
index=i + 1,
pair=pair,
profit=round(rate * 100, 2)
) for i, (pair, rate) in enumerate(pair_rates))
message = '<b>Performance:</b>\n{}'.format(stats)
logger.debug(message)
send_msg(message, parse_mode=ParseMode.HTML)
def get_sum_of_rates_in_period(self, start_date, end_date, currency):
"""
SELECT provider_id, count(*), SUM(rate) FROM "USD_exchange_rates" WHERE date >= '%Y-%m-%d' AND date <= '%Y-%m-%d' GROUP BY provider_id
"""
return self.db_session\
.query(ExchangeRate.provider_id, func.count(), func.sum(ExchangeRate.rate))\
.filter(
and_(ExchangeRate.date >= start_date,
ExchangeRate.date <= end_date,
ExchangeRate.currency == currency,
ExchangeRate.rate.isnot(None))
)\
.group_by(ExchangeRate.provider_id)\
.order_by(ExchangeRate.provider_id)\
.all()
def total(self, cgccpf):
res = self.sql_connector.session.query(
func.sum(CaptacaoModel.CaptacaoReal).label('total_doado')
).join(InteressadoModel, CaptacaoModel.CgcCpfMecena==InteressadoModel.CgcCpf)
res = res.filter(InteressadoModel.CgcCpf.like('%' + cgccpf + '%') )
def single_stat_request(player, code, stat):
"""Actually retrieves the stat and returns the stat info in an embed"""
session = Session()
message = ""
if code == 1:
(table, col, message) = stat_dict[stat]
columns = [col]
res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name == player).first()
#Returns a tuple containing the stat, but only the first element is defined for some reason.
num = truncate_decimals(res[0])
name = res[1]
elif code == 2 or code == 3:
(table, col, message) = medal_dict[stat]
columns = [col]
res = session.query(func.sum(*(getattr(table, column) for column in columns))).join(Account).filter(Account.display_name == player).group_by(AccountMedals.id).first()
num = float(res[0])
name = player
if message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score":
message = f"Total {message} Medals"
if code == 3:
denominator = session.query(PvPAggregate.activitiesEntered).join(Account).filter(Account.display_name == player).first()
act = denominator[0]
num = num/act
if message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score":
message = f"{message} Medals per Game"
elif code == 4:
(table, col, message) = character_dict[stat]
columns = [col]
res = session.query(func.max(*(getattr(table, column) for column in columns)), Account.display_name).join(Account).filter(Account.display_name == player).first()
#Returns a tuple containing the stat, but only the first element is defined for some reason.
num = truncate_decimals(res[0])
name = res[1]
#em = discord.Embed(title = f"{author}{message}{result}", colour=0xADD8E6)
em = f"```{message} for {name}: {num}```"
return em
def multi_stat_request(players, code, stat):
session = Session()
data = []
if code == 1:
(table, col, message) = stat_dict[stat]
columns = [col]
res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all()
data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None]
elif code == 2 or code == 3:
(table, col, message) = medal_dict[stat]
columns = [col]
res = session.query(func.sum(*(getattr(table, column) for column in columns)), Account.display_name).join(Account).filter(Account.display_name.in_(players)).group_by(AccountMedals.id).order_by(Account.display_name).all()
data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None]
if code == 3:
num_activities = session.query(PvPAggregate.activitiesEntered).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all()
data = [(res[i][1], truncate_decimals(float(res[i][0])/num_activities[i][0])) for i in range(len(res)) if res[i][0] is not None]
elif code == 4:
(table, col, message) = pveStatDict[stat]
columns = [col]
res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all()
data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None]
data = sorted(data, key=lambda x: x[1], reverse=True)
if (code == 2 or code == 3) and message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score":
message = f"Total {message} Medals"
em = discord.Embed(title = f"{message}", colour=0xADD8E6)
if len(data) > 10:
data = data[:9]
for (name, num) in data:
em.add_field(name=name, value=num)
return em
def _obs_get_total_size():
from sqlalchemy import func
from .file import File
from .observation import Observation
return db.session.query(func.sum(File.size)).filter(File.obsid == Observation.obsid).as_scalar()
def total_size(self):
"The total size (in bytes) of all Files associated with this session."
from sqlalchemy import func
from .file import File
my_obsids = db.session.query(Observation.obsid).filter(Observation.session_id == self.id)
return (db.session.query(func.sum(File.size))
.filter(File.obsid.in_(my_obsids))
.scalar())
def total_size(self):
"The total size (in bytes) of all Files associated with this observation."
from sqlalchemy import func
from .file import File
return (db.session.query(func.sum(File.size))
.filter(File.obsid == self.obsid)
.scalar())
def sum_equally_voted_reputation(self, contribution, value):
"""return the sum of reputation of evaluators of evaluation.contribution that
have evaluated the same value"""
equally_voted_rep = DBSession.query(func.sum(User.reputation)).\
join(Evaluation).\
filter(Evaluation.contribution_id == contribution.id).\
filter(Evaluation.value == value).\
one()[0] or 0
return equally_voted_rep
def total_reputation(self):
return DBSession.query(func.sum(User.reputation)).filter(User.contract == self).one()[0]
def get_sum_count_being_deleted(rse_id, session=None):
"""
:param rse_id: The id of the RSE.
:param session: The database session in use.
:returns: A dictionary with total and bytes.
"""
none_value = None
total, bytes = session.query(func.count(models.RSEFileAssociation.tombstone), func.sum(models.RSEFileAssociation.bytes)).filter_by(rse_id=rse_id).\
filter(models.RSEFileAssociation.tombstone != none_value).\
filter(models.RSEFileAssociation.state == ReplicaState.BEING_DELETED).\
one()
return {'bytes': bytes or 0, 'total': total or 0}
def filterDatasetQueryObjectWithRules(queryObject,rules):
for rule in rules:
field = rule['field']
ruletype = rule['type']
value = rule['value']
queryObject = filterQueryByRule(Dataset,queryObject,field,ruletype,value)
return queryObject
# Create and run the query for
# Group by a column and return the sum for each group
def aggregate(cls, value):
return {
'average_age': func.avg(cls.age),
'average_wage': func.avg(cls.wage),
'wage': func.sum(cls.wage),
'jobs': func.count(cls.employee),
'average_establishment_size': func.count(cls.employee) / func.count(distinct(cls.establishment))
}[value]
def aggregate(cls, value):
return {
'enrolleds': func.count(),
'entrants': func.sum(cls.entrant),
'graduates': func.sum(cls.graduate),
'average_age': func.avg(cls.age)
}[value]
def aggregate(cls, value):
return {
'weight': func.sum(cls.weight),
'value': func.sum(cls.value),
}[value]
def aggregate(cls, value):
return {
'professionals': func.count(),
'other_hours_worked': func.sum(cls.other_hours_worked),
'hospital_hour': func.sum(cls.hospital_hour),
'ambulatory_hour': func.sum(cls.ambulatory_hour),
}[value]
def aggregate(cls, value):
return {
'beds': func.sum(cls.number_existing_bed),
'number_existing_bed': func.sum(cls.number_existing_bed),
'number_existing_contract': func.sum(cls.number_existing_contract),
'number_sus_bed': func.sum(cls.number_sus_bed),
'number_non_sus_bed': func.sum(cls.number_non_sus_bed),
}[value]
def aggregate(cls, value):
return {
'value': func.sum(cls.value),
'kg': func.sum(cls.kg),
}[value]
def query(table):
col1 = literal_column("TIMESTAMP_TRUNC(timestamp, DAY)").label("timestamp_label")
col2 = func.sum(table.c.integer)
query = (
select([
col1,
col2,
])
.where(col1 < '2017-01-01 00:00:00')
.group_by(col1)
.order_by(col2)
)
return query
