Вопросы Теги

Как вычислить самую длинную полосу в SQL?

Я имею 

  TABLE EMPLOYEE - ID,DATE,IsPresent

Я хочу вычислить самую длинную полосу для присутствия сотрудника. Существующий бит будет ложью в течение многих дней, он не приехал.. Таким образом, я хочу вычислить самое долгое количество дней, он пришел к власти для последовательных дат.. У меня есть поле столбца Date, уникально... Таким образом, я попробовал этот путь -

Select Id,Count(*) from Employee where IsPresent=1

Но вышеупомянутое не работает... Кто-либо может вести меня к тому, как я могу вычислить полосу для этого?.... Я уверен, что люди столкнулись с этим... Я пытался искать онлайн, но... не понял это хорошо..., выручите меня..

5
задан Vishal 13 November 2013 в 21:36
поделиться

5 ответов

groupby отсутствует.

Выбрать общую посещаемость всего офиса в человеко-днях (для каждого). 

Select Id,Count(*) from Employee where IsPresent=1

Для выбора посещаемости в человеко-днях на одного сотрудника. 

Select Id,Count(*)
from Employee
where IsPresent=1
group by id;

Но это все равно плохо, потому что учитывается общее количество дней посещения, а НЕ продолжительность непрерывного посещения.

Что вам нужно сделать, так это построить временную таблицу с другим столбцом даты date2. date2 установлено на сегодня. Таблица представляет собой список всех дней отсутствия сотрудника. 

create tmpdb.absentdates as
Select id, date, today as date2
from EMPLOYEE
where IsPresent=0
order by id, date;

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

update tmpdb.absentdates
set date2 = 
  select min(a2.date)
  from
   tmpdb.absentdates a1,
   tmpdb.absentdates a2
  where a1.id = a2.id
    and a1.date < a2.date

Вышеупомянутый запрос обновляется путем выполнения соединения с самим собой и может вызвать тупиковый запрос, поэтому лучше создать две копии временной таблицы. 

create tmpdb.absentdatesX as
Select id, date
from EMPLOYEE
where IsPresent=0
order by id, date;

create tmpdb.absentdates as
select *, today as date2
from tmpdb.absentdatesX;

Вам необходимо ввести дату приема на работу, предполагая, что самая ранняя дата для каждого сотрудника в базе данных - это дата приема на работу. 

insert into tmpdb.absentdates a
select a.id, min(e.date), today
from EMPLOYEE e
where a.id = e.id

Теперь обновите date2 со следующей более поздней отсутствующей датой, чтобы иметь возможность выполнять date2 - date. 

update tmpdb.absentdates
set date2 = 
  select min(x.date)
  from
   tmpdb.absentdates a,
   tmpdb.absentdatesX x
  where a.id = x.id
    and a.date < x.date

Здесь будет перечислена продолжительность непрерывного присутствия emp: 

select id, datediff(date2, date) as continuousPresence
from tmpdb.absentdates
group by id, continuousPresence
order by id, continuousPresence

Но вам нужна только самая длинная полоса: 

select id, max(datediff(date2, date) as continuousPresence)
from tmpdb.absentdates
group by id
order by id

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

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

create tmpdb.absentCount as
Select a.id, a.date, a.date2, count(*) as continuousPresence
from EMPLOYEE e, tmpdb.absentdates a
where e.id = a.id
  and e.date >= a.date
  and e.date < a.date2
group by a.id, a.date
order by a.id, a.date;

Помните, каждый раз, когда вы используете агрегатор, например count, ave Вам нужно сгруппировать по выбранному списку элементов, потому что здравый смысл состоит в том, что вы должны агрегировать по ним.

Теперь выберите максимальную полосу 

select id, max(continuousPresence)
from tmpdb.absentCount
group by id

Чтобы перечислить даты полосы: 

select id, date, date2, continuousPresence
from tmpdb.absentCount
group by id
having continuousPresence = max(continuousPresence);

Могут быть некоторые ошибки (sql server tsql) выше, но это общая идея.

4
ответ дан 13 December 2019 в 22:02
поделиться

EDIT Вот SQL Server версия запроса:

with LowerBound as (select second_day.EmployeeId
        , second_day."DATE" as LowerDate
        , row_number() over (partition by second_day.EmployeeId 
            order by second_day."DATE") as RN
    from T second_day
    left outer join T first_day
        on first_day.EmployeeId = second_day.EmployeeId
        and first_day."DATE" = dateadd(day, -1, second_day."DATE")
        and first_day.IsPresent = 1
    where first_day.EmployeeId is null
    and second_day.IsPresent = 1)
, UpperBound as (select first_day.EmployeeId
        , first_day."DATE" as UpperDate
        , row_number() over (partition by first_day.EmployeeId 
            order by first_day."DATE") as RN
    from T first_day
    left outer join T second_day
        on first_day.EmployeeId = second_day.EmployeeId
        and first_day."DATE" = dateadd(day, -1, second_day."DATE")
        and second_day.IsPresent = 1
    where second_day.EmployeeId is null
    and first_day.IsPresent = 1)
select LB.EmployeeID, max(datediff(day, LowerDate, UpperDate) + 1) as LongestStreak
from LowerBound LB
inner join UpperBound UB
    on LB.EmployeeId = UB.EmployeeId
    and LB.RN = UB.RN
group by LB.EmployeeId

SQL Server Версия тестовых данных:

create table T (EmployeeId int
    , "DATE" date not null
    , IsPresent bit not null 
    , constraint T_PK primary key (EmployeeId, "DATE")
)


insert into T values (1, '2000-01-01', 1);
insert into T values (2, '2000-01-01', 0);
insert into T values (3, '2000-01-01', 0);
insert into T values (3, '2000-01-02', 1);
insert into T values (3, '2000-01-03', 1);
insert into T values (3, '2000-01-04', 0);
insert into T values (3, '2000-01-05', 1);
insert into T values (3, '2000-01-06', 1);
insert into T values (3, '2000-01-07', 0);
insert into T values (4, '2000-01-01', 0);
insert into T values (4, '2000-01-02', 1);
insert into T values (4, '2000-01-03', 1);
insert into T values (4, '2000-01-04', 1);
insert into T values (4, '2000-01-05', 1);
insert into T values (4, '2000-01-06', 1);
insert into T values (4, '2000-01-07', 0);
insert into T values (5, '2000-01-01', 0);
insert into T values (5, '2000-01-02', 1);
insert into T values (5, '2000-01-03', 0);
insert into T values (5, '2000-01-04', 1);
insert into T values (5, '2000-01-05', 1);
insert into T values (5, '2000-01-06', 1);
insert into T values (5, '2000-01-07', 0);

Извините, это написано в Oracle, поэтому подставьте соответствующую арифметику даты SQL Server.

Допущения:

  • Дата - это либо значение Date, либо DateTime с компонентом времени 00:00:00.
  • Первичным ключом является. (EmployeeId, Date)
  • Все поля not null

  • Если для сотрудника отсутствует дата, то он был not. (Используется для обработки начала и конца ряда данных, но также означает, что пропущенные даты в середине будут нарушать ряд. Может быть проблемой в зависимости от требований. 

    with LowerBound as (select second_day.EmployeeId
 , second_day. "DATE" as LowerDate
 , row_number() over (partition by second_day.EmployeeId 
 order by second_day. "DATE") as RN
 из T второй_день
 left outer join T first_day
 on first_day.EmployeeId = second_day.EmployeeId
 and first_day. "DATE" = second_day. "DATE" - 1
 и first_day.IsPresent = 1
 где first_day.EmployeeId - null
 and second_day.IsPresent = 1)
, UpperBound as (select first_day.EmployeeId
 , first_day. "DATE" as UpperDate
 , row_number() over (partition by first_day.EmployeeId 
 order by first_day. "DATE") as RN
 из T первый_день
 left outer join T second_day
 on first_day.EmployeeId = second_day.EmployeeId
 and first_day. "DATE" = second_day. "DATE" - 1
 и second_day.IsPresent = 1
 где second_day.EmployeeId - null
 и first_day.IsPresent = 1)
select LB.EmployeeID, max(UpperDate - LowerDate + 1) as LongestStreak
из LowerBound LB
inner join UpperBound UB
 on LB.EmployeeId = UB.EmployeeId
 и LB.RN = UB.RN
group by LB.EmployeeId

Test Data:

    create table T (EmployeeId number(38) 
        , "DATE" date not null check ("DATE" = trunc("DATE"))
        , IsPresent number not null check (IsPresent in (0, 1))
        , constraint T_PK primary key (EmployeeId, "DATE")
    )
    /

    insert into T values (1, to_date('2000-01-01', 'YYYY-MM-DD'), 1);
    insert into T values (2, to_date('2000-01-01', 'YYYY-MM-DD'), 0);
    insert into T values (3, to_date('2000-01-01', 'YYYY-MM-DD'), 0);
    insert into T values (3, to_date('2000-01-02', 'YYYY-MM-DD'), 1);
    insert into T values (3, to_date('2000-01-03', 'YYYY-MM-DD'), 1);
    insert into T values (3, to_date('2000-01-04', 'YYYY-MM-DD'), 0);
    insert into T values (3, to_date('2000-01-05', 'YYYY-MM-DD'), 1);
    insert into T values (3, to_date('2000-01-06', 'YYYY-MM-DD'), 1);
    insert into T values (3, to_date('2000-01-07', 'YYYY-MM-DD'), 0);
    insert into T values (4, to_date('2000-01-01', 'YYYY-MM-DD'), 0);
    insert into T values (4, to_date('2000-01-02', 'YYYY-MM-DD'), 1);
    insert into T values (4, to_date('2000-01-03', 'YYYY-MM-DD'), 1);
    insert into T values (4, to_date('2000-01-04', 'YYYY-MM-DD'), 1);
    insert into T values (4, to_date('2000-01-05', 'YYYY-MM-DD'), 1);
    insert into T values (4, to_date('2000-01-06', 'YYYY-MM-DD'), 1);
    insert into T values (4, to_date('2000-01-07', 'YYYY-MM-DD'), 0);
    insert into T values (5, to_date('2000-01-01', 'YYYY-MM-DD'), 0);
    insert into T values (5, to_date('2000-01-02', 'YYYY-MM-DD'), 1);
    insert into T values (5, to_date('2000-01-03', 'YYYY-MM-DD'), 0);
    insert into T values (5, to_date('2000-01-04', 'YYYY-MM-DD'), 1);
    insert into T values (5, to_date('2000-01-05', 'YYYY-MM-DD'), 1);
    insert into T values (5, to_date('2000-01-06', 'YYYY-MM-DD'), 1);
    insert into T values (5, to_date('2000-01-07', 'YYYY-MM-DD'), 0);
4
ответ дан 13 December 2019 в 22:02
поделиться

Попробуйте следующее: 

select 
    e.Id,
    e.date,
    (select 
       max(e1.date) 
     from 
       employee e1 
     where 
       e1.Id = e.Id and
       e1.date < e.date and 
       e1.IsPresent = 0) StreakStartDate,
    (select 
       min(e2.date) 
     from 
       employee e2 
     where 
       e2.Id = e.Id and
       e2.date > e.date and
       e2.IsPresent = 0) StreakEndDate           
from 
    employee e
where
    e.IsPresent = 1

Затем выясняет самую длинную полосу для каждого сотрудника: 

select id, max(datediff(streakStartDate, streakEndDate))
from (<use subquery above>)
group by id

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

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

Для каждой строки в таблице, когда сотрудник присутствовал, начало полосы - это максимальная дата, которая меньше даты текущей строки, когда сотрудник отсутствовал.

1
ответ дан 13 December 2019 в 22:02
поделиться

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

with LowerBound as (select second_day.EmployeeId
        , second_day."DATE" as LowerDate
        , row_number() over (partition by second_day.EmployeeId 
            order by second_day."DATE") as RN
    from T second_day
    left outer join T first_day
        on first_day.EmployeeId = second_day.EmployeeId
        and first_day."DATE" = dateadd(day, -1, second_day."DATE")
        and first_day.IsPresent = 1
    where first_day.EmployeeId is null
    and second_day.IsPresent = 1)
, UpperBound as (select first_day.EmployeeId
        , first_day."DATE" as UpperDate
        , row_number() over (partition by first_day.EmployeeId 
            order by first_day."DATE") as RN
    from T first_day
    left outer join T second_day
        on first_day.EmployeeId = second_day.EmployeeId
        and first_day."DATE" = dateadd(day, -1, second_day."DATE")
        and second_day.IsPresent = 1
    where second_day.EmployeeId is null
    and first_day.IsPresent = 1)
select LB.EmployeeID, max(datediff(day, LowerDate, UpperDate) + 1) as LongestStreak
from LowerBound LB
inner join UpperBound UB
    on LB.EmployeeId = UB.EmployeeId
    and LB.RN = UB.RN
group by LB.EmployeeId

go

with NumberedRows as (select EmployeeId
        , "DATE"
        , IsPresent
        , row_number() over (partition by EmployeeId
            order by "DATE") as RN
--        , min("DATE") over (partition by EmployeeId, IsPresent) as MinDate
--        , max("DATE") over (partition by EmployeeId, IsPresent) as MaxDate
    from T)
, LowerBound as (select SecondRow.EmployeeId
        , SecondRow.RN
        , row_number() over (partition by SecondRow.EmployeeId 
            order by SecondRow.RN) as LowerBoundRN
    from NumberedRows SecondRow
    left outer join NumberedRows FirstRow
        on FirstRow.IsPresent = 1
        and FirstRow.EmployeeId = SecondRow.EmployeeId
        and FirstRow.RN + 1 = SecondRow.RN
    where FirstRow.EmployeeId is null
    and SecondRow.IsPresent = 1)
, UpperBound as (select FirstRow.EmployeeId
       , FirstRow.RN
       , row_number() over (partition by FirstRow.EmployeeId
            order by FirstRow.RN) as UpperBoundRN
    from NumberedRows FirstRow
    left outer join NumberedRows SecondRow
        on SecondRow.IsPresent = 1
        and FirstRow.EmployeeId = SecondRow.EmployeeId
        and FirstRow.RN + 1 = SecondRow.RN
    where SecondRow.EmployeeId is null
    and FirstRow.IsPresent = 1)
select LB.EmployeeId, max(UB.RN - LB.RN + 1)
from LowerBound LB 
inner join UpperBound UB
    on LB.EmployeeId = UB.EmployeeId
    and LB.LowerBoundRN = UB.UpperBoundRN
group by LB.EmployeeId
1
ответ дан 13 December 2019 в 22:02
поделиться

Я делал это однажды, чтобы определить последовательные дни, когда пожарный находился на смене не менее 15 минут.

Ваш случай немного более простой.

Если вы хотите предположить, что ни один сотрудник не приходил более 32 раз подряд, вы можете просто использовать общее выражение таблицы. Но лучшим подходом было бы использование временной таблицы и цикла while.

Вам понадобится столбец под названием StartingRowID. Продолжайте соединять данные из временной таблицы с данными таблицы employeeWorkDay для следующего подряд рабочего дня сотрудника и вставлять их обратно в временную таблицу. Когда @@Row_Count = 0, вы поймали самую длинную полосу.

Теперь выполните агрегацию по StartingRowID, чтобы получить первый день самой длинной полосы. У меня мало времени, иначе я бы включил пример кода.

0
ответ дан 13 December 2019 в 22:02
поделиться
Другие вопросы по тегам:

Похожие вопросы: