logo标签列表

MySQL交叉表聚合查询，带有两个条件。

mysqlsqlcrosstab
4

我有一个查询，用于创建交叉表。结果是计算“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

还有两张表格 (产品) - (sku, 品牌, 产品名称) 和 (国籍) - (国籍 ID, 国籍)。

我想新增第三列，以获取购买了两个品牌的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，但是这显然会返回太多数据（无论是否一起购买，都会返回品牌a或品牌b）。我知道不能使用AND，因为显然没有单个单元格既是品牌a又是品牌b。
我还尝试过：COUNT((CASE brand WHEN IN('branda', 'brandb') THEN txn_id ELSE NULL END)) AS combined - 但这不是有效的语法。
我觉得应该使用HAVING子句，但我不确定它在列列表中该如何工作。
1个回答
0

我认为你需要两个层次的聚合：

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;
1
这个答案是有效的并被接受，但需要注意的是，这个查询在大表（3M行）上的性能表现非常糟糕。 - Adam Copley
@AdamCopley……你的新查询性能真的比原来的要差那么多吗？ - Gordon Linoff
是的，它不能在一分钟内运行（这时我停止了它），所以我将其拆分成块并仅运行了 subquery。然后我从“列列表”和“group by”中剥离了 txn_id 并得到了一些结果。通过这些结果，我可以得出如果有“txn_id”，则集合将是正确的结论。当我周一回到办公室时，我会发布一些解释，以显示更多关于索引和长度的详细信息。我所继承的垃圾表结构也不是很有用，因为 350 万行中有 150 万个不同的交易 ID，每个“txn id”之间的行数在 1 至 20 行之间不等。 - Adam Copley
@AdamCopley . . . 也许你应该问另一个关于如何提高性能的问题。我还应该指出，你原来的查询实际上需要使用count(distinct)而不是count()来完成你想要的操作...你可能会发现distinct真的会影响性能。 - Gordon Linoff
你说得完全正确，我现在想起来了。按照我的方法，当同一篮子里购买某个品牌两次时，我得到的计数是2，但实际上应该是1。哦天啊，为什么我会认为用PHP/MySQL来表示几百万是个好主意！！ - Adam Copley
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接