SQL Query to group dates and includes different dates in the aggregation-CodePudding

I have a table with two columns, dates and number of searches in each date. What I want to do is group by the dates, and find the sum of number of searches for each date. The trick is that for each group, I also want to include the number of searches for the date exactly the following week, and the number of searches for the date exactly the previous week.

So If I have

Date	Searches
2/3/2023	2
2/10/2023	4
2/17/2023	1
2/24/2023	5

I want the output for the 2/10/2023 and 2/17/2023 groups to be

Date	Sum
2/10/2023	7
2/17/2023	10

How can I write a query for this?

CodePudding user response：

You can use the SQL function DATE_ADD to add or subtract a certain number of days from a date, and the GROUP BY clause to group the rows by date. Here's an example in MySQL:

SELECT 
  date, 
  SUM(searches) AS sum
FROM 
  (
    SELECT 
      date, 
      searches
    FROM 
      my_table
    UNION ALL
    SELECT 
      DATE_ADD(date, INTERVAL -7 DAY), 
      searches
    FROM 
      my_table
    UNION ALL
    SELECT 
      DATE_ADD(date, INTERVAL 7 DAY), 
      searches
    FROM 
      my_table
  ) AS all_dates
GROUP BY 
  date

Note that in this example, the my_table name should be replaced with the actual name of your table.

CodePudding user response：

You can use a correlated query for this:

select date, (
    select sum(searches)
    from t as x
    where x.date between t.date - interval '7 day' and t.date   interval '7 day'
) as sum_win
from t

Replace interval 'x day' with the appropriate date add function for your RDBMS.

If your RDBMS supports interval in window functions then a much better solution would be:

select date, sum(searches) over (
    order by date
    range between interval '7 day' preceding and interval '7 day' following
) as sum_win
from t