mysql - 统计不同表中的违规次数

Question

这个问题让我抓狂，因为我知道问题的根本原因，但不知道如何解决。这就是我要解决的问题我有 3 个表:

_表“Publisher”:列是“ASIN”、“publisher_ID”等。ASIN 当然是主键，因为 1 个出版商可以拥有很多书

_“权利”表:侵犯权利。列是“ASIN”、“违规类型”、“操作”(“失败”或“通过”)等。因此 1 个 ASIN 可以多次操作(例如:ASIN A - 商标 - 失败，ASIN A - 版权- 通过等)

_Table“升级”:我们收到的升级。列是“ASIN”等。

当然这些表格有更多列，但列出的列更相关。

现在我的目的是，当我输入出版商 ID 时，我希望查询能够为我提供我们收到的该出版商的升级计数，以及来自该出版商的未获得版权的图书数量

这就是我所拥有的

    SELECT publisher_ID,
           num_of_rights_violations,
           num_of_escalations 
    FROM (SELECT p.publisher_ID,
                 sum(if(e.asin = p.asin, 1, 0)) as num_of_escalations,
                 sum(if(r.asin = p.asin and r.acion = 'fail'),1,0) as num_of_rights_violations
          FROM publisher p
          LEFT JOIN rights r
               ON r.asin = p.asin
          LEFT JOIN escalations e
               ON e.asin = p.asin
          WHERE p.publisher_ID = 'xxx'
          GROUP BY p.publisher_ID) a

然而，结果却很奇怪。所以对于这个发布者来说，只有 3 个 ASIN 因侵权而失败。这 3 个 ASIN 总共上报给我们 6 次。此发布者总共升级了 18 次。所以，正确的结果应该是:xxx - 3 - 18。但它给了我 xxx - 6 - 18。所以我怀疑是出于某种原因 num_of_rights_violation，我的联合函数返回了 ASIN 的升级总数，在表 r 中找到并且操作是“失败”(3)，收到 (6)。

有人可以帮我解决这个问题吗？

问候，

Answer 1

我认为您需要在连接之前获取计数，因为第二个连接人为地增加了计数。

所以我们会有

Publisher  escalation right
1          A          Z
1          A          Y
1          A          X
2          A          1
2          B          1

因此，当您只想计算一次时，您将上面的发布者 1 的 A 计算了三次。由于连接的 2 个升级，当您只想要一个时，您会为发布者 2 正确计数两次。

您从升级中获得计数，并且首先正确，然后加入。 (首选 imo)

或者，如果我们区分升级和权利，我们可能会得到正确的计数(假设我们正在计算每个表的 PK)更多 co

SELECT p.publisher_ID
     , num_of_escalations,
     , num_of_rights_violations
FROM publisher p
LEFT JOIN (SELECT asin, count(*) as Num_of_Escalations 
           FROM rights where acion = 'fail' 
           GROUP BY asin)  r
   ON r.asin = p.asin
LEFT JOIN (SELECT asin, count(*) as num_of_rights_violations 
           FROM escalations 
           GROUP BY asin) e
   ON e.asin = p.asi
WHERE p.publisher_ID = 'xxx'

另一种可行的方法是计算每个表的主键的不同值。

SELECT p.publisher_ID
     , count(distinct e.pk) as num_of_escalations --assume each table has a pk change the pk to the primary key field of table
     , count(distinct r.pk) as num_of_rights_violations --assume each table has a pk change the pk to the primary key field of table
FROM publisher p
LEFT JOIN rights r
  ON r.asin = p.asin
 AND r.acion='fail' --moved limit to join 
LEFT JOIN escalations e
  ON e.asin = p.asin
WHERE p.publisher_ID = 'xxx'
GROUP BY p.publisher_ID

替代引擎也支持可以执行此操作的窗口函数，或者您可以使用用户变量模拟它们；但我认为上述概念之一应该可行。