def test_aggregate_funcs__or_default_if(self):
self._test_aggr(
F.argMinOrDefaultIf(Person.first_name, Person.height,
Person.last_name > 'Z'))
self._test_aggr(F.countOrDefaultIf(Person.last_name > 'Z'), 0)
self._test_aggr(
F.minOrDefaultIf(Person.height, Person.last_name > 'Z'), 0)
def test_arithmetic_operators(self):
one = F.plus(1, 0)
two = F.plus(1, 1)
# +
self._test_func(one + two, 3)
self._test_func(one + 2, 3)
self._test_func(2 + one, 3)
# -
self._test_func(one - two, -1)
self._test_func(one - 2, -1)
self._test_func(1 - two, -1)
# *
self._test_func(one * two, 2)
self._test_func(one * 2, 2)
self._test_func(1 * two, 2)
# /
self._test_func(one / two, 0.5)
self._test_func(one / 2, 0.5)
self._test_func(1 / two, 0.5)
# //
self._test_func(one // two, 0)
self._test_func(two // one, 2)
self._test_func(one // 2, 0)
self._test_func(1 // two, 0)
# %
self._test_func(one % two, 1)
self._test_func(one % 2, 1)
self._test_func(1 % two, 1)
# sign
self._test_func(-one, -1)
self._test_func(--one, 1)
self._test_func(+one, 1)
def test_aggregate_funcs__or_null_if(self):
self._test_aggr(
F.argMinOrNullIf(Person.first_name, Person.height,
Person.last_name > 'Z'))
self._test_aggr(F.countOrNullIf(Person.last_name > 'Z'), None)
self._test_aggr(F.minOrNullIf(Person.height, Person.last_name > 'Z'),
None)
def test_uuid_functions(self):
from uuid import UUID
uuid = self._test_func(F.generateUUIDv4())
self.assertEqual(type(uuid), UUID)
s = str(uuid)
self._test_func(F.toUUID(s), uuid)
self._test_func(F.UUIDNumToString(F.UUIDStringToNum(s)), s)
def test_base64_functions(self):
try:
self._test_func(F.base64Decode(F.base64Encode('Hello')), 'Hello')
self._test_func(F.tryBase64Decode(F.base64Encode('Hello')),
'Hello')
self._test_func(F.tryBase64Decode(':-)'))
except ServerError as e:
# ClickHouse version that doesn't support these functions
raise unittest.SkipTest(e.message)
def test_filter_float_field(self):
qs = Person.objects_in(self.database)
# Height > 2
self._test_qs(qs.filter(F.greater(Person.height, 2)), 0)
self._test_qs(qs.filter(Person.height > 2), 0)
# Height > 1.61
self._test_qs(qs.filter(F.greater(Person.height, 1.61)), 96)
self._test_qs(qs.filter(Person.height > 1.61), 96)
# Height < 1.61
self._test_qs(qs.filter(F.less(Person.height, 1.61)), 4)
self._test_qs(qs.filter(Person.height < 1.61), 4)
def test_type_conversion_functions__utc_only(self):
if self.database.server_timezone != pytz.utc:
raise unittest.SkipTest(
'This test must run with UTC as the server timezone')
self._test_func(F.toDateTime('2018-12-31 11:22:33'),
datetime(2018, 12, 31, 11, 22, 33, tzinfo=pytz.utc))
self._test_func(F.parseDateTimeBestEffort('31/12/2019 10:05AM'),
datetime(2019, 12, 31, 10, 5, tzinfo=pytz.utc))
self._test_func(F.parseDateTimeBestEffortOrNull('31/12/2019 10:05AM'),
datetime(2019, 12, 31, 10, 5, tzinfo=pytz.utc))
self._test_func(F.parseDateTimeBestEffortOrZero('31/12/2019 10:05AM'),
datetime(2019, 12, 31, 10, 5, tzinfo=pytz.utc))
def test_top_k_funcs(self):
self._test_aggr(F.topK(3)(Person.height))
self._test_aggr(F.topKOrDefault(3)(Person.height))
self._test_aggr(F.topKIf(3)(Person.height, Person.last_name > 'H'))
self._test_aggr(
F.topKOrDefaultIf(3)(Person.height, Person.last_name > 'H'))
weight_expr = F.toUInt32(F.round(Person.height))
self._test_aggr(F.topKWeighted(3)(Person.height, weight_expr))
self._test_aggr(F.topKWeightedOrDefault(3)(Person.height, weight_expr))
self._test_aggr(
F.topKWeightedIf(3)(Person.height, weight_expr,
Person.last_name > 'H'))
self._test_aggr(
F.topKWeightedOrDefaultIf(3)(Person.height, weight_expr,
Person.last_name > 'H'))
def test_comparison_operators(self):
one = F.plus(1, 0)
two = F.plus(1, 1)
self._test_func(one > one, 0)
self._test_func(two > one, 1)
self._test_func(one >= two, 0)
self._test_func(one >= one, 1)
self._test_func(one < one, 0)
self._test_func(one < two, 1)
self._test_func(two <= one, 0)
self._test_func(one <= one, 1)
self._test_func(one == two, 0)
self._test_func(one == one, 1)
self._test_func(one != one, 0)
self._test_func(one != two, 1)
def test_limit_by(self):
if self.database.server_version < (19, 17):
raise unittest.SkipTest('ClickHouse version too old')
# Test without offset
qs = Person.objects_in(self.database).aggregate('first_name', 'last_name', 'height', n='count()').\
order_by('first_name', '-height').limit_by(1, 'first_name')
self.assertEqual(qs.count(), 94)
self.assertEqual(list(qs)[89].last_name, 'Bowen')
# Test with funcs and fields
qs = Person.objects_in(self.database).aggregate(Person.first_name, Person.last_name, Person.height, n=F.count()).\
order_by(Person.first_name, '-height').limit_by(1, F.upper(Person.first_name))
self.assertEqual(qs.count(), 94)
self.assertEqual(list(qs)[89].last_name, 'Bowen')
# Test with limit and offset, also mixing LIMIT with LIMIT BY
qs = Person.objects_in(self.database).filter(height__gt=1.67).order_by(
'height', 'first_name')
limited_qs = qs.limit_by((0, 3), 'height')
self.assertEqual([p.first_name for p in limited_qs[:3]],
['Amanda', 'Buffy', 'Dora'])
limited_qs = qs.limit_by((3, 3), 'height')
self.assertEqual([p.first_name for p in limited_qs[:3]],
['Elton', 'Josiah', 'Macaulay'])
limited_qs = qs.limit_by((6, 3), 'height')
self.assertEqual([p.first_name for p in limited_qs[:3]],
['Norman', 'Octavius', 'Oliver'])
def test_filter_date_field(self):
qs = Person.objects_in(self.database)
# People born on the 30th
self._test_qs(
qs.filter(F('equals', F('toDayOfMonth', Person.birthday), 30)), 3)
self._test_qs(qs.filter(F('toDayOfMonth', Person.birthday) == 30), 3)
self._test_qs(qs.filter(F.toDayOfMonth(Person.birthday) == 30), 3)
# People born on Sunday
self._test_qs(
qs.filter(F('equals', F('toDayOfWeek', Person.birthday), 7)), 18)
self._test_qs(qs.filter(F('toDayOfWeek', Person.birthday) == 7), 18)
self._test_qs(qs.filter(F.toDayOfWeek(Person.birthday) == 7), 18)
# People born on 1976-10-01
self._test_qs(qs.filter(F('equals', Person.birthday, '1976-10-01')), 1)
self._test_qs(
qs.filter(F('equals', Person.birthday, date(1976, 10, 1))), 1)
self._test_qs(qs.filter(Person.birthday == date(1976, 10, 1)), 1)
def test_aggregate_no_grouping(self):
qs = Person.objects_in(self.database).aggregate(
average_height='avg(height)', count='count()')
print(qs.as_sql())
self.assertEqual(qs.count(), 1)
for row in qs:
self.assertAlmostEqual(row.average_height, 1.6923, places=4)
self.assertEqual(row.count, 100)
# With functions
qs = Person.objects_in(self.database).aggregate(average_height=F.avg(
Person.height),
count=F.count())
print(qs.as_sql())
self.assertEqual(qs.count(), 1)
for row in qs:
self.assertAlmostEqual(row.average_height, 1.6923, places=4)
self.assertEqual(row.count, 100)
def test_mixed_filter(self):
qs = Person.objects_in(self.database)
qs = qs.filter(Q(first_name='a'),
F('greater', Person.height, 1.7),
last_name='b')
self.assertEqual(
qs.conditions_as_sql(),
"(first_name = 'a') AND (greater(`height`, 1.7)) AND (last_name = 'b')"
)
def test_null_funcs(self):
self._test_func(F.ifNull(17, 18), 17)
self._test_func(F.ifNull(None, 18), 18)
self._test_func(F.nullIf(17, 18), 17)
self._test_func(F.nullIf(18, 18), None)
self._test_func(F.isNotNull(17), 1)
self._test_func(F.isNull(17), 0)
self._test_func(F.coalesce(None, None, 17, 18), 17)
def test_aggregate_with_filter__funcs(self):
# When filter comes before aggregate
qs = Person.objects_in(
self.database).filter(Person.first_name == 'Warren').aggregate(
average_height=F.avg(Person.height), count=F.count())
print(qs.as_sql())
self.assertEqual(qs.count(), 1)
for row in qs:
self.assertAlmostEqual(row.average_height, 1.675, places=4)
self.assertEqual(row.count, 2)
# When filter comes after aggregate
qs = Person.objects_in(self.database).aggregate(
average_height=F.avg(Person.height),
count=F.count()).filter(Person.first_name == 'Warren')
print(qs.as_sql())
self.assertEqual(qs.count(), 1)
for row in qs:
self.assertAlmostEqual(row.average_height, 1.675, places=4)
self.assertEqual(row.count, 2)
class ModelWithAliasFields(Model):
int_field = Int32Field()
date_field = DateField()
str_field = StringField()
alias_str = StringField(alias=u'str_field')
alias_int = Int32Field(alias='int_field')
alias_date = DateField(alias='date_field')
alias_func = Int32Field(alias=F.toYYYYMM(date_field))
engine = MergeTree('date_field', ('date_field', ))
class ModelWithMaterializedFields(Model):
int_field = Int32Field()
date_time_field = DateTimeField()
str_field = StringField()
mat_str = StringField(materialized='lower(str_field)')
mat_int = Int32Field(materialized='abs(int_field)')
mat_date = DateField(materialized=u'toDate(date_time_field)')
mat_func = StringField(materialized=F.lower(str_field))
engine = MergeTree('mat_date', ('mat_date',))
class SimpleModel(Model):
date_field = DateField()
datetime_field = DateTimeField()
str_field = StringField(default='dozo')
int_field = Int32Field(default=17)
float_field = Float32Field()
alias_field = Float32Field(alias='float_field')
default_func = Float32Field(default=F.sqrt(float_field) + 17)
engine = MergeTree('date_field', ('int_field', 'date_field'))
def test_misc_funcs(self):
self._test_func(F.ifNotFinite(17, 18), 17)
self._test_func(F.isFinite(17), 1)
self._test_func(F.isInfinite(17), 0)
self._test_func(F.isNaN(17), 0)
self._test_func(F.least(17, 18), 17)
self._test_func(F.greatest(17, 18), 18)
def test_replace_functions(self):
haystack = 'hello'
self._test_func(F.replace(haystack, 'l', 'L'), 'heLLo')
self._test_func(F.replaceAll(haystack, 'l', 'L'), 'heLLo')
self._test_func(F.replaceOne(haystack, 'l', 'L'), 'heLlo')
self._test_func(F.replaceRegexpAll(haystack, '[eo]', 'X'), 'hXllX')
self._test_func(F.replaceRegexpOne(haystack, '[eo]', 'X'), 'hXllo')
self._test_func(F.regexpQuoteMeta('[eo]'), '\\[eo\\]')
def test_rand_functions(self):
self._test_func(F.rand())
self._test_func(F.rand(17))
self._test_func(F.rand64())
self._test_func(F.rand64(17))
if self.database.server_version >= (19, 15): # buggy in older versions
self._test_func(F.randConstant())
self._test_func(F.randConstant(17))
class DiffDepthStream(Model):
timestamp = DateTime64Field(codec="Delta,ZSTD")
first_update_id = UInt64Field(codec="Delta,ZSTD")
final_update_id = UInt64Field(codec="Delta,ZSTD")
bids_quantity = ArrayField(Float64Field())
bids_price = ArrayField(Float64Field())
asks_quantity = ArrayField(Float64Field())
asks_price = ArrayField(Float64Field())
symbol = LowCardinalityField(StringField())
engine = ReplacingMergeTree(
partition_key=(F.toMonday(timestamp), "symbol"),
order_by=("timestamp", "first_update_id", "final_update_id"),
)
def test_in_and_not_in(self):
qs = Person.objects_in(self.database)
self._test_qs(qs.filter(Person.first_name.isIn(['Ciaran', 'Elton'])),
4)
self._test_qs(qs.filter(~Person.first_name.isIn(['Ciaran', 'Elton'])),
96)
self._test_qs(
qs.filter(Person.first_name.isNotIn(['Ciaran', 'Elton'])), 96)
self._test_qs(qs.exclude(Person.first_name.isIn(['Ciaran', 'Elton'])),
96)
# In subquery
subquery = qs.filter(F.startsWith(Person.last_name,
'M')).only(Person.first_name)
self._test_qs(qs.filter(Person.first_name.isIn(subquery)), 4)
def test_logical_operators(self):
one = F.plus(1, 0)
two = F.plus(1, 1)
# &
self._test_func(one & two, 1)
self._test_func(one & two, 1)
self._test_func(one & 0, 0)
self._test_func(0 & one, 0)
# |
self._test_func(one | two, 1)
self._test_func(one | 0, 1)
self._test_func(0 | one, 1)
# ^
self._test_func(one ^ one)
self._test_func(one ^ 0)
self._test_func(0 ^ one)
# ~
self._test_func(~one, 0)
self._test_func(~~one, 1)
# compound
self._test_func(one & 0 | two, 1)
self._test_func(one & 0 & two, 0)
self._test_func(one & 0 | 0, 0)
self._test_func((one | 0) & two, 1)
def test_quantile_funcs(self):
cond = Person.last_name > 'H'
weight_expr = F.toUInt32(F.round(Person.height))
# Quantile
self._test_aggr(F.quantile(0.9)(Person.height))
self._test_aggr(F.quantileOrDefault(0.9)(Person.height))
self._test_aggr(F.quantileOrNull(0.9)(Person.height))
self._test_aggr(F.quantileIf(0.9)(Person.height, cond))
self._test_aggr(F.quantileOrDefaultIf(0.9)(Person.height, cond))
self._test_aggr(F.quantileOrNullIf(0.9)(Person.height, cond))
self._test_aggr(F.quantileDeterministic(0.9)(Person.height, 17))
self._test_aggr(F.quantileExact(0.9)(Person.height))
self._test_aggr(F.quantileExactOrDefault(0.9)(Person.height))
# Quantile weighted
self._test_aggr(
F.quantileExactWeighted(0.9)(Person.height, weight_expr))
self._test_aggr(
F.quantileExactWeightedOrNull(0.9)(Person.height, weight_expr))
self._test_aggr(F.quantileTiming(0.9)(Person.height))
self._test_aggr(F.quantileTimingIf(0.9)(Person.height, cond))
self._test_aggr(
F.quantileTimingWeighted(0.9)(Person.height, weight_expr))
self._test_aggr(
F.quantileTimingWeightedOrDefaultIf(0.9)(Person.height,
weight_expr, cond))
self._test_aggr(F.quantileTDigest(0.9)(Person.height))
self._test_aggr(F.quantileTDigestOrNullIf(0.9)(Person.height, cond))
self._test_aggr(
F.quantileTDigestWeighted(0.9)(Person.height, weight_expr))
# Quantiles
self._test_aggr(F.quantiles(0.9, 0.95, 0.99)(Person.height))
self._test_aggr(
F.quantilesDeterministic(0.9, 0.95, 0.99)(Person.height, 17))
self._test_aggr(F.quantilesExact(0.9, 0.95, 0.99)(Person.height))
self._test_aggr(
F.quantilesExactWeighted(0.9, 0.95, 0.99)(Person.height,
weight_expr))
self._test_aggr(F.quantilesTiming(0.9, 0.95, 0.99)(Person.height))
self._test_aggr(
F.quantilesTimingIf(0.9, 0.95, 0.99)(Person.height, cond))
self._test_aggr(
F.quantilesTimingWeighted(0.9, 0.95, 0.99)(Person.height,
weight_expr))
self._test_aggr(
F.quantilesTimingWeightedOrDefaultIf(0.9, 0.95,
0.99)(Person.height,
weight_expr, cond))
self._test_aggr(F.quantilesTDigest(0.9, 0.95, 0.99)(Person.height))
self._test_aggr(
F.quantilesTDigestIf(0.9, 0.95, 0.99)(Person.height, cond))
self._test_aggr(
F.quantilesTDigestWeighted(0.9, 0.95, 0.99)(Person.height,
weight_expr))
def test_aggregate_funcs__if(self):
self._test_aggr(
F.argMinIf(Person.first_name, Person.height,
Person.last_name > 'H'))
self._test_aggr(F.countIf(Person.last_name > 'H'), 57)
self._test_aggr(F.minIf(Person.height, Person.last_name > 'H'), 1.6)
def test_aggregate_funcs__or_null(self):
self.database.raw('TRUNCATE TABLE person')
self._test_aggr(F.countOrNull(), None)
self._test_aggr(F.maxOrNull(Person.height), None)
def test_aggregate_funcs__or_default(self):
self.database.raw('TRUNCATE TABLE person')
self._test_aggr(F.countOrDefault(), 0)
self._test_aggr(F.maxOrDefault(Person.height), 0)
class TestModel(Model):
a = DateTimeField(default=datetime.datetime(2020, 1, 1))
b = DateField(default=F.toDate(a))
c = Int32Field(default=7)
d = Int32Field(default=c * 5)
engine = Memory()
def test_aggregate_funcs(self):
self._test_aggr(F.any(Person.first_name))
self._test_aggr(F.anyHeavy(Person.first_name))
self._test_aggr(F.anyLast(Person.first_name))
self._test_aggr(F.argMin(Person.first_name, Person.height))
self._test_aggr(F.argMax(Person.first_name, Person.height))
self._test_aggr(F.round(F.avg(Person.height), 4),
sum(p.height for p in self._sample_data()) / 100)
self._test_aggr(F.corr(Person.height, Person.height), 1)
self._test_aggr(F.count(), 100)
self._test_aggr(F.round(F.covarPop(Person.height, Person.height), 2),
0)
self._test_aggr(F.round(F.covarSamp(Person.height, Person.height), 2),
0)
self._test_aggr(F.kurtPop(Person.height))
self._test_aggr(F.kurtSamp(Person.height))
self._test_aggr(F.min(Person.height), 1.59)
self._test_aggr(F.max(Person.height), 1.80)
self._test_aggr(F.skewPop(Person.height))
self._test_aggr(F.skewSamp(Person.height))
self._test_aggr(F.round(F.sum(Person.height), 4),
sum(p.height for p in self._sample_data()))
self._test_aggr(F.uniq(Person.first_name, Person.last_name), 100)
self._test_aggr(F.uniqExact(Person.first_name, Person.last_name), 100)
self._test_aggr(F.uniqHLL12(Person.first_name, Person.last_name), 99)
self._test_aggr(F.varPop(Person.height))
self._test_aggr(F.varSamp(Person.height))
