Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE schedule (
  id SERIAL NOT NULL PRIMARY KEY,
  start_time TIME NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  days TEXT NOT NULL
);

CREATE TABLE


INSERT INTO schedule(start_time, start_date, end_date, days)
VALUES 
  ('10:30', '2023-06-01', '2023-06-20', 'Пн.Ср.Пт.Пн.Ср.Пт.Пн'),
  ('12:30', '2023-06-01', '2023-06-20', 'Пн.Ср.Пт.Пн.Ср.Пт.Пн');

INSERT 0 2


SELECT * FROM schedule;


id
start_time
start_date
end_date
days


1
10:30:00
2023-06-01
2023-06-20
Пн.Ср.Пт.Пн.Ср.Пт.Пн

2
12:30:00
2023-06-01
2023-06-20
Пн.Ср.Пт.Пн.Ср.Пт.Пн



SELECT 2


-- Распаковываем список дней недели
SELECT 
  start_time,
  day_of_week,
  row_number
FROM 
  schedule,
  unnest(string_to_array(days, '.')) WITH ORDINALITY AS t(day_of_week, row_number);


start_time
day_of_week
row_number


10:30:00
Пн
1

10:30:00
Ср
2

10:30:00
Пт
3

10:30:00
Пн
4

10:30:00
Ср
5

10:30:00
Пт
6

10:30:00
Пн
7

12:30:00
Пн
1

12:30:00
Ср
2

12:30:00
Пт
3

12:30:00
Пн
4

12:30:00
Ср
5

12:30:00
Пт
6

12:30:00
Пн
7



SELECT 14


-- Добавляем нумерацию чтобы не потерять порядок
-- Добавляем номер недели
WITH 
unpacked_schedule AS (
  SELECT 
    id,
    start_time,
    day_of_week,
    row_number
  FROM 
    schedule,
    unnest(string_to_array(days, '.')) WITH ORDINALITY AS t(day_of_week, row_number)
)

SELECT 
  *,
  row_number() OVER(
    PARTITION BY start_time, day_of_week ORDER BY row_number
  ) AS week_number
FROM unpacked_schedule
ORDER BY start_time, row_number;


id
start_time
day_of_week
row_number
week_number


1
10:30:00
Пн
1
1

1
10:30:00
Ср
2
1

1
10:30:00
Пт
3
1

1
10:30:00
Пн
4
2

1
10:30:00
Ср
5
2

1
10:30:00
Пт
6
2

1
10:30:00
Пн
7
3

2
12:30:00
Пн
1
1

2
12:30:00
Ср
2
1

2
12:30:00
Пт
3
1

2
12:30:00
Пн
4
2

2
12:30:00
Ср
5
2

2
12:30:00
Пт
6
2

2
12:30:00
Пн
7
3



SELECT 14


-- Задаем номер русскоязычного дня недели
SELECT day_number, day_of_week
FROM (
  VALUES 
    (1, 'Пн'),
    (2, 'Вт'),
    (3, 'Ср'),
    (4, 'Чт'),
    (5, 'Пт'),
    (6, 'Сб'),
    (0, 'Вс')
) AS day_number(day_number, day_of_week);


day_number
day_of_week


1
Пн

2
Вт

3
Ср

4
Чт

5
Пт

6
Сб

0
Вс



SELECT 7


-- Добавляем нумерацию русскоязычных дней недели в основной запрос
WITH 
unpacked_schedule AS (
  SELECT 
    id,
    start_time,
    day_of_week,
    row_number
  FROM 
    schedule,
    unnest(string_to_array(days, '.')) WITH ORDINALITY AS t(day_of_week, row_number)
), 
counted_unpacked_schedule AS (
  SELECT 
    *,
    row_number() OVER(
      PARTITION BY start_time, day_of_week ORDER BY id, row_number
    ) AS week_number
  FROM unpacked_schedule
),
rus_dow_numbers AS (
  SELECT day_number, day_of_week
  FROM (
    VALUES (1, 'Пн'), (2, 'Вт'), (3, 'Ср'), (4, 'Чт'), (5, 'Пт'), (6, 'Сб'), (0, 'Вс')
  ) AS day_number(day_number, day_of_week)
)

SELECT 
  counted_unpacked_schedule.*,
  rus_dow_numbers.day_number AS dow_number
FROM counted_unpacked_schedule
INNER JOIN rus_dow_numbers
  ON rus_dow_numbers.day_of_week = counted_unpacked_schedule.day_of_week;


id
start_time
day_of_week
row_number
week_number
dow_number


1
10:30:00
Пн
1
1
1

1
10:30:00
Пн
4
2
1

1
10:30:00
Пн
7
3
1

1
10:30:00
Пт
3
1
5

1
10:30:00
Пт
6
2
5

1
10:30:00
Ср
2
1
3

1
10:30:00
Ср
5
2
3

2
12:30:00
Пн
1
1
1

2
12:30:00
Пн
4
2
1

2
12:30:00
Пн
7
3
1

2
12:30:00
Пт
3
1
5

2
12:30:00
Пт
6
2
5

2
12:30:00
Ср
2
1
3

2
12:30:00
Ср
5
2
3



SELECT 14


-- Распаковываем заданный календарный период
-- Добавляем нумерацию чтобы не потерять порядок
-- Воскресенье это 0. Переделываем 0 в 7 COALESCE(NULLIF(EXTRACT(DOW FROM date), 0), 7)
-- Добавляем номер предыдущего дня
-- Предыдущий день для первой записи NULL. Вычитаем 1 из текущего, 0 опять в 7.
WITH
all_calendar_days AS (
  SELECT 
    id,
    row_number,
    date::DATE,
    COALESCE(NULLIF(EXTRACT(DOW FROM date), 0), 7) AS day_number
  FROM 
    schedule, 
    generate_series(
      start_date, 
      end_date, 
      '1 day'::interval
    ) WITH ORDINALITY AS t(date, row_number)
)

SELECT
  *,
  lag(day_number, 1, COALESCE(NULLIF(day_number - 1, 0), 7)) OVER (
      PARTITION BY id ORDER BY id, row_number
  ) AS previous_day_number
FROM all_calendar_days
ORDER BY id, row_number;


id
row_number
date
day_number
previous_day_number


1
1
2023-06-01
4
3

1
2
2023-06-02
5
4

1
3
2023-06-03
6
5

1
4
2023-06-04
7
6

1
5
2023-06-05
1
7

1
6
2023-06-06
2
1

1
7
2023-06-07
3
2

1
8
2023-06-08
4
3

1
9
2023-06-09
5
4

1
10
2023-06-10
6
5

1
11
2023-06-11
7
6

1
12
2023-06-12
1
7

1
13
2023-06-13
2
1

1
14
2023-06-14
3
2

1
15
2023-06-15
4
3

1
16
2023-06-16
5
4

1
17
2023-06-17
6
5

1
18
2023-06-18
7
6

1
19
2023-06-19
1
7

1
20
2023-06-20
2
1

2
1
2023-06-01
4
3

2
2
2023-06-02
5
4

2
3
2023-06-03
6
5

2
4
2023-06-04
7
6

2
5
2023-06-05
1
7

2
6
2023-06-06
2
1

2
7
2023-06-07
3
2

2
8
2023-06-08
4
3

2
9
2023-06-09
5
4

2
10
2023-06-10
6
5

2
11
2023-06-11
7
6

2
12
2023-06-12
1
7

2
13
2023-06-13
2
1

2
14
2023-06-14
3
2

2
15
2023-06-15
4
3

2
16
2023-06-16
5
4

2
17
2023-06-17
6
5

2
18
2023-06-18
7
6

2
19
2023-06-19
1
7

2
20
2023-06-20
2
1



SELECT 40


-- Считаем недели. Если номер текущего дня меньше предыдущего, то это новая неделя.
-- count(*) FILTER увеличивает счетчик каждый раз как условие выполняется
WITH
all_calendar_days AS (
  SELECT 
    id,
    row_number,
    date::DATE,
    COALESCE(NULLIF(EXTRACT(DOW FROM date), 0), 7) AS day_number
  FROM 
    schedule, 
    generate_series(
      start_date, 
      end_date, 
      '1 day'::interval
    ) WITH ORDINALITY AS t(date, row_number)
),
counted_all_calendar_days AS (
  SELECT
    *,
    lag(day_number, 1, COALESCE(NULLIF(day_number - 1, 0), 7)) OVER (
      PARTITION BY id ORDER BY id, row_number
    ) AS previous_day_number
  FROM all_calendar_days
)

SELECT 
  *,
  1 + count(*) FILTER (
    WHERE previous_day_number > day_number
  )
  OVER (
    PARTITION BY id ORDER BY id, row_number
  ) AS week_number
FROM counted_all_calendar_days
ORDER BY id, row_number;


id
row_number
date
day_number
previous_day_number
week_number


1
1
2023-06-01
4
3
1

1
2
2023-06-02
5
4
1

1
3
2023-06-03
6
5
1

1
4
2023-06-04
7
6
1

1
5
2023-06-05
1
7
2

1
6
2023-06-06
2
1
2

1
7
2023-06-07
3
2
2

1
8
2023-06-08
4
3
2

1
9
2023-06-09
5
4
2

1
10
2023-06-10
6
5
2

1
11
2023-06-11
7
6
2

1
12
2023-06-12
1
7
3

1
13
2023-06-13
2
1
3

1
14
2023-06-14
3
2
3

1
15
2023-06-15
4
3
3

1
16
2023-06-16
5
4
3

1
17
2023-06-17
6
5
3

1
18
2023-06-18
7
6
3

1
19
2023-06-19
1
7
4

1
20
2023-06-20
2
1
4

2
1
2023-06-01
4
3
1

2
2
2023-06-02
5
4
1

2
3
2023-06-03
6
5
1

2
4
2023-06-04
7
6
1

2
5
2023-06-05
1
7
2

2
6
2023-06-06
2
1
2

2
7
2023-06-07
3
2
2

2
8
2023-06-08
4
3
2

2
9
2023-06-09
5
4
2

2
10
2023-06-10
6
5
2

2
11
2023-06-11
7
6
2

2
12
2023-06-12
1
7
3

2
13
2023-06-13
2
1
3

2
14
2023-06-14
3
2
3

2
15
2023-06-15
4
3
3

2
16
2023-06-16
5
4
3

2
17
2023-06-17
6
5
3

2
18
2023-06-18
7
6
3

2
19
2023-06-19
1
7
4

2
20
2023-06-20
2
1
4



SELECT 40


-- Накладываем заданные дни недели на наше расписание
-- Добавляем номер стартовой недели в расписании
WITH 
unpacked_schedule AS (
  SELECT 
    id,
    start_time,
    day_of_week,
    row_number
  FROM 
    schedule,
    unnest(string_to_array(days, '.')) WITH ORDINALITY AS t(day_of_week, row_number)
),
rus_dow_numbers AS (
  SELECT day_number, day_of_week
  FROM (
    VALUES (1, 'Пн'), (2, 'Вт'), (3, 'Ср'), (4, 'Чт'), (5, 'Пт'), (6, 'Сб'), (0, 'Вс')
  ) AS day_number(day_number, day_of_week)
),
counted_unpacked_schedule AS (
  SELECT 
    unpacked_schedule.*,
    rus_dow_numbers.day_number,
    row_number() OVER(
      PARTITION BY start_time, unpacked_schedule.day_of_week ORDER BY id, row_number
    ) AS week_number
  FROM unpacked_schedule
  INNER JOIN rus_dow_numbers
    ON rus_dow_numbers.day_of_week = unpacked_schedule.day_of_week
),
all_calendar_days AS (
  SELECT 
    id,
    row_number,
    date::DATE,
    COALESCE(NULLIF(EXTRACT(DOW FROM date), 0), 7) AS day_number
  FROM 
    schedule, 
    generate_series(
      start_date, 
      end_date, 
      '1 day'::interval
    ) WITH ORDINALITY AS t(date, row_number)
),
counted_all_calendar_days AS (
  SELECT
    *,
    lag(day_number, 1, COALESCE(NULLIF(day_number - 1, 0), 7)) OVER (
      PARTITION BY id ORDER BY id, row_number
    ) AS previous_day_number
  FROM all_calendar_days
),
counted_all_calendar_days_by_week AS (
  SELECT 
    *,
    1 + count(*) FILTER (WHERE previous_day_number > day_number) OVER (
      PARTITION BY id ORDER BY id, row_number
    ) AS week_number
  FROM counted_all_calendar_days
)

SELECT 
  counted_unpacked_schedule.id,
  counted_unpacked_schedule.start_time,
  counted_all_calendar_days_by_week.date,
  counted_unpacked_schedule.row_number,
  counted_all_calendar_days_by_week.week_number,
  counted_unpacked_schedule.week_number AS schedule_week_number,
  counted_unpacked_schedule.day_of_week,
  FIRST_VALUE(counted_all_calendar_days_by_week.week_number) OVER(
    PARTITION BY counted_unpacked_schedule.id 
    ORDER BY 
      counted_unpacked_schedule.id, 
      counted_unpacked_schedule.row_number,
      counted_all_calendar_days_by_week.week_number
  ) AS first_week_number
FROM counted_all_calendar_days_by_week
LEFT JOIN counted_unpacked_schedule
  ON counted_all_calendar_days_by_week.id = counted_unpacked_schedule.id
  AND
  counted_all_calendar_days_by_week.day_number = counted_unpacked_schedule.day_number
WHERE counted_unpacked_schedule.id IS NOT NULL
ORDER BY 
  counted_unpacked_schedule.id, 
  counted_unpacked_schedule.row_number,
  counted_all_calendar_days_by_week.week_number;


id
start_time
date
row_number
week_number
schedule_week_number
day_of_week
first_week_number


1
10:30:00
2023-06-05
1
2
1
Пн
2

1
10:30:00
2023-06-12
1
3
1
Пн
2

1
10:30:00
2023-06-19
1
4
1
Пн
2

1
10:30:00
2023-06-07
2
2
1
Ср
2

1
10:30:00
2023-06-14
2
3
1
Ср
2

1
10:30:00
2023-06-02
3
1
1
Пт
2

1
10:30:00
2023-06-09
3
2
1
Пт
2

1
10:30:00
2023-06-16
3
3
1
Пт
2

1
10:30:00
2023-06-05
4
2
2
Пн
2

1
10:30:00
2023-06-12
4
3
2
Пн
2

1
10:30:00
2023-06-19
4
4
2
Пн
2

1
10:30:00
2023-06-07
5
2
2
Ср
2

1
10:30:00
2023-06-14
5
3
2
Ср
2

1
10:30:00
2023-06-02
6
1
2
Пт
2

1
10:30:00
2023-06-09
6
2
2
Пт
2

1
10:30:00
2023-06-16
6
3
2
Пт
2

1
10:30:00
2023-06-05
7
2
3
Пн
2

1
10:30:00
2023-06-12
7
3
3
Пн
2

1
10:30:00
2023-06-19
7
4
3
Пн
2

2
12:30:00
2023-06-05
1
2
1
Пн
2

2
12:30:00
2023-06-12
1
3
1
Пн
2

2
12:30:00
2023-06-19
1
4
1
Пн
2

2
12:30:00
2023-06-07
2
2
1
Ср
2

2
12:30:00
2023-06-14
2
3
1
Ср
2

2
12:30:00
2023-06-02
3
1
1
Пт
2

2
12:30:00
2023-06-09
3
2
1
Пт
2

2
12:30:00
2023-06-16
3
3
1
Пт
2

2
12:30:00
2023-06-05
4
2
2
Пн
2

2
12:30:00
2023-06-12
4
3
2
Пн
2

2
12:30:00
2023-06-19
4
4
2
Пн
2

2
12:30:00
2023-06-07
5
2
2
Ср
2

2
12:30:00
2023-06-14
5
3
2
Ср
2

2
12:30:00
2023-06-02
6
1
2
Пт
2

2
12:30:00
2023-06-09
6
2
2
Пт
2

2
12:30:00
2023-06-16
6
3
2
Пт
2

2
12:30:00
2023-06-05
7
2
3
Пн
2

2
12:30:00
2023-06-12
7
3
3
Пн
2

2
12:30:00
2023-06-19
7
4
3
Пн
2



SELECT 38


-- Последний этап.
-- Нужно синхронизировать номер недели в списке дней недели и расписании
-- Надо просто прибавить номер стартовой недели к номеру недели в списке дней - 1
-- Например, первая неделя в списке дней это вторая неделя в расписании
-- т.к. расписание стратует с пятницы
-- Первый Пн приходится на вторую неделю, значит стартовая неделя 2. 1 + 2 - 1 = 2
-- Значит первый Пн из второй недели, второй из третьей итд.
-- Чтобы не взять первую Пт из первой недели отсекаем недели раньше стартовой
WITH 
unpacked_schedule AS (
  SELECT 
    id,
    start_time,
    day_of_week,
    row_number
  FROM 
    schedule,
    unnest(string_to_array(days, '.')) WITH ORDINALITY AS t(day_of_week, row_number)
),
rus_dow_numbers AS (
  SELECT day_number, day_of_week
  FROM (
    VALUES (1, 'Пн'), (2, 'Вт'), (3, 'Ср'), (4, 'Чт'), (5, 'Пт'), (6, 'Сб'), (0, 'Вс')
  ) AS day_number(day_number, day_of_week)
),
counted_unpacked_schedule AS (
  SELECT 
    unpacked_schedule.*,
    rus_dow_numbers.day_number,
    row_number() OVER(
      PARTITION BY start_time, unpacked_schedule.day_of_week ORDER BY id, row_number
    ) AS week_number
  FROM unpacked_schedule
  INNER JOIN rus_dow_numbers
    ON rus_dow_numbers.day_of_week = unpacked_schedule.day_of_week
),
all_calendar_days AS (
  SELECT 
    id,
    row_number,
    date::DATE,
    COALESCE(NULLIF(EXTRACT(DOW FROM date), 0), 7) AS day_number
  FROM 
    schedule, 
    generate_series(
      start_date, 
      end_date, 
      '1 day'::interval
    ) WITH ORDINALITY AS t(date, row_number)
),
counted_all_calendar_days AS (
  SELECT
    id,
    row_number,
    date,
    day_number,
    lag(day_number, 1, COALESCE(NULLIF(day_number - 1, 0), 7)) OVER (
      PARTITION BY id ORDER BY id, row_number
    ) AS previous_day_number
  FROM all_calendar_days
),
counted_all_calendar_days_by_week AS (
  SELECT 
    *,
    1 + count(*) FILTER (WHERE previous_day_number > day_number) OVER (
      PARTITION BY id ORDER BY id, row_number
    ) AS week_number
  FROM counted_all_calendar_days
),
schedule_by_week AS (
  SELECT 
    counted_unpacked_schedule.id,
    counted_unpacked_schedule.start_time,
    counted_all_calendar_days_by_week.date,
    counted_unpacked_schedule.row_number,
    counted_all_calendar_days_by_week.week_number,
    counted_unpacked_schedule.week_number AS schedule_week_number,
    counted_unpacked_schedule.day_of_week,
    FIRST_VALUE(counted_all_calendar_days_by_week.week_number) OVER(
      PARTITION BY counted_unpacked_schedule.id 
      ORDER BY 
        counted_unpacked_schedule.id, 
        counted_unpacked_schedule.row_number,
        counted_all_calendar_days_by_week.week_number
    ) AS first_week_number
  FROM counted_all_calendar_days_by_week
  LEFT JOIN counted_unpacked_schedule
    ON counted_all_calendar_days_by_week.id = counted_unpacked_schedule.id
    AND
    counted_all_calendar_days_by_week.day_number = counted_unpacked_schedule.day_number
  WHERE counted_unpacked_schedule.id IS NOT NULL
  ORDER BY 
    counted_unpacked_schedule.id, 
    counted_unpacked_schedule.row_number,
    counted_all_calendar_days_by_week.week_number
)

SELECT 
  start_time,
  day_of_week,
  date
FROM schedule_by_week
WHERE 
  week_number >= first_week_number 
  AND
  week_number = schedule_week_number + first_week_number - 1
ORDER BY id, row_number


start_time
day_of_week
date


10:30:00
Пн
2023-06-05

10:30:00
Ср
2023-06-07

10:30:00
Пт
2023-06-09

10:30:00
Пн
2023-06-12

10:30:00
Ср
2023-06-14

10:30:00
Пт
2023-06-16

10:30:00
Пн
2023-06-19

12:30:00
Пн
2023-06-05

12:30:00
Ср
2023-06-07

12:30:00
Пт
2023-06-09

12:30:00
Пн
2023-06-12

12:30:00
Ср
2023-06-14

12:30:00
Пт
2023-06-16

12:30:00
Пн
2023-06-19



SELECT 14
id	start_time	start_date	end_date	days
1	10:30:00	2023-06-01	2023-06-20	Пн.Ср.Пт.Пн.Ср.Пт.Пн
2	12:30:00	2023-06-01	2023-06-20	Пн.Ср.Пт.Пн.Ср.Пт.Пн
start_time	day_of_week	row_number
10:30:00	Пн	1
10:30:00	Ср	2
10:30:00	Пт	3
10:30:00	Пн	4
10:30:00	Ср	5
10:30:00	Пт	6
10:30:00	Пн	7
12:30:00	Пн	1
12:30:00	Ср	2
12:30:00	Пт	3
12:30:00	Пн	4
12:30:00	Ср	5
12:30:00	Пт	6
12:30:00	Пн	7
id	row_number	date	day_number	previous_day_number
1	1	2023-06-01	4	3
1	2	2023-06-02	5	4
1	3	2023-06-03	6	5
1	4	2023-06-04	7	6
1	5	2023-06-05	1	7
1	6	2023-06-06	2	1
1	7	2023-06-07	3	2
1	8	2023-06-08	4	3
1	9	2023-06-09	5	4
1	10	2023-06-10	6	5
1	11	2023-06-11	7	6
1	12	2023-06-12	1	7
1	13	2023-06-13	2	1
1	14	2023-06-14	3	2
1	15	2023-06-15	4	3
1	16	2023-06-16	5	4
1	17	2023-06-17	6	5
1	18	2023-06-18	7	6
1	19	2023-06-19	1	7
1	20	2023-06-20	2	1
2	1	2023-06-01	4	3
2	2	2023-06-02	5	4
2	3	2023-06-03	6	5
2	4	2023-06-04	7	6
2	5	2023-06-05	1	7
2	6	2023-06-06	2	1
2	7	2023-06-07	3	2
2	8	2023-06-08	4	3
2	9	2023-06-09	5	4
2	10	2023-06-10	6	5
2	11	2023-06-11	7	6
2	12	2023-06-12	1	7
2	13	2023-06-13	2	1
2	14	2023-06-14	3	2
2	15	2023-06-15	4	3
2	16	2023-06-16	5	4
2	17	2023-06-17	6	5
2	18	2023-06-18	7	6
2	19	2023-06-19	1	7
2	20	2023-06-20	2	1