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