0
已经使用 Google 搜索过,没找到合适的答案(有些看不懂)

有以下表:

表字段:
id 是用户唯一 id
reg_time 是用户注册时间戳
mysql> SELECT id,reg_time FROM `apps_user` ;
+----+------------+
| id | reg_time   |
+----+------------+
|  1 | 1524322845 |
|  2 | 1524322945 |
|  3 | 1524323277 |
|  4 | 1524325325 |
|  5 | 1524325451 |
|  6 | 1524325517 |
|  7 | 1524340390 |
|  8 | 1524346779 |
|  9 | 1524346875 |
| 10 | 1524419896 |
| 11 | 1524456557 |
| 12 | 1524456564 |
| 13 | 1524456663 |
| 14 | 1524499581 |
| 15 | 1524504637 |
| 16 | 1524504718 |
| 17 | 1524523875 |
| 18 | 1524528715 |
| 19 | 1524542755 |
| 20 | 1524543480 |
+----+------------+
20 rows in set (0.00 sec)

我想要获取近 7 天、自定义时间范围内中的 每一天用户注册量

例如,我想要近 7 天中每一天的用户注册量,预想返回结果如下(这里的预想返回结果与开头表的数据无关联):
+-------------+-------+
|    date     | total |
+-------------+-------+
|  2019-04-19 |   3   |
|  2019-04-20 |   0   |
|  2019-04-21 |   0   |
|  2019-04-22 |   8   |
|  2019-04-23 |   0   |
|  2019-04-24 |   9   |
|  2019-04-25 |   2   |

请大神解答,先谢谢了!

4月25日提问
2 个回答
0

7天的可以这样:
select a.click_date time1,ifnull(b.idcount,0) idcount

            from (
                SELECT date_sub(curdate(), interval 6 day) as click_date
                union all
                SELECT date_sub(curdate(), interval 5 day) as click_date
                union all
                SELECT date_sub(curdate(), interval 4 day) as click_date
                union all
                SELECT date_sub(curdate(), interval 3 day) as click_date
                union all
                SELECT date_sub(curdate(), interval 2 day) as click_date
                    union all
                SELECT date_sub(curdate(), interval 1 day) as click_date
                union all
                SELECT curdate() as click_date
            ) a left join (
              select date(addtime) as datetime, count(id) idcount 
              from user where addTime>=DATE_SUB(NOW(),interval 7 day)
              group by date(addtime)
            ) b on a.click_date = b.datetime
0
SELECT
    count(
        IF (
            reg_time > unix_timestamp(now()) - 7 * 86400,
            id,
            NULL
        )
    ) AS count_7,
    count(
        IF (
            reg_time > unix_timestamp(now()) - 15 * 86400,
            id,
            NULL
        )
    ) AS count_15,
    count(
        IF (
            reg_time > unix_timestamp(now()) - 30 * 86400,
            id,
            NULL
        )
    ) AS count_30
FROM
    USER;

撰写答案

推广链接