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 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 nights(cls):
return func.date_part('day', func.age(cls.check_out, cls.check_in))