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), )