Python func.max函数代码示例

    def summarise(cls, start_ts=0, end_ts=0):
        """"Gets usage from their snapshots between start_ts and end_ts.

        Maximal usage of the period is returned.
        """
        id_query = Snapshot.id_between(start_ts, end_ts)

        query = cls.query.filter(cls.snapshot_id.in_(id_query)).\
            group_by(cls.virtual_volume_id, cls.owner_id).\
            with_entities(cls.virtual_volume_id, cls.owner_id,
                          func.max(cls.quota).label('quota'),
                          func.max(cls.files).label('files'),
                          func.max(cls.usage).label('usage'))

        fq = VirtualVolume.query.join(Filesystem).\
            with_entities(VirtualVolume.id, Filesystem.name, VirtualVolume.name).all()
        file_systems = {}
        for fs in fq:
            file_systems[fs[0]] = fs[1:]

        # Not all virtual volumes has owner
        owners = dict(Owner.query.with_entities(Owner.id, Owner.name).all())

        fields = ['filesystem', 'virtual_volume', 'owner', 'quota', 'files', 'usage']
        rslt = []

        for q in query.all():
            fn, vn = file_systems[q[0]]
            owner = owners[q[1]] if q[1] else ''
            mappings = (fn, vn, owner, q[2], q[3], q[4])
            rslt.append(dict(zip(fields, mappings)))
        return rslt

    def annotation_objects_in_frame(self, frame):
        """
        Returns annotation objects related to this video that are visible in given time.
        AnnotationValues are lazily-loaded, in comparison to annotation_objects_in_frame_intervals()

        SQL:
            SELECT annotation_objects....., min(annotation_values.frame_from) AS min_1, max(annotation_values.frame_from) AS max_1
            FROM annotation_objects
            INNER JOIN annotation_values ON annotation_objects.id = annotation_values.annotation_object_id
            WHERE annotation_objects.video_id = %s
            GROUP BY annotation_objects.id
            HAVING min(annotation_values.frame_from) <= %s AND max(annotation_values.frame_from) >= %s
            ORDER BY min(annotation_values.frame_from), max(annotation_values.frame_from), annotation_objects.id

        :rtype: list of (AnnotationObject, int, int)
        """

        q = database.db.session.query(AnnotationObject, func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from))
        q = q.filter_by(video_id=self.id)
        q = q.join(AnnotationObject.annotation_values)
        q = q.group_by(AnnotationObject.id)
        q = q.having((func.min(AnnotationValue.frame_from) <= frame) & (func.max(AnnotationValue.frame_from) >= frame))
        q = q.order_by(func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from), AnnotationObject.id)
        q = q.all()

        return q

    def summarise(cls, start_ts=0, end_ts=0):
        """"Gets usage from their snapshots between start_ts and end_ts.

        Maximal usage of the period is returned.
        Grouped by filesystem.
        """
        id_query = Snapshot.id_between(start_ts, end_ts)

        # 1. soft and hard quotas are not changed very often
        # 2. Record number of Host, Filesystem and Owner are relatively very small,
        # link them in code to avoid expand usage rows whose number is very very high
        query = Usage.query.filter(Usage.snapshot_id.in_(id_query)).\
            group_by(Usage.filesystem_id).\
            with_entities(Usage.filesystem_id,
                          func.max(Usage.soft).label('soft'),
                          func.max(Usage.hard).label('hard'),
                          func.max(Usage.usage).label('usage'))

        fq = Filesystem.query.join(Host).\
            with_entities(Filesystem.id, Host.name, Filesystem.name).all()
        file_systems = {}
        for fs in fq:
            file_systems[fs[0]] = fs[1:]

        fields = ['host', 'filesystem', 'soft', 'hard', 'usage']
        rslt = []

        for q in query.all():
            hn, fn = file_systems[q[0]]
            mappings = (hn, fn, q[1], q[2], q[3])
            rslt.append(dict(zip(fields, mappings)))
        return rslt

    def stats(self, survey_id):
        """Get stats for a survey."""
        result = (
            self.session
            .query(
                func.max(Survey.created_on),
                func.min(Submission.save_time),
                func.max(Submission.save_time),
                func.count(Submission.id),
            )
            .select_from(Submission)
            .join(Survey)
            # TODO: ask @jmwohl what this line is supposed to do
            # .filter(User.id == self.current_user_model.id)
            .filter(Submission.survey_id == survey_id)
            .one()
        )

        response = {
            "created_on": result[0],
            "earliest_submission_time": result[1],
            "latest_submission_time": result[2],
            "num_submissions": result[3]
        }
        return response

    def __init__(self, engine=None, start_date='1925-12-31', end_date='',
                limit=None, all_vars=None, **kwargs):
        super(CCMNamesQuery, self).__init__(engine, limit)
        logging.info("---- Creating a CCM-MSENAMES query session. ----")

        msenames = self.tables['msenames']
        ccmxpf_linktable = self.tables['ccmxpf_linktable']

        id_vars = [msenames.c.permno, msenames.c.permco,
                     ccmxpf_linktable.c.gvkey, msenames.c.comnam]

        query = sa.select(id_vars+\
                        [func.min(msenames.c.namedt).label('sdate'),
                        func.max(msenames.c.nameendt).label('edate')],
                    group_by = id_vars,
                    order_by = id_vars,
                    limit= self.limit).\
            where(ccmxpf_linktable.c.linktype.startswith('L')).\
            where(ccmxpf_linktable.c.linkprim.in_(['P','C'])).\
            where(ccmxpf_linktable.c.usedflag==1).\
            where((ccmxpf_linktable.c.linkdt <= msenames.c.namedt) |
                  (ccmxpf_linktable.c.linkdt == None)).\
            where((msenames.c.nameendt <= ccmxpf_linktable.c.linkenddt) |
                  (ccmxpf_linktable.c.linkenddt == None)).\
            where(msenames.c.permno == ccmxpf_linktable.c.lpermno).\
            where(msenames.c.permco == ccmxpf_linktable.c.lpermco)

        if start_date:
            query = query.having(func.min(msenames.c.namedt) >= start_date)

        if end_date:
            query = query.having(func.max(msenames.c.nameendt) <= end_date)

        logging.debug(query)
        self.query = query

    def __get_subquery(self, *args, ord_by=None):
        def add_joined_search(field_name):
            joined = db(Search.index, func.min(Search.text).label('text'),
                        func.min(Search.table_name).label('table_name'),
                        index=subquery_search.subquery().c.index).filter(
                    Search.kind.in_(tuple(field_name))).group_by(Search.index)
            return joined

        subquery_search = db(Search.index.label('index'),
                             func.sum(Search.relevance).label('relevance'),
                             func.min(Search.table_name).label('table_name'),
                             func.min(Search.md_tm).label('md_tm'),
                             func.max(Search.position).label('position'),
                             func.max(Search.text).label('text')).filter(
                or_(*self.__get_search_params(*args))).group_by('index')
        if type(ord_by) in (str, list, tuple):
            order = self.__get_order('text', 'text')
            subquery_search = add_joined_search(ord_by)
        elif type(ord_by) == int:
            ord_to_str = self.__order_by_to_str[ord_by]
            order = self.__get_order(ord_to_str, ord_to_str)
        else:
            order = self.__get_order('relevance', 'relevance')
        if 'md_tm' in str(order):
            subquery_search = subquery_search.order_by(order)
        else:
            subquery_search = subquery_search.order_by(order).order_by(
                    self.__get_order('md_tm', 'md_tm'))
        return subquery_search

    def mls_draft_format(self, dtype):
        """
        Return number of rounds in draft, given draft type.

        :param dtype: Draft type (AcquisitionType)
        :return:
        """
        return self.session.query(func.max(PlayerDrafts.round), func.max(PlayerDrafts.selection)).filter(
            PlayerDrafts.path == dtype, PlayerDrafts.year == self.year).one()

def backup_duration(bddate):
  s = select([pool.c.name, func.min(job.c.starttime), func.max(job.c.endtime)],  use_labels=True).where(and_(job.c.poolid == pool.c.poolid, cast(job.c.schedtime,Date) <= datetime.fromtimestamp(float(bddate)), cast(job.c.schedtime,Date) >= datetime.fromtimestamp(float(bddate)) - timedelta(days=1))).group_by(pool.c.name, job.c.schedtime)
  bd = db.execute(s).fetchall()
  bd_result = {}
  for bpool in bd:
    bd_result.update({ bpool[0]: { 'start': bpool[1], 'end': bpool[2] } })
  s = select([func.min(job.c.starttime), func.max(job.c.endtime)],  use_labels=True).where(job.c.poolid == pool.c.poolid)
  _min_date, _max_date = db.execute(s).fetchone()
  min_date = int(mktime((strptime(str(_min_date), "%Y-%m-%d %H:%M:%S"))))
  max_date = int(mktime((strptime(str(_max_date), "%Y-%m-%d %H:%M:%S"))))
  return render_template('backup_duration.html', title="Backup duration time", bd_result=bd_result, bddate=int(bddate), min_date=min_date, max_date=max_date)

    def annotation_object_next(self, current_frame, current_annotation_object=None, to_future=True):
        """
        When current_annotation_object is None, nearest AnnotationObject in the future (in respect to current_frame) is returned.
        With current_annotation_object given, "next" AnnotationObject is returned, i.e. an object with higher ID in the current frame
        or the nearest in the future.

        If to_future is false, the search is done in the past instead of the future.

        Returned tuple contains the next object and its starting and ending frame.

        :rtype: (AnnotationObject, int, int)
        """

        q = database.db.session.query(AnnotationObject, func.min(AnnotationValue.frame_from), func.max(AnnotationValue.frame_from))
        q = q.filter_by(video_id=self.id)
        q = q.join(AnnotationObject.annotation_values)
        q = q.group_by(AnnotationObject.id)

        if to_future:
            if current_annotation_object is None:
                # nearest AnnotationObject in future
                q = q.having(func.min(AnnotationValue.frame_from) > current_frame)
                q = q.order_by(func.min(AnnotationValue.frame_from), AnnotationObject.id)
            else:
                # nearest AnnotationObject in future, or in the same frame with bigger ID
                current_id = current_annotation_object.id
                q = q.having( (func.min(AnnotationValue.frame_from) > current_frame) |
                              ((AnnotationObject.id > current_id) & (func.min(AnnotationValue.frame_from) <= current_frame) & (func.max(AnnotationValue.frame_from) >= current_frame))
                            )
                q = q.order_by(func.min(AnnotationValue.frame_from), AnnotationObject.id)
        else:
            if current_annotation_object is None:
                # nearest AnnotationObject in past
                q = q.having(func.max(AnnotationValue.frame_from) < current_frame)
                q = q.order_by(desc(func.max(AnnotationValue.frame_from)), desc(AnnotationObject.id))
            else:
                # nearest AnnotationObject in past, or in the same frame with lower ID
                current_id = current_annotation_object.id
                q = q.having( (func.max(AnnotationValue.frame_from) < current_frame) |
                              ((AnnotationObject.id < current_id) & (func.min(AnnotationValue.frame_from) <= current_frame) & (func.max(AnnotationValue.frame_from) >= current_frame))
                            )
                q = q.order_by(desc(func.max(AnnotationValue.frame_from)), desc(AnnotationObject.id))

        q = q.limit(1)
        q = q.all()

        if len(q) < 1:
            return None

        if len(q) > 1:
            raise Exception('Returned collection cannot contain more than 1 item.')

        return q[0]

 def get_order(order_name, desc_asc, field):
     order_name += "+" if desc_asc == "desc" else "-"
     result = {
         "text+": lambda field_name: desc(func.max(getattr(Search, field_name, Search.text))),
         "text-": lambda field_name: asc(func.max(getattr(Search, field_name, Search.text))),
         "md_tm+": lambda field_name: desc(func.min(getattr(Search, field_name, Search.md_tm))),
         "md_tm-": lambda field_name: asc(func.min(getattr(Search, field_name, Search.md_tm))),
         "relevance+": lambda field_name: desc(func.sum(getattr(Search, field_name, Search.relevance))),
         "relevance-": lambda field_name: asc(func.sum(getattr(Search, field_name, Search.relevance))),
         "position+": lambda field_name: desc(func.max(getattr(Search, field_name, Search.position))),
         "position-": lambda field_name: asc(func.max(getattr(Search, field_name, Search.position))),
     }[order_name](field)
     return result

    def end(self):
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        # Adjust idColumn
        maxId = db.getSession().query(func.max(Task.id)).one()[0]
        self.idColumn.width = max(2, len(str(maxId)))

        # Adjust titleColumn
        self.titleColumn.width = self.maxTitleWidth
        totalWidth = sum([x.width for x in self.columns]) + len(self.columns)
        if totalWidth >= self.termWidth:
            self.titleColumn.width -= (totalWidth - self.termWidth) + len(self.columns)
        self.titleColumn.formater = TitleFormater(self.titleColumn.width, self.cryptoMgr)

        # Print table
        for sectionName, taskList in self.taskLists:
            dateSplitters = [(1, "day"), (7, "week"), (30, "month"), (30 * 4, "quarter"), (365, "year")]
            splitterRange, splitterName = dateSplitters.pop()
            splitterText = None
            self._renderTaskListHeader(sectionName)
            for task in taskList:
                while self.splitOnDate and task.creationDate > today - timedelta(splitterRange):
                    splitterText = "Last %s" % splitterName
                    if len(dateSplitters) > 0:
                        splitterRange, splitterName = dateSplitters.pop()
                    else:
                        self.splitOnDate = False

                if splitterText:
                    print(C.GREEN + splitterText.center(totalWidth) + C.RESET, file=self.out)
                    splitterText = None

                self._renderTaskListRow(task)

def show_stats():
    joint = query.for_field(Transaction.account).filter_by(person='')

    timespan = query.all() \
        .add_columns(func.max(Transaction.date).label('maxDate')) \
        .add_columns(func.min(Transaction.date).label('minDate'))
    datespan = timespan[0][0] - timespan[0][1]

    merchants = query.group_format(Transaction.merchant)
    top_merchants = OrderedDict()
    for key in merchants.keys()[0:20]:
        top_merchants[key] = merchants[key]

    amount_data = [
        make_template_data(query.group_format(Transaction.person), "per person"),
        make_template_data(query.group_format(Transaction.account, joint), "joint transactions by account"),
        make_template_data(query.group_format(Transaction.category), "all transactions by category"),
        make_template_data(top_merchants, "top 20 merchants")
    ]

    return flask.render_template('stats.html',
                                 datespan=datespan,
                                 number_of_days=datespan.total_seconds() / (60*60*24),
                                 number_of_months=datespan.total_seconds() / (60*60*24*30),
                                 amount_data=amount_data)

	def extractData(self):
		self.logger.info('Connecting to database')
		engine = create_engine(self.config['database'])
		meta = MetaData()
		meta.bind = engine
		self.job = Table('job', meta, autoload=True)
		self.job_status = Table('job_status', meta, autoload=True)
		self.job_status_type_description = Table('job_status_type_description', meta, autoload=True)

		job=self.job
		job_status=self.job_status

		s=self.job_status_type_description.select()
		self.type_db_values = s.execute().fetchall()

		self.queue_db_values = self.getJobSummary(job.c.queue)
		self.user_db_values = self.getJobSummary(job.c.userId, job.c.localUser)
		self.node_db_values = self.getJobSummary(job.c.workerNode)

		self.logger.info('Generating job list')
		node_job_status = self.config['node_job_status'].split(',')

		maximum = func.max(self.job_status.c.id).label('m')
		s1 = select([maximum]).group_by(job_status.c.jobId).alias('a')
		s2 = select([job.c.id, job.c.lrmsAbsLayerJobId, job.c.workerNode, job_status.c.type,
			job_status.c.time_stamp]).select_from(job.join(job_status).join(s1,job_status.c.id==text('m'))). \
			where(and_(job_status.c.type.in_(node_job_status)))

		self.job_db_values = s2.execute().fetchall()

		return {}

    def __init__(self, sess, unfiltered, filt_crit, tt, window_size=WINDOW_SIZE):
        self.sess = sess
        self.unfiltered = unfiltered
        self.filt_crit = filt_crit
        self.tt = tt
        self.window_size = window_size

        self.skipped = []

        # select-only, can't be used for updates
        self.filtered_s = filtered = select(unfiltered.c).where(filt_crit).alias("filtered")

        self.selectable = (
            select(
                [
                    filtered.c.size,
                    func.count().label("inode_count"),
                    func.max(filtered.c.has_updates).label("has_updates"),
                ]
            )
            .group_by(filtered.c.size)
            .having(and_(literal_column("inode_count") > 1, literal_column("has_updates") > 0))
        )

        # This is higher than selectable.first().size, in order to also clear
        # updates without commonality.
        self.upper_bound = self.sess.query(self.unfiltered.c.size).order_by(-self.unfiltered.c.size).limit(1).scalar()

    def summary(self):
        subquery = (
            self.db.query(
                SQLPackage.name,
                func.max(SQLPackage.last_modified).label("last_modified"),
            )
            .group_by(SQLPackage.name)
            .subquery()
        )
        rows = self.db.query(
            SQLPackage.name, SQLPackage.last_modified, SQLPackage.summary
        ).filter(
            (SQLPackage.name == subquery.c.name)
            & (SQLPackage.last_modified == subquery.c.last_modified)
        )

        # Dedupe because two packages may share the same last_modified
        seen_packages = set()
        packages = []
        for row in rows:
            if row[0] in seen_packages:
                continue
            seen_packages.add(row[0])
            packages.append(
                {"name": row[0], "last_modified": row[1], "summary": row[2]}
            )
        return packages