如何计算SQL中所有合并的出现次数？ [英] How to count all combined occurrences in SQL?

问题描述

是否可以选择在不使用临时表或过程的情况下获得一个 SQL查询中所有元素的计数组合？

Is there any option to gain counted combinations of all elements in one SQL query, without using temp tables or procedures?

请考虑以下内容三个表：

Consider these three tables:

• 产品（id，product_name）

• products (id, product_name)

交易（id，日期）

transaction_has_product（id，product_id，transaction_id）

transaction_has_product (id, product_id, transaction_id)

样本数据

• 产品

• 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

结果应为：

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

推荐答案

如果您知道所有产品都将首先推出，则可以通过旋转数据来实现。

If you know what all the products will be up front, you could do it by pivoting the data like this.

如果您不知道将首先使用什么产品，则可以在存储过程中动态构建此查询。如果产品数量很多，则这两种方法的实用性都会下降，但是我认为，无论如何满足此要求，这都可能是正确的。

If you don't know what the products will be up front, you could build this query dynamically in a stored procedure. The practicality of either approach would break down if the number of products is large, but I think that would probably be true no matter how this requirement is accomplished.

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

这篇关于如何计算SQL中所有合并的出现次数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！