我如何通过注释的Django中的串联来聚集字符串？

发布于 2025-02-11 09:44:52 字数 810 浏览 2 评论 0原文

我在Django中有关注的模型：

class Click(models.Model):
    url = models.ForeignKey(Url, on_delete=models.CASCADE)
    browser = models.CharField(max_length=255)
    platform = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

我想构建一个查询，以便在当月获得每天的总点击，以及浏览器和平台的串联。例如：

[
  {
    'created_at__day': 28,
    'browsers': 'Chrome, Firefox',
    'platforms': 'Windows, Linux',
    'clicks': 4
  }
]

直到这一刻，我做了什么：

queryset = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month
).values('created_at__day', 'browser').annotate(
    clicks=Count('created_at'),
)

我如何才能将每个浏览器和平台加以限制，只通过create_at__day进行分组？

原文

I have the followed model in Django:

class Click(models.Model):
    url = models.ForeignKey(Url, on_delete=models.CASCADE)
    browser = models.CharField(max_length=255)
    platform = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

I want to build a query to get the total clicks per day on the current month, along with browsers and platforms concatenated. For example:

[
  {
    'created_at__day': 28,
    'browsers': 'Chrome, Firefox',
    'platforms': 'Windows, Linux',
    'clicks': 4
  }
]

What I did until this moment was that query:

queryset = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month
).values('created_at__day', 'browser').annotate(
    clicks=Count('created_at'),
)

How can I concat every browser and platform, only grouping by created_at__day?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

终遇你 2025-02-18 09:44:53

我认为您很亲密，如@shabble所述，StringAgg如果您使用的是Postresql：

from django.contrib.postgres.aggregates import StringAgg

queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=StringAgg("platform", delimiter=", ", distinct=True),
    )
)

如果不是，则必须使用subquery，并为数据库编写适当的字符串汇总。我会像：

# Inner query
click_platforms = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month,
    created_at__day=OuterRef("created_at__day"),
).distinct()

# Outer query
queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=YourAggregation(click_platforms),
    )
)

我无法编写youraggregation，这取决于数据库中的可用内容。

I think you're close, as @Shabble mentioned, StringAgg if you're using Postresql:

from django.contrib.postgres.aggregates import StringAgg

queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=StringAgg("platform", delimiter=", ", distinct=True),
    )
)

If you're not, you'd have to go with a Subquery, and write an appropriate string Aggregation for the database. I'd be something like:

# Inner query
click_platforms = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month,
    created_at__day=OuterRef("created_at__day"),
).distinct()

# Outer query
queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=YourAggregation(click_platforms),
    )
)

I cannot write YourAggregation, it'd depend on what's available on your database.

回复收藏 0 原文