J'ai un tableau comme ci-dessous:

id | ncode | code
=================
1      1     ABC
1      2     DEF
2      1     ABC
3      1     ABC
4      1     ABC
4      2     DEF

J'ai deux exigences:

  1. Tous les enregistrements où ncode == 1 et code == ABC
  2. Mais seulement si id n'est pas dupliqué quelque part dans la table (c'est-à-dire que je ne veux pas d'enregistrement avec code == ABC si ce même id a également un { {X4}}> 1 quelque part dans le tableau.

select * from table where code == "ABC" and ncode == 1 satisfera la première condition mais je ne peux pas comprendre comment satisfaire la seconde.

Mon résultat souhaité étant donné le tableau d'exemple ci-dessus serait:

id | ncode | code
=================
2      1     ABC
3      1     ABC

Selon la réponse ci-dessous, j'ai utilisé une fonction de fenêtre:

select *
from (
   select *,
    sum(case when ncode > 1 then 1 else 0 end) over(partition by id) cnt
   from mytable
)
where code = 'ABC' and ncode = 1 and cnt = 0 order by id

J'ai ensuite implémenté la syntaxe spécifique à Google BigQuery fournie par la réponse acceptée:

select id, ANY_VALUE(ncode) ncode, ANY_VALUE(code) code
from mytable
group by id
having count(1) = 1
and (cpt, ncode) = ('ABC', 1)
0
Dylan Russell 25 sept. 2020 à 01:37

4 réponses

Meilleure réponse

Ci-dessous devrait également fonctionner (BigQuery Standard SQL)

#standardSQL
SELECT id, 
  ANY_VALUE(ncode) ncode, 
  ANY_VALUE(code) code
FROM `project.dataset.table`
GROUP BY id
HAVING COUNT(1) = 1
AND (code, ncode) = ('ABC', 1)   
0
Mikhail Berlyant 24 sept. 2020 à 23:26

Le moyen le plus simple est probablement d'utiliser une condition NOT EXISTS - devrait être quelque chose comme:

SELECT  t.*
FROM    TABLE t
WHERE   t.code = "ABC"
        AND t.ncode = 1
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    TABLE ti
        WHERE   ti.id = t.id
                AND ti.ncode > 1
        );
        
-1
boydc7 24 sept. 2020 à 22:47

Vous pouvez utiliser NOT EXISTS et une sous-requête corrélée qui recherche dans la table des enregistrements avec le même id mais grand ncode.

SELECT t1.*
       FROM elbat t1
       WHERE t1.code = 'ABC'
             AND t1.ncode = 1
             AND NOT EXISTS (SELECT *
                                    FROM elbat t2
                                    WHERE t2.id = t1.id
                                          AND t2.ncode > t1.ncode);

Et au fait: arrêtez d'utiliser des guillemets doubles pour les chaînes littérales. Bien que votre SGBD puisse accepter cela, les guillemets doubles standard sont pour les identificateurs et les guillemets simples pour les chaînes littérales. En suivant cela, votre code devient plus portable. Il en va de même pour == comme opérateur de comparaison. Mieux vaut utiliser =.

Pour les performances, vous voulez un index sur (id, ncode).

-1
sticky bit 25 sept. 2020 à 09:09

Vous pouvez utiliser les fonctions de la fenêtre:

select *
from (select t.*, count(*) over(partition by id, code) cnt from mytable) t
where code = 'ABC' and ncode = 1 and cnt = 1

Cela suppose qu'il n'y a pas de doublon ncode par code, ni de valeur inférieure à 1. Sinon, nous pouvons être plus précis dans la fonction de fenêtre:

select *
from (
    select t.*, 
        sum(case when ncode > 1 then 1 else 0 end) over(partition by id, code) cnt 
    from mytable 
) t
where code = 'ABC' and ncode = 1 and cnt = 0
0
GMB 24 sept. 2020 à 22:52