我们从Python开源项目中，提取了以下18个代码示例，用于说明如何使用sqlalchemy.func.row_number()。
def __init__(self, func, partition_by=None, order_by=None): """Produce an :class:`.Over` object against a function. Used against aggregate or so-called "window" functions, for database backends that support window functions. E.g.:: from sqlalchemy import over over(func.row_number(), order_by='x') Would produce "ROW_NUMBER() OVER(ORDER BY x)". :param func: a :class:`.FunctionElement` construct, typically generated by :data:`~.expression.func`. :param partition_by: a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct. :param order_by: a column element or string, or a list of such, that will be used as the ORDER BY clause of the OVER construct. This function is also available from the :data:`~.expression.func` construct itself via the :meth:`.FunctionElement.over` method. .. versionadded:: 0.7 """ self.func = func if order_by is not None: self.order_by = ClauseList( *util.to_list(order_by), _literal_as_text=_literal_as_label_reference) if partition_by is not None: self.partition_by = ClauseList( *util.to_list(partition_by), _literal_as_text=_literal_as_label_reference)
def __init__(self, func, partition_by=None, order_by=None): """Produce an :class:`.Over` object against a function. Used against aggregate or so-called "window" functions, for database backends that support window functions. E.g.:: from sqlalchemy import over over(func.row_number(), order_by='x') Would produce "ROW_NUMBER() OVER(ORDER BY x)". :param func: a :class:`.FunctionElement` construct, typically generated by :data:`~.expression.func`. :param partition_by: a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct. :param order_by: a column element or string, or a list of such, that will be used as the ORDER BY clause of the OVER construct. This function is also available from the :data:`~.expression.func` construct itself via the :meth:`.FunctionElement.over` method. .. versionadded:: 0.7 """ self.func = func if order_by is not None: self.order_by = ClauseList(*util.to_list(order_by)) if partition_by is not None: self.partition_by = ClauseList(*util.to_list(partition_by))
def get_missions(lat, lon, radius, limit, lang, user_id): try: location = WKTElement('POINT('+str(lon)+' '+str(lat)+')', srid=4326) no_of_errors = 10 # get already solved error ids already_solved = db_session.query(api.models.Solution.error_id). \ filter(api.models.Solution.user_id == user_id) # get nearest neighbors candidates from location q = db_session.query(api.models.kort_errors.schema, api.models.kort_errors.errorId) \ .filter((~api.models.kort_errors.errorId.in_(already_solved))) \ .order_by(api.models.kort_errors.geom.distance_centroid(location)) \ .limit(limit*no_of_errors).subquery() # partition by error type q = db_session.query(api.models.kort_errors, func.row_number().over( partition_by=api.models.kort_errors.error_type).label("row_number")) \ .filter(tuple_(api.models.kort_errors.schema, api.models.kort_errors.errorId).in_(q))\ .filter(func.ST_DistanceSphere(api.models.kort_errors.geom, location) < radius).subquery() # set max errors of each type q = db_session.query(api.models.kort_errors).select_entity_from(q).filter(q.c.row_number <= limit/no_of_errors) except Exception as e: logger.error(traceback.format_exc()) return [p.dump(lang) for p in q][:limit]
def column_windows(session, w_column, w_size, fb_kw=None, f_expr=None): """Return a series of WHERE clauses against a given column that break it into windows. Parameters ---------- session : object An instance of SQLAlchemy Session. w_column : object Column object that is used to split into windows, should be an integer column. w_size : int Size of the window fb_kw : dict The filter_by keywords, used by query.filter_by(). f_expr : list The filter expressions, used by query.filter(). Returns ------- iterable Each element of the iterable is a whereclause expression, which specify the range of the window over the column `w_col`. Exmaple ------- for whereclause in column_windows(q.session, w_column, w_size): for row in q.filter(whereclause).order_by(w_column): yield row """ def int_for_range(start_id, end_id): """Internal function to build range.""" if end_id: return and_(w_column >= start_id, w_column < end_id) else: return w_column >= start_id q = session.query( w_column, func.row_number().over(order_by=w_column).label('w_row_num')) if fb_kw: q = q.filter_by(**fb_kw) if f_expr: q = q.filter(*f_expr) q = q.from_self(w_column) if w_size > 1: q = q.filter(sqlalchemy.text("w_row_num % {}=1".format(w_size))) intervals = [id for id, in q] while intervals: start = intervals.pop(0) if intervals: end = intervals[0] else: end = None yield int_for_range(start, end)
