Example #1
0
 def member_age(self):
     """
     Being used in the UI sorting and filtering
     :return:member age
     """
     return func.date_part("year",
                           func.age(self.join_date)).label("member_age")
Example #2
0
    def index(self):
        from gviz_data_table import Table
        from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent
        if request.args.get('activity') == 'activity':
            activity_model, activity_date = UserActivity, UserActivity.date_actioned
        else:
            activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date

        try:
            interval_count = int(request.args['interval_count'])
        except Exception:
            interval_count = 10

        interval = request.args.get('interval')
        if interval not in ('week', 'month'):
            interval = 'week'

        cohort = func.date_part(interval, User.date_joined)
        cohort_label = func.min(func.date(User.date_joined))
        active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval')

        q = readonly_session.query(User).filter(
            User.date_joined > LAUNCHDATE, User.refresh_token != '')
        if request.args.get('gender') in ('m', 'f'):
            q = q.filter(User.gender == request.args['gender'])
        if request.args.get('locale') in app.config['ENABLED_LOCALES']:
            q = q.filter(User.locale == request.args['locale'])
        if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'):
            age1, age2 = map(int, request.args['age'].split('-'))
            q = q.filter(between(
                func.age(User.date_of_birth),
                text("interval '%d years'" % age1),
                text("interval '%d years'" % age2)
            ))

        active_users = dict(
            ((c, int(w)), u) for c, w, u in
            q.join(
                activity_model,
                (activity_model.user == User.id) &
                (activity_date >= User.date_joined)
            ).group_by(cohort, active_interval).values(
                cohort, active_interval, func.count(func.distinct(activity_model.user))
            )
        )

        table = Table(
            [dict(id='cohort', type=date)] +
            [dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)]
        )

        totals = q.group_by(cohort).order_by(cohort)
        for c, l, t in totals.values(cohort, cohort_label, func.count()):
            data = []
            for i in range(interval_count):
                a = active_users.get((c, i), '')
                data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a))
            table.append([l] + data)

        return self.render('admin/retention_stats.html', data=table.encode())
Example #3
0
 def __init__(self, context):
     super(PersonsQueryBuilder, self).__init__(context)
     self._subq_contacts = query_person_contacts().subquery()
     self._subq_passports = query_person_passports().subquery()
     self._fields = {
         'id': Person.id,
         '_id': Person.id,
         'name': Person.name,
         'birthday': Person.birthday,
         'person_category': PersonCategory.name,
         'age': case([(
             Person.birthday != None,
             func.date_part('year', func.age(Person.birthday))
         )]),
     }
     self._simple_search_fields = [
         Person.name,
         Person.first_name,
         Person.last_name,
         self._subq_contacts.c.phone,
         self._subq_contacts.c.email,
         self._subq_contacts.c.skype,
         self._subq_passports.c.citizen,
         self._subq_passports.c.foreign,
     ]
     self.build_query()
Example #4
0
 def get(self):
     '''방문시 연령대(10세 단위)별 방문 수'''
     Json = dict()
     q = db.session.query(
         func.floor(
             extract('year',
                     func.age(v.visit_start_datetime, p.birth_datetime)) /
             10) * 10, func.count()
     ).join(p, p.person_id == v.person_id).group_by(
         func.floor(
             extract('year',
                     func.age(v.visit_start_datetime, p.birth_datetime)) /
             10) *
         10).order_by(
             func.floor(
                 extract('year',
                         func.age(v.visit_start_datetime, p.birth_datetime))
                 / 10) * 10)
     for name, num in q:
         Json[str(int(name)) + "대"] = num
     return Json
Example #5
0
 def count_visit_by_age_group():
     # select age_group, person_id
     ages = db.session.query(
         (func.floor(extract('year', (func.age(Person.birth_datetime))) / 10) * 10).label('age_group'),
         Person.person_id.label('person_id')
     ).subquery()
     # group by age_group
     result = db.session \
         .query(ages.c.age_group, func.count(ages.c.age_group)) \
         .filter(VisitOccurrence.person_id == ages.c.person_id) \
         .group_by(ages.c.age_group) \
         .all()
     return dict(result)
Example #6
0
    async def get(self):
        await self.check_import_exists()

        age = func.age(self.CURRENT_DATE, citizens_table.c.birth_date)
        age = func.date_part('year', age)
        query = select([
            citizens_table.c.town,
            rounded(func.percentile_cont(0.5).within_group(age)).label('p50'),
            rounded(func.percentile_cont(0.75).within_group(age)).label('p75'),
            rounded(func.percentile_cont(0.99).within_group(age)).label('p99')
        ]).select_from(citizens_table).group_by(citizens_table.c.town).where(
            citizens_table.c.import_id == self.import_id)

        stats = await self.pg.fetch(query)
        return Response(body={'data': stats})
Example #7
0
def plot_host_status_for_plotly(session):
    from plotly import graph_objs as go, plotly as py
    internal_hosts = ['per210-1', 'paper1']
    internal_to_ui = {
        'per210-1': 'qmaster',
    }

    # Fill in arrays from the DB.

    data = []

    for host in internal_hosts:
        times = []
        loads = []

        q = (session.query(HostStatus).
             filter(func.age(HostStatus.time) < '30 day').
             filter(HostStatus.hostname == host).
             order_by(HostStatus.time).
             all())

        for item in q:
            times.append(item.time)
            loads.append(item.load_average)

        data.append(go.Scatter(x=times,
                               y=loads,
                               name=internal_to_ui.get(host, host)))

    # Finish plot

    layout = go.Layout(showlegend=True,
                       title='Karoo host load averages',
                       xaxis={'title': 'Date',
                              },
                       yaxis={'title': '5-minute load average',
                              },
                       )
    fig = go.Figure(data=data,
                    layout=layout,
                    )
    py.plot(fig,
            auto_open=False,
            filename='karoo_host_load_averages',
            )
async def get_age_statistics(import_id: int, database: Database) -> List[dict]:
    """Get age percentiles by each town."""
    age = func.date_part("year", func.age(citizens.c.birth_date)).label("age")
    query = (select([
        citizens.c.town,
        func.percentile_cont(0.5).within_group(age).label("p50"),
        func.percentile_cont(0.75).within_group(age).label("p75"),
        func.percentile_cont(0.99).within_group(age).label("p99"),
    ]).where(citizens.c.import_id == import_id).group_by(citizens.c.town))
    res = []
    async for row in database.iterate(query):
        obj = {
            "town": row[0],
            "p50": row[1],
            "p75": row[2],
            "p99": row[3],
        }
        res.append(obj)
    return res
Example #9
0
    def get(self, station_id: UUID):
        args = record_limit_parser.parse_args()
        with session_scope() as sess:
            limit = args.get("limit", None)
            limit_by_day = args.get("day_limit", None)

            req = (sess.query(NivoRecord).filter(
                NivoRecord.nr_nivo_sensor == station_id).order_by(
                    NivoRecord.nr_date.desc()))
            if limit_by_day:
                last_date = sess.query(
                    func.max(NivoRecord.nr_date).label('max')).filter(
                        NivoRecord.nr_nivo_sensor == station_id).subquery(
                            'last_date')
                req = req.filter(
                    func.age(last_date.c.max, NivoRecord.nr_date) < timedelta(
                        days=limit_by_day))
            if limit:
                req = req.limit(limit)
            return req.all()
Example #10
0
async def get_town_age_statistics(db: PG, import_id: int) -> List[Record]:
    """
    Возвращает статистику возврастов жителей по городам.

    :param db: объект для взаимодействия с БД
    :param import_id: идентификатор выгрузки
    :return: статистика
    """
    age = func.age(CURRENT_DATE, citizens_table.c.birth_date)
    age = func.date_part("year", age)

    query = (select([
        citizens_table.c.town,
        rounded(func.percentile_cont(0.5).within_group(age)).label("p50"),
        rounded(func.percentile_cont(0.75).within_group(age)).label("p75"),
        rounded(func.percentile_cont(0.99).within_group(age)).label("p99"),
    ]).select_from(citizens_table).group_by(
        citizens_table.c.town).where(citizens_table.c.import_id == import_id))

    stats = await db.fetch(query)
    return stats
Example #11
0
    def get_users(self):
        users = User.query.filter_by(is_active=True)

        if self.filter:
            for expr, type, values in BroadcastMessage.parse_filter_string(self.filter):
                if type == 'email':
                    users = users.filter(User.email.like('%%%s' % values))
                if type == 'locale':
                    users = users.filter(User.locale.like('%s%%' % values))
                if type == 'gender':
                    users = users.filter(User.gender == values[0])
                if type == 'age':
                    users = users.filter(between(
                        func.age(User.date_of_birth),
                        text("interval '%s years'" % values[0]),
                        text("interval '%s years'" % values[1])))
                if type == 'subscribed':
                    users = users.join(Subscription,
                                       (Subscription.user == User.id) &
                                       (Subscription.channel == values[0]))
                if type == 'interested':
                    from rockpack.mainsite.services.video.models import Category
                    users = users.\
                        join(UserInterest, UserInterest.user == User.id).\
                        join(Category, Category.id == UserInterest.category).\
                        filter((Category.name == values[0]))

        if self.external_system == 'apns':
            from rockpack.mainsite.services.oauth.models import ExternalToken
            users = users.join(
                ExternalToken,
                (ExternalToken.external_system == 'apns') &
                (ExternalToken.user == User.id)
            )

        return users
Example #12
0
 def hn_score(cls):
     s = cls.upvotes - cls.downvotes
     item_hour_age = -1 * func.date_part(
         'epoch', func.age(cls.timestamp, func.current_timestamp())) / 3600
     return (s - 1) / func.power((item_hour_age + 2), 1.8)
Example #13
0
 def days(cls):
     return func.date_part('day', func.age(cls.date_to, cls.date_from))
Example #14
0
 def member_age(self):
     """
     Being used in the UI sorting and filtering
     :return:member age
     """
     return func.date_part("year", func.age(self.join_date)).label("member_age")
Example #15
0
 def nights(cls):
     return func.date_part('day', func.age(cls.check_out, cls.check_in))