def get(self, record_id: UUID) -> Dict: """ Return a specific BRA with it's ID. """ with session_scope() as sess: global_rec = sess.query( BraRecord, func.lag(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("previous_bra_id"), func.lead(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("next_bra_id"), ).subquery() result_filtered = (sess.query(global_rec).filter( global_rec.c.br_id == record_id).first()) record_as_dict = result_filtered._asdict() record_as_dict["massif"] = (sess.query(Massif).filter( Massif.m_id == result_filtered.br_massif).first()) record_as_dict["risks"] = sess.query(Risk).filter( Risk.r_record_id == result_filtered.br_id) record_as_dict["snow_records"] = sess.query(SnowRecord).filter( SnowRecord.s_bra_record == result_filtered.br_id) record_as_dict["fresh_snow_records"] = sess.query( FreshSnowRecord).filter( FreshSnowRecord.fsr_bra_record == result_filtered.br_id) record_as_dict["weather_forecasts"] = sess.query( WeatherForecast).filter( WeatherForecast.wf_bra_record == result_filtered.br_id) record_as_dict["risk_forecasts"] = sess.query(RiskForecast).filter( RiskForecast.rf_bra_record == result_filtered.br_id) return marshal(record_as_dict, bra_model)
def count_datasets_through_time_query(self, start, end, period, time_field, expressions): raw_expressions = self._alchemify_expressions(expressions) start_times = select( (func.generate_series(start, end, cast( period, INTERVAL)).label('start_time'), )).alias('start_times') time_range_select = (select((func.tstzrange( start_times.c.start_time, func.lead(start_times.c.start_time).over()).label('time_period'), ))).alias('all_time_ranges') # Exclude the trailing (end time to infinite) row. Is there a simpler way? time_ranges = (select( (time_range_select, )).where(~func.upper_inf(time_range_select.c.time_period)) ).alias('time_ranges') count_query = (select((func.count('*'), )).select_from( self._from_expression(DATASET, expressions)).where( and_( time_field.alchemy_expression.overlaps( time_ranges.c.time_period), DATASET.c.archived == None, *raw_expressions))) return select( (time_ranges.c.time_period, count_query.label('dataset_count')))
def get(self) -> List[Dict]: """ Return the last record for all massifs. """ with session_scope() as sess: results = (sess.query( BraRecord, func.lag(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("previous_bra_id"), func.lead(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("next_bra_id"), ).filter( BraRecord.br_production_date.cast(Date) == select([ func.max(BraRecord.br_production_date.cast(Date)) ])).options(subqueryload(BraRecord.risks)).options( subqueryload(BraRecord.snow_records)).options( subqueryload(BraRecord.fresh_snow_records)).options( subqueryload(BraRecord.weather_forecasts)).options( subqueryload( BraRecord.risk_forecasts)).options( subqueryload(BraRecord.massif))) final = [] for res in results.all(): encoded_bra_record = marshal(res.BraRecord, bra_model) encoded_bra_record["previous_bra_id"] = (str( res.previous_bra_id) if res.previous_bra_id else None) encoded_bra_record["next_bra_id"] = (str(res.next_bra_id) if res.next_bra_id else None) final.append(encoded_bra_record) return final
def count_datasets_through_time(self, start, end, period, time_field, expressions): """ :type period: str :type start: datetime.datetime :type end: datetime.datetime :type expressions: tuple[datacube.index.postgres._fields.PgExpression] :rtype: list[((datetime.datetime, datetime.datetime), int)] """ raw_expressions = self._alchemify_expressions(expressions) start_times = select(( func.generate_series(start, end, cast(period, INTERVAL)).label('start_time'), )).alias('start_times') time_range_select = ( select(( func.tstzrange( start_times.c.start_time, func.lead(start_times.c.start_time).over() ).label('time_period'), )) ).alias('all_time_ranges') # Exclude the trailing (end time to infinite) row. Is there a simpler way? time_ranges = ( select(( time_range_select, )).where( ~func.upper_inf(time_range_select.c.time_period) ) ).alias('time_ranges') count_query = ( select( (func.count('*'),) ).select_from( self._from_expression(DATASET, expressions) ).where( and_( time_field.alchemy_expression.overlaps(time_ranges.c.time_period), DATASET.c.archived == None, *raw_expressions ) ) ) results = self._connection.execute(select(( time_ranges.c.time_period, count_query.label('dataset_count') ))) for time_period, dataset_count in results: # if not time_period.upper_inf: yield Range(time_period.lower, time_period.upper), dataset_count
def get_most_selected(db: Session): subquery = db.query( models.Player, func.round((models.Player.selected_by_percent - func.lead(models.Player.selected_by_percent, 1).over( order_by=models.Player.timestamp.desc(), partition_by=models.Player.id )), 2).label('selected_by_percent_change') ) \ .subquery() return db \ .query(subquery) \ .order_by(subquery.c.timestamp.desc(), subquery.c.selected_by_percent.desc()) \ .all()
def get(self, massif_id: UUID) -> Dict: """ Return a record for a massifs. With all the associated metadata. """ with session_scope() as sess: request = (sess.query( BraRecord, func.lag(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("previous_bra_id"), func.lead(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("next_bra_id")).filter( BraRecord.br_massif == massif_id).order_by( BraRecord.br_production_date.desc()).limit(1)) result = request.first() json = marshal(result.BraRecord, bra_model) json["previous_bra_id"] = result.previous_bra_id json["next_bra_id"] = result.next_bra_id return json
def get(self, massif_id, bra_date) -> Dict: parsed_date = date.fromisoformat(bra_date) with session_scope() as sess: global_rec = sess.query( BraRecord, func.lag(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("previous_bra_id"), func.lead(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("next_bra_id"), ).subquery() result_filtered = (sess.query(global_rec).filter( global_rec.c.br_production_date.cast(Date) == parsed_date ).filter(global_rec.c.br_massif == massif_id).first()) if result_filtered is None: return bra_api.abort( 404, f"Record for massif id {massif_id} for the date {bra_date} could not be found." ) record_as_dict = result_filtered._asdict() record_as_dict["massif"] = (sess.query(Massif).filter( Massif.m_id == result_filtered.br_massif).first()) record_as_dict["risks"] = sess.query(Risk).filter( Risk.r_record_id == result_filtered.br_id) record_as_dict["snow_records"] = sess.query(SnowRecord).filter( SnowRecord.s_bra_record == result_filtered.br_id) record_as_dict["fresh_snow_records"] = sess.query( FreshSnowRecord).filter( FreshSnowRecord.fsr_bra_record == result_filtered.br_id) record_as_dict["weather_forecasts"] = sess.query( WeatherForecast).filter( WeatherForecast.wf_bra_record == result_filtered.br_id) record_as_dict["risk_forecasts"] = sess.query(RiskForecast).filter( RiskForecast.rf_bra_record == result_filtered.br_id) return marshal(record_as_dict, bra_model)
def get(self, massif_id: UUID) -> List[Dict]: """ Return a list of records for a massif. Limit to last 50 days by default. """ args = massif_record_parser.parse_args() with session_scope() as sess: results = (sess.query( BraRecord, func.lag(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("previous_bra_id"), func.lead(BraRecord.br_id).over( order_by=BraRecord.br_production_date, partition_by=BraRecord.br_massif, ).label("next_bra_id"), ).filter(BraRecord.br_production_date > ( func.now() - func.cast(concat(args["limit"], "DAYS"), INTERVAL))).filter( BraRecord.br_massif == massif_id).order_by( BraRecord.br_production_date.desc()).options( subqueryload(BraRecord.risks)).options( subqueryload(BraRecord.snow_records)).options( subqueryload( BraRecord.fresh_snow_records)). options(subqueryload( BraRecord.weather_forecasts)).options( subqueryload(BraRecord.risk_forecasts)).options( subqueryload(BraRecord.massif)).all()) final = [] for res in results: encoded_bra_record = marshal(res.BraRecord, bra_model) encoded_bra_record["previous_bra_id"] = (str( res.previous_bra_id) if res.previous_bra_id else None) encoded_bra_record["next_bra_id"] = (str(res.next_bra_id) if res.next_bra_id else None) final.append(encoded_bra_record) return final
def get_final_states_query(cls): """Return query that filters complete/failed states and includes a 'duration' column.""" # We enumerate states for each request and get 'updated' field of the following state. # It allows to find out time between two states. states = db.session.query( cls.request_id, cls.updated, func.lead(cls.updated, 1).over(partition_by=cls.request_id, order_by=cls.updated).label("next_updated"), func.row_number().over(partition_by=cls.request_id, order_by=cls.updated).label("num"), ).subquery() return ( db.session.query( cls.request_id, cls.state, cls.state_reason, cls.updated, func.extract( "epoch", cls.updated.cast(TIMESTAMP) - states.c.updated.cast(TIMESTAMP), ).label("duration"), func.extract( "epoch", states.c.next_updated.cast(TIMESTAMP) - states.c.updated.cast(TIMESTAMP), ).label("time_in_queue"), ).join(states, states.c.request_id == cls.request_id).filter( cls.state.in_([ RequestStateMapping.complete.value, RequestStateMapping.failed.value ])) # We need only 'init' state information here to join it with the final state. .filter(states.c.num == 1))