async def run(): await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]}) await Tortoise.generate_schemas() a1 = await Author.create(name="author1") a2 = await Author.create(name="author2") for i in range(10): await Book.create(name=f"book{i}", author=a1, rating=i) for i in range(5): await Book.create(name=f"book{i}", author=a2, rating=i) ret = await Book.annotate(count=Count("id")).group_by("author_id").values( "author_id", "count") print(ret) # >>> [{'author_id': 1, 'count': 10}, {'author_id': 2, 'count': 5}] ret = (await Book.annotate(count=Count("id") ).filter(count__gt=6).group_by("author_id").values( "author_id", "count")) print(ret) # >>> [{'author_id': 1, 'count': 10}] ret = await Book.annotate(sum=Sum("rating")).group_by("author_id").values( "author_id", "sum") print(ret) # >>> [{'author_id': 1, 'sum': 45.0}, {'author_id': 2, 'sum': 10.0}] ret = (await Book.annotate(sum=Sum("rating") ).filter(sum__gt=11).group_by("author_id").values( "author_id", "sum")) print(ret) # >>> [{'author_id': 1, 'sum': 45.0}] ret = await Book.annotate(avg=Avg("rating")).group_by("author_id").values( "author_id", "avg") print(ret) # >>> [{'author_id': 1, 'avg': 4.5}, {'author_id': 2, 'avg': 2.0}] ret = (await Book.annotate(avg=Avg("rating") ).filter(avg__gt=3).group_by("author_id").values( "author_id", "avg")) print(ret) # >>> [{'author_id': 1, 'avg': 4.5}] # and use .values_list() ret = (await Book.annotate(count=Count("id")).group_by("author_id").values_list( "author_id", "count")) print(ret) # >>> [(1, 10), (2, 5)] # group by with join ret = (await Book.annotate(count=Count("id")).group_by("author__name").values( "author__name", "count")) print(ret)
async def test_aggregate_sum_with_f_expression(self): await testmodels.DecimalFields.create(decimal=Decimal("0"), decimal_nodec=1) await testmodels.DecimalFields.create(decimal=Decimal("9.99"), decimal_nodec=1) await testmodels.DecimalFields.create(decimal=Decimal("27.27"), decimal_nodec=1) values = ( await testmodels.DecimalFields.all() .annotate(sum_decimal=Sum(F("decimal"))) .values("sum_decimal") ) self.assertEqual( values[0], {"sum_decimal": Decimal("37.26")}, ) values = ( await testmodels.DecimalFields.all() .annotate(sum_decimal=Sum(F("decimal") + 1)) .values("sum_decimal") ) self.assertEqual( values[0], {"sum_decimal": Decimal("40.26")}, ) values = ( await testmodels.DecimalFields.all() .annotate(sum_decimal=Sum(F("decimal") + F("decimal"))) .values("sum_decimal") ) self.assertEqual( values[0], {"sum_decimal": Decimal("74.52")}, ) values = ( await testmodels.DecimalFields.all() .annotate(sum_decimal=Sum(F("decimal") + F("decimal_nodec"))) .values("sum_decimal") ) self.assertEqual( values[0], {"sum_decimal": Decimal("4E+1")}, ) values = ( await testmodels.DecimalFields.all() .annotate(sum_decimal=Sum(F("decimal") + F("decimal_null"))) .values("sum_decimal") ) self.assertEqual( values[0], {"sum_decimal": None}, )
async def test_aggregate_sum_no_exist_field_with_f_expression(self): with self.assertRaisesRegex( FieldError, "There is no non-virtual field not_exist on Model DecimalFields", ): await testmodels.DecimalFields.all().annotate( sum_decimal=Sum(F("not_exist"))).values("sum_decimal")
async def test_sum_values_list_group_by_with_join(self): ret = ( await Book.annotate(sum=Sum("rating")) .group_by("author__name") .values_list("author__name", "sum") ) self.assertListSortEqual(ret, [("author1", 45.0), ("author2", 10.0)])
async def test_aggregate_sum_different_field_type_at_left_with_f_expression(self): with self.assertRaisesRegex( FieldError, "Cannot use arithmetic expression between different field type" ): await testmodels.DecimalFields.all().annotate( sum_decimal=Sum(F("id") + F("decimal")) ).values("sum_decimal")
async def test_sum_filter_group_by(self): ret = (await Book.annotate(sum=Sum("rating") ).filter(sum__gt=11).group_by("author_id").values( "author_id", "sum")) self.assertEqual(len(ret), 1) self.assertEqual(ret[0].get("sum"), 45.0)
async def test_default_order_annotated_query(self): instance = await DefaultOrdered.create(one="2", second=1) await FKToDefaultOrdered.create(link=instance, value=10) await DefaultOrdered.create(one="1", second=1) queryset = DefaultOrdered.all().annotate(res=Sum("related__value")) queryset._make_query() query = queryset.query.get_sql() self.assertTrue("order by" not in query.lower())
async def test_sum_group_by_with_join(self): ret = ( await Book.annotate(sum=Sum("rating")) .group_by("author__name") .values("author__name", "sum") ) self.assertEqual( ret, [{"author__name": "author1", "sum": 45.0}, {"author__name": "author2", "sum": 10.0}], )
async def command_usage(self, ctx: commands.Context): usage_counts = (await CommandUsageCount.all().annotate( use_count=Sum('counter') ).order_by('name').group_by('name').values_list('name', 'use_count')) embed = discord.Embed(title='Command Usage') asyncio.create_task( run_paged_message( ctx, embed, [f'{name}: {count}' for name, count in usage_counts] + [f'total: {sum(c for _, c in usage_counts)}'], page_size=40))
async def test_sum_group_by(self): ret = ( await Book.annotate(sum=Sum("rating")).group_by("author_id").values("author_id", "sum") ) for item in ret: author_id = item.get("author_id") sum_ = item.get("sum") if author_id == self.a1.pk: self.assertEqual(sum_, 45.0) elif author_id == self.a2.pk: self.assertEqual(sum_, 10.0)
async def test_aggregation(self): tournament = Tournament(name="New Tournament") await tournament.save() await Tournament.create(name="Second tournament") await Event(name="Without participants", tournament_id=tournament.id).save() event = Event(name="Test", tournament_id=tournament.id) await event.save() participants = [] for i in range(2): team = Team(name=f"Team {(i + 1)}") await team.save() participants.append(team) await event.participants.add(participants[0], participants[1]) await event.participants.add(participants[0], participants[1]) tournaments_with_count = (await Tournament.all().annotate( events_count=Count("events")).filter(events_count__gte=1)) self.assertEqual(len(tournaments_with_count), 1) self.assertEqual(tournaments_with_count[0].events_count, 2) event_with_lowest_team_id = (await Event.filter( event_id=event.event_id ).first().annotate(lowest_team_id=Min("participants__id"))) self.assertEqual(event_with_lowest_team_id.lowest_team_id, participants[0].id) ordered_tournaments = (await Tournament.all().annotate( events_count=Count("events")).order_by("events_count")) self.assertEqual(len(ordered_tournaments), 2) self.assertEqual(ordered_tournaments[1].id, tournament.id) event_with_annotation = (await Event.all().annotate( tournament_test_id=Sum("tournament__id")).first()) self.assertEqual(event_with_annotation.tournament_test_id, event_with_annotation.tournament_id) with self.assertRaisesRegex(ConfigurationError, "name__id not resolvable"): await Event.all().annotate(tournament_test_id=Sum("name__id") ).first()
async def test_aggregate_sum(self): await testmodels.DecimalFields.create(decimal=Decimal("0"), decimal_nodec=1) await testmodels.DecimalFields.create(decimal=Decimal("9.99"), decimal_nodec=1) await testmodels.DecimalFields.create(decimal=Decimal("27.27"), decimal_nodec=1) values = (await testmodels.DecimalFields.all().annotate( sum_decimal=Sum("decimal")).values("sum_decimal")) self.assertEqual( values[0], {"sum_decimal": Decimal("37.26")}, )
async def run(): await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]}) await Tortoise.generate_schemas() tournament = await Tournament.create(name="New Tournament", desc="great") await tournament.save() await Tournament.create(name="Second tournament") await Tournament.create(name=" final tournament ") await Event(name="Without participants", tournament_id=tournament.id).save() event = Event(name="Test", tournament_id=tournament.id) await event.save() participants = [] for i in range(10): team = Team(name=f"Team {(i + 1)}") await team.save() participants.append(team) await event.participants.add(*participants) # await event.participants.add(participants[0], participants[1]) print(await Tournament.all().annotate(events_count=Count("events")).filter(events_count__lte=3).values()) print( await Tournament .annotate(events_count_with_filter=Count("events", _filter=Q(name="New Tournament"))) .filter(events_count_with_filter__gte=0).values() ) print(await Event.annotate(lowest_team_id=Count("participants__id")).values()) print(await Tournament.annotate(events_count=Count("events")).order_by("events_count")) print(await Event.annotate(tournament_test_id=Sum("tournament__id")).first()) print( await Tournament.annotate(clean_desciption=Coalesce("desc", "hehe")).values() ) print( await Tournament.annotate(trimmed_name=Trim("name")).values() ) print( await Tournament.annotate(name_len=Length("name")).filter( name_len__gt=len("New Tournament") ) ) print(await Tournament.annotate(name_lo=Lower("name")).filter(name_lo="new tournament").values()) print(await Tournament.annotate(name_lo=Upper("name")).filter(name_lo="NEW TOURNAMENT").values()) print()
async def run(): await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]}) await Tortoise.generate_schemas() tournament = await Tournament.create(name="New Tournament", desc="great") await tournament.save() await Tournament.create(name="Second tournament") await Tournament.create(name=" final tournament ") await Event(name="Without participants", tournament_id=tournament.id).save() event = Event(name="Test", tournament_id=tournament.id) await event.save() participants = [] for i in range(2): team = Team(name=f"Team {(i + 1)}") await team.save() participants.append(team) await event.participants.add(participants[0], participants[1]) await event.participants.add(participants[0], participants[1]) print(await Tournament.all().annotate(events_count=Count("events") ).filter(events_count__gte=1)) print(await Event.filter(id=event.id).first().annotate( lowest_team_id=Min("participants__id"))) print(await Tournament.all().annotate(events_count=Count("events") ).order_by("events_count")) print(await Event.all().annotate(tournament_test_id=Sum("tournament__id") ).first()) print(await Tournament.annotate(clean_desciption=Coalesce("desc") ).filter(clean_desciption="")) print(await Tournament.annotate(trimmed_name=Trim("name") ).filter(trimmed_name="final tournament")) print(await Tournament.annotate(name_len=Length("name") ).filter(name_len__gt=len("New Tournament"))) print(await Tournament.annotate(name_lo=Lower("name") ).filter(name_lo="new tournament")) print(await Tournament.annotate(name_lo=Upper("name") ).filter(name_lo="NEW TOURNAMENT"))
async def get_interval_data(cls, meta_ids: list, start: arrow.Arrow, end: arrow.Arrow) -> list: """ 获取时间区内统计数据和 :param meta_ids: :param start: :param end: :param db: :return: """ res = await StatisticsData.filter( d_t__gte=start.datetime, d_t__lte=end.datetime, meta_id__in=meta_ids, ).group_by('meta_id').annotate(s=Sum('val')).values('meta_id', val='s') return res
async def emojistats_server(self, ctx): """Shows statistics about the emoji usage on server.""" embeds = [] guild = ctx.guild get_member = lambda member_id: guild.get_member(member_id) data = (await models.EmojiUsageStat.filter(guild_id=guild.id).annotate( sum=Sum("amount")).group_by("emoji_id").order_by("-sum").values()) last_usage = (await models.EmojiUsageStat.filter( guild_id=guild.id).order_by("-last_usage").limit(1).values()) last_usage = last_usage[0] for data in functions.list_to_matrix(data): emoji = ctx.get_emoji(ctx.guild, last_usage["emoji_id"]) if not emoji: emoji = "\U0001f5d1" embed = discord.Embed(color=self.bot.color) embed.set_author(name=guild, icon_url=guild.icon.url) embed.description = "{}\n\nEn son: {}".format( self.get_emoji_stats(ctx, data, key="sum"), "{} {} `({})`".format( emoji, get_member(last_usage["user_id"]).mention, util_time.humanize(last_usage["last_usage"]), ), ) embeds.append(embed) menu = menus.MenuPages( timeout=30, clear_reactions_after=True, source=paginator.EmbedSource(data=embeds), ) try: await menu.start(ctx) except IndexError: await ctx.send("Kayıt bulunamadı!")
async def run(): await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]}) # await Tortoise.generate_schemas() # # a1 = await Author.create(name="author1") # a2 = await Author.create(name="author2") # for i in range(10): # await Book.create(name=f"book{i}", author_id=a1.id, rating=i) # for i in range(5): # await Book.create(name=f"book{i}", author=a2, rating=i) ret = await Book.annotate(count=Count('id')).group_by('author_id').values() ret2 = await Book.all().group_by('author_id').annotate(count = Count('id')).values() print(ret) print(ret2) print() # >>> [{'author_id': 1, 'count': 10}, {'author_id': 2, 'count': 5}] ret = ( await Book.annotate(count=Count("id")) .filter(count__gt=6) .group_by("author_id") .values("author_id", "count") ) print(ret) # >>> [{'author_id': 1, 'count': 10}] ret = await Book.annotate(sum=Sum("rating")).group_by("author_id").values("author_id", "sum") print(ret) # >>> [{'author_id': 1, 'sum': 45.0}, {'author_id': 2, 'sum': 10.0}] ret = ( await Book.annotate(sum=Sum("rating")) .filter(sum__gt=11) .group_by("author_id") .values("author_id", "sum") ) print(ret) # >>> [{'author_id': 1, 'sum': 45.0}] ret = await Book.annotate(avg=Avg("rating")).group_by("author_id").values("author_id", "avg") print(ret) # >>> [{'author_id': 1, 'avg': 4.5}, {'author_id': 2, 'avg': 2.0}] ret = ( await Book.annotate(avg=Avg("rating")) .filter(avg__gt=3) .group_by("author_id") .values("author_id", "avg") ) print(ret) # >>> [{'author_id': 1, 'avg': 4.5}] # and use .values_list() ret = ( await Book.annotate(count=Count("id")) .group_by("author_id") .values_list("author_id", "count") ) print(ret) # >>> [(1, 10), (2, 5)] # group by with join ret = ( await Book.annotate(count=Count("id")) .group_by("author__name") .values("author__name", "count") ) print(ret)