mysql - 使用 SQL 计算 "activation"百分比

Question

所以我有以下 SQL 架构 ( http://sqlfiddle.com/#!2/b366c )，我想要实现的是我认为已激活的公司的百分比。

在架构中，您可以看到有以下表格

1. 组织(也称为公司)
2. 比赛
3. 比赛成员
4. 事件条目

我想做的是，计算组织中公司的百分比(即总用户)创建竞赛(竞赛表)，至少邀请另一个人(竞赛成员表)并完成至少一项事件(事件条目表)

这可能太复杂了，但我还想做的是创建一个漏斗 - 以可视化大多数公司在何处下车。为此，我知道我应该为每个步骤创建一个单独的查询，然后将它们堆叠起来以查看流程。

使用此处提供的示例数据 (http://sqlfiddle.com/#!2/b366c)，您可以看到:

1. 4家企业已注册
2. 2家公司创建了比赛
3. 1 家公司有至少 2 名参赛者参加的比赛(不仅仅是管理员)
4. 1家公司至少注册了一项事件

因此 25% 的公司变得“活跃”。

如果能帮助我构建这些查询和可视化百分比，我将不胜感激!

Answer 1

也许不是最有效的方法，但中间结果应该足够小，所以这无关紧要。

您可以自行运行内部查询以查看不同的结果:

SELECT COUNT(oid) AS organizations,
       SUM(IF(competitions > 0, 1, 0)) AS competing,
       SUM(IF(activations > 0, 1, 0)) AS activated,
       100.0*SUM(IF(activations > 0, 1, 0))/COUNT(oid) AS actpercent
FROM (
  SELECT oid,
         SUM(IF(cid IS NULL,0,1)) AS competitions,
         SUM(IF(aid IS NULL,0,1)) AS activations
  FROM (
    SELECT
        o.organisationId AS oid,
        c.competitionId AS cid,
        a.id AS aid
        FROM organisations AS o
        LEFT JOIN competitions c USING (organisationId)
        LEFT JOIN activity_entries AS a USING (competitionId)
  ) AS situation GROUP BY oid
) AS summary;

首先我们得到所有组织、比赛和事件的情况列表；在这里您可以添加 WHERE 条件来过滤感兴趣的组织、已删除的比赛等。

从这里我们得到组织的摘要，以及每个组织的比赛和激活数量。如果激活，每个比赛只能计为一个(即，如果您获得三个比赛，一个有三个事件，两个有零个，您将检索三个作为比赛数，一个作为激活数)。

然后我们只获取组织的总数，并计算激活数的百分比。

上面的输出是，

ORGANIZATIONS   COMPETING   ACTIVATED   ACTPERCENT
4                  2            1           25

加法

lserni would it be possible to add one more layer to your query, which is the "inviting" aspect. i.e. if there are more than 2 users in the competitionmembers table for a competition?

在这种情况下，对于每个比赛，我们需要知道另一个表中有多少成员。所以我们必须对 competitionId 可用的查询进行操作，我们修改 situation:

  SELECT
        o.organisationId AS oid,
        c.competitionId AS cid,
        a.id AS aid
        FROM organisations AS o
        LEFT JOIN competitions c USING (organisationId)
        LEFT JOIN activity_entries AS a USING (competitionId)

我们只需添加必要的GROUP BY existing-columns 和新的聚合字段，当然还有必要的LEFT JOIN:

  SELECT
        o.organisationId AS oid,
        c.competitionId AS cid,
        a.id AS aid,
        COUNT(m.id) AS members
  FROM organisations AS o
  LEFT JOIN competitions c USING (organisationId)
  LEFT JOIN activity_entries AS a USING (competitionId)
  LEFT JOIN competitionmembers AS m ON (c.competitionId = m.competitionid)
  GROUP BY oid, cid, aid;

(我认为这说明了嵌套“序列化”查询的优势之一 - 它们更易于维护。至少这是我的观点。也许事实只是我无法围绕更复杂的一体化查询...)。

现在我们有了竞争的成员，我们立即查看上述查询之外的查询:

  SELECT oid,
         SUM(IF(cid IS NULL,0,1)) AS competitions,
         SUM(IF(aid IS NULL,0,1)) AS activations
  FROM v_situation GROUP BY oid

顺便说一句:您可以通过将查询卸载到 VIEW 来简化这些查询的编写。 CREATE VIEW v_situation AS SELECT o.organisationId AS oid, ... GROUP BY oid, cid, aid; 并且您有一个虚拟表 v_situation 可以在任何地方使用一张 table )。

...并重写它，添加一个成员和更多成员的比赛次数:

SELECT oid,
  SUM(IF(cid IS NULL,0,1)) AS competitions,
  SUM(IF(aid IS NULL,0,1)) AS activations,
  SUM(IF(members > 1, 1, 0)) AS withmany,
  SUM(IF(members = 1, 1, 0)) AS withone
FROM ( ... ) AS situation
GROUP BY oid;

然后您只需要决定如何处理这些信息。您可以通过它并重新选择父查询中的 withone 字段，也可以计算它的百分比。只有在这种情况下，请记住比赛的数量可能为零，因此您需要在以下情况下采取行动

activations_with_many_members / activations

在分母处有一个零，如果不存在激活，则使用 IF 将公式更改为 0.0:

IF(activations > 0, <percent formula>, 0.0 ) AS percent_with_many

此外，如果您只希望在存在激活的地方加入成员，您应该在 members 的定义中这样做，这样只有当成员的 id 不为 null 时才计算该成员(我们有一个成员)并且援助不为空(我们有激活):

SUM(IF(a.id IS NOT NULL AND m.id IS NOT NULL,1, 0)) AS members