Comment compter toutes les occurrences combinées en SQL ?

Question

Existe-t-il une option pour obtenir des combinaisons comptées de tous les éléments dans un Requête SQL, sans utiliser de tables ou de procédures temporaires ?

Considérez ces trois tableaux :

• produits (id, nom_produit)

• transactions (id, date)

• transaction_has_product (id, product_id, transaction_id)

Exemple de données

• produits

  1   AAA
  2   BBB
  3   CCC

• transactions

  1   some_date
  2   some_date

• transaction_has_products

  1   1   1
  2   2   1
  3   3   1
  4   1   2
  5   2   2

Le résultat devrait être :

AAA, BBB = 2   
AAA, CCC = 1   
BBB, CCC = 1   
AAA, BBB, CCC = 1

Answer 1

Pas facilement, car le nombre de produits appariés dans la dernière rangée est différent de celui des autres rangées. Vous pouvez peut-être le faire avec une sorte d'opérateur GROUP_CONCAT() (disponible dans MySQL ; implémentable dans d'autres SGBD, comme Informix et probablement PostgreSQL), mais je n'en suis pas sûr.

Appariement par paires

SELECT p1.product_name AS name1, p2.product_name AS name2, COUNT(*)
  FROM (SELECT p.product_name, h.transaction_id
          FROM products AS p
          JOIN transactions_has_products AS h ON h.product_id = p.product_id
       ) AS p1
  JOIN (SELECT p.product_name, h.transaction_id
          FROM products AS p
          JOIN transactions_has_products AS h ON h.product_id = p.product_id
       ) AS p2
    ON p1.transaction_id = p2.transaction_id
   AND p1.product_name   < p2.product_name
 GROUP BY p1.name, p2.name;

La gestion de la triple correspondance n'est pas triviale ; l'étendre plus loin est certainement assez difficile.

Answer 2

Si vous savez quels seront les produits au départ, vous pouvez le faire en faisant pivoter les données comme ceci.

Si vous ne savez pas quels seront les produits à l'avance, vous pouvez construire cette requête dynamiquement dans une procédure stockée. L'une ou l'autre de ces approches ne serait pas pratique si le nombre de produits était important, mais je pense que cela serait probablement vrai quelle que soit la façon dont cette exigence est satisfaite.

select
    product_combination, 
    case product_combination
        when 'AAA, BBB' then aaa_bbb
        when 'AAA, CCC' then aaa_ccc
        when 'BBB, CCC' then bbb_ccc
        when 'AAA, BBB, CCC' then aaa_bbb_ccc
    end as number_of_transactions
from
(
    select 'AAA, BBB' as product_combination union all
    select 'AAA, CCC' union all
    select 'BBB, CCC' union all
    select 'AAA, BBB, CCC'
) as combination_list
cross join
(
    select
        sum(case when aaa = 1 and bbb = 1 then 1 else 0 end) as aaa_bbb,
        sum(case when aaa = 1 and ccc = 1 then 1 else 0 end) as aaa_ccc,
        sum(case when bbb = 1 and ccc = 1 then 1 else 0 end) as bbb_ccc,
        sum(case when aaa = 1 and bbb = 1 and ccc = 1 then 1 else 0 end) as aaa_bbb_ccc
    from
    (
        select
            count(case when a.product_name = 'AAA' then 1 else null end) as aaa,
            count(case when a.product_name = 'BBB' then 1 else null end) as bbb,
            count(case when a.product_name = 'CCC' then 1 else null end) as ccc,
            b.transaction_id
        from
            products a
        inner join
            transaction_has_products b
        on
            a.id = b.product_id
        group by
            b.transaction_id
    ) as product_matrix
) as combination_counts

Résultat :

product_combination  number_of_transactions
AAA, BBB             2
AAA, CCC             1
BBB, CCC             1
AAA, BBB, CCC        1

Answer 3

Selon le degré de contrôle que vous avez sur la requête, vous pourriez faire (ceci est TSQL et pourrait être modifié pour postgresql)

SELECT COUNT(*) FROM transactions t WHERE
(
     SELECT COUNT(DISTINCT tp.product) 
     FROM transaction_has_products tp 
     WHERE tp.[transaction_id] = t.id and tp.product IN (1, 2, 3)
) = 3

où (1,2,3) est la liste des identifiants que vous souhaitez vérifier et le bouton = 3 est égal au nombre d'entrées dans la liste.

Answer 4

1. génère toutes les combinaisons possibles. Je me suis soutenu avec ça : https://stackoverflow.com/a/9135162/2244766 (c'est un peu délicat, je ne comprends pas bien la logique... mais ça marche !)
2. faites une sous-requête où vous agrégez les produits_dans_transactions dans des tableaux de produits par transaction_id.
3. Joignez-les tous les deux en utilisant les opérateurs d'inclusion de tableau

Après les étapes ci-dessus, vous pourriez obtenir quelque chose comme :

with all_combis as (
    with RECURSIVE y1 as (
            with x1 as (
                --select id from products
                select distinct product_id as a from transaction_has_products 
            )
            select array[a] as b ,a as c ,1 as d 
            from x1
            union all
            select b||a,a,d+1
            from x1
            join y1 on (a < c)
    )
    select *
    from y1
)
, grouped_transactions as (
  SELECT 
    array_agg(product_id) as products
  FROM transaction_has_products
  GROUP BY transaction_id
)
SELECT all_combis.b, count(*)
from all_combis
left JOIN grouped_transactions ON grouped_transactions.products @> all_combis.b 
--WHERE array_upper(b, 1) > 1 -- or whatever
GROUP BY all_combis.b
order by array_upper(b, 1) desc, count(*) desc

Vous pouvez joindre votre table de produits pour remplacer les ids de produits par leurs noms - mais je suppose que vous l'obtiendrez d'ici. voici le violon (sqlfiddle n'est pas dans un bon jour aujourd'hui - donc vérifiez ceci sur votre base de données au cas où il jetterait une erreur bizarre comme un timeout ou quelque chose comme ça)

GL, HF :D