SQLite: получить среднее число рабочих дней между двумя датами.

Question

SQLite: получить среднее число рабочих дней между двумя датами.

У меня есть стол Trucks с двумя столбцами даты: Arrival а также Released, Я могу рассчитать среднее количество дней между этими датами следующим образом:

SELECT avg(julianday(released) - julianday(arrival)) 
FROM Trucks

Однако я хочу только считать дни недели, то есть игнорировать субботы и воскресенья. Есть ли способ сделать это в SQLite? Я видел решения для более надежных СУБД, таких как Oracle и MSSQL, но ни одно из них не работает для SQLite.

0

sqlite datetime average weekday

Источник

user812883 01 ноя '13 в 17:06

3 ответа

Решение

Хорошо, я нашел очень грязное решение, используя много вложенных CASE заявления. Проверяет номер дня недели released а затем номер дня недели arrival и делает расчет, чтобы выяснить, сколько недель прошло. После этого я добавляю 0, 1 или 2 в качестве базового числа выходных дней, которые должны пройти между этими двумя днями (т. Е. С пятницы по понедельник всегда +2 выходных дня, даже если между даты).

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

(Отредактировано для простоты на основе отзывов CL)

SELECT 
avg(
julianday(released) - julianday(arrival) - 

CASE WHEN julianday(released) = julianday(arrival) THEN 0 ELSE
CASE strftime('%w', released) 
WHEN '0' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)    
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)          
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '1' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)  
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)          
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '2' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)       
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1) 
 END
WHEN '3' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1) 
 END
WHEN '4' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '5' THEN
 CASE strftime('%w', arrival)
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '6' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2) 
 END
END
END

), avg(julianday(released)-julianday(arrival))
from trucks

(Обратите внимание avg(julianday(released)-julianday(arrival)) в конце просто для целей тестирования, чтобы показать, что новое вычисленное среднее на самом деле меньше, чем прямое среднее разности между двумя датами).

1

Источник

user812883 05 ноя '13 в 15:14

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

SELECT t.id
,t.summary
,t.category
,u.email
,(CAST(strftime('%j', t.closed_at) as INTEGER) - CAST(strftime('%j', t.created_at) as INTEGER) - /*I can't figure it out, but julianday() wasn't giving me the correct distances for some numbers. I used the day of the year, instead, which resolved things as expected*/
    CASE                                                    
    WHEN CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER) = 0 THEN 0 /*If they are in the same week then there is no weekend to count. If you closed a ticket, that was opened on a Monday, on Saturday, then you must of worked on Saturday and therefore counts as work day the same as if you finished it on Monday*/
    WHEN CAST(strftime('%w', t.created_at) as INTEGER) = 0 THEN (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 - 1 /*If they made their ticket on Sunday, don't count that Sunday*/
    WHEN CAST(strftime('%w', t.created_at) as INTEGER) = 6 THEN (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 - 2 /*If they made their ticket on Saturday, don't count that Saturday*/
    ELSE (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 /*Ignoring the possibility for closed dates that happen before open dates, take the number of weeks between each date and assume each had a Saturday and Sunday within them*/
    END) as 'Week Days Elapsed' /*This equation in full represents: Full_Distance_In_Days_Between - Number of weekend days calculated*/
    FROM Tickets as t
    LEFT JOIN Users as u
    on t.created_by = u.id
    WHERE  strftime('%Y-%m', t.created_at) = strftime('%Y-%m', 'now')

0

Источник

user5772189 23 мар '16 в 21:54

Другие вопросы по тегам sqlite datetime average weekday

user11654 05 ноя '13 в 20:49 2013-11-05 20:49 · Accepted Answer · 2013-11-05 20:49

Необработанная разница в днях должна корректироваться в зависимости (только) от дней недели arrival а также released дней:

rel arr|0 1 2 3 4 5 6
-------+-+-+-+-+-+-+-
  0    |2 0 0 0 0 0 1
  1    |2 2 0 0 0 0 1
  2    |2 2 2 0 0 0 1
  3    |2 2 2 2 0 0 1
  4    |2 2 2 2 2 0 1
  5    |2 2 2 2 2 2 1
  6    |2 2 2 2 2 2 2

Это число может быть вычислено с помощью простого выражения (здесь: внутреннее выражение CASE):

SELECT
AVG(julianday(released) - julianday(arrival) -
    CASE WHEN julianday(released) = julianday(arrival) THEN 0
    ELSE (CAST((julianday(released) - julianday(arrival)) / 7 AS INTEGER) * 2
         ) +
      CASE WHEN strftime('%w', arrival) <= strftime('%w', released) THEN 2
           ELSE strftime('%w', arrival) = '6'
      END
    END)
FROM trucks

(Логическое выражение типа x='6' возвращает 0 или 1.)