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_group_aggregation(self):
author = await Author.create(name="Some One")
await Book.create(name="First!", author=author, rating=4)
await Book.create(name="Second!", author=author, rating=3)
await Book.create(name="Third!", author=author, rating=3)
authors = await Author.all().annotate(
average_rating=Avg("books__rating"))
self.assertAlmostEqual(authors[0].average_rating, 3.3333333333)
authors = await Author.all().annotate(
average_rating=Avg("books__rating")).values()
self.assertAlmostEqual(authors[0]["average_rating"], 3.3333333333)
authors = (await
Author.all().annotate(average_rating=Avg("books__rating")
).values("id", "name",
"average_rating"))
self.assertAlmostEqual(authors[0]["average_rating"], 3.3333333333)
authors = await Author.all().annotate(
average_rating=Avg("books__rating")).values_list()
self.assertAlmostEqual(authors[0][2], 3.3333333333)
authors = (await
Author.all().annotate(average_rating=Avg("books__rating")
).values_list("id", "name",
"average_rating"))
self.assertAlmostEqual(authors[0][2], 3.3333333333)
async def test_aggregate_avg_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(avg_decimal=Avg(F("decimal")))
.values("avg_decimal")
)
self.assertEqual(
values[0],
{"avg_decimal": Decimal("12.42")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(avg_decimal=Avg(F("decimal") + 1))
.values("avg_decimal")
)
self.assertEqual(
values[0],
{"avg_decimal": Decimal("13.42")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(avg_decimal=Avg(F("decimal") + F("decimal")))
.values("avg_decimal")
)
self.assertEqual(
values[0],
{"avg_decimal": Decimal("24.84")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(avg_decimal=Avg(F("decimal") + F("decimal_nodec")))
.values("avg_decimal")
)
self.assertEqual(
values[0],
{"avg_decimal": Decimal("13")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(avg_decimal=Avg(F("decimal") + F("decimal_null")))
.values("avg_decimal")
)
self.assertEqual(
values[0],
{"avg_decimal": None},
)
async def test_avg_values_list_group_by_with_join(self):
ret = (
await Book.annotate(avg=Avg("rating"))
.group_by("author__name")
.values_list("author__name", "avg")
)
self.assertListSortEqual(ret, [("author1", 4.5), ("author2", 2.0)])
async def test_avg_values_list_filter_group_by(self):
ret = (
await Book.annotate(avg=Avg("rating"))
.filter(avg__gt=3)
.group_by("author_id")
.values_list("author_id", "avg")
)
self.assertEqual(len(ret), 1)
self.assertEqual(ret[0][1], 4.5)
async def test_avg_group_by_with_join(self):
ret = (
await Book.annotate(avg=Avg("rating"))
.group_by("author__name")
.values("author__name", "avg")
)
self.assertEqual(
ret, [{"author__name": "author1", "avg": 4.5}, {"author__name": "author2", "avg": 2}]
)
async def test_avg_group_by(self):
ret = (
await Book.annotate(avg=Avg("rating")).group_by("author_id").values("author_id", "avg")
)
for item in ret:
author_id = item.get("author_id")
avg = item.get("avg")
if author_id == self.a1.pk:
self.assertEqual(avg, 4.5)
elif author_id == self.a2.pk:
self.assertEqual(avg, 2.0)
async def test_aggregate_avg(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(
avg_decimal=Avg("decimal")).values("avg_decimal"))
self.assertEqual(
values[0],
{"avg_decimal": Decimal("12.42")},
)
async def get_pr_stats() -> List:
pr_stats_result = (
await PullRequest
.annotate(
min=Min("duration"),
max=Max("duration"),
avg=Avg("duration")
)
.filter(state='closed')
.limit(1)
.values("min", "max", "avg")
)
print(pr_stats_result)
print(type(pr_stats_result))
return pr_stats_result
async def heatmap_data(request):
return web.json_response({
"data": [
{
"timestamp": m["interval"],
"value": {
"voc": m["voc_avg"]
}
} for m in (await Measurement
# Take the average of dat in a 15 min interval
.annotate(voc_avg=Avg("voc"),
interval=Interval("timestamp", 60 *
60)).group_by("interval").
order_by("interval").values("interval", "voc_avg"))
]
})
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)