Skip to content

Commit 3ce0ade

Browse files
rivudhkrivudhk
committed
py: add aggregate tests for interval months
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent c5557cd commit 3ce0ade

File tree

6 files changed

+682
-0
lines changed

6 files changed

+682
-0
lines changed

‎python/tests/runtime_aggtest/aggregate_tests2/test_atbl_interval.py‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,3 +130,16 @@ def __init__(self):
130130
# EXTRACT(EPOCH FROM f_c5) AS m_c5_seconds,
131131
# EXTRACT(EPOCH FROM f_c6) AS m_c6_seconds
132132
# FROM agg_view;
133+
134+
# For interval months: 30.436875 days × 24 hours × 3600 seconds = 2629746 seconds
135+
# 30.436875 days = avg number of days in a month given a calendar year contains 365.2425 days
136+
# (Gregorian Calendar) : https://en.wikipedia.org/wiki/Gregorian_calendar
137+
#
138+
# SELECT
139+
# EXTRACT(EPOCH FROM f_c1 / 2629746) AS m_c1_seconds,
140+
# EXTRACT(EPOCH FROM f_c2 / 2629746) AS m_c2_seconds,
141+
# EXTRACT(EPOCH FROM f_c3 / 2629746) AS m_c3_seconds,
142+
# EXTRACT(EPOCH FROM f_c4 / 2629746) AS m_c4_seconds,
143+
# EXTRACT(EPOCH FROM f_c5 / 2629746) AS m_c5_seconds,
144+
# EXTRACT(EPOCH FROM f_c6 / 2629746) AS m_c6_seconds
145+
# FROM agg_view;

‎python/tests/runtime_aggtest/aggregate_tests6/main.py‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,10 @@
3333
from test_varbinary_arg_min_append import * # noqa: F403
3434
from test_varbinary_max_append import * # noqa: F403
3535
from test_varbinary_min_append import * # noqa: F403
36+
from test_interval_count_mths import * # noqa: F403
37+
from test_interval_count_col_mths import * # noqa: F403
38+
from test_interval_mths_min import * # noqa: F403
39+
from test_interval_mths_max import * # noqa: F403
3640

3741
base.APPEND_ONLY = True
3842

‎python/tests/runtime_aggtest/aggregate_tests6/test_interval_count_col_mths.py‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
from tests.runtime_aggtest.aggtst_base import TstView
2+
3+
4+
class aggtst_interval_count_col_mths(TstView):
5+
def __init__(self):
6+
# Validated on Postgres
7+
self.data = [{"f_c1": 5, "f_c2": 5, "f_c3": 5, "f_c4": 5, "f_c5": 5, "f_c6": 5}]
8+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_mths AS SELECT
9+
COUNT(c1_minus_c2) AS f_c1,
10+
COUNT(c2_minus_c1) AS f_c2,
11+
COUNT(c1_minus_c3) AS f_c3,
12+
COUNT(c3_minus_c1) AS f_c4,
13+
COUNT(c2_minus_c3) AS f_c5,
14+
COUNT(c3_minus_c2) AS f_c6
15+
FROM atbl_interval_months"""
16+
17+
18+
class aggtst_interval_count_col_gby_mths(TstView):
19+
def __init__(self):
20+
# Validated on Postgres
21+
self.data = [
22+
{"id": 0, "f_c1": 2, "f_c2": 2, "f_c3": 2, "f_c4": 2, "f_c5": 2, "f_c6": 2},
23+
{"id": 1, "f_c1": 3, "f_c2": 3, "f_c3": 3, "f_c4": 3, "f_c5": 3, "f_c6": 3},
24+
]
25+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_gby_mths AS SELECT
26+
id,
27+
COUNT(c1_minus_c2) AS f_c1,
28+
COUNT(c2_minus_c1) AS f_c2,
29+
COUNT(c1_minus_c3) AS f_c3,
30+
COUNT(c3_minus_c1) AS f_c4,
31+
COUNT(c2_minus_c3) AS f_c5,
32+
COUNT(c3_minus_c2) AS f_c6
33+
FROM atbl_interval_months
34+
GROUP BY id"""
35+
36+
37+
class aggtst_interval_count_col_distinct_mths(TstView):
38+
def __init__(self):
39+
# Validated on Postgres
40+
self.data = [{"f_c1": 4, "f_c2": 4, "f_c3": 5, "f_c4": 5, "f_c5": 5, "f_c6": 5}]
41+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_distinct_mths AS SELECT
42+
COUNT(DISTINCT c1_minus_c2) AS f_c1,
43+
COUNT(DISTINCT c2_minus_c1) AS f_c2,
44+
COUNT(DISTINCT c1_minus_c3) AS f_c3,
45+
COUNT(DISTINCT c3_minus_c1) AS f_c4,
46+
COUNT(DISTINCT c2_minus_c3) AS f_c5,
47+
COUNT(DISTINCT c3_minus_c2) AS f_c6
48+
FROM atbl_interval_months"""
49+
50+
51+
class aggtst_interval_count_col_distinct_gby_mths(TstView):
52+
def __init__(self):
53+
# Validated on Postgres
54+
self.data = [
55+
{"id": 0, "f_c1": 2, "f_c2": 2, "f_c3": 2, "f_c4": 2, "f_c5": 2, "f_c6": 2},
56+
{"id": 1, "f_c1": 3, "f_c2": 3, "f_c3": 3, "f_c4": 3, "f_c5": 3, "f_c6": 3},
57+
]
58+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_distinct_gby_mths AS SELECT
59+
id,
60+
COUNT(DISTINCT c1_minus_c2) AS f_c1,
61+
COUNT(DISTINCT c2_minus_c1) AS f_c2,
62+
COUNT(DISTINCT c1_minus_c3) AS f_c3,
63+
COUNT(DISTINCT c3_minus_c1) AS f_c4,
64+
COUNT(DISTINCT c2_minus_c3) AS f_c5,
65+
COUNT(DISTINCT c3_minus_c2) AS f_c6
66+
FROM atbl_interval_months
67+
GROUP BY id"""
68+
69+
70+
class aggtst_interval_count_col_where_mths(TstView):
71+
def __init__(self):
72+
# Validated on Postgres
73+
self.data = [{"f_c1": 3, "f_c2": 3, "f_c3": 1, "f_c4": 1, "f_c5": 2, "f_c6": 2}]
74+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_where_mths AS SELECT
75+
COUNT(c1_minus_c2) FILTER(WHERE c1_minus_c2 > c2_minus_c1) AS f_c1,
76+
COUNT(c2_minus_c1) FILTER(WHERE c1_minus_c2 > c2_minus_c1) AS f_c2,
77+
COUNT(c1_minus_c3) FILTER(WHERE c1_minus_c3 > c3_minus_c1) AS f_c3,
78+
COUNT(c3_minus_c1) FILTER(WHERE c1_minus_c3 > c3_minus_c1) AS f_c4,
79+
COUNT(c2_minus_c3) FILTER(WHERE c2_minus_c3 > c3_minus_c2) AS f_c5,
80+
COUNT(c3_minus_c2) FILTER(WHERE c2_minus_c3 > c3_minus_c2) AS f_c6
81+
FROM atbl_interval_months"""
82+
83+
84+
class aggtst_interval_count_col_where_groupby_mths(TstView):
85+
def __init__(self):
86+
# Validated on Postgres
87+
self.data = [
88+
{"id": 0, "f_c1": 1, "f_c2": 1, "f_c3": 0, "f_c4": 0, "f_c5": 1, "f_c6": 1},
89+
{"id": 1, "f_c1": 2, "f_c2": 2, "f_c3": 1, "f_c4": 1, "f_c5": 1, "f_c6": 1},
90+
]
91+
self.sql = """CREATE MATERIALIZED VIEW interval_count_col_where_gby_mths AS SELECT
92+
id,
93+
COUNT(c1_minus_c2) FILTER(WHERE c1_minus_c2 > c2_minus_c1) AS f_c1,
94+
COUNT(c2_minus_c1) FILTER(WHERE c1_minus_c2 > c2_minus_c1) AS f_c2,
95+
COUNT(c1_minus_c3) FILTER(WHERE c1_minus_c3 > c3_minus_c1) AS f_c3,
96+
COUNT(c3_minus_c1) FILTER(WHERE c1_minus_c3 > c3_minus_c1) AS f_c4,
97+
COUNT(c2_minus_c3) FILTER(WHERE c2_minus_c3 > c3_minus_c2) AS f_c5,
98+
COUNT(c3_minus_c2) FILTER(WHERE c2_minus_c3 > c3_minus_c2) AS f_c6
99+
FROM atbl_interval_months
100+
GROUP BY id"""

‎python/tests/runtime_aggtest/aggregate_tests6/test_interval_count_mths.py‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
from tests.runtime_aggtest.aggtst_base import TstView
2+
3+
4+
class aggtst_interval_count_mths(TstView):
5+
def __init__(self):
6+
# checked manually
7+
self.data = [{"count": 5}]
8+
self.sql = """CREATE MATERIALIZED VIEW interval_count_mths AS SELECT
9+
COUNT(*) AS count
10+
FROM atbl_interval_months"""
11+
12+
13+
class aggtst_interval_count_groupby_mths(TstView):
14+
def __init__(self):
15+
# checked manually
16+
self.data = [{"id": 0, "count": 2}, {"id": 1, "count": 3}]
17+
self.sql = """CREATE MATERIALIZED VIEW interval_count_gby_mths AS SELECT
18+
id, COUNT(*) AS count
19+
FROM atbl_interval_months
20+
GROUP BY id"""
21+
22+
23+
class aggtst_interval_count_where_mths(TstView):
24+
def __init__(self):
25+
# checked manually
26+
self.data = [{"count": 2}]
27+
self.sql = """CREATE MATERIALIZED VIEW interval_count_where_mths AS SELECT
28+
COUNT(*) FILTER(WHERE c2_minus_c1 > c1_minus_c2) AS count
29+
FROM atbl_interval_months"""
30+
31+
32+
class aggtst_interval_count_where_groupby_mths(TstView):
33+
def __init__(self):
34+
# checked manually
35+
self.data = [{"id": 0, "count": 1}, {"id": 1, "count": 1}]
36+
self.sql = """CREATE MATERIALIZED VIEW interval_count_where_gby_mths AS SELECT
37+
id, COUNT(*) FILTER(WHERE c2_minus_c1 > c1_minus_c2) AS count
38+
FROM atbl_interval_months
39+
GROUP BY id"""

0 commit comments

Comments
 (0)