sql - 在查询的输出上应用 DENSE_RANK()

Question

我有三个表，分别名为 Groups、GroupMembers 和 DailyTable。以下是我为他们每个人建立的模型:

模型.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;

下面是我得到的输出 现在我想根据我燃烧的总卡路里对组进行排名，因此我在查询中进行了以下更改:

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

错误:列“total_calories”不存在

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

Answer 1

这是因为您正尝试在 dense_rank() 中使用派生列别名，而该别名在那里不可用。在 CTE 中包装查询并在其上应用 dense_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