SQL Подсчет отдельных периодов отсутствия студентов

Я ищу способ подсчета отдельных периодов без пробелов, в которых студенты отсутствовали:

Каждый раз, когда учащийся отсутствует, я создаю запись в таблице отсутствия, некоторые отсутствия могут перекрываться, некоторые продлевать предыдущее отсутствие.

StuId    StrPer       EndPer
------   -----------  -----------
111111   2011-01-10   2011-01-15
222222   2011-02-01   2011-02-05
222222   2011-02-06   2011-02-08
333333   2011-04-07   2011-04-14
444444   2011-04-20   2011-04-25
444444   2011-04-23   2011-04-28
111111   2011-05-01   2011-05-03

Теперь я хочу подсчитать количество уникальных периодов отсутствия с пропусками, результат должен быть:

StuId   NbrAbs
------  ------
111111  2
222222  1
333333  1
444444  1

111111 имеет два периода отсутствия с промежутком между

222222 также имеет два периода отсутствия, но без перерыва, поэтому его следует рассматривать как 1 период отсутствия.

333333 имеет только 1 период отсутствия

444444 имеет 2 периода, которые перекрываются, также без промежутка, поэтому его следует рассматривать как 1 период отсутствия.

Может ли кто-нибудь помочь мне написать запрос для этого?


sql gaps-and-islands
person Christophe    schedule 27.07.2011    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Предполагая, что SQL 2005+ должен работать:

SELECT '111111' as stuid,'2011-01-10' as start_date,'2011-01-15' as end_date into #data UNION ALL
SELECT '222222','2011-02-01','2011-02-05' UNION ALL
SELECT '222222','2011-02-06','2011-02-08' UNION ALL
SELECT '333333','2011-04-07','2011-04-14' UNION ALL
SELECT '444444','2011-04-20','2011-04-25' UNION ALL
SELECT '444444','2011-04-23','2011-04-28' UNION ALL
SELECT '111111','2011-05-01','2011-05-03' 

;with periods as
(
select 
stuid
,start_date
,end_date
,row_number() OVER (PARTITION BY stuid ORDER BY end_date ASC) as period_number
FROM #data
)
,periods2 AS
(
SELECT 
p1.stuid
,p1.start_date
,p1.end_date
,p1.period_number
,ISNULL(DATEDIFF(DD,p1.end_date,p2.start_date),1) as period_gap
from periods p1
LEFT OUTER JOIN periods p2 on p2.stuid = p1.stuid
AND p2.period_number = p1.period_number + 1
)
SELECT 
stuid
,count(period_gap) as number_discrete_absences
FROM periods2
WHERE period_gap > 0
GROUP BY stuid
person Dibstar    schedule 27.07.2011
comment
ВЫБЕРИТЕ «222222», «2011-02-01», «2011-02-09» ОБЪЕДИНЕНИЕ ВСЕХ - person Aducci; 27.07.2011
comment
Фантастический Давин. Я работаю над DB2, но преобразование sql должно быть в порядке, я временно попробовал ваш запрос на своем SQL Server, и результат не совсем то, что я ожидаю, 222222 возвращает 2 как number_discrete_absences, это должно быть 1, потому что его отсутствие начинается с 2011 года. -02-01» до «2011-02-05», а затем его продлили с «2011-02-06» до «2011-02-08», это один период, потому что между ними нет промежутка. - person Christophe; 27.07.2011
comment
Дэвин, в DB2 мне просто пришлось заменить ISNULL(DATEDIFF(DD,p1.end_date,p2.start_date),1) на COALESCE(p2.strdateab - p1.enddateab, 1), но та же проблема, когда период следует за datediff, продолжают считать 1 день, когда это необходимо, считается за 1 период. - person Christophe; 27.07.2011
comment
Дэвин, я думаю, что я решил проблему в DB2, я просто добавил -1 в следующую часть оператора COALESCE(p2.strdateab -1 - p1.enddateab, 1), судя по тому, что я тестировал, он работает так, как ожидалось. Большое спасибо за вашу помощь! Если вы считаете, что мое решение по добавлению -1 может иметь другие проблемы, сообщите мне об этом. - person Christophe; 27.07.2011

arrow_upward
1
arrow_downward

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

SELECT `StudId`, COUNT(`StudId`) as `NbrAbs` FROM `AbsenseTableName` GROUP BY `StdId`

Я не тестировал код. Но это основная идея.

person Lachezar Todorov    schedule 27.07.2011
comment
Спасибо Lachezar за быстрый ответ, но в моей таблице есть только столбцы StuId (идентификатор студента), StrPer (начальный период) и EndPer (конечный период). Я хочу подсчитать количество периодов болезни студента за указанный диапазон дат! Но периоды должны быть уникальными... - person Christophe; 27.07.2011