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]