Python sqlalchemy.func.avg() Examples

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 

def test_aggregate_3(self):
        foo, Foo = self.tables.foo, self.classes.Foo

        query = create_session().query(Foo)

        avg_f = (
            query.filter(foo.c.bar < 30)
            .with_entities(sa.func.avg(foo.c.bar))
            .scalar()
        )
        eq_(float(round(avg_f, 1)), 14.5)

        avg_o = (
            query.filter(foo.c.bar < 30)
            .with_entities(sa.func.avg(foo.c.bar))
            .scalar()
        )
        eq_(float(round(avg_o, 1)), 14.5) 

def top_rated_packages(cls, limit=10):
        # NB Not using sqlalchemy as sqla 0.4 doesn't work using both group_by
        # and apply_avg
        package = table('package')
        rating = table('rating')
        sql = select([package.c.id, func.avg(rating.c.rating), func.count(rating.c.rating)], from_obj=[package.join(rating)]).\
              where(and_(package.c.private==False, package.c.state=='active')). \
              group_by(package.c.id).\
              order_by(func.avg(rating.c.rating).desc(), func.count(rating.c.rating).desc()).\
              limit(limit)
        res_ids = model.Session.execute(sql).fetchall()
        res_pkgs = [(model.Session.query(model.Package).get(unicode(pkg_id)), avg, num) for pkg_id, avg, num in res_ids]
        return res_pkgs 

def test_query_five(self):
        BankAccount = self.BankAccount
        session = Session()

        # 4d. average balance in EUR
        query = session.query(func.avg(BankAccount.balance.as_currency("eur")))
        self.assert_compile(
            query,
            "SELECT avg(:balance_1 * bank_account.balance) AS avg_1 "
            "FROM bank_account",
            checkparams={"balance_1": Decimal("0.724743")},
        ) 

def test_aggregate_2(self):
        foo = self.tables.foo

        query = create_session().query(func.avg(foo.c.bar))
        avg = query.filter(foo.c.bar < 30).one()[0]
        eq_(float(round(avg, 1)), 14.5) 

def data_for_hour(start):
    end = start + datetime.timedelta(hours=1)
    # return the average of all data for an hour, starting from start
    # note the parentheses around the comparisons in the filter; this is
    # required because & binds tighter than comparison!
    return session.query(StaticSample.sensor_id, func.avg(StaticSample.data)) \
        .filter((StaticSample.time >= start) & (StaticSample.time < end)) \
        .group_by(StaticSample.sensor_id).all() 

def data_for_hour(start):
    end = start + datetime.timedelta(hours=1)
    # return the average of all data for an hour, starting from start
    # note the parentheses around the comparisons in the filter; this is
    # required because & binds tighter than comparison!
    return session.query(StaticSample.sensor_id, func.avg(StaticSample.data)) \
        .filter((StaticSample.time >= start) & (StaticSample.time < end)) \
        .group_by(StaticSample.sensor_id).all() 

def avg_openess(persons):
    openness = Person.query.filter(Person.orcid_id.in_(persons)).with_entities(func.avg(Person.openness)).scalar()
    return openness 

def get_endpoint_averages(db_session):
    """ Returns the average duration of all endpoints. If there are no requests for an endpoint,
        the average will be none.
    :param db_session: session for the database
    :return tuple of (endpoint_name, avg_duration)
    """
    result = (
        db_session.query(Endpoint.name, func.avg(Request.duration).label('average'))
        .outerjoin(Request)
        .group_by(Endpoint.name)
        .all()
    )
    return result 

def get_avg_duration(db_session, endpoint_id):
    """ Returns the average duration of all the requests of an endpoint. If there are no requests
        for that endpoint, it returns 0.
    :param db_session: session for the database
    :param endpoint_id: id of the endpoint
    :return average duration
    """
    result = (
        db_session.query(func.avg(Request.duration).label('average'))
        .filter(Request.endpoint_id == endpoint_id)
        .one()
    )
    if result[0]:
        return result[0]
    return 0 

def _calculate_value(self, s, statistic_name, summary, order_asc, start_time, finish_time, interval, measures):

        t = _tables()
        sjx = inspect(STATISTIC_JOURNAL_CLASSES[statistic_name.statistic_journal_type]).local_table
        units = statistic_name.units
        activity_group_id = interval.activity_group.id if interval.activity_group else None

        if summary == S.MAX:
            result = func.max(sjx.c.value)
        elif summary == S.MIN:
            result = func.min(sjx.c.value)
        elif summary == S.SUM:
            result = func.sum(sjx.c.value)
        elif summary == S.CNT:
            result = func.count(sjx.c.value)
            units = None
        elif summary == S.AVG:
            result = func.avg(sjx.c.value)
        elif summary == S.MSR:
            self._calculate_measures(s, statistic_name, order_asc, start_time, finish_time, interval, measures)
            return None, None
        else:
            raise Exception('Bad summary: %s' % summary)

        stmt = select([result]). \
            select_from(sjx).select_from(t.sj).select_from(t.src). \
            where(and_(t.sj.c.id == sjx.c.id,
                       t.sj.c.statistic_name_id == statistic_name.id,
                       t.sj.c.time >= start_time,
                       t.sj.c.time < finish_time,
                       t.sj.c.source_id == t.src.c.id,
                       t.src.c.activity_group_id == activity_group_id))

        return next(s.connection().execute(stmt))[0], units 

def getStatistics(self, tutorials=None, students=None, statistics=None, prefix='lec'):
        if statistics is None:
            statistics = AutoVivification()
        session = Session.object_session(self)
        if not students:
            students = self.lecture.lecture_students_for_tutorials(tutorials).all()
        pointsQuery = self.exercise_points.filter(ExerciseStudent.student_id.in_([s.student_id for s  in students]))\
                                                .filter(ExerciseStudent.points!=None)
        pointsStmt = pointsQuery.subquery()
        exerciseStatistics = session.query(\
                        pointsStmt.c.exercise.label('exercise_id'),
                        func.count(pointsStmt.c.student).label('count'),
                        func.avg(pointsStmt.c.points).label('avg'),
                        func.variance(pointsStmt.c.points).label('variance')
                ).group_by(pointsStmt.c.exercise)
        examPoints = session.query(\
                        pointsStmt.c.student.label('student_id'),
                        func.sum(pointsStmt.c.points).label('points'),
                ).group_by(pointsStmt.c.student).subquery()
        examStatistics = session.query(\
                        func.count(examPoints.c.student_id).label('count'),
                        func.avg(examPoints.c.points).label('avg'),
                        func.variance(examPoints.c.points).label('variance'),
                ).one()
        statistics['exam'] = {
                prefix+'_avg': examStatistics.avg,
                prefix+'_std': math.sqrt(examStatistics.variance) if examStatistics.variance else None,
                prefix+'_count': examStatistics.count,
                'max': self.getMaxpoints()}
        for e in self.exercises:
            statistics[e.id] = {prefix+'_avg': None, prefix+'_std': None, prefix+'_count': 0, 'max': e.maxpoints}
        for e in exerciseStatistics.all():
            statistics[e.exercise_id] = {
                    prefix+'_avg': e.avg,
                    prefix+'_std': math.sqrt(e.variance) if e.variance else None,
                    prefix+'_count': e.count
                    }
        return statistics 

def get_inline_query_performance_statistics(session):
    """Plot statistics regarding performance of inline query requests."""
    creation_date = func.cast(InlineQueryRequest.created_at, Date).label(
        "creation_date"
    )
    # Group the started users by date
    strict_search_subquery = (
        session.query(
            creation_date, func.avg(InlineQueryRequest.duration).label("count")
        )
        .group_by(creation_date)
        .order_by(creation_date)
        .all()
    )
    strict_queries = [("strict", q[0], q[1]) for q in strict_search_subquery]

    # Combine the results in a single dataframe and name the columns
    request_statistics = strict_queries
    dataframe = pandas.DataFrame(
        request_statistics, columns=["type", "date", "duration"]
    )

    months = mdates.MonthLocator()  # every month
    months_fmt = mdates.DateFormatter("%Y-%m")

    # Plot each result set
    fig, ax = plt.subplots(figsize=(30, 15), dpi=120)
    for key, group in dataframe.groupby(["type"]):
        ax = group.plot(ax=ax, kind="bar", x="date", y="duration", label=key)
        ax.xaxis.set_major_locator(months)
        ax.xaxis.set_major_formatter(months_fmt)

    image = image_from_figure(fig)
    image.name = "request_duration_statistics.png"
    return image 

def test_where_correlated_subquery(self):
        expr = self._case_where_correlated_subquery()

        foo = self._to_sqla(self.foo)
        t0 = foo.alias('t0')
        t1 = foo.alias('t1')
        subq = sa.select([F.avg(t1.c.y).label('mean')]).where(
            t0.c.dept_id == t1.c.dept_id
        )
        stmt = sa.select([t0]).where(t0.c.y > subq)
        self._compare_sqla(expr, stmt) 

def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        x_column = getattr(self.model, value['x_column'])
        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())

        if value['x_lookup'] and value['x_lookup'] in ['date']:
            x_lookup = getattr(func, value['x_lookup'])
            x_func = x_lookup(x_column)
        else:
            x_func = x_column

        qs = qs.session.query(x_func.label('group'), y_func.label('y_func')).group_by('group').order_by('group').all()

        return qs 

def getStatisticsBySubjects(self, tutorials=None, students=None, statistics=None, prefix='lec'):
        session = Session.object_session(self)
        if not students:
            students = self.lecture.lecture_students_for_tutorials(tutorials)
        exercise_points = session.query(ExerciseStudent, ExerciseStudent.student)
        pointsQuery = self.exercise_points.filter(ExerciseStudent.student_id.in_([s.student_id for s  in students]))\
                .filter(ExerciseStudent.exercise_id.in_([e.id for e in self.exercises]))\
                .filter(ExerciseStudent.points!=None).subquery()
        pointsStmt = session.query(User.subject, pointsQuery).join(pointsQuery, pointsQuery.c.student==User.id).subquery()
        exerciseStatistics = session.query(\
                        pointsStmt.c.exercise.label('exercise_id'),
                        pointsStmt.c.subject.label('subject'),
                        func.count(pointsStmt.c.student).label('count'),
                        func.avg(pointsStmt.c.points).label('avg'),
                        func.variance(pointsStmt.c.points).label('variance')
                ).group_by(pointsStmt.c.exercise, pointsStmt.c.subject).all()
        examPoints = session.query(\
                        pointsStmt.c.student.label('student_id'),
                        pointsStmt.c.subject.label('subject'),
                        func.sum(pointsStmt.c.points).label('points'),
                ).group_by(pointsStmt.c.student, pointsStmt.c.subject).subquery()
        examStatistics = session.query(\
                        examPoints.c.subject.label('subject'),
                        func.count(examPoints.c.student_id).label('count'),
                        func.avg(examPoints.c.points).label('avg'),
                        func.variance(examPoints.c.points).label('variance'),
                ).group_by(examPoints.c.subject).all()
        if statistics is None:
            statistics = AutoVivification()
        maxpoints = self.getMaxpoints()
        for res in examStatistics:
            statistics[res.subject]['exam'] = {prefix+'_avg': res.avg,
                                               prefix+'_std': math.sqrt(res.variance) if res.variance else None,
                                               prefix+'_count': res.count,
                                               'max': maxpoints}
        for e in exerciseStatistics:
            statistics[e.subject][e.exercise_id] = {
                    prefix+'_avg': e.avg,
                    prefix+'_std': math.sqrt(e.variance) if e.variance else None,
                    prefix+'_count': e.count,
                    'max': session.query(Exercise).get(e.exercise_id).maxpoints
                    }
        return statistics 

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 

def getSummary(self, kwds={}):
        filters = Sqlalchemy.getFilters(kwds)
        session = sessionmaker(self.db)()
        count = func.count(Measurements.id).label('count')
        min_elapsed = func.min(Measurements.elapsed).label('minElapsed')
        max_elapsed = func.max(Measurements.elapsed).label('maxElapsed')
        avg_elapsed = func.avg(Measurements.elapsed).label('avgElapsed')
        query = session.query(
            Measurements.method,
            Measurements.name,
            count,
            min_elapsed,
            max_elapsed,
            avg_elapsed
        )

        if filters["startedAt"]:
            query = query.filter(Measurements.startedAt >= filters["startedAt"])
        if filters["endedAt"]:
            query = query.filter(Measurements.endedAt <= filters["endedAt"])
        if filters["elapsed"]:
            query = query.filter(Measurements.elapsed >= filters["elapsed"])

        query = query.group_by(Measurements.method, Measurements.name)
        if filters["sort"][1] == 'desc':
            if filters["sort"][0] == 'count':
                query = query.order_by(count.desc())
            elif filters["sort"][0] == 'minElapsed':
                query = query.order_by(min_elapsed.desc())
            elif filters["sort"][0] == 'maxElapsed':
                query = query.order_by(max_elapsed.desc())
            elif filters["sort"][0] == 'avgElapsed':
                query = query.order_by(avg_elapsed.desc())
            else:
                query = query.order_by(
                    getattr(Measurements, filters["sort"][0]).desc())
        else:
            if filters["sort"][0] == 'count':
                query = query.order_by(count.asc())
            elif filters["sort"][0] == 'minElapsed':
                query = query.order_by(min_elapsed.asc())
            elif filters["sort"][0] == 'maxElapsed':
                query = query.order_by(max_elapsed.asc())
            elif filters["sort"][0] == 'avgElapsed':
                query = query.order_by(avg_elapsed.asc())
            else:
                query = query.order_by(
                    getattr(Measurements, filters["sort"][0]).asc())
        rows = query.all()

        result = []
        for r in rows:
            result.append({
                "method": r[0],
                "name": r[1],
                "count": r[2],
                "minElapsed": r[3],
                "maxElapsed": r[4],
                "avgElapsed": r[5]
            })
        return result