django - Count distinct on an aggregated dimension - Stack Overflow

admin管理员组

文章数量:1334887

I have the following model:

class Historique(models.Model):
    type_of_object_choices = (
        (1, 'Cadeau')
        , (2, 'Offre')
        , (3, 'Commentaire')
        )

    event_type_choices = (
        (1, 'modification')
        , (2, 'creation')
        , (4, 'suppression')
        )

    type_of_object = models.IntegerField(choices=type_of_object_choices, null=True)
    event_type = models.IntegerField(choices=event_type_choices)
    object_pk = models.IntegerField()

Each object can be subject to three actions, each action occurring only once per object. These actions are encoded as an integer field called event_type_choices, which can take the values 1, 2, 4. By summing all event_type_choices for a given object, we compute a value in the range [1, 2, 4, 3, 5, 6, 7] representing the cumulative actions that have occurred on this object so far. Based on this value, a derived field called type_of_action is created to represent a simplified view of the actions performed on the object, whish is shown to the user.

I want to count the number of objects there are for each combinaison of type_of_object and type_of_action. The following SQL query achieves this, but I would like to express it in a Django's query syntax.

            SELECT
                type_object
                , CASE
                    WHEN sum_event_type in (1) THEN 'modification'
                    WHEN sum_event_type in (2, 3) THEN 'creation'
                    WHEN sum_event_type in (4, 6) THEN 'supression'
                    END as type_of_action
                , COUNT(DISTINCT object_pk) as nb_objects
            FROM
                (   
                SELECT 
                    type_object
                    , object_pk
                    , sum(event_type) as sum_event_type
                
                FROM historique 
                GROUP BY type_object, object_pk
                ) as temp
            GROUP BY type_object
                , CASE
                    WHEN sum_event_type in (1) THEN 'modification'
                    WHEN sum_event_type in (2, 3) THEN 'creation'
                    WHEN sum_event_type in (4, 6) THEN 'supression'
                    END

I have been trying different solutions based on annotate and aggregate but all faied.

本文标签： djangoCount distinct on an aggregated dimensionStack Overflow