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