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")
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())
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()
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
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)
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})
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
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()
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
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
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)
def days(cls): return func.date_part('day', func.age(cls.date_to, cls.date_from))
def nights(cls): return func.date_part('day', func.age(cls.check_out, cls.check_in))