Объединение таблиц с различными измерениями данных (еженедельно и ежедневно)

Подзапроса будет достаточно, но только если вас интересуют именно последние 7 дней для каждой строки

select id, rating, aquisition_date , 
(select avg(value) from table2 a2 where a2.id = a1.id and 
a2.aquisition_date between a1.aquisition_date - 6 and a1.aquisition_date) avg
from table1 a1

Полученные результаты

В прикрепленном примере вы также указываете значения времени (но из-за определения таблицы они обрезаются во время вставки)

Краткий ответ: Вы должны включить неделю в JOIN:

SELECT 
    table1.id
    ,EXTRACT(WEEK FROM table1.aquisition_date) AS week
    ,table1.rating AS table1_rating
    ,AVG(table2.value) as table2_value_avg
FROM 
    table1 table1
INNER JOIN 
    table2 table2
ON
    table2.id =   table1.id AND
    EXTRACT(WEEK FROM table1.aquisition_date) = EXTRACT(WEEK FROM table2.aquisition_date)
GROUP BY
    table1.id
   ,table1.rating
   ,table1.aquisition_date
;

Это дает нам

id  week    table1_rating   table2_value_avg
1   17      8.2             20.0000000000000000
1   16      10              13.1428571428571429

Длинный ответ: Как нам туда добраться? Рассмотрим внутреннее соединение без как агрегацию:

Ваша первая попытка

SELECT 
    table1.id
    ,EXTRACT(WEEK FROM table1.aquisition_date) AS week1
    ,EXTRACT(WEEK FROM table2.aquisition_date) AS week2
--    ,AVG(EXTRACT(WEEK FROM table2.aquisition_date)) AS week2
    ,table1.rating AS table1_rating
    ,table2.value AS table2_value
--    ,AVG(table2.value) as table2_value_avg
FROM 
    table1 table1
INNER JOIN 
    table2 table2
ON
    table2.id =   table1.id

Это сопоставит строки из обеих таблиц, которые не принадлежат друг другу, например, значения за 15-ю неделю с рейтингами за 16-ю неделю.

id  week1   week2   table1_rating   table2_value
1   16      15      10              2
1   16      15      10              4
[... 40 more rows]

На самом деле он соответствует каждой строке из таблицы 1 с таблицей 2. Вот почему среднее значение является мировым средним, даже если GROUP BY работает.

Таким образом, решение, приведенное выше, заключается в удалении тех строк из внутреннего соединения, где недели не совпадают.

N.B.: Если вы хотите включить данные в свои результаты, где данные есть только в одной из таблиц, вам нужен FULL OUTER JOIN, но идея та же.

NB: (Спасибо, @shawnt00) (ДОПОЛНИТЕЛЬНАЯ НЕДЕЛЯ ОТ ...) означает ISO 8601 неделя. Она начинается в понедельник, и могут быть другие определения недели.

NB: (Спасибо @shawnt00) В решении год не учитывается. Если присутствуют данные более чем за один год, вам необходимо сопоставить год и неделю в JOIN.