def gender_body_composition(gender):
"""
INPUT: Enter M or F for selecting gender
OUTPUT: Return the the median age, height and weight of all Gold medal winners for all the events.
The events are filter based on predefined list of selected sports.
"""
selected_sports = ('Basketball', 'Boxing', 'Cycling', 'Figure Skating','Gymnastics',\
'Judo', 'Swimming',\
'Tennis', 'Weightlifting', 'Wrestling')
results = db.session.query(Athletes.event, Athletes.sport,
func.percentile_cont(0.5).within_group(Athletes.age).label('median age'),\
func.percentile_cont(0.5).within_group(Athletes.height).label('median height'),\
func.percentile_cont(0.5).within_group(Athletes.weight).label('median weight'))\
.filter(Athletes.age.isnot(None))\
.filter(Athletes.height.isnot(None))\
.filter(Athletes.weight.isnot(None))\
.filter(Athletes.medal == 'Gold')\
.filter(Athletes.sex == gender)\
.filter(Athletes.sport.in_(selected_sports))\
.group_by(Athletes.event, Athletes.sport)\
.all()
event_body_composition = []
for event, sport, medianAge, medianHeight, medianWeight in results:
body_composition = {}
body_composition["event"] = event
body_composition["sport"] = sport
body_composition["age"] = medianAge
body_composition["height"] = medianHeight
body_composition["weight"] = medianWeight
event_body_composition.append(body_composition)
return jsonify(event_body_composition)
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 estimated_ticket_stats():
row = db.session.query(
func.percentile_cont(0.5).
within_group(Ticket.total_unclaimed_seconds).
label('estResponse'),
func.percentile_cont(0.5).
within_group(Ticket.total_claimed_seconds).
label('estCompletion'))\
.filter(or_(Ticket.status == 3, Ticket.status == 5))\
.one()
return {
'estimates': {
'estResponse': row[0],
'estCompletion': row[1]
}
}
def test_funcfilter_within_group_desc(self):
stmt = select([
table1.c.myid,
func.percentile_cont(0.5).within_group(table1.c.name.desc())
])
self.assert_compile(
stmt, "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name DESC) "
"AS anon_1 "
"FROM mytable", {'percentile_cont_1': 0.5})
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 test_funcfilter_within_group_w_over(self):
stmt = select([
table1.c.myid,
func.percentile_cont(0.5).within_group(
table1.c.name.desc()).over(partition_by=table1.c.description)
])
self.assert_compile(
stmt, "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name DESC) "
"OVER (PARTITION BY mytable.description) AS anon_1 "
"FROM mytable", {'percentile_cont_1': 0.5})
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 test_funcfilter_within_group(self):
stmt = select(
table1.c.myid,
func.percentile_cont(0.5).within_group(table1.c.name),
)
self.assert_compile(
stmt,
"SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name) "
"AS anon_1 "
"FROM mytable",
{"percentile_cont_1": 0.5},
)
def test_funcfilter_within_group_w_over(self):
stmt = select([
table1.c.myid,
func.percentile_cont(0.5).within_group(
table1.c.name.desc()
).over(partition_by=table1.c.description)
])
self.assert_compile(
stmt,
"SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name DESC) "
"OVER (PARTITION BY mytable.description) AS anon_1 "
"FROM mytable",
{'percentile_cont_1': 0.5}
)
def test_funcfilter_within_group_desc(self):
stmt = select([
table1.c.myid,
func.percentile_cont(0.5).within_group(
table1.c.name.desc()
)
])
self.assert_compile(
stmt,
"SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name DESC) "
"AS anon_1 "
"FROM mytable",
{'percentile_cont_1': 0.5}
)
def get_waiting_time(cls, unit):
unit_time = 86400 if unit == UnitTime.DAY.value else 60 * 60 if unit == UnitTime.HR.value else 60
median_waiting_time = db.session.query(
func.percentile_cont(0.5).within_group((func.extract('epoch', Event.eventDate) -
func.extract('epoch', Request.submittedDate)) / unit_time).label(
'examinationTime')). \
join(Request, and_(Event.nrId == Request.id)). \
filter(Event.action == EventAction.PATCH.value,
Event.stateCd.in_(
[EventState.APPROVED.value, EventState.REJECTED.value,
EventState.CONDITIONAL.value, EventState.CANCELLED.value]),
Event.userId != EventUserId.SERVICE_ACCOUNT.value,
Event.eventDate.cast(Date) >= (func.now() - timedelta(days=1)).cast(Date)
)
return median_waiting_time
def test_funcfilter_within_group_multi(self):
stmt = select(
[
table1.c.myid,
func.percentile_cont(0.5).within_group(
table1.c.name, table1.c.description
),
]
)
self.assert_compile(
stmt,
"SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
"WITHIN GROUP (ORDER BY mytable.name, mytable.description) "
"AS anon_1 "
"FROM mytable",
{"percentile_cont_1": 0.5},
)
def test_percentile_cont_array_desc(self):
expr = func.percentile_cont(0.5, 0.7).within_group(
column('data', Integer).desc())
is_(expr.type._type_affinity, ARRAY)
is_(expr.type.item_type._type_affinity, Integer)
def test_percentile_cont(self):
expr = func.percentile_cont(0.5).within_group(column('data', Integer))
is_(expr.type._type_affinity, Integer)
def percentile_cont(self, column, percent):
return self.with_entities(
func.percentile_cont(percent).within_group(
column
),
).one()[0]
