Python func.sum方法代码示例

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def test_session_query(session, table, session_using_test_dataset, table_using_test_dataset):
    for session, table in [(session, table), (session_using_test_dataset, table_using_test_dataset)]:
        col_concat = func.concat(table.c.string).label('concat')
        result = (
            session
            .query(
                table.c.string,
                col_concat,
                func.avg(table.c.integer),
                func.sum(case([(table.c.boolean == True, 1)], else_=0))
            )
            .group_by(table.c.string, col_concat)
            .having(func.avg(table.c.integer) > 10)

        ).all()
        assert len(result) > 0 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def num_stale_accounts(sess=None):
        """
        Return the number of accounts with stale data.

        @TODO This is a hack because I just cannot figure out how to do this
        natively in SQLAlchemy.

        :return: count of accounts with stale data
        :rtype: int
        """
        if sess is None:
            sess = db_session
        return sum(
            1 if a.is_stale else 0 for a in sess.query(
                Account).filter(Account.is_active.__eq__(True)).all()
        ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def budget_account_sum(sess=None):
        """
        Return the sum of current balances for all is_budget_source accounts.

        :return: Combined balance of all budget source accounts
        :rtype: float
        """
        if sess is None:
            sess = db_session
        sum = Decimal('0.0')
        for acct in sess.query(Account).filter(
                Account.is_budget_source.__eq__(True),
                Account.is_active.__eq__(True)
        ):
            if acct.balance is not None:
                sum += acct.balance.ledger
        return sum 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def budget_account_unreconciled(sess=None):
        """
        Return the sum of unreconciled txns for all is_budget_source accounts.

        :return: Combined unreconciled amount of all budget source accounts
        :rtype: float
        """
        if sess is None:
            sess = db_session
        sum = Decimal('0.0')
        for acct in sess.query(Account).filter(
                Account.is_budget_source.__eq__(True),
                Account.is_active.__eq__(True)
        ):
            sum += acct.unreconciled_sum
        return sum 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def standing_budgets_sum(sess=None):
        """
        Return the sum of current balances of all standing budgets.

        :return: sum of current balances of all standing budgets
        :rtype: float
        """
        if sess is None:
            sess = db_session
        res = sess.query(func.sum(Budget.current_balance)).filter(
            Budget.is_periodic.__eq__(False),
            Budget.is_active.__eq__(True)
        ).all()[0][0]
        if res is None:
            return 0
        return res 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def pp_sum(sess=None):
        """
        Return the overall allocated sum for the current payperiod minus the
        sum of all reconciled Transactions for the pay period.

        :return: overall allocated sum for the current pay period minus the sum
          of all reconciled Transactions for the pay period.
        :rtype: float
        """
        if sess is None:
            sess = db_session
        pp = BiweeklyPayPeriod.period_for_date(dtnow(), sess)
        allocated = pp.overall_sums['allocated']
        spent = pp.overall_sums['spent']
        logger.debug('PayPeriod=%s; allocated=%s; spent=%s',
                     pp, allocated, spent)
        return allocated - spent 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def get_points_by_outcome(self, db_session):
        """ Returns how many points are bet on win and how many points
        are bet on lose """

        rows = (
            db_session.query(HSBetBet.outcome, func.sum(HSBetBet.points))
            .filter_by(game_id=self.id)
            .group_by(HSBetBet.outcome)
            .all()
        )

        points = {key: 0 for key in HSGameOutcome}
        for outcome, num_points in rows:
            points[outcome] = num_points

        return points 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        y_column = getattr(self.model, value['y_column'])

        if value['y_func'] == 'count':
            y_func = func.count(y_column)
        elif value['y_func'] == 'sum':
            y_func = func.sum(y_column)
        elif value['y_func'] == 'min':
            y_func = func.min(y_column)
        elif value['y_func'] == 'max':
            y_func = func.max(y_column)
        elif value['y_func'] == 'avg':
            y_func = func.avg(y_column)
        else:
            return qs.filter(sql.false())

        qs = qs.session.query(y_func).one()

        return qs 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def test_aggregate_having(self):
        st = self.sa_star1.alias('t0')

        cases = self._case_aggregate_having()

        metric = F.sum(st.c.f)
        k1 = st.c.foo_id
        expected = [
            sa.select([k1, metric.label('total')])
            .group_by(k1)
            .having(metric > L(10)),
            sa.select([k1, metric.label('total')])
            .group_by(k1)
            .having(F.count('*') > L(100)),
        ]

        for case, ex_sqla in zip(cases, expected):
            self._compare_sqla(case, ex_sqla) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def test_cte_factor_distinct_but_equal(self):
        expr = self._case_cte_factor_distinct_but_equal()

        alltypes = self._get_sqla('alltypes')

        t2 = alltypes.alias('t2')
        t0 = (
            sa.select([t2.c.g, F.sum(t2.c.f).label('metric')])
            .group_by(t2.c.g)
            .cte('t0')
        )

        t1 = t0.alias('t1')
        table_set = t0.join(t1, t0.c.g == t1.c.g)
        stmt = sa.select([t0]).select_from(table_set)

        self._compare_sqla(expr, stmt) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def test_subquery_aliased(self):
        expr = self._case_subquery_aliased()

        s1 = self._get_sqla('star1').alias('t2')
        s2 = self._get_sqla('star2').alias('t1')

        agged = (
            sa.select([s1.c.foo_id, F.sum(s1.c.f).label('total')])
            .group_by(s1.c.foo_id)
            .alias('t0')
        )

        joined = agged.join(s2, agged.c.foo_id == s2.c.foo_id)
        expected = sa.select([agged, s2.c.value1]).select_from(joined)

        self._compare_sqla(expr, expected) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def running_slots(self, session: Session):
        """
        Get the number of slots used by running tasks at the moment.

        :param session: SQLAlchemy ORM Session
        :return: the used number of slots
        """
        from airflow.models.taskinstance import TaskInstance  # Avoid circular import

        return (
            session
            .query(func.sum(TaskInstance.pool_slots))
            .filter(TaskInstance.pool == self.pool)
            .filter(TaskInstance.state == State.RUNNING)
            .scalar()
        ) or 0 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def queued_slots(self, session: Session):
        """
        Get the number of slots used by queued tasks at the moment.

        :param session: SQLAlchemy ORM Session
        :return: the used number of slots
        """
        from airflow.models.taskinstance import TaskInstance  # Avoid circular import

        return (
            session
            .query(func.sum(TaskInstance.pool_slots))
            .filter(TaskInstance.pool == self.pool)
            .filter(TaskInstance.state == State.QUEUED)
            .scalar()
        ) or 0 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def setStartScore(self):
		downloads = db.session.query(func.sum(PackageRelease.downloads)). \
				filter(PackageRelease.package_id == self.id).scalar() or 0

		forum_score = 0
		forum_bonus = 0
		topic = self.forums and ForumTopic.query.get(self.forums)
		if topic:
			months = (datetime.datetime.now() - topic.created_at).days / 30
			years  = months / 12
			forum_score = topic.views / max(years, 0.0416) + 80*min(max(months, 0.5), 6)
			forum_bonus = topic.views + topic.posts

		self.score = max(downloads, forum_score * 0.6) + forum_bonus

		if self.getMainScreenshotURL() is None:
			self.score *= 0.8 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import sum [as 别名]
def ts_locs_array(
    config: ColumnElement, text: ColumnElement, tsquery: ColumnElement,
) -> ColumnElement:
    options = f"HighlightAll = TRUE, StartSel = {TS_START}, StopSel = {TS_STOP}"
    delimited = func.ts_headline(config, text, tsquery, options)
    parts = func.unnest(func.string_to_array(delimited, TS_START)).alias()
    part = column(parts.name)
    part_len = func.length(part) - len(TS_STOP)
    match_pos = func.sum(part_len).over(rows=(None, -1)) + len(TS_STOP)
    match_len = func.strpos(part, TS_STOP) - 1
    return func.array(
        select([postgresql.array([match_pos, match_len])])
        .select_from(parts)
        .offset(1)
        .as_scalar(),
    )

# When you add a new operator to this, also update zerver/lib/narrow.py