def test_duplicate_aggregation_columns(query, sql_query_describer):
with pytest.raises(DuplicateColumnError):
step = AggregateStep(
name='aggregate',
on=['Group'],
aggregations=[
Aggregation(
aggfunction='count distinct including empty',
columns=['Group', 'Group'],
newcolumns=['coucou'],
),
],
)
translate_aggregate(step, query, index=1)
def test_with_group_by_with_aggregation_with_last_with_first(
query, sql_query_describer):
step = AggregateStep(
name='aggregate',
on=['category'],
aggregations=[
Aggregation(aggfunction='last',
columns=['Label'],
newcolumns=['last_label']),
Aggregation(aggfunction='first',
columns=['title'],
newcolumns=['first_title']),
Aggregation(aggfunction='sum',
columns=['title'],
newcolumns=['sum_title']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT A.*, F.first_title, F.last_label FROM (SELECT SUM(title) AS sum_title, category FROM SELECT_STEP_0 GROUP BY category) A INNER JOIN (SELECT first_title, last_label, category FROM (SELECT title AS first_title, Label AS last_label, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY title, Label DESC) AS R FROM SELECT_STEP_0 QUALIFY R = 1)) F ON A.category=F.category)'
)
def test_last_no_group_by_no_aggregation_no_first(query, sql_query_describer):
step = AggregateStep(
name='aggregate',
aggregations=[
Aggregation(aggfunction='last',
columns=['Label'],
newcolumns=['last_Label']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT last_Label FROM (SELECT Label AS last_Label, ROW_NUMBER() OVER (ORDER BY Label DESC) AS R FROM SELECT_STEP_0 QUALIFY R = 1))'
)
def test_count(query, sql_query_describer):
step = AggregateStep(
name='aggregate',
on=['category'],
aggregations=[
Aggregation(aggfunction='count',
columns=['Label'],
newcolumns=['count']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT COUNT(Label) AS count, category FROM SELECT_STEP_0 GROUP BY category)'
)
def test_first_no_aggregation_with_groupby(query, sql_query_describer):
step = AggregateStep(
name='aggregate',
on=['category'],
aggregations=[
Aggregation(aggfunction='first',
columns=['Label'],
newcolumns=['first_Label']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT first_Label, category FROM (SELECT Label AS first_Label, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY Label) AS R FROM SELECT_STEP_0 QUALIFY R = 1))'
)
def test_count_distinct(query, sql_query_describer):
step = AggregateStep(
name='aggregate',
on=[],
aggregations=[
Aggregation(
aggfunction='count distinct',
columns=['Group'],
newcolumns=['Group_CD'],
)
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT COUNT(DISTINCT Group) AS Group_CD FROM SELECT_STEP_0)'
)
def test_last_no_group_by_with_aggregation_with_first(query,
sql_query_describer):
step = AggregateStep(
name='aggregate',
aggregations=[
Aggregation(aggfunction='first',
columns=['Label'],
newcolumns=['first_label']),
Aggregation(aggfunction='sum',
columns=['title'],
newcolumns=['sum_title']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT A.*, F.first_label FROM (SELECT SUM(title) AS sum_title FROM SELECT_STEP_0) A INNER JOIN (SELECT first_label FROM (SELECT Label AS first_label, ROW_NUMBER() OVER (ORDER BY Label) AS R FROM SELECT_STEP_0 QUALIFY R = 1)) F)'
)
def test_translate_aggregate_with_group_by(query, sql_query_describer):
step = AggregateStep(
name='aggregate',
on=['category'],
aggregations=[
Aggregation(
aggfunction='sum',
columns=['Value1', 'Value2'],
newcolumns=['Sum_Value1', 'Sum_Value2'],
),
Aggregation(aggfunction='avg',
columns=['Value1'],
newcolumns=['Avg_Value1']),
],
)
sql_query = translate_aggregate(step,
query,
index=1,
sql_query_describer=sql_query_describer)
assert (
sql_query.transformed_query ==
'WITH SELECT_STEP_0 AS (SELECT * FROM products), AGGREGATE_STEP_1 AS (SELECT SUM(Value1) AS Sum_Value1, SUM(Value2) AS Sum_Value2, AVG(Value1) AS Avg_Value1, category FROM SELECT_STEP_0 GROUP BY category)'
)