PostgreSQL Where计数条件 [英] PostgreSQL Where count condition

问题描述

我在 PostgreSQL 中有以下查询:

I have following query in PostgreSQL:

SELECT 
    COUNT(a.log_id) AS overall_count
FROM 
    "Log" as a, 
    "License" as b 
WHERE 
    a.license_id=7 
AND 
    a.license_id=b.license_id 
AND
    b.limit_call > overall_count
GROUP BY 
    a.license_id;

为什么会出现此错误:

错误:overall_count"列不存在

ERROR: column "overall_count" does not exist

我的表结构:

License(license_id, license_name, limit_call, create_date, expire_date)
Log(log_id, license_id, log, call_date)

我想检查许可证是否已达到特定月份的通话限制.

I want to check if a license has reached the limit for calls in a specific month.

推荐答案

SELECT a.license_id, a.limit_call
     , count(b.license_id) AS overall_count
FROM   "License"  a
LEFT   JOIN "Log" b USING (license_id)
WHERE  a.license_id = 7 
GROUP  BY a.license_id  -- , a.limit_call  -- add in old versions
HAVING a.limit_call > count(b.license_id)

自 Postgres 9.1 起，主键覆盖 GROUP BY 子句中表的所有列.在旧版本中，您必须将 a.limit_call 添加到 GROUP BY 列表.9.1 发行说明:

Since Postgres 9.1 the primary key covers all columns of a table in the GROUP BY clause. In older versions you'd have to add a.limit_call to the GROUP BY list. The release notes for 9.1:

允许查询目标列表中的非GROUP BY列键在 GROUP BY 子句

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause

进一步阅读:

• 当我按键聚合时，为什么不能从GROUP BY"中排除依赖列?

您在 WHERE 子句中的条件必须移动到 HAVING 子句，因为它指的是聚合函数的结果 (after WHERE 已应用).并且您不能在 HAVING 子句中引用 输出列(列别名)，您只能在其中引用输入列.所以你必须重复这个表达式.手册:

The condition you had in the WHERE clause has to move to the HAVING clause since it refers to the result of an aggregate function (after WHERE has been applied). And you cannot refer to output columns (column aliases) in the HAVING clause, where you can only reference input columns. So you have to repeat the expression. The manual:

输出列的名称可用于引用列的值ORDER BY 和 GROUP BY 子句，但不在 WHERE 或 HAVING 中条款；在那里你必须写出表达式.

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

我在 FROM 子句中颠倒了表格的顺序，并稍微清理了语法以减少混乱.USING 在这里只是一个符号方便.

I reversed the order of tables in the FROM clause and cleaned up the syntax a bit to make it less confusing. USING is just a notational convenience here.

我使用了 LEFT JOIN 而不是 JOIN，因此您根本不会排除没有任何日志的许可证.

I used LEFT JOIN instead of JOIN, so you do not exclude licenses without any logs at all.

count() 只计算非空值.由于您想计算表 "Log" 中的相关条目，使用 count(b.license_id) 更安全且成本更低>.这个列是在join中使用的，所以我们不用纠结这个列是否可以为null.
count(*) 更短，速度也更快.如果您不介意为左表中的 0 行获取 1 的计数，请使用它.

Only non-null values are counted by count(). Since you want to count related entries in table "Log" it is safer and slightly cheaper to use count(b.license_id). This column is used in the join, so we don't have to bother whether the column can be null or not.
count(*) is even shorter and slightly faster, yet. If you don't mind to get a count of 1 for 0 rows in the left table, use that.

旁白:我建议不要使用混合大小写标识符 在 Postgres 中(如果可能).非常容易出错.

Aside: I would advise not to use mixed case identifiers in Postgres if possible. Very error prone.

这篇关于PostgreSQL Where计数条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！