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

Откуда:
Сообщений: 51
Есть таблица вида
delivery_id, Date
1 1 января 2009
1 1 января 2009
1 1 января 2009
1 7 января 2009
1 7 января 2009
1 12 февраля 2009
....................................
5 2 января 2009
5 7 января 2009
5 12 января 2009
5 27 января 2009
5 27 января 2009
5 14 февраля 2009

Надо получить следующее: упорядоченную по delivery_id таблицу и для каждого delivery_Id средняя частота обновления в днях.
7 сен 09, 17:07    [7628149]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Приведите, пожалуйста, на русском языке алгоритм (или формулу) подсчета показателя
antananarivu83
средняя частота обновления в днях.
7 сен 09, 17:10    [7628164]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
iljy
Member

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

а "средняя частота обновления в днях" - это что? средняя разница между последовательными записями? И как считать если в течении дня несколько записей? (у вас - 1 января 2009)
7 сен 09, 17:11    [7628168]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
Среднее арифметическое между всеми периодами обновления.
7 сен 09, 17:11    [7628177]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
i2akai1
Member

Откуда: Петербург
Сообщений: 217
antananarivu83,

select delivery_id, (max(date) - min(date)) / count(*)
from t
group by delivery_id

типа того?
7 сен 09, 17:12    [7628178]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
delivery_id = 123
3 января 2009 - 1 января 2009 = 2
7 января 2009 - 3 января 2009 = 4
12 января 20009 - 7 января 2009 = 5
......

(2 + 4 + 5)/3 = 11/3 = 3,(6)

delivery_id S
123 3,66666
7 сен 09, 17:14    [7628202]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Тогда на этот вопрос
iljy
И как считать если в течении дня несколько записей? (у вас - 1 января 2009)
мне тоже хотелось бы услышать ответ
7 сен 09, 17:17    [7628222]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
Не учитывать, это неважно что скажем 1 января позиция появлялась несколько раз
7 сен 09, 17:19    [7628234]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
i2akai1
Member

Откуда: Петербург
Сообщений: 217
antananarivu83,

select delivery_id, (max(date) - min(date)) / (count(distinct date) - 1)
from t
group by delivery_id
7 сен 09, 17:21    [7628247]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
iljy
Member

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

как-то так
with T as (select *, row_number() over(partition by delivery_id order by date) N from Table1)
select t1.delivery_id,
	sum(case when t1.date != t2.date then t2.date - t1.date end) /
	count(case when t1.date != t2.date then 1 end)
from
T t1 join T t2 on t1.delivery_id = t2.delivery_id and t1.N+1 = t2.N
group by t1.delivery_id
7 сен 09, 17:24    [7628269]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
iljy
Member

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

кстати да, так проще. Еще деление на 0 надо проверку добавить, если дата только одна.
7 сен 09, 17:26    [7628295]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
принцип i2akai1 вроде как раз то что нужно автору
-- test data
declare @t table(delivery_id int, date smalldatetime)
insert into @t(delivery_id, date)
select 1, '20090101' union all
select 1, '20090101' union all
select 1, '20090101' union all
select 1, '20090107' union all
select 1, '20090107' union all
select 1, '20090212' union all
select 5, '20090102' union all
select 5, '20090107' union all
select 5, '20090112' union all
select 5, '20090127' union all
select 5, '20090127' union all
select 5, '20090214'
-- end of test data

select delivery_id
      ,1.0 * datediff(dd, min(date), max(date))
           / (count(distinct date) - 1) as [средняя частота обновления]
  from @t
 group by delivery_id
 order by delivery_id

delivery_id средняя частота обновления
----------- --------------------------------------
1           21.000000000000
5           10.750000000000

(2 row(s) affected)
7 сен 09, 17:26    [7628297]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iljy
Еще деление на 0 надо проверку добавить, если дата только одна.
Точно, как-то я тоже подсознательно не проверяю на 0 если речь идет о count
7 сен 09, 17:31    [7628337]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
i2akai1
Member

Откуда: Петербург
Сообщений: 217
Паганель,

а я помнил :), но подумал это не суть :)
7 сен 09, 17:33    [7628347]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
Всем огромное спасибо! )
7 сен 09, 17:40    [7628392]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
Еще проблема возникла (count(DISTINCT [date])) - дает некорректный результат - потому что много позиций у которых одна и таже календарная дата, а часы и минуты разняться.
123 1 января 12:00
123 1 января 12:01

Я думаю и сам справлюсь, но если вас не затруднит, подскажите, пожалуйста. Я так понимаю надо сначала преобразовать дату к формату число месяц год без часов и минут, верно?
7 сен 09, 17:46    [7628425]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Отрезать время от даты и дату от даты-времени. Округлить дату
7 сен 09, 17:47    [7628432]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
iljy
Member

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

count(distinct dateadd(dd, 0, datediff(dd, 0, date))
7 сен 09, 17:49    [7628442]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
antananarivu83
Member

Откуда:
Сообщений: 51
Супер, огромное спасибо.
7 сен 09, 17:56    [7628475]     Ответить | Цитировать Сообщить модератору
 Re: Как грамотно сотавить запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Версия сервера неизвестна. А вдруг 2008?
Тогда отрезать время совсем просто - достаточно преобразовать к типу DATE.
7 сен 09, 20:44    [7628929]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить