mysql - 多个 LEFT JOINs to self 与标准产生分配

Question

尽管several . questions . come . close .到我想要的(并且在我写这个 stackoverflow 时提出了更多建议，但没有一个能完全解决我的问题)，我似乎无法找到摆脱 SQL 丛林的出路。

我有一个表(我们称它为 user_classification_fct)，它包含三个字段:用户、周和类(class)(例如，第 1 周的第 1 名用户的类别为“普通用户”，而第 2 周的用户#1 有一类“不常使用的用户”)。 (顺便说一句，我已经将类实现为 INT，但我希望在整理 SQL 时使用 VARCHAR 形式的易读内容。)

我想做的是根据以下几行聚合生成用户行为如何变化的摘要报告:

1. 在第 1 周和第 2 周有 50 位用户是普通用户，并且...
2. 有 10 位用户在第 1 周是常规用户，但在第 2 周下降为非频繁用户
3. 有 5 位用户从第 1 周的不频繁变为第 2 周的常规
4. ...等等...

让这稍微有点棘手的是，用户 #5000 可能只在第 2 周才开始使用该服务，因此在第 1 周的表中没有记录。在这种情况下，我希望看到 NULL FOR week 1 和第 2 周的“普通用户”(或任何合适的用户)。表的大小并不严格相关，但有 5 周的数据，我正在查看 4200 万行，所以我做 不想为在第 5 周左右才开始使用该服务的人插入 4 个“假”行的“非用户”。

对我来说，这似乎很明显像是在 MySQL 中使用 LEFT 或 RIGHT JOIN 的情况，因为 NULL 应该通过“缺失”记录。

我已经尝试在 LEFT JOIN 上同时使用 WHERE 和 AND 条件，但只是没有得到“正确”的答案(即，在尾随 WHERE 条件的情况下，我要么根本没有得到 NULL 值，要么我的计数很远，在下面使用的 AND 约束的情况下，对于不同用户的数量(大约 1000 万)来说太高了)。这是我最后一次尝试让它工作:

SELECT
    ucf1.class_nm AS 'Class in 2012/15',
    ucf2.class_nm AS 'Class in 2012/16',
    ucf3.class_nm AS 'Class in 2012/17',
    ucf4.class_nm AS 'Class in 2012/18',
    ucf5.class_nm AS 'Class in 2012/19',
    count(*) AS 'Count'
FROM
    user_classification_fct ucf5
LEFT JOIN user_classification_fct ucf4 
    ON ucf5.user_id=ucf4.user_id 
        AND ucf5.week_key=201219 AND ucf4.week_key=201218
LEFT JOIN user_classification_fct ucf3 
    ON ucf4.user_id=ucf3.user_id 
       AND ucf4.week_key=201218 AND ucf3.week_key=201217
LEFT JOIN user_classification_fct ucf2 
    ON ucf3.user_id=ucf2.user_id 
       AND ucf3.week_key=201217 AND ucf2.week_key=201216
LEFT JOIN user_classification_fct ucf1 
    ON ucf2.user_id=ucf1.user_id 
       AND ucf2.week_key=201216 AND ucf1.week_key=201215
GROUP BY 1,2,3,4,5;

在查看 stackoverflow.com 上的各种其他问题时，很可能我需要一次执行一个查询并将结果集 UNION 在一起或使用括号将它们一对一地链接起来，但这些方法不是我(还)熟悉的方法，我什至无法获得单个 LEFT JOIN(即第 5 周到第 1 周，删除所有其他周的数据)来返回有用的东西。

任何提示都会非常非常感谢，我非常感谢在 MySQL 中工作的建议，因为切换数据库产品不是一种选择。

Answer 1

您可以使用分组依据来执行此操作。我首先将这五周的所有可能组合总结为:

select c_201215, c_201216, c_201217, c_201218, c_201219,
       count(*) as cnt
from (select user_id,
             max(case when week_key=201215 then class_nm end) as c_201215,
             max(case when week_key=201216 then class_nm end) as c_201216,
             max(case when week_key=201217 then class_nm end) as c_201217,
             max(case when week_key=201218 then class_nm end) as c_201218,
             max(case when week_key=201219 then class_nm end) as c_201219
      from user_classification_fct  ucf
      group by user_id
     ) t
group by c_201215, c_201216, c_201217, c_201218, c_201219

这可能会解决您的问题。如果您有 5 个类(包括 NULL)，那么这将返回最多 5^5 或 3,125 行。

这适用于 Excel，因此您可以在那里进行最后的处理。或者，您仍然可以使用数据库。

如果你想提取成对的星期，那么我建议将上面的内容放入一个临时表中，比如“t”。并使用联合进行一系列摘录:

select *
from ((select '201215' as weekstart, c_201215, c_201216, sum(cnt) as cnt
       from t
       group by c_201215, c_201216
      ) union all
      (select '201216', c_201216, c_201217, sum(cnt) as cnt
       from t
       group by c_201216, c_201217

      ) union all
      (select '201217', c_201217, c_201218, sum(cnt) as cnt
       from t
       group by c_201217, c_201218

      ) union all
      (select '201218', c_201218, c_201219, sum(cnt) as cnt
       from t
       group by c_201218, c_201219
      )
     ) tg
order by 1, cnt desc

我建议将它放在子查询中，因为您不想在如此大的表上使用公共(public)子查询优化来传递信息。您将首先进行总结，然后将数据整合在一起，从而得出最终答案。