Condition de comptage PostgreSQL Where

Question

J'ai la requête suivante dans 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;

Pourquoi est-ce que je reçois cette erreur :

ERROR : la colonne "overall_count" n'existe pas.

La structure de ma table :

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

Je veux vérifier si une licence a atteint la limite d'appels pour un mois donné.

Answer 1

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)

Depuis Postgres 9.1, la clé primaire couvre toutes les colonnes d'une table dans le format GROUP BY clause. Dans les anciennes versions, vous deviez ajouter a.limit_call à la GROUP BY liste. Le site notes de mise à jour de la 9.1 :

Permettre aux non GROUP BY dans la liste des cibles de la requête lorsque la clé primaire est spécifiée dans le champ GROUP BY clause

Pour en savoir plus :

• Pourquoi ne puis-je pas exclure les colonnes dépendantes de `GROUP BY` lorsque j'agrège par une clé ?

La condition que vous aviez dans le WHERE La clause doit être déplacée vers la HAVING puisqu'elle fait référence au résultat d'une fonction d'agrégation ( après WHERE a été appliquée). Et vous ne pouvez pas faire référence à colonnes de sortie (alias de colonne) dans le HAVING où vous ne pouvez faire référence qu'aux colonnes d'entrée. Vous devez donc répéter l'expression. Le manuel :

Le nom d'une colonne de sortie peut être utilisé pour se référer à la valeur de la colonne dans le fichier ORDER BY y GROUP BY mais pas dans les clauses WHERE ou HAVING dans lesquelles vous devez écrire l'expression à la place.

J'ai inversé l'ordre des tables dans le FROM et nettoyé un peu la syntaxe pour la rendre moins confuse. USING est juste une commodité de notation ici.

J'ai utilisé LEFT JOIN au lieu de JOIN afin de ne pas exclure les licences sans aucun enregistrement.

Seules les valeurs non nulles sont comptées par count() . Puisque vous voulez compter entrées associées en table "Log" il est plus sûr et légèrement moins cher à utiliser count(b.license_id) . Cette colonne est utilisée dans la jointure, donc nous n'avons pas à nous soucier de savoir si la colonne peut être nulle ou non.
count(*) est encore plus court et légèrement plus rapide, pourtant. Si ça ne vous dérange pas d'avoir un compte de 1 para 0 dans le tableau de gauche, utilisez-les.

A propos : je conseillerais no à utiliser identifiants en majuscules et en minuscules dans Postgres si possible. Très propice aux erreurs.

Answer 2

Comptage conditionnel pur (*) :

  SELECT COUNT(*) FILTER(where a.myfield > 0) AS my_count
    FROM "Log" as a 

GROUP BY a.license_id

donc vous :

• obtenir 0 pour les groupes où la condition n'est jamais remplie
• vous pouvez ajouter autant de colonnes count(*) que nécessaire

---

Filtrer les groupes dont les conditions ne correspondent pas :

REMARQUE : vous ne pouvez pas utiliser HAVING b.limit_call > ... à moins que vous ne regroupiez par limit_call . Mais vous pouvez utiliser une fonction agregate pour faire correspondre plusieurs "limit_calls" du groupe en une seule valeur. Par exemple, dans votre cas, vous pouvez utiliser MAX :

  SELECT COUNT(a.log_id) AS overall_count
    FROM "Log" as a 
    JOIN "License" b ON(a.license_id=b.license_id)

GROUP BY a.license_id
  HAVING MAX(b.limit_call) > COUNT(a.log_id)

Et ne se soucient pas de la duplication COUNT(a.log_id) dans la première et la dernière ligne. Postgres l'optimisera.

Answer 3

El where ne reconnaît pas votre alias de colonne, et de plus, vous essayez de filtrer les lignes après agrégation. Essayez :

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 
GROUP BY 
a.license_id
having b.limit_call > count(a.log_id);

El having est similaire à la clause where sauf qu'elle traite les colonnes après une agrégation, alors que la clause where fonctionne sur les colonnes avant une agrégation.

Par ailleurs, y a-t-il une raison pour laquelle les noms de vos tables sont placés entre guillemets ?