def test_query_window_partition_order_many(self): query_window = QueryWindow().partition_by('field_one').partition_by( 'field_two').order_by('field_one').order_by('-field_two') query_str = query_window.get_sql() expected_query = 'OVER (PARTITION BY field_one, field_two ORDER BY field_one ASC, field_two DESC)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_query_window_partition_order(self): query_window = QueryWindow().partition_by( 'field_one' ).order_by( 'field_one' ) query_str = query_window.get_sql() expected_query = 'OVER (PARTITION BY field_one ORDER BY field_one ASC)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_over(self): query = Query().from_table(table=Order, fields=[RankField(over=QueryWindow())]) query_str = query.get_sql() expected_query = 'SELECT RANK() OVER () AS rank FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_over_order(self): query = Query().from_table( table=Order, fields=['id', RankField(over=QueryWindow().order_by('id'))]) query_str = query.get_sql() expected_query = 'SELECT tests_order.id, RANK() OVER (ORDER BY id ASC) AS rank FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_over_partition(self): query = Query().from_table( table=Order, fields=[ 'id', RankField(over=QueryWindow().partition_by('account_id')) ]) query_str = query.get_sql() expected_query = 'SELECT tests_order.id, RANK() OVER (PARTITION BY account_id) AS rank FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_lead(self): query = Query().from_table( table=Order, fields=[ '*', LeadField('margin', over=QueryWindow().order_by('-margin')) ]) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'LEAD(tests_order.margin, 1) OVER (ORDER BY margin DESC) AS margin_lead ' 'FROM tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_last_value(self): query = Query().from_table( table=Order, fields=[ '*', LastValueField('margin', over=QueryWindow().order_by('margin')) ]) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'LAST_VALUE(tests_order.margin) OVER (ORDER BY margin ASC) AS margin_last_value ' 'FROM tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_first_value(self): query = Query().from_table( table=Order, fields=[ '*', FirstValueField('margin', over=QueryWindow().order_by('-margin')) ]) query_str = query.get_sql() expected_query = ( 'SELECT querybuilder_tests_order.*, ' 'FIRST_VALUE(querybuilder_tests_order.margin) OVER (ORDER BY margin DESC) AS "margin_first_value" ' 'FROM querybuilder_tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def lead_lag_difference_test(self): query = Query().from_table( table=Order, fields=[ 'margin', LagDifferenceField('margin', over=QueryWindow().order_by('-margin')), LeadDifferenceField('margin', over=QueryWindow().order_by('-margin')), ]) expected_query = ( 'SELECT tests_order.margin, ' '((tests_order.margin) - (LAG(tests_order.margin, 1) ' 'OVER (ORDER BY margin DESC))) AS margin_lag, ' '((tests_order.margin) - (LEAD(tests_order.margin, 1) ' 'OVER (ORDER BY margin DESC))) AS margin_lead ' 'FROM tests_order') self.assertEqual(expected_query, query.get_sql()) rows = query.select() self.assertEqual(4, len(rows)) self.assertEqual(None, rows[0]['margin_lag']) self.assertEqual(500.0, rows[0]['margin_lead']) self.assertEqual(-75.0, rows[3]['margin_lag']) self.assertEqual(None, rows[3]['margin_lead'])
def test_cume_dist(self): query = Query().from_table( table=Order, fields=[ '*', CumeDistField(over=QueryWindow().order_by('-margin')) ]).order_by('cume_dist') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'CUME_DIST() OVER (ORDER BY margin DESC) AS cume_dist ' 'FROM tests_order ' 'ORDER BY cume_dist ' 'ASC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_percent(self): query = Query().from_table( table=Order, fields=[ '*', PercentRankField(over=QueryWindow().order_by('-margin')) ]).order_by('percent_rank') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'PERCENT_RANK() OVER (ORDER BY margin DESC) AS percent_rank ' 'FROM tests_order ' 'ORDER BY percent_rank ' 'ASC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_dense_rank(self): query = Query().from_table( table=Order, fields=[ '*', DenseRankField(over=QueryWindow().order_by('-margin')) ]).order_by('dense_rank') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'DENSE_RANK() OVER (ORDER BY margin DESC) AS dense_rank ' 'FROM tests_order ' 'ORDER BY dense_rank ' 'ASC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_lag_default(self): query = Query().from_table( table=Order, fields=[ '*', LagField('margin', default=0, over=QueryWindow().order_by('-margin')) ]) query_str = query.get_sql() expected_query = ( 'SELECT querybuilder_tests_order.*, ' 'LAG(querybuilder_tests_order.margin, 1, \'0\') OVER (ORDER BY margin DESC) AS "margin_lag" ' 'FROM querybuilder_tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_nth_value(self): query = Query().from_table( table=Order, fields=[ '*', NthValueField('margin', n=2, over=QueryWindow().order_by('-margin')) ]) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'NTH_VALUE(tests_order.margin, 2) OVER (ORDER BY margin DESC) AS "margin_nth_value" ' 'FROM tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_ntile(self): query = Query().from_table( table=Order, fields=[ '*', NTileField(num_buckets=2, over=QueryWindow().order_by('-margin')) ]).order_by('ntile') query_str = query.get_sql() expected_query = ('SELECT tests_order.*, ' 'NTILE(2) OVER (ORDER BY margin DESC) AS ntile ' 'FROM tests_order ' 'ORDER BY ntile ' 'ASC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_row_number(self): query = Query().from_table( table=Order, fields=[ '*', RowNumberField(over=QueryWindow().order_by('-margin')) ]).order_by('row_number') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' 'ROW_NUMBER() OVER (ORDER BY margin DESC) AS row_number ' 'FROM tests_order ' 'ORDER BY row_number ' 'ASC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank(self): query = Query().from_table( table=Order, fields=[ 'id', RankField(over=QueryWindow().partition_by( 'account_id').order_by('id')) ]).order_by('-rank') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.id, ' 'RANK() OVER (PARTITION BY account_id ORDER BY id ASC) AS rank ' 'FROM tests_order ' 'ORDER BY rank ' 'DESC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_num_stddev(self): query = Query().from_table(table=Order, fields=[ '*', NumStdDevField('margin', over=QueryWindow()) ]).order_by('-margin_num_stddev') query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' '(CASE WHEN (STDDEV(tests_order.margin) OVER ()) <> 0 ' 'THEN ((tests_order.margin - (' 'AVG(tests_order.margin) OVER ())) / (STDDEV(tests_order.margin) OVER ())) ' 'ELSE 0 ' 'END) ' 'AS margin_num_stddev ' 'FROM tests_order ' 'ORDER BY margin_num_stddev ' 'DESC') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_query_window(self): query_window = QueryWindow() query_str = query_window.get_sql() expected_query = 'OVER ()' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def get_forests_for_csv(forest_ids: list = None): forestcustomercontact_rank_query = Query().from_table( ForestCustomerContact, [ "forestcustomer_id", "customercontact_id", RowNumberField( alias="rank", over=QueryWindow() .order_by( "coalesce(attributes->>'default', 'false')", desc=True, nulls_last=True, ) .order_by("created_at") .partition_by("forestcustomer_id"), ), ], ) forestcustomer_rank_query = Query().from_table( ForestCustomer, [ "id", "forest_id", "customer_id", RowNumberField( alias="rank", over=QueryWindow() .order_by( "coalesce(attributes->>'default', 'false')", desc=True, nulls_last=True, ) .order_by("created_at") .partition_by("forest_id"), ), ], ) forestcustomer_query = ( Query() .from_table("fc_rank", fields=["forest_id"]) .join(Customer, condition="crm_customer.id = fc_rank.customer_id") .join( CustomerContact, condition="crm_customercontact.customer_id = crm_customer.id " "and crm_customercontact.is_basic = true", ) .join( Contact, condition="crm_customercontact.contact_id = crm_contact.id", fields=[ {"customer_name_kanji": "name_kanji"}, {"customer_name_kana": "name_kana"}, {"customer_address": "address"}, ], ) .join( "fcc", condition="fc_rank.id = fcc.forestcustomer_id and fcc.rank = 1", join_type="LEFT JOIN", ) .join( {"contact_cc": CustomerContact}, condition="contact_cc.id = fcc.customercontact_id " "and contact_cc.id != crm_customercontact.id", join_type="LEFT JOIN", ) .join( {"contact_c": Contact}, join_type="LEFT JOIN", condition="contact_cc.contact_id = contact_c.id", fields=[ {"contact_name_kanji": "name_kanji"}, {"contact_name_kana": "name_kana"}, {"contact_address": "address"}, ], ) .where(**{"fc_rank.rank": 1}) ) queryset = ( Query() .with_query(forestcustomer_query, alias="fc") .with_query(forestcustomercontact_rank_query, alias="fcc") .with_query(forestcustomer_rank_query, alias="fc_rank") .from_table( {"f": Forest}, fields=[ "land_attributes", "cadastral", "id", "internal_id", "tags", "forest_attributes", "contracts", ], ) .join( "fc", condition="fc.forest_id = f.id", join_type="LEFT JOIN", fields=[ "customer_name_kanji", "customer_name_kana", "customer_address", "contact_name_kanji", "contact_name_kana", "contact_address", ], ) ) if forest_ids is not None and len(forest_ids) > 0: queryset = queryset.where(id__in=forest_ids) try: return queryset.select() except ProgrammingError: return []
def test_query_window_partition(self): query_window = QueryWindow().partition_by('field_one') query_str = query_window.get_sql() expected_query = 'OVER (PARTITION BY field_one)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_query_window_order(self): query_window = QueryWindow().order_by('field_one') query_str = query_window.get_sql() expected_query = 'OVER (ORDER BY field_one ASC)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))