Django和Postgres - 百分位数(中位数)和分组

Question

我需要计算每个销售员ID的周期中位数（见下面简化的模型）。问题是我无法构建ORM查询。

模型

class MyModel:
    period = models.IntegerField(null=True, default=None)
    seller_ids = ArrayField(models.IntegerField(), default=list)
    aux = JSONField(default=dict)

查询

queryset = (
    MyModel.objects.filter(period=25)
    .annotate(seller_id=Func(F("seller_ids"), function="unnest"))
    .values("seller_id")
    .annotate(
        duration=Cast(KeyTextTransform("duration", "aux"), IntegerField()),
        median=Func(
            F("duration"),
            function="percentile_cont",
            template="%(function)s(0.5) WITHIN GROUP (ORDER BY %(expressions)s)",
        ),
    )
    .values("median", "seller_id")
)

数组字段聚合（seller_id）来源

我认为我需要做的是以下几点。

select t.*, p_25, p_75
from t join
     (select district,
             percentile_cont(0.25) within group (order by sales) as p_25,
             percentile_cont(0.75) within group (order by sales) as p_75
      from t
      group by district
     ) td
     on t.district = td.district

以上示例源代码

Python 3.7.5，Django 2.2.8，Postgres 11.1

- Dušan Maďar

---

请澄清一下，您是否正在使用Django与SQLServer？ - ivissani

@ivissani，问题下有一个“postgresql”标签，所以不是。 - Dušan Maďar

是的...对此感到抱歉。 - ivissani

你遇到了什么错误？ - Yann

那么你的问题是什么？你展示的查询有什么问题吗？你是想使用ORM来完成这个任务吗？ - Lord Elrond

添加了模型和示例查询。 - Dušan Maďar

Answer 1

你可以创建一个Median类，继承Aggregate类，就像Ryan Murphy所做的那样(https://gist.github.com/rdmurphy/3f73c7b1826cacee34f6c2a855b12e2e)。然后Median类的使用方式与Avg相同：

    from django.db.models import Aggregate, FloatField


    class Median(Aggregate):
        function = 'PERCENTILE_CONT'
        name = 'median'
        output_field = FloatField()
        template = '%(function)s(0.5) WITHIN GROUP (ORDER BY %(expressions)s)'

要找到一个字段的中位数，请使用

    my_model_aggregate = MyModel.objects.all().aggregate(Median('period'))

接下来可以通过 my_model_aggregate['period__median'] 来访问该值。

- GCru

Answer 2

这就是奏效的方法。

from django.db.models import F, Func, IntegerField
from django.db.models.aggregates import Aggregate


queryset = (
    MyModel.objects.filter(period=25)
    .annotate(duration=Cast(KeyTextTransform("duration", "aux"), IntegerField()))
    .filter(duration__isnull=False)
    .annotate(seller_id=Func(F("seller_ids"), function="unnest"))
    .values("seller_id")  # group by
    .annotate(
        median=Aggregate(
            F("duration"),
            function="percentile_cont",
            template="%(function)s(0.5) WITHIN GROUP (ORDER BY %(expressions)s)",
        ),
    )
)

请注意，median注释使用Aggregate而不是问题中的Func。 此外，annotate()和filter()子句的顺序以及annotate()和values()子句的顺序 非常重要！ 顺便说一下，生成的SQL没有嵌套选择和连接。

- Dušan Maďar