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)
评论列表
文章目录
