Блог

Главная - Вопросы по программированию - Динамический / настраиваемый номер недели

Динамический / настраиваемый номер недели

#sql #postgresql

#sql #postgresql

Вопрос:

Допустим, есть таблица, в которой хранятся данные о посещаемости учащихся на занятиях. Учащемуся необходимо посещать каждое занятие только один раз в неделю. Используя столбец days_diff, мы решаем, за какие недели студент должен получить кредит. Как обычно, каждая неделя состоит из 7 дней, а конец недели (в днях) делится на 7. В моем сценарии для недели 3 другой случай: всякий раз, когда посещается week_3, неделя заканчивается в эту дату посещения, а week_4 начинается на следующий день.

Я использую PostgreSQL 11.4!

 select * from students_data
имя_студента class_subject дата регистрации_date посещение_дата days_diff week_num
Фрэнк Химия 2021-10-15 2021-10-18 3 week_1
Фрэнк Химия 2021-10-15 2021-10-24 9 week_2
Фрэнк Химия 2021-10-15 2021-11-01 17 week_3
Фрэнк Химия 2021-10-15 2021-11-10 26 week_4
Фрэнк Химия 2021-10-15 2021-11-20 36 week_6
Дэнни Физика 2021-10-20 2021-10-25 5 week_1
Дэнни Физика 2021-10-20 2021-11-02 13 week_2
Дэнни Физика 2021-10-20 2021-11-08 19 week_3
Дэнни Физика 2021-10-20 2021-11-23 34 week_5
Дэнни Физика 2021-10-20 2021-11-30 41 week_6

Желаемая разбивка по неделям:

 student    days_in_week      week_number
Frank         1-7               week_1
Frank         8-14              week_2
Frank        15-17              week_3     <-----week_3 shakes up the week breakdown
Frank        18-24              week_4
Frank        25-31              week_5........
Danny         1-7               week_1
Danny         8-14              week_2
Danny        15-19              week_3     <----week_3 shakes up the week breakdown
Danny        20-26              week_4
Danny        27-33              week_5........

Запрошено: столбец new_week_num

имя_студента class_subject дата регистрации_date посещение_дата days_diff week_num new_week_num
Фрэнк Химия 2021-10-15 2021-10-18 3 week_1 week_1
Фрэнк Химия 2021-10-15 2021-10-24 9 week_2 week_2
Фрэнк Химия 2021-10-15 2021-11-01 17 week_3 week_3
Фрэнк Химия 2021-10-15 2021-11-10 26 week_4 week_5
Фрэнк Химия 2021-10-15 2021-11-20 36 week_6 week_7
Дэнни Физика 2021-10-20 2021-10-25 5 week_1 week_1
Дэнни Физика 2021-10-20 2021-11-02 13 week_2 week_2
Дэнни Физика 2021-10-20 2021-11-08 19 week_3 week_3
Дэнни Физика 2021-10-20 2021-11-23 34 week_5 week_6
Дэнни Физика 2021-10-20 2021-11-30 41 week_6 week_7

Ответ №1:

Решение 1: cte и ОБЪЕДИНЕНИЕ

 WITH list AS
(
SELECT student_name, class_subject, registration_date, days_diff
  FROM your_table
 WHERE week_num = 'week_3'
)
SELECT t.*
     , CASE 
         WHEN t.days_diff <= l.days_diff
         THEN t.week_num
         ELSE 'week_' || (3   ceil((t.days_diff - l.days_diff)/7.0)) :: text
       END AS new_week_num
  FROM your_table AS t
  LEFT JOIN list AS l
    ON t.student_name = l.student_name
   AND t.class_subject = l.class_subject
   AND t.registration_date = l.registration_date

Решение 2: БОКОВОЕ СОЕДИНЕНИЕ

 SELECT t.*
     , CASE 
         WHEN t.days_diff <= l.days_diff
         THEN t.week_num
         ELSE 'week_' || (3   ceil((t.days_diff - l.days_diff)/7.0)) :: text
       END AS new_week_num
  FROM your_table AS t
 CROSS JOIN LATERAL
     ( SELECT l.days_diff
         FROM your_table AS l
        WHERE l.student_name = t.student_name
         AND l.class_subject = t.class_subject
         AND l.registration_date = t.registration_date
         AND l.week_num = 'week_3'
       LIMIT 1
     ) AS l

Решение 3: функция окна

 SELECT t.*
     , CASE 
         WHEN days_diff <= (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1]
         THEN week_num
         ELSE 'week_' || (3   ceil((t.days_diff - (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1])/7.0)) :: text
       END AS new_week_num
  FROM your_table AS t
WINDOW w AS (PARTITION BY student_name, class_subject, registration_date ORDER BY days_diff ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

тест в dbfiddle

Метки: