Home How to aggregate its previous 30 days for each line in MySQL without using self-join?
Reply: 1

How to aggregate its previous 30 days for each line in MySQL without using self-join?

Ken Skywalker
1#
Ken Skywalker Published in 2018-01-13 06:12:13Z

Suppose we have a table Named UserSessions. Each user can have multiple sessions in a day or across different days. The columns of this table are

UserSessions table: 
---------------------
SessionID (int) PK, 
UserID (int), 
datetime (datetime) 

and sample data look like this:

1, 1001, 2017-01-01  
2, 1001, 2017-01-01  
3, 1002, 2017-01-01   
4, 1002, 2017-01-02  
...  

Now, we want to write a query that display the count of distinct users in last 30 days for each day. What is the best way to do this?

An obvious solution is to use self-join, but we hope we can avoid that.
Does it has something to do with the window function? I am wondering if there is a feature that, for each line, we can aggregate within x rows ahead and y rows after.

For example, similar questions like "what is the average of orange price in a window of 30 days ahead of each day recorded so far" etc. To be more specific, it's like asking an aggregation over a period of x-30 to x-1 days on day x.

scaisEdge
2#
scaisEdge Reply to 2018-01-13 07:42:09Z

you could use a group by ..eg:

  select UserId, count(*)
  from my_table 
  where datetime between date_sub(now(), interval 30 day ) 
                        AND date_sub(now(), interval 1 day )
  group by UserID
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317507 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO