def test_last_value_multi_argument(self):
expr = an.LastValue(self.table_abc.fizz, self.table_abc.buzz) \
.over(self.table_abc.foo) \
.orderby(self.table_abc.date)
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
'SELECT '
'LAST_VALUE("fizz","buzz") '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"', str(q))
def test_orderby_desc(self):
expr = an.LastValue(self.table_abc.fizz) \
.over(self.table_abc.foo) \
.orderby(self.table_abc.date, order=Order.desc)
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
'SELECT '
'LAST_VALUE("fizz") '
'OVER(PARTITION BY "foo" ORDER BY "date" DESC) '
'FROM "abc"', str(q))
def test_last_value_ignore_nulls(self):
expr = an.LastValue(self.table_abc.fizz) \
.over(self.table_abc.foo) \
.orderby(self.table_abc.date) \
.ignore_nulls()
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
'SELECT '
'LAST_VALUE("fizz" IGNORE NULLS) '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"', str(q))
def test_last_value(self):
expr = an.LastValue(self.table_abc.fizz).over(
self.table_abc.foo).orderby(self.table_abc.date)
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
"SELECT "
'LAST_VALUE("fizz") '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"',
str(q),
)
def test_filter(self):
expr = (an.LastValue(self.table_abc.fizz).filter(
Criterion.all([self.table_abc.bar == True])).over(
self.table_abc.foo).orderby(self.table_abc.date))
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
"SELECT "
'LAST_VALUE("fizz") '
'FILTER(WHERE "bar"=true) '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"',
str(q),
)
def test_last_value_rows_between_current_row_unbounded_following(self):
expr = an.LastValue(self.table_abc.fizz) \
.over(self.table_abc.foo) \
.orderby(self.table_abc.date) \
.rows(an.CURRENT_ROW, an.Following())
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
'SELECT '
'LAST_VALUE("fizz") '
'OVER('
'PARTITION BY "foo" ORDER BY "date" '
'ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING'
') '
'FROM "abc"', str(q))
def test_filter_quote_table_in_filter(self):
expr = (an.LastValue(self.table_efg.fizz).filter(
self.table_efg.filed.eq('yes')).over(self.table_efg.foo).orderby(
self.table_efg.date))
q = (Query.from_(self.table_abc).inner_join(self.table_efg).on(
self.table_abc.id.eq(self.table_efg.id)).select(expr))
self.assertEqual(
'SELECT '
'LAST_VALUE("efg"."fizz") '
'FILTER(WHERE "efg"."filed"=\'yes\') '
'OVER(PARTITION BY "efg"."foo" ORDER BY "efg"."date") '
'FROM "abc" '
'JOIN "efg" ON "abc"."id"="efg"."id"',
str(q),
)
def test_last_value_rows_between_current_row_unbounded_following_ignore_nulls(
self):
expr = (an.LastValue(self.table_abc.fizz).over(
self.table_abc.foo).orderby(
self.table_abc.date).ignore_nulls().rows(
an.CURRENT_ROW, an.Following(8)))
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
"SELECT "
'LAST_VALUE("fizz" IGNORE NULLS) '
"OVER("
'PARTITION BY "foo" ORDER BY "date" '
"ROWS BETWEEN CURRENT ROW AND 8 FOLLOWING"
") "
'FROM "abc"',
str(q),
)
