PostgreSQL — получить значение из той же таблицы

Прежде всего, вам нужно исправить свои недостатки в дизайне схемы таблицы и объявить:

• даты с типом "ДАТА" (вместо VARCHAR(50))
• недельные значения с типом INT (вместо VARCHAR(5))
• значения трафика с типом DECIMAL (вместо FLOAT)

CREATE TABLE tab(
    DATE      DATE,
    Tname     VARCHAR(50),
    Week      INT,
    Traffic   DECIMAL(4,2)
);

После того, как вы выполнили это, вы можете решить эту проблему следующим образом:

• создание значения рейтинга для каждого дня недели в ваших неделях, используя EXTRACT на вашей дате
• извлечение значения вашего трафика с предыдущей недели с помощью LAG, путем разделения вашего рейтинга, созданного на предыдущем шаге, и упорядочения по номеру недели.

WITH cte AS (
    SELECT date, Tname, Week, Traffic,
           ROUND(AVG(Traffic) OVER(PARTITION BY Week), 2) AS AVGTraffic,
           EXTRACT(ISODOW FROM date) - 1                  AS week_day
    FROM tab
)
SELECT date, Tname, Week, 
       LAG(Traffic) OVER(PARTITION BY week_day ORDER BY Week) AS prevweek_traffic,
       AVGTraffic
FROM cte
ORDER BY Week, week_day

И если вы понимаете, что у вас могут быть пробелы среди ваших недель (..., неделя 17, неделя 18, неделя 20, неделя 21, ...) и вам нужны значения именно за предыдущую неделю (которые могут отсутствовать), вы можно добавить фильтр к функции LAG, которая проверяет, являются ли неделя и предыдущая неделя последовательными:

...
CASE WHEN LAG(Week) OVER(PARTITION BY week_day ORDER BY Week) = Week-1
     THEN LAG(Traffic) OVER(PARTITION BY week_day ORDER BY Week) 
END
...

(только вместо LAG(Traffic) OVER(...))

Выход:

Посмотрите демо здесь.

Этот запрос допускает любые дыры в ваших данных, если это необходимо.

Примечание. Последнее предложение ORDER BY не требуется. Он там только для целей визуализации.

Используйте оконную функцию LAG с фреймом 7 PRECEDING, чтобы получить значения за 7 дней назад, чтобы избежать использования ненужных подзапросов или даже самостоятельных соединений, например.

SELECT 
  date, Tname, Week,
  LAG(traffic,7) OVER (PARTITION BY tname ORDER BY tname,to_date(date,'mm/dd/yyyy')),
  AVG(traffic)   OVER (PARTITION BY week,tname)
FROM a
ORDER BY tname,week;

   date    | tname | week |  lag  |        avg        
-----------+-------+------+-------+-------------------
 5/1/2023  | ID1   | 18   |       | 7.492857142857143
 5/2/2023  | ID1   | 18   |       | 7.492857142857143
 5/3/2023  | ID1   | 18   |       | 7.492857142857143
 5/4/2023  | ID1   | 18   |       | 7.492857142857143
 5/5/2023  | ID1   | 18   |       | 7.492857142857143
 5/6/2023  | ID1   | 18   |       | 7.492857142857143
 5/7/2023  | ID1   | 18   |       | 7.492857142857143
 5/8/2023  | ID1   | 19   |  7.98 | 7.988333333333333
 5/9/2023  | ID1   | 19   |  4.44 | 7.988333333333333
 5/10/2023 | ID1   | 19   |  5.66 | 7.988333333333333
 5/11/2023 | ID1   | 19   | 10.01 | 7.988333333333333
 5/12/2023 | ID1   | 19   |  9.41 | 7.988333333333333
 5/13/2023 | ID1   | 19   |  6.71 | 7.988333333333333
(13 rows)

Примечание:

• этот запрос предполагает, что существует только одно измерение на дату/имя
• Попробуйте изменить тип данных «дата» на date. Мне пришлось применить его, чтобы запрос работал правильно.

Демо: db<>рабочий пример

Попробуйте левое самосоединение следующим образом:

SELECT t1.date,
       t1.Tname,
       t1.week,
       t1.traffic,
       t2.traffic traffic_D7,
       AVG(t1.traffic) OVER(PARTITION BY t1.week) AS AVTraffic
FROM a t1 LEFT JOIN a t2
ON t1.date = t2.date + interval '7 day'

демо