在查询的输出上应用DENSE_RANK（）

Akshay Shah 发表于 Dev

阿克沙伊（Akshay Shah）

我有三个分别名为Groups，GroupMembers和DailyTable的表。以下是我为每个模型制作的模型：

models.py

class Group(models.Model):

    name = models.CharField(max_length=255, blank=False, null=False)
    group_type = models.ForeignKey('GroupType',blank=True,default=False)

    class Meta:
        db_table = 'groups'

class GroupMembers(models.Model):
    group = models.ForeignKey('Group')
    user = models.ForeignKey('User')
    status = models.CharField(max_length=20)

    class Meta:
        db_table = 'group_members'
        unique_together = ['group', 'user']

class DailyTable(models.Model):    
    class Meta:
        managed = True
        db_table = 'dailytable'

    user_id = models.IntegerField(db_index=True)        
    calories = models.FloatField()        
    date = models.DateField()

我想计算每个小组各自消耗的总卡路里。所以我启动了以下查询：

 select groups.id,groups.name as group_display_name,
(select count(*) from group_members where group_id = groups.id and status = 'accepted' and groups.group_type_id = 1) as total_members,
(select sum(calories) from dailytable where user_id in (select user_id from group_members where group_id = groups.id and groups.group_type_id = 1) and dailytable.date='2016-02-02') as total_calories,
(select case when exists(select * from group_members where group_id = groups.id and user_id = 3 and status = 'accepted' and groups.group_type_id = 1) then cast(1 as bit) else cast(0 as bit) end) as is_member_of_group
from groups 
where groups.group_type_id = 1
order by total_calories desc 
nulls last;

下面是我得到的输出。现在，我想根据已刻录的total_calories对组进行排名，因此我在查询中进行了以下更改：

....
dense_rank() over(order by total_calories)
from groups
....

错误： “ total_calories”列不存在

我想将RANK应用于查询的输出。有什么办法可以做到这一点。

亚历山大·拉特尼科夫（Aleksey Ratnikov）

这是因为您正在尝试在density_rank（）中使用派生的列别名，该别名在此处不可用。在CTE中包装查询，并在其上应用density_rank（）：

WITH baseQuery AS (
    SELECT
        groups.id,
        groups.name AS group_display_name,
        (
            SELECT
                count(*)
            FROM group_members
            WHERE
                group_id = groups.id AND status = 'accepted' AND groups.group_type_id = 1) AS total_members,
        (
            SELECT
                sum(calories)
            FROM dailytable
            WHERE
                user_id IN (
                    SELECT
                        user_id
                    FROM group_members
                    WHERE
                        group_id = groups.id 
                        AND groups.group_type_id = 1
                ) AND dailytable.date = '2016-02-02'
        ) AS total_calories,
        (
            SELECT
                CASE WHEN exists(SELECT
                                     *
                                 FROM group_members
                                 WHERE
                                     group_id = groups.id 
                                     AND user_id = 3 
                                     AND status = 'accepted' 
                                     AND groups.group_type_id = 1)
                    THEN cast(1 AS BIT)
                ELSE cast(0 AS BIT) END) AS is_member_of_group
    FROM groups
    WHERE
        groups.group_type_id = 1
)
    select * ,
        dense_rank() over(order by total_calories)
    from baseQuery
    ORDER BY total_calories DESC
    NULLS LAST