求教大佬数据库问题

mysql 相想查询七天之前的总数，查询 6 天之前的总数 5 天之前的总数。。。1 天之前的总数当天总数并按照七天日期分组，有没有一条语句就能解决的办法呢目前是 SELECT COUNT(id) as count FROM applist WHERE time<DATE_SUB(NOW(), INTERVAL 6 DAY) 循环执行进行查询有没有更好的办法呢

总数

count

查询

applist

11 条回复 • 2020-02-04 10:46:43 +08:00

liprais

2020-02-03 17:52:22 +08:00

算出每天的量，再去选不就行了

phpfpm

2020-02-03 17:55:07 +08:00

你直接 group by 一下然后用程序 sum 得了。

另一个思路：group by 然后自连接（想想 howto

zzn

2020-02-03 18:05:29 +08:00

可以生写多个 count filter, `COUNT(*) FILTER (WHERE <condition>)`

类似这样：

```
SELECT COUNT(id) FILTER( WHERE time<DATE_SUB(NOW(), INTERVAL 6 DAY)) AS _6, COUNT(id) FILTER( WHERE time<DATE_SUB(NOW(), INTERVAL 5 DAY)) AS _5
```

akira

2020-02-03 18:05:50 +08:00

每天生成一次比较合理吧

zzn

2020-02-03 18:10:05 +08:00

@zzn 忘了说，pg 可以这样写，mysql 不知道行不行

toesbieya

2020-02-03 18:17:22 +08:00

和 3 楼写的差不多，
SELECT COUNT(0) count7,sum(if(time<DATE_SUB(NOW(),INTERVAL 6 DAY),1,0)) count6... FROM applist WHERE time<DATE_SUB(NOW(), INTERVAL 7 DAY)

lxk11153

2020-02-03 19:34:56 +08:00

头像交出来

chaodada

2020-02-03 20:11:42 +08:00

@lxk11153 #7 http://www.sohu.com/a/238654495_612229 去这里拿吧原图我也没找到哈哈哈哈哈哈哈哈

chaodada

2020-02-03 20:12:38 +08:00

@toesbieya #6
@zzn #5
@akira #4
@zzn #3
@phpfpm #2
@liprais #1 感谢大佬们的指教我写一下实施

chaodada

2020-02-04 09:40:08 +08:00

@phpfpm #2
@zzn #3
@zzn #5
大佬们我最终写成了这样的到了我想要的结果不知道有没有更好的办法
select temp.* from
(
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 6 DAY) as count_time FROM `test` WHERE `reg_time`<DATE_SUB(NOW(), INTERVAL 6 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 5 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 5 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 4 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 4 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 3 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 3 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 2 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 2 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 1 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 1 DAY)
UNION ALL
SELECT COUNT(`user_id`) as count , DATE_SUB(NOW(), INTERVAL 0 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 0 DAY)
) as temp

phpfpm

2020-02-04 10:46:43 +08:00

首先你最好有一个维度表（各个日期的维度表）
select date from date_list
join xxx on xxx.date>{begin date}

这样将数据从 n 天 m 个数据扩展成 n*m 行

然后 group by date
然后 count 你的条件就好（某天单独的，大于某天的）等