def test_window_frame_repr(self):
self.assertEqual(
repr(RowRange(start=-1)),
'<RowRange: ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING>')
self.assertEqual(
repr(ValueRange(start=None, end=1)),
'<ValueRange: RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING>')
self.assertEqual(
repr(ValueRange(start=0, end=0)),
'<ValueRange: RANGE BETWEEN CURRENT ROW AND CURRENT ROW>')
self.assertEqual(
repr(RowRange(start=0, end=0)),
'<RowRange: ROWS BETWEEN CURRENT ROW AND CURRENT ROW>')
def test_invalid_type_end_row_range(self):
msg = "end argument must be a positive integer, zero, or None, but got 'a'."
with self.assertRaisesMessage(ValueError, msg):
list(Employee.objects.annotate(test=Window(
expression=Sum('salary'),
frame=RowRange(end='a'),
)))
def test_row_range_rank(self):
"""
A query with ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING.
The resulting sum is the sum of the three next (if they exist) and all
previous rows according to the ordering clause.
"""
qs = Employee.objects.annotate(sum=Window(
expression=Sum('salary'),
order_by=[F('hire_date').asc(), F('name').desc()],
frame=RowRange(start=None, end=3),
)).order_by('sum', 'hire_date')
self.assertIn('ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING', str(qs.query))
self.assertQuerysetEqual(qs, [
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 280000),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 325000),
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 362000),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 415000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 453000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 513000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 553000),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 603000),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 637000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 637000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 637000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 637000),
], transform=lambda row: (row.name, row.salary, row.department, row.hire_date, row.sum))
def test_subquery_row_range_rank(self):
qs = Employee.objects.annotate(
highest_avg_salary_date=Subquery(
Employee.objects.filter(
department=OuterRef('department'),
).annotate(
avg_salary=Window(
expression=Avg('salary'),
order_by=[F('hire_date').asc()],
frame=RowRange(start=-1, end=1),
),
).order_by('-avg_salary', 'hire_date').values('hire_date')[:1],
),
).order_by('department', 'name')
self.assertQuerysetEqual(qs, [
('Adams', 'Accounting', datetime.date(2005, 11, 1)),
('Jenson', 'Accounting', datetime.date(2005, 11, 1)),
('Jones', 'Accounting', datetime.date(2005, 11, 1)),
('Williams', 'Accounting', datetime.date(2005, 11, 1)),
('Moore', 'IT', datetime.date(2011, 3, 1)),
('Wilkinson', 'IT', datetime.date(2011, 3, 1)),
('Johnson', 'Management', datetime.date(2005, 6, 1)),
('Miller', 'Management', datetime.date(2005, 6, 1)),
('Johnson', 'Marketing', datetime.date(2009, 10, 1)),
('Smith', 'Marketing', datetime.date(2009, 10, 1)),
('Brown', 'Sales', datetime.date(2007, 6, 1)),
('Smith', 'Sales', datetime.date(2007, 6, 1)),
], transform=lambda row: (row.name, row.department, row.highest_avg_salary_date))
def test_invalid_type_start_row_range(self):
msg = "start argument must be a negative integer, zero, or None, but got 'a'."
with self.assertRaisesMessage(ValueError, msg):
list(Employee.objects.annotate(test=Window(
expression=Sum('salary'),
order_by=F('hire_date').asc(),
frame=RowRange(start='a'),
)))
def get_all_deposits(self, running_totals=False):
"""
:return: a list of (date, amt, total) tuples
"""
if not self:
return []
if running_totals:
return self.deposits().annotate(
cum_total=Window(expression=Sum('net_amount'),
order_by=F('trade_date').asc(),
frame=RowRange(end=0))).values_list(
'trade_date', 'cum_total')
else:
return self.deposits().values_list(
'trade_date',
F('net_amount') * F('account__joint_share'))
def newest_books_by_author(request):
"""
Try emulating the following query:
select
id,
title,
published,
author_id,
last_value(id) over (
partition by author_id
order by published
range between unbounded preceding and unbounded following
) last
from books_book
order by author_id, published;
"""
books = Book.objects.annotate(last_id=Window(
expression=LastValue('id'),
partition_by=F('author_id'),
order_by=F('published'),
frame=RowRange(start=None, end=None),
))
return render(request, 'books/newest.html', {'books': books})
def moving_average_strategy(user):
"""Initialise moving average strategy."""
strategies = Strategy.objects.filter(user=user).active()
if not strategies.exists():
return
strategy_symbols = list(strategies.values_list("asset__symbol", flat=True))
# Update latest bars
update_bars(strategy_symbols, "15Min", limit=1)
api = TradeApiRest()
for strategy in strategies:
total_bars_count = fetch_bar_data_for_strategy(strategy)
if not total_bars_count:
logger.info(
f"Insufficient bar data for asset: {strategy.asset.id}")
continue
# Calculate moving average and conditionally place order
annotated_bars = Bar.objects.filter(
asset_id=strategy.asset.id).annotate(moving_average=Window(
expression=Avg("c"),
order_by=F("t").desc(),
frame=RowRange(start=0, end=int(total_bars_count - 1)),
), )
latest_bar = annotated_bars.first()
previous_bar = annotated_bars[1]
symbol = strategy.asset.symbol
if (latest_bar.c >= latest_bar.moving_average
and previous_bar.c < previous_bar.moving_average):
side = Order.BUY
account = api.account_info()
trade_value = min(float(strategy.trade_value),
float(account.__dict__["_raw"]["equity"]))
elif (latest_bar.c <= latest_bar.moving_average
and previous_bar.c > previous_bar.moving_average):
side = Order.SELL
try:
position = api.list_position_by_symbol(symbol)
except APIError:
logger.info(
f"No position exists, unable to sell: {strategy.asset.id}")
continue
else:
if position.__dict__["_raw"]["side"] == "short":
logger.info(
f"Asset is long, unable to sell: {strategy.asset.id}")
continue
trade_value = min(
float(strategy.trade_value),
float(position.__dict__["_raw"]["market_value"]),
)
else:
# No order required with current quote
logger.info(
f"No order placed. symbol, close price, moving average {symbol}, {latest_bar.c}, {latest_bar.moving_average}"
)
continue
try:
order = api.submit_order(
symbol=symbol,
notional=trade_value,
side=side,
type=Order.MARKET,
time_in_force=Order.GTC,
)
logger.info(
f"Order placed. symbol, side, close, moving_average: {symbol}, {side}, {latest_bar.c}, {latest_bar.moving_average}"
)
except Exception as e:
logger.warning(f"Tradeview order failed: {e}")
return
raw_order = order.__dict__["_raw"]
order = Order.objects.create(
user=user,
id=raw_order.get("id"),
client_order_id=raw_order.get("client_order_id"),
created_at=raw_order.get("created_at"),
updated_at=raw_order.get("updated_at"),
submitted_at=raw_order.get("submitted_at"),
filled_at=raw_order.get("filled_at"),
expired_at=raw_order.get("expired_at"),
canceled_at=raw_order.get("canceled_at"),
failed_at=raw_order.get("failed_at"),
replaced_at=raw_order.get("replaced_at"),
replaced_by=raw_order.get("replaced_by"),
replaces=raw_order.get("replaces"),
asset_id=strategy.asset,
notional=raw_order.get("notional"),
qty=raw_order.get("qty"),
filled_qty=raw_order.get("filled_qty"),
filled_avg_price=raw_order.get("filled_avg_price"),
order_class=raw_order.get("order_class"),
type=raw_order.get("type"),
side=raw_order.get("side"),
time_in_force=raw_order.get("time_in_force"),
limit_price=raw_order.get("limit_price"),
stop_price=raw_order.get("stop_price"),
status=raw_order.get("status"),
extended_hours=raw_order.get("extended_hours"),
trail_percent=raw_order.get("trail_percent"),
trail_price=raw_order.get("trail_price"),
hwm=raw_order.get("hwm"),
)
legs = raw_order.get("legs")
if legs:
order.legs.set(legs)