gpt4 book ai didi

mysql - 在 mysql 中选择新用户和返回用户的总票数

转载 作者:行者123 更新时间:2023-11-30 23:24:09 25 4
gpt4 key购买 nike

我有 2 个表:投票和用户。我想在当天的总票数旁边按日期显示新注册用户的总票数(不是总和)。

查询的时间范围可以是所有,我可以稍后添加from - 到时间范围。

因此,如果每个日期 X 我有 10 票,我想知道有多少票是由新用户产生的,有多少是由回访用户产生的,以及有多少新用户和回头用户投了票(* 还包括没有投票的新用户投票)。

票数:

id | vote | user_id | created_at
1 | 30 | 28 | 2012-06-10
1 | 12 | 15 | 2012-06-10
1 | 30 | 28 | 2012-06-10
...

用户:

users_ id | created_at
28 | 2012-06-01
29 | 2012-06-03
30 | 2012-06-10
...

我想要得到的结果是这样的:

Date | total votes | votes for new | votes for returning | total new users | total returning users

谢谢!

----- 当前代码:

        SELECT
DATE(created_at) AS Date,
SUM(CASE WHEN `Type` = 'Votes' THEN 1 ELSE 0 END) AS 'Total Votes',
SUM(CASE WHEN `Type` = 'Users' THEN 1 ELSE 0 END) AS 'Total Users'
FROM
(
SELECT created_at, 'Votes' `Type` FROM votes
UNION ALL
SELECT created_at, 'Users' FROM users
) t
GROUP by DATE(created_at)
ORDER by DATE(created_at) DESC

最佳答案

我会考虑在几个查询中分解它。如果我对你的理解是正确的,这应该是你要找的。

所有投票日期:

SELECT created_at AS date, COUNT(*) AS totalvotes FROM votes GROUP BY created_at;

所有新用户pr date的投票

SELECT v.created_at AS date, COUNT(*) AS newuservotes FROM votes v INNER JOIN users u WHERE v.user_id = u.user_id AND v.created_at = u.created_at GROUP BY v.created_at;

所有老用户pr date的投票

The two numbers subtracted

如果你想在一个查询中完成所有事情:

SELECT totvotes.date AS date, totvotes.totalvotes AS totalvotes, IFNULL(newvotes.newuservotes, 0) AS newuservotes, totvotes.totalvotes-IFNULL(newvotes.newuservotes, 0) AS olduservotes
FROM
(
SELECT created_at AS date, COUNT(*) AS totalvotes FROM votes GROUP BY created_at
) totvotes
LEFT JOIN
(
SELECT v.created_at AS date, COUNT(*) AS newuservotes FROM votes v INNER JOIN users u WHERE v.user_id = u.user_id AND v.created_at = u.created_at GROUP BY v.created_at
) newvotes ON totvotes.date = newvotes.date

测试了mysql> 从用户中选择 *;

+---------+------------+
| user_id | created_at |
+---------+------------+
| 1 | 2012-06-01 |
| 2 | 2012-06-02 |
| 3 | 2012-06-03 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> 从投票中选择 *;

+----+------+---------+------------+
| id | vote | user_id | created_at |
+----+------+---------+------------+
| 1 | 10 | 1 | 2012-06-01 |
| 2 | 20 | 1 | 2012-06-10 |
| 3 | 30 | 2 | 2012-06-02 |
| 4 | 40 | 2 | 2012-06-10 |
+----+------+---------+------------+
4 rows in set (0.00 sec)

结果:

+------------+------------+--------------+--------------+
| date | totalvotes | newuservotes | olduservotes |
+------------+------------+--------------+--------------+
| 2012-06-01 | 1 | 1 | 0 |
| 2012-06-02 | 1 | 1 | 0 |
| 2012-06-10 | 2 | 0 | 2 |
+------------+------------+--------------+--------------+

类似的东西可以用于“从未投票”的用户等等

我不确定你对 SQL 的精通程度,所以如果有任何问题,请发表评论,我会更新答案。

关于mysql - 在 mysql 中选择新用户和返回用户的总票数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14120560/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com