How can I combine 2 SQL queries and retrieve a cumulative count?

I'm trying to retrieve the number of visits and number of conversions each day for a particular A/B test combination. Each combination represents a different variation of an A/B test. Here I'm using just '1' and '2' to represent the variations, but technically there could be more variations.

I've written the following 2 queries, which work independently. Is it possible to combine these or write a single query that retrieves the data I want?

visits query:

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

visits result:

conversions query:

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

conversions result:

Also it would be great if I could get retrieve a running total (cumulative) count, like below, see the last 2 columns. I've grouped the the table below by combination so the cumulative counts are easier to understand:

+---------------+-------------+----------------------+-----------------+--------------+--------------+
| 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 |
+---------------+-------------+----------------------+-----------------+--------------+--------------+

The database schema:

---

Combining is quite simple: add 0-value columns, do UNION_ALL , then group again and sum.

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

Now, for running total. In more advanced DBMS this is called "window" or "analytic" functions. For example, in Oracle you can do this:

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

for the above query, and it would give you just what you want. However, MySQL doesn't have such functions. There are ways around, described here and here for example, but they are quite tricky.

---

我希望这会有所帮助。

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/89184.html

上一篇: 将JavaScript函数应用于除第i个元素之外的所有Array元素

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