Msql:随着时间的推移计数增长 [英] Msql: Counting growth over time
问题描述
我试图查询数据库以检索一段时间内唯一条目的数量。数据看起来像这样:
Day
| UserID
1 | A
1 | B
2 | B
3 | A
4 | B
4 | C
5 | D
我希望查询结果看起来像这样
时间跨度| COUNT(DISTINCT UserID
)
第1天到第1天| 2
第1天至第2天| 2
第1天至第3天| 2
第1天至第4天| 3
第1天至第5天| 4
如果我做了类似于
SELECT COUNT(DISTINCT` UserID`)FROM`table` GROUP BY`Day`
,不同的计数不会考虑用户ID以前的日子。
任何想法?我使用的数据集非常大,因此多重查询和后期处理需要很长时间(这就是我目前的做法)。
谢谢
您可以使用子查询
样本表
create table visits(day int,userid char(1));
插入访问值
(1,'a'),
(1,'b'),
(2,'b'),
(3, 'a'),
(4,'b'),
(4,'c'),
(5,'d');
查询
select d.day,(从当天访问日期<= d.day)选择count(distinct userid)
from(选择不同的访问日)d
I posted about this a few weeks ago, but I don't think I asked the question clearly because the answers I got were not what I was looking for. I think it's best to start again.
I'm trying to query a database to retrieve the number of unique entries over time. The data looks something like this:
Day
| UserID
1 | A
1 | B
2 | B
3 | A
4 | B
4 | C
5 | D
I'd like the query result to look this this
Time Span | COUNT(DISTINCT UserID
)
Day 1 to Day 1 | 2
Day 1 to Day 2 | 2
Day 1 to Day 3 | 2
Day 1 to Day 4 | 3
Day 1 to Day 5 | 4
If I do something like
SELECT COUNT(DISTINCT `UserID`) FROM `table` GROUP BY `Day`
, the distinct counts will not consider user IDs of previous days.
Any Ideas? The data set I'm using is quite large, so multiple-queries and post processing takes a long time (that's how I'm currently doing it).
Thanks
You can use a subquery
Sample table
create table visits (day int, userid char(1));
insert visits values
(1,'a'),
(1,'b'),
(2,'b'),
(3,'a'),
(4,'b'),
(4,'c'),
(5,'d');
The query
select d.day, (select count(distinct userid) from visits where day<=d.day)
from (select distinct day from visits) d
这篇关于Msql:随着时间的推移计数增长的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!