PostgreSQL SQL: 列必须出现在GROUP BY子句中或在聚合函数中使用

Question

(Postgres)

我不太清楚以下内容：我从STUDY_T选择各种字段，其中可能有使用聚合函数的子查询。我正在检索一个独立的字段，这是一个LOOKUP_T连接：lookupStudyType.description，它与任何聚合函数都没有关联。但我收到了错误提示：

ERROR:  column "lookupstudytype.description" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     lookupStudyType.description     AS studyTypeDescription,...

为什么我需要按照s.id和lookupStudyType.description进行分组（而其他s.分组不是必需的）？

        SELECT 
            s.id                                        AS id, 
            lookupStudyType.description                 AS studyTypeDescription, 
            s.name                                      AS name, 
            s.abbreviation                              AS abbreviation, 
            s.start_date                                AS startDate, 
            s.end_date                                  AS endDate, 
            (SELECT COUNT(r.id)   
                FILTER 
                (WHERE r.status_id IN (76, 77) ))       AS  recallCount, 
            (SELECT COUNT(DISTINCT sp.id))              AS  participantCount, 
            (SELECT MAX(r.created_date) 
                FILTER 
                (WHERE r.status_id IN (76,77) ))        AS lastRecall,
            s.login_access_required                     AS loginAccessRequired, 
            s.description                               AS description, 
            s.custom_participant_exit_message           AS customParticipantExitMessage 
        FROM study_t s 
        INNER JOIN lookup_t lookupStudyType 
           ON s.study_type_id = lookupStudyType.id 
        INNER JOIN study_staff_t ss 
            ON s.id = ss.study_id 
        INNER JOIN users_t u 
            ON ss.researcher_id = u.id 
        LEFT JOIN study_participants_t sp 
            ON s.id = sp.study_id 
        LEFT JOIN recalls_t r 
            ON r.user_id = sp.user_id 
        WHERE  u.user_name = '[e-mail]'
        GROUP BY 
            s.id
        ORDER  BY s.abbreviation ASC

- gene b.

---

错误看起来很明显。在“group by”中包括该列。 - Gordon Linoff

问题是为什么。这是一个独立的列，与任何“STUDY_T”字段无关。 - gene b.

PostgreSQL 不知道列的独立性。作为 SQL 的一般规则，在聚合查询中，所有列必须被分组或聚合。然而，一些 DBMS 的工作方式不同。 - Arvo

但是为什么其他的STUDY_T列不需要分组呢？没有其他s.字段是必需的。 - gene b.

这是因为id是您的主键：如果分组列（或其子集）是包含未分组列的表的主键，则存在功能依赖关系。https://www.postgresql.org/docs/current/sql-select.html - Frank Heikens

Answer 1

通常情况下，任何未在GROUP BY子句中列出的列都应该在SELECT列表中以聚合的形式显示。

例如，由于s.name未在GROUP BY列表中出现，因此它应该显示为max(s.name)或min(s.name)。然而，PostgreSQL实现了GROUP BY子句的函数依赖关系（一种SQL标准特性），并检测到s.name依赖于s.id列（可能是主键）；简单来说，每个s.id只有一个可能的值s.name。因此，在PostgreSQL中不需要对该列进行聚合（可以，但不需要）。

另一方面，对于lookupStudyType.description，PostgreSQL无法确定它是否与s.id具有函数依赖关系。因此，您需要将其作为max(lookupStudyType.description)或min(lookupStudyType.description)或任何其他聚合表达式进行聚合。

顺便说一句，我很少看到其他数据库实现函数依赖关系。PostgreSQL真是太棒了！（我与PostgreSQL没有任何关联）。

- The Impaler