Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Расчет среднего значения дат в днях  [new]
bravo82
Member

Откуда:
Сообщений: 14
Доброго времени суток!

Помогите пожалуйста, как выполнить данную задачу с помощью SQL:

Требуется вычислить значение между датами (более двух), и расчитать их среднее значение.

Прилагаю файл.

Заранее благодарен!

К сообщению приложен файл. Размер - 85Kb
14 окт 16, 20:53    [19783620]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
iljy
Member

Откуда:
Сообщений: 8711
bravo82,

1. выбрать соседние даты: в зависимости от версии сервера LAG/LEAD либо ROW_NUMBER + JOIN
2. Разница дат - DATEDIFF
3. Среднее - GROUP BY + AVG
14 окт 16, 22:08    [19783924]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
bravo82
Member

Откуда:
Сообщений: 14
Если не затруднит, прошу выложить пример на SQL.
16 окт 16, 14:59    [19786911]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
Никита_1980
Member

Откуда:
Сообщений: 7
НЕ совмем понял, что именно требуется?
17 окт 16, 15:49    [19791055]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
Что-то в этом духе:
DECLARE @T TABLE ([Дата замены]     date          NOT NULL
                 ,[Деталь]          nvarchar(100) NOT NULL
                 ,[Место установки] nvarchar(100) NOT NULL);

INSERT @T
VALUES ('2012-02-16', N'Подшипник №1', N'Машина №1')
      ,('2013-03-05', N'Подшипник №1', N'Машина №1')
      ,('2014-01-28', N'Подшипник №1', N'Машина №1')
      ,('2015-05-21', N'Подшипник №1', N'Машина №1')
      ,('2016-03-17', N'Подшипник №1', N'Машина №1')
      ,('2013-04-15', N'Подшипник №1', N'Машина №2')
      ,('2014-05-20', N'Подшипник №1', N'Машина №2');

SELECT [Дата замены]
      ,[Деталь]
      ,[Место установки]
      ,DATEDIFF(day, LAG([Дата замены], 1) OVER (PARTITION BY [Место установки], [Деталь] ORDER BY [Дата замены]), [Дата замены]) AS [Отработанное время (дней)]
  FROM @T;

;WITH [CTE]
AS
(
    SELECT [Дата замены]
          ,[Деталь]
          ,[Место установки]
          ,DATEDIFF(day, LAG([Дата замены], 1) OVER (PARTITION BY [Место установки], [Деталь] ORDER BY [Дата замены]), [Дата замены]) AS [Отработанное время (дней)]
      FROM @T
)
SELECT [Деталь]
      ,[Место установки]
      ,DATEADD(day, AVG([Отработанное время (дней)]), MAX([Дата замены])) AS [Прогноз]
      ,AVG([Отработанное время (дней)]) AS [Среднее (дней)]
  FROM [CTE]
 GROUP BY [Место установки], [Деталь];


P.S. только разницу я в днях взял для большей наглядности и упрощения кода.
18 окт 16, 15:33    [19795229]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
bravo82
Member

Откуда:
Сообщений: 14
Увы, но LAG не является известным именем встроенной функции. 2008R2.
19 окт 16, 14:10    [19799873]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
bravo82
Member

Откуда:
Сообщений: 14
iljy,
требуется подсчитать среднее значение периода заданного диапазона дат.
19 окт 16, 14:13    [19799889]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
Тогда, как вам и сказали через Row_Number и JOIN делайте
DECLARE @T TABLE ([Дата замены]     date          NOT NULL
                 ,[Деталь]          nvarchar(100) NOT NULL
                 ,[Место установки] nvarchar(100) NOT NULL);

INSERT @T
VALUES ('2012-02-16', N'Подшипник №1', N'Машина №1')
      ,('2013-03-05', N'Подшипник №1', N'Машина №1')
      ,('2014-01-28', N'Подшипник №1', N'Машина №1')
      ,('2015-05-21', N'Подшипник №1', N'Машина №1')
      ,('2016-03-17', N'Подшипник №1', N'Машина №1')
      ,('2013-04-15', N'Подшипник №1', N'Машина №2')
      ,('2014-05-20', N'Подшипник №1', N'Машина №2');

;WITH [CTE]
AS
(
    SELECT [Дата замены]
          ,[Деталь]
          ,[Место установки]
          ,ROW_NUMBER() OVER (PARTITION BY [Место установки], [Деталь] ORDER BY [Дата замены]) AS [RowNum]
      FROM @T
)
SELECT [R1].[Дата замены]
      ,[R1].[Деталь]
      ,[R1].[Место установки]
      ,DATEDIFF(day, [R2].[Дата замены], [R1].[Дата замены]) AS [Отработанное время (дней)]
  FROM [CTE] AS [R1]
       LEFT OUTER JOIN [CTE] AS [R2]
       ON [R2].[Место установки] = [R1].[Место установки] AND
          [R2].[Деталь] = [R1].[Деталь] AND
          [R2].[RowNum] = [R1].[RowNum] - 1
 ORDER BY [R1].[Место установки], [R1].[Деталь], [R1].[Дата замены];

;WITH [CTE]
AS
(
    SELECT [Дата замены]
          ,[Деталь]
          ,[Место установки]
          ,ROW_NUMBER() OVER (PARTITION BY [Место установки], [Деталь] ORDER BY [Дата замены]) AS [RowNum]
      FROM @T
)
SELECT [R1].[Деталь]
      ,[R1].[Место установки]
      ,DATEADD(day, AVG(DATEDIFF(day, [R2].[Дата замены], [R1].[Дата замены])), MAX([R1].[Дата замены])) AS [Прогноз]
      ,AVG(DATEDIFF(day, [R2].[Дата замены], [R1].[Дата замены])) AS [Среднее (дней)]
  FROM [CTE] AS [R1]
       LEFT OUTER JOIN [CTE] AS [R2]
       ON [R2].[Место установки] = [R1].[Место установки] AND
          [R2].[Деталь] = [R1].[Деталь] AND
          [R2].[RowNum] = [R1].[RowNum] - 1
 GROUP BY [R1].[Место установки], [R1].[Деталь]
 ORDER BY [R1].[Место установки], [R1].[Деталь];
19 окт 16, 14:36    [19799998]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
А вообще iljy вам же сразу сказал, что подходы зависят от версии сервера. Могли бы после этого версию свою написать. И на будущее, когда вопросы задаёте пишите свою версию, лучше как результат запроса:
SELECT @@Version
19 окт 16, 14:41    [19800037]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
bravo82
Member

Откуда:
Сообщений: 14
Sybex,
Спасибо.
19 окт 16, 20:58    [19801823]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего значения дат в днях  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
DECLARE @T TABLE (t_date     date          NOT NULL
                 ,detail          nvarchar(100) NOT NULL
                 ,place nvarchar(100) NOT NULL);

INSERT @T
VALUES ('2012-02-16', N'Подшипник №1', N'Машина №1')
      ,('2013-03-05', N'Подшипник №1', N'Машина №1')
      ,('2014-01-28', N'Подшипник №1', N'Машина №1')
      ,('2015-05-21', N'Подшипник №1', N'Машина №1')
      ,('2016-03-17', N'Подшипник №1', N'Машина №1')
      ,('2013-04-15', N'Подшипник №1', N'Машина №2')
      ,('2014-05-20', N'Подшипник №1', N'Машина №2');

	  select t.t_date,t.detail,t.place,datediff(dd,t.t_date,min(t1.t_date)) dif
	  from @T t
	  join @T t1 on t.detail = t1.detail
					and t.place = t1.place
					and t.t_date < t1.t_date
	  group by t.detail,t.place,t.t_date

	 select detail,place,avg(dif) as avg_dif
	 from(
	  select t.detail,t.place,datediff(dd,t.t_date,min(t1.t_date)) dif
	  from @T t
	  join @T t1 on t.detail = t1.detail
					and t.place = t1.place
					and t.t_date < t1.t_date
	  group by t.detail,t.place,t.t_date
	  ) a
	  group by detail,place
19 окт 16, 22:44    [19802100]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить