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

# 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#
 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