How to count all concatenated occurrences in SQL?

Question

Is there any option for counting combinations of all elements in a single SQL query without using temporary tables or procedures?

Consider these three tables:

• products (id, product_name)

• transactions (id, date)

• transaction_has_product (id, product_id, transaction_id)

Data examples

• the products

  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
  

The result should be:

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

Answer 1

, . , GROUP_CONCAT() ( MySQL, , Informix , , PostgreSQL), .

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;

; , .

Answer 2

, , , .

, , . , , , , , , .

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

:

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

Answer 3

, , ( TSQL, , 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

(1,2,3) - , , = 3 .

Answer 4

• . : fooobar.com/questions/1057906/... ( , ... !)
• , products_in_transactions _id

- :

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

, , , . (sqlfiddle ), db, - , , - - )

GL, HF: D