具有 2 个条件的 MySQL Crosstab 聚合

Question

我有一个创建交叉表的查询。结果是 branda 的 txn_id 计数，以及 brandb 的 txn_id 计数。

txn_id 不是唯一的。这是交易表的示例。:

txn_id | nationality_id | sku | sales | units

 1     |      1         |  1  |  20   | 2
 1     |      1         |  2  |  15   | 1
 2     |      4         |  1  |  20   | 2
 3     |      2         |  1  |  10   | 1
 4     |      3         |  2  |  15   | 1
 5     |      4         |  1  |  10   | 1

还有另外两个表(products) - (sku, brand, product name)，和(nationalities) - (nationality_id, nationality)。

我想添加第三列，让我获得购买两个品牌的 txn_id 的计数

输出应该是

nationality | branda | brandb | combined

  1         |  1     |   1    |   1
  2         |  1     |   0    |   0
  3         |  0     |   1    |   0
  4         |  2     |   0    |   0

当前查询。

SELECT 
    nationalities.nationality,
    COUNT((CASE brand WHEN 'branda' THEN txn_id ELSE NULL END)) AS branda,
    COUNT((CASE brand WHEN 'brandb' THEN txn_id ELSE NULL END)) AS brandb
 <I want my 3rd column here>
FROM
    transaction_data
        INNER JOIN
    products USING (sku)
        INNER JOIN
    nationalities USING (nationality_id)
GROUP BY nationality
ORDER BY branda DESC
LIMIT 20;

我试过使用:

COUNT((CASE brand WHEN 'brandb' OR 'brandb' THEN txn_id ELSE NULL END)) AS combined - 但是这显然返回太多(返回 branda 或 brandb，无论它们是否被购买一起)。我知道我不能使用 AND，因为显然没有一个单元格会同时是 branda 和 brandb。

我也试过使用:COUNT((CASE brand WHEN IN('branda', 'brandb') THEN txn_id ELSE NULL END)) AS combined - 然而这不是有效的语法。

我觉得我应该使用 HAVING 子句，但我不确定这在列列表中如何工作。

Answer 1

我认为您将需要两个级别的聚合:

SELECT n.nationality,
       sum(branda), sum(brandb), sum(branda * brandb)
FROM (SELECT t.txn_id, n.nationality,
             MAX(CASE brand WHEN 'branda' THEN 1 ELSE 0 END) AS branda,
             MAX(CASE brand WHEN 'brandb' THEN 1 ELSE 0 END) AS brandb
      FROM transaction_data t INNER JOIN
           products p
           USING (sku) INNER JOIN
           nationalities n
           USING (nationality_id)
      GROUP BY t.txn_id, n.nationality
     ) tn
GROUP BY n.nationality
ORDER BY max(txn_id) DESC
LIMIT 20;