我如何组合2个SQL查询并检索累计计数？

我正在尝试检索特定A / B测试组合的每日访问次数和转化次数。 每种组合代表A / B测试的不同变化。 在这里，我只用'1'和'2'来表示变化，但技术上可能会有更多的变化。

我写了以下2个独立工作的查询。 是否可以组合这些或编写一个查询来检索我想要的数据？

访问查询：

SELECT DATE(visit.created), visit.combination, COUNT(visit.id) 
FROM visit 
WHERE visit.user_id = 6
AND visit.experiment_id = 1
GROUP BY DATE(visit.created), visit.combination

访问结果：

转换查询：

SELECT DATE(conversion.created), conversion.combination, COUNT(conversion.id) 
FROM conversion 
WHERE conversion.user_id = 6
AND conversion.experiment_id = 1
AND conversion.goal_id = 1
GROUP BY DATE(conversion.created), conversion.combination

转化结果：

如果我能得到一个正在运行的总数（累计），如下所示，这将是非常好的，请参见最后2列。 我已将下表按组合分组，所以累计计数更容易理解：

+---------------+-------------+----------------------+-----------------+--------------+--------------+
| DATE(created) | combination | COUNT(conversion.id) | COUNT(visit.id) | cumulative_c | cumulative_v |
+---------------+-------------+----------------------+-----------------+--------------+--------------+
| 2015-11-17    | 1           |                    1 |               3 |            1 |            3 |
| 2015-11-18    | 1           |                    7 |               4 |            8 |            7 |
| 2015-11-19    | 1           |                    3 |               8 |           11 |           15 |
| 2015-11-17    | 2           |                    4 |               1 |            4 |            1 |
| 2015-11-18    | 2           |                    2 |               6 |            6 |            7 |
| 2015-11-19    | 2           |                    9 |               6 |           15 |           13 |
+---------------+-------------+----------------------+-----------------+--------------+--------------+

数据库模式：

---

组合非常简单：添加0值列，执行UNION_ALL ，然后再次组合并求和。

SELECT dt, combination, SUM(v_count) as v_count, SUM(c_count) as c_count
FROM 
(
SELECT DATE(visit.created) as dt, visit.combination as combination, COUNT(visit.id) as v_count, 0 as c_count
FROM visit 
WHERE visit.user_id = 6
AND visit.experiment_id = 1
GROUP BY DATE(visit.created), visit.combination

UNION ALL

SELECT DATE(conversion.created) as dt, conversion.combination as combination, 0 as v_count, COUNT(conversion.id) as c_count
FROM conversion 
WHERE conversion.user_id = 6
AND conversion.experiment_id = 1
AND conversion.goal_id = 1
GROUP BY DATE(conversion.created), conversion.combination
) as t
GROUP BY dt, combination

现在，运行总数。 在更高级的DBMS中，这被称为“窗口”或“分析”功能。 例如，在Oracle中，您可以这样做：

SELECT dt, combination, SUM(v_count) OVER (PARTITION BY combination ORDER BY dt) as v_cumulative

对于上面的查询，它会给你正是你想要的。 但是，MySQL没有这样的功能。 有办法，在这里和这里举例说明，但它们非常棘手。

---

我希望这会有所帮助。

SELECT DATE(v.created), v.combination ,DATE(c.created), c.combination, COUNT(v.id), COUNT(c.id) FROM user u 
LEFT JOIN conversion c ON u.id = c.user_id
LEFT JOIN visit v ON u.id = v.user_id
WHERE c.id = 6 AND v.experiment_id = 1 AND c.goal_id = 1
GROUP BY DATE(v.created), v.combination, DATE(c.created), c.combination

链接地址: http://www.djcxy.com/p/89183.html

上一篇: How can I combine 2 SQL queries and retrieve a cumulative count?

下一篇: How do I increase the speed of my Postgres select statement?