Python functions.count函数代码示例

    def top_maps(self):
        """Returns the raw data shared by all renderers."""
        try:
            top_maps_q = DBSession.query(
                fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
                Game.map_id, Map.name, func.count().label("times_played"))\
                .filter(Map.map_id == Game.map_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .group_by(Game.map_id)\
                .group_by(Map.name) \
                .order_by(expr.desc(func.count()))

            if self.last:
                top_maps_q = top_maps_q.offset(self.last)

            if self.limit:
                top_maps_q = top_maps_q.limit(self.limit)

            top_maps = top_maps_q.all()
        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_maps

def top_maps_by_times_played_q(cutoff_days, region = None, game_type_cd = None):
    """
    Query to retrieve the top maps by the amount of times it was played
    during a date range.

    Games older than cutoff_days days old are ignored.
    """
    # only games played during this range are considered
    right_now = datetime.utcnow()
    cutoff_dt = right_now - timedelta(days=cutoff_days)

    top_maps_q = DBSession.query(Game.map_id, Map.name,
            func.count()).\
            filter(Map.map_id==Game.map_id).\
            filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
            order_by(expr.desc(func.count())).\
            group_by(Game.map_id).\
            group_by(Map.name)

    if region and region != "" and region != "0":
      top_maps_q = top_maps_q.filter(Server.region==region).filter(Server.server_id==Game.server_id)
    if game_type_cd and game_type_cd != "":
      top_maps_q = top_maps_q.filter(Game.game_type_cd == game_type_cd)    

    return top_maps_q

  def get(self):
    obj_count = \
      sa.sql.select([
        Tag.ns,
        functions.count(entity_tag_tbl.c.entity_id).label('obj_count'),
      ])\
            .select_from(Tag.__table__.join(entity_tag_tbl))\
            .group_by(Tag.ns)\
            .alias()

    ns_query = sa.sql.select(
      [Tag.ns,
       functions.count(Tag.id).label('tag_count'),
       obj_count.c.obj_count],
      from_obj=[Tag.__table__.outerjoin(obj_count, Tag.ns == obj_count.c.ns)]
    )\
                     .group_by(Tag.ns, obj_count.c.obj_count)\
                     .order_by(Tag.ns)

    session = current_app.db.session()
    namespaces = session.execute(ns_query)

    return render_template(
      'admin/tags.html',
      namespaces=namespaces,
    )

 def execute(self, message, user, params):
     
     tick = Updates.current_tick() + (params.group(1) or 1)
     replies = []
     
     Q = session.query(Galaxy.x, Galaxy.y, count())
     Q = Q.join(Target.planet)
     Q = Q.join(Planet.galaxy)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Galaxy.x, Galaxy.y)
     result = Q.all()
     prev = []
     for x, y, bitches in result:
         prev.append("%s:%s(%s)"%(x,y,bitches))
     replies.append("Active bookings: " + ", ".join(prev))
     
     Q = session.query(Alliance.name, count())
     Q = Q.join(Target.planet)
     Q = Q.outerjoin(Planet.intel)
     Q = Q.outerjoin(Intel.alliance)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Alliance.name)
     result = Q.all()
     prev = []
     for name, bitches in result:
         prev.append("%s (%s)"%(name or "Unknown", bitches))
     replies.append("Active bitches: " + ", ".join(prev))
     
     if len(replies) < 1:
         replies.append("No active bookings. This makes %s sad. Please don't make %s sad." %((Config.get("Connection","nick"),)*2))
     message.reply("\n".join(replies))

    def fleet_overview(self):
        if self.scantype not in ("J",):
            return

        from sqlalchemy.sql.functions import min, sum

        f = aliased(FleetScan)
        a = aliased(FleetScan)
        d = aliased(FleetScan)

        Q = session.query(
            f.landing_tick,
            f.landing_tick - min(Scan.tick),
            count(a.id),
            coalesce(sum(a.fleet_size), 0),
            count(d.id),
            coalesce(sum(d.fleet_size), 0),
        )
        Q = Q.join(f.scan)
        Q = Q.filter(f.scan == self)

        Q = Q.outerjoin((a, and_(a.id == f.id, a.mission.ilike("Attack"))))
        Q = Q.outerjoin((d, and_(d.id == f.id, d.mission.ilike("Defend"))))

        Q = Q.group_by(f.landing_tick)
        Q = Q.order_by(asc(f.landing_tick))
        return Q.all()

    def get_counts_for_query(self, q):
        # HACKITY HACK
        entities = [
            x.entity_zero.entity for x in q._entities]
        entities = {e.__mapper__.tables[0].name: e for e in entities}
        content_entity = entities['content']

        post = with_polymorphic(
            Post, [], Post.__table__,
            aliased=False, flat=True)
        q = q.join(
            post, (content_entity.id == post.id) &
                  (post.publication_state.in_(countable_publication_states)))

        if self.user_id:
            action_entity = entities['action']
            return q.with_entities(
                count(content_entity.id),
                count(post.creator_id.distinct()),
                count(action_entity.id)).first()
        else:
            (post_count, contributor_count) = q.with_entities(
                count(content_entity.id),
                count(post.creator_id.distinct())).first()
            return (post_count, contributor_count, 0)

 def execute(self, request, user, x, y, z, h=False, hs=False, ticks=None):
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not hs:
         sizediffvalue = PlanetHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = PlanetHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = PlanetHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(PlanetHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not (h or hs):
         landings = session.query(PlanetLandings.hour, count()).filter(PlanetLandings.planet==planet).group_by(PlanetLandings.hour).all()
         landed = session.query(PlanetLandedOn.hour, count()).filter(PlanetLandedOn.planet==planet).group_by(PlanetLandedOn.hour).all()
         vdrops = session.query(PlanetValueDrops.hour, count()).filter(PlanetValueDrops.planet==planet).group_by(PlanetValueDrops.hour).all()
         idles = session.query(PlanetIdles.hour, count()).filter(PlanetIdles.planet==planet).group_by(PlanetIdles.hour).all()
         hourstats = {
                         'landings' : dict(landings), 'landingsT' : sum([c for hour,c in landings]),
                         'landed'   : dict(landed),   'landedT'   : sum([c for hour,c in landed]),
                         'vdrops'   : dict(vdrops),   'vdropsT'   : sum([c for hour,c in vdrops]),
                         'idles'    : dict(idles),    'idlesT'    : sum([c for hour,c in idles]),
                         }
     else:
         hourstats = None
     
     if not h:
         Q = session.query(PlanetHistory)
         Q = Q.filter(or_(PlanetHistory.hour == 23, PlanetHistory.tick == Updates.current_tick()))
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["planet.tpl",["hplanet.tpl","hsplanet.tpl"][hs]][h or hs],
                     request,
                     planet = planet,
                     history = history,
                     hour = datetime.utcnow().hour, hourstats = hourstats,
                     hsummary = hsummary,
                     ticks = ticks,
                   )

def main_index(request):
    leaderboard_count = 10
    recent_games_count = 32

    # top players by score
    top_players = DBSession.query(Player.player_id, Player.nick, 
            func.sum(PlayerGameStat.score)).\
            filter(Player.player_id == PlayerGameStat.player_id).\
            filter(Player.player_id > 2).\
            order_by(expr.desc(func.sum(PlayerGameStat.score))).\
            group_by(Player.nick).\
            group_by(Player.player_id).all()[0:10]

    top_players = [(player_id, html_colors(nick), score) \
            for (player_id, nick, score) in top_players]

    for i in range(leaderboard_count-len(top_players)):
        top_players.append(('-', '-', '-'))

    # top servers by number of total players played
    top_servers = DBSession.query(Server.server_id, Server.name, 
            func.count()).\
            filter(Game.server_id==Server.server_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Server.server_id).\
            group_by(Server.name).all()[0:10]

    for i in range(leaderboard_count-len(top_servers)):
        top_servers.append(('-', '-', '-'))

    # top maps by total times played
    top_maps = DBSession.query(Map.map_id, Map.name, 
            func.count(Game.game_id)).\
            filter(Map.map_id==Game.game_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Map.map_id).\
            group_by(Map.name).all()[0:10]

    for i in range(leaderboard_count-len(top_maps)):
        top_maps.append(('-', '-', '-'))

    recent_games = DBSession.query(Game, Server, Map).\
            filter(Game.server_id==Server.server_id).\
            filter(Game.map_id==Map.map_id).\
            order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count]

    for i in range(recent_games_count-len(recent_games)):
        recent_games.append(('-', '-', '-'))

    return {'top_players':top_players,
            'top_servers':top_servers,
            'top_maps':top_maps,
            'recent_games':recent_games,
            }

    def getAlarmCount(days=0):
        """
        Get number of alarms, grouped by state

        :param optional days: 0 for all alarms, since days else
        :return: list grouped by state
        """
        if days != 0:
            return db.get(Alarm.state, count(Alarm.id)).filter(Alarm.timestamp > (datetime.datetime.now() - datetime.timedelta(days=days))).order_by(Alarm.timestamp.desc()).group_by(Alarm.state).all()
        else:
            return db.get(Alarm.state, count(Alarm.id)).group_by(Alarm.state).all()

 def execute(self, request, user, name):
     alliance = Alliance.load(name)
     if alliance is None:
         return HttpResponseRedirect(reverse("alliance_ranks"))
     
     ph = aliased(PlanetHistory)
     members = count().label("members")
     size = sum(ph.size).label("size")
     value = sum(ph.value).label("value")
     score = sum(ph.score).label("score")
     avg_size = size.op("/")(members).label("avg_size")
     avg_value = value.op("/")(members).label("avg_value")
     t10v = count(case(whens=((ph.value_rank <= 10 ,1),), else_=None)).label("t10v")
     t100v = count(case(whens=((ph.value_rank <= 100 ,1),), else_=None)).label("t100v")
     
     pho = aliased(PlanetHistory)
     sizeo = sum(pho.size).label("sizeo")
     valueo = sum(pho.value).label("valueo")
     scoreo = sum(pho.score).label("scoreo")
     
     Q = session.query(PlanetHistory.tick.label("tick"),
                       Alliance.id.label("id"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.size) DESC)").label("size_rank"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.value) DESC)").label("value_rank"),
                       )
     Q = Q.filter(PlanetHistory.active == True)
     Q = Q.join(PlanetHistory.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.group_by(PlanetHistory.tick, Alliance.id)
     ranks = Q.subquery()
     
     Q = session.query(ph.tick, members,
                       size, value,
                       avg_size, avg_value,
                       size-sizeo, value-valueo, score-scoreo,
                       t10v, t100v,
                       )
     Q = Q.filter(ph.active == True)
     Q = Q.join(ph.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.outerjoin((pho, and_(ph.id==pho.id, ph.tick-1==pho.tick),))
     Q = Q.filter(Intel.alliance == alliance)
     Q = Q.group_by(ph.tick)
     
     Q = Q.from_self().add_columns(ranks.c.size_rank, ranks.c.value_rank)
     Q = Q.outerjoin((ranks, and_(ph.tick == ranks.c.tick, alliance.id == ranks.c.id),))
     Q = Q.order_by(desc(ph.tick))
     
     history = Q.all()
     
     return render("ialliancehistory.tpl", request, alliance=alliance, members=alliance.intel_members, history=history)

def upgrade(pyramid_env):
    from assembl.models import IdeaLink, get_session_maker
    db = get_session_maker()()
    # First, reorder live links.
    with transaction.manager:
        ids = db.query(IdeaLink.source_id)\
            .filter(IdeaLink.tombstone_date == None)\
            .group_by(IdeaLink.source_id, IdeaLink.order)\
            .having((count(IdeaLink.id) > 1)).all()
        for (source_id,) in ids:
            links = db.query(IdeaLink).filter_by(
                source_id=source_id,
                tombstone_date=None).order_by(
                IdeaLink.order, IdeaLink.id).all()
            for n, link in enumerate(links):
                link.order = n + 1
        # Then dead links
        q = db.query(
            IdeaLink.source_id, IdeaLink.tombstone_date).\
            group_by(IdeaLink.source_id, IdeaLink.order,
                     IdeaLink.tombstone_date).\
            having((count(IdeaLink.id) > 1)).all()
        for (source_id, date) in q:
            if not date:
                continue
            dest_links = db.query(IdeaLink).filter_by(
                source_id=source_id,
                tombstone_date=date).all()
            # Try to find the order of the ordered link the closest
            # in date to each current link.
            all_links = db.query(IdeaLink).filter_by(source_id=source_id).all()
            by_base_id = defaultdict(list)
            for link in all_links:
                by_base_id[link.base_id].append(link)
            signatures = {}
            for dest in dest_links:
                base_id = dest.base_id
                links = by_base_id[base_id]
                # source_id should be the same.
                links = [l for l in links if l.order]

                def distance(l):
                    if l == dest:
                        return -1
                    if not l.tombstone_date:
                        return 0
                    return abs((l.tombstone_date-dest.tombstone_date).seconds)
                links.sort(key=distance)
                signatures[base_id] = tuple((l.order for l in links))
            dest_links.sort(key=lambda l: signatures[l.base_id])
            for n, link in enumerate(dest_links):
                link.order = n

def calculateDomainAffinities():
    DeityDomain2 = aliased(DeityDomain)
    Domain2 = aliased(Domain)
    q_affinity = session.query(Domain.name, Domain2.name, count()).\
        join(DeityDomain).\
        join(Deity).\
        join(DeityDomain2).\
        join(Domain2).\
        filter(Domain.id != Domain2.id).\
        order_by(Domain.name, count().desc(), Domain2.name).\
        group_by(Domain.name, Domain2.name)

    return q_affinity.all()

 def execute(self, message, user, params):
     
     tag_count = PA.getint("numbers", "tag_count")
     
     alliance = Alliance.load(params.group(1))
     if alliance is None:
         message.reply("No alliance matching '%s' found"%(params.group(1),))
         return
     
     Q = session.query(sum(Planet.value), sum(Planet.score),
                       sum(Planet.size), sum(Planet.xp),
                       count())
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.group_by(Intel.alliance_id)
     result = Q.first()
     if result is None:
         message.reply("No planets in intel match alliance %s"%(alliance.name,))
         return
     
     value, score, size, xp, members = result
     if members <= tag_count:
         reply="%s Members: %s/%s, Value: %s, Avg: %s," % (alliance.name,members,alliance.members,value,value//members)
         reply+=" Score: %s, Avg: %s," % (score,score//members) 
         reply+=" Size: %s, Avg: %s, XP: %s, Avg: %s" % (size,size//members,xp,xp//members)
         message.reply(reply)
         return
     
     Q = session.query(Planet.value, Planet.score, 
                       Planet.size, Planet.xp, 
                       Intel.alliance_id)
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.order_by(desc(Planet.score))
     Q = Q.limit(tag_count)
     Q = Q.from_self(sum(Planet.value), sum(Planet.score),
                     sum(Planet.size), sum(Planet.xp),
                     count())
     Q = Q.group_by(Intel.alliance_id)
     ts_result = Q.first()
     
     ts_value, ts_score, ts_size, ts_xp, ts_members = ts_result
     reply="%s Members: %s/%s (%s)" % (alliance.name,members,alliance.members,ts_members)
     reply+=", Value: %s (%s), Avg: %s (%s)" % (value,ts_value,value//members,ts_value//ts_members)
     reply+=", Score: %s (%s), Avg: %s (%s)" % (score,ts_score,score//members,ts_score//ts_members)
     reply+=", Size: %s (%s), Avg: %s (%s)" % (size,ts_size,size//members,ts_size//ts_members)
     reply+=", XP: %s (%s), Avg: %s (%s)" % (xp,ts_xp,xp//members,ts_xp//ts_members)
     message.reply(reply)

 def get_counts_for_query(self, q):
     if self.user_id:
         # HACKITY HACK
         (content_entity, action_entity) = [
             x.entity_zero.entity for x in q._entities]
         return q.with_entities(
             count(content_entity.id), count(action_entity.id)).first()
         return (post_count, viewed_count)
     else:
         (content_entity,) = [
             x.entity_zero.entity for x in q._entities]
         (post_count,) = q.with_entities(
             count(content_entity.id)).first()
         return (post_count, 0)

def calculateDomainAffinitiesForSetting(setting):
    DeityDomain2 = aliased(DeityDomain)
    Domain2 = aliased(Domain)
    q_settingaffinity = session.query(Domain.name, Domain2.name, count()).\
        join(DeityDomain).\
        join(Deity).\
        join(DeityDomain2).\
        join(Domain2).\
        join(DeitySetting).\
        filter(Domain.id != Domain2.id, DeitySetting.setting == setting).\
        order_by(Domain.name, count().desc(), Domain2.name).\
        group_by(Domain.name, Domain2.name)

    return q_settingaffinity.all()