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

Откуда: Бердск, НСО
Сообщений: 499
Есть таблица заказов, одним из столбцов которой является дата создания заказа. Как получить выборку количества заказов в разное время суток с интервалом, например, в 1 час?
30 май 14, 21:36    [16099699]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2960
maxapet, если есть только столбец с датой но нет никаких данных о времени то никак, если-же есть ещё и время - то можно сначала выделить нужные элементы функцией datepart() а потом сгруппировать через ..count(1)..group by datehour например или добавить ещё и объём ..count(1)n,sum(vol)tot..group by datehour ..
Структуру таблицы приведите с образцом данных, а то пока гадание на кофейной гуще получается.
31 май 14, 00:59    [16100156]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
Alexander2
Member

Откуда: Calgary
Сообщений: 1167
maxapet,

Ну тут как бы решение в лоб.., не особо мудрствуя.
Сделал дополнительную таблицу списка часов MyPeriod ( 1...24 строки)


select 
A.Id
,Count(B.MyHour) as N_Orders
from dbo.MyPeriod A
 left join (
select
case 
    when DatePart(hh,MyDate) >= 0  and DatePart(hh,MyDate) < 1 then '1' 
    when DatePart(hh,MyDate) >= 1  and DatePart(hh,MyDate) < 2 then '2' 
    when DatePart(hh,MyDate) >= 2  and DatePart(hh,MyDate) < 3 then '3' 
    when DatePart(hh,MyDate) >= 3  and DatePart(hh,MyDate) < 4 then '4' 
    when DatePart(hh,MyDate) >= 4  and DatePart(hh,MyDate) < 5 then '5' 
    when DatePart(hh,MyDate) >= 5  and DatePart(hh,MyDate) < 6 then '6' 
    when DatePart(hh,MyDate) >= 6  and DatePart(hh,MyDate) < 7 then '7' 
    when DatePart(hh,MyDate) >= 7  and DatePart(hh,MyDate) < 8 then '8' 
    when DatePart(hh,MyDate) >= 8  and DatePart(hh,MyDate) < 9 then '9' 
    when DatePart(hh,MyDate) >= 9  and DatePart(hh,MyDate) < 10 then '10' 
    when DatePart(hh,MyDate) >= 10 and DatePart(hh,MyDate) < 11 then '11' 
    when DatePart(hh,MyDate) >= 11 and DatePart(hh,MyDate) < 12 then '12' 
    when DatePart(hh,MyDate) >= 12 and DatePart(hh,MyDate) < 13 then '13' 
    when DatePart(hh,MyDate) >= 13 and DatePart(hh,MyDate) < 14 then '14' 
    when DatePart(hh,MyDate) >= 14 and DatePart(hh,MyDate) < 15 then '15' 
    when DatePart(hh,MyDate) >= 15 and DatePart(hh,MyDate) < 16 then '16' 
    when DatePart(hh,MyDate) >= 16 and DatePart(hh,MyDate) < 17 then '17' 
    when DatePart(hh,MyDate) >= 17 and DatePart(hh,MyDate) < 18 then '18' 
    when DatePart(hh,MyDate) >= 18 and DatePart(hh,MyDate) < 19 then '19' 
    when DatePart(hh,MyDate) >= 19 and DatePart(hh,MyDate) < 20 then '20' 
    when DatePart(hh,MyDate) >= 20 and DatePart(hh,MyDate) < 21 then '21' 
    when DatePart(hh,MyDate) >= 21 and DatePart(hh,MyDate) < 22 then '22' 
    when DatePart(hh,MyDate) >= 22 and DatePart(hh,MyDate) < 23 then '23' 
    when DatePart(hh,MyDate) >= 23 and DatePart(hh,MyDate) < 24 then '24'
end
as MyHour
from dbo.MyOrders
) B on A.Id = B.MyHour 
group by A.Id
order by 1
31 май 14, 01:31    [16100184]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
vikkiv, виноват, есть, конечно, и время создания заказа, как часть даты. Таблица заказов такая:
OrderId uniqueidentifier,
CustomerId uniqueidentifier,
CreationDate datetime

Проблема для меня в том, чтобы получить статистику именно через заданные интервалы времени. Что, если не каждый час, а, скажем, каждые полчаса?
31 май 14, 06:45    [16100333]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Alexander2, спасибо, но решение в лоб меня как раз и не устраивает. :-)
Что, если статистика потребуется не на каждый час, а на каждые полчаса? Или каждые 15 минут?
Хотелось бы как-то автоматически построить таблицу с заданным интервалом времени. Значение даты и времени хранится в float, где целая часть - дата, а дробная - время. Можно ли это как-то использовать для формирования таблицы MyPeriod?
31 май 14, 06:52    [16100334]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
Alexander2
Member

Откуда: Calgary
Сообщений: 1167
maxapet,

нууу...скажем, если маркетологу взбрендило мониторить интервалы по (минимум) 10-15 минут - то это:

10 минут = 144 условия таблица My144Period
15 минут = 96 условий таблица My96Period
30 минут = 48 условий таблица My48Period
1 час = 24 условия таблица My24Period

если я не путаю, в case мона до 255 строк проверок, так что по 10 минут еще прокатит.

Ну и что такого!? Сделай 4 таблицы, и для разных вариантов вызывай соответствующую процедуру из 4-х.

select 
A.Id
,Count(B.MyHour) as N_Orders
from dbo.My24Period A
 left join (
select
case 
    when DatePart(hh,CreationDate) >= 0  and DatePart(hh,CreationDate) < 1 then '1' 
    when DatePart(hh,CreationDate) >= 1  and DatePart(hh,CreationDate) < 2 then '2' 
    when DatePart(hh,CreationDate) >= 2  and DatePart(hh,CreationDate) < 3 then '3' 
...
    when DatePart(hh,CreationDate) >= 22 and DatePart(hh,CreationDate) < 23 then '23' 
    when DatePart(hh,CreationDate) >= 23 and DatePart(hh,CreationDate) < 24 then '24'
end
as MyHour
from dbo.MyOrders
) B on A.Id = B.MyHour 
group by A.Id
order by 1


А если у кого-то прихоть задавать устанавливаемый интервал, то это изврат, оторванный от действительности.
31 май 14, 09:00    [16100386]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Alexander2,
Alexander2
если у кого-то прихоть задавать устанавливаемый интервал, то это изврат, оторванный от действительности
- это верно! :D
31 май 14, 09:20    [16100396]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
Alexander2
Member

Откуда: Calgary
Сообщений: 1167
maxapet,

автор
Что, если статистика потребуется не на каждый час, а на каждые полчаса? Или каждые 15 минут?
Или каждые 5 минут, или минуту...... - это уже не статистика, это онлайн мониторинг :) Тут уже сейсмограф прикрутить надо!
31 май 14, 09:36    [16100405]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
declare @t table (dt datetime, v int);

insert into @t
values
 ('20140530 10:00', 1),
 ('20140530 10:00:03', 1),
 ('20140530 10:01', 1),
 ('20140530 10:15', 1),
 ('20140530 10:22', 1),
 ('20140530 12:03', 1),
 ('20140530 14:00', 1),
 ('20140530 14:31', 1),
 ('20140531 8:59', 1),
 ('20140531 11:45', 1),
 ('20140531 11:55', 1);

declare @interval tinyint = 30;

select
 x.g, sum(t.v)
from
 @t t cross apply
 (select dateadd(mi, (datediff(mi, '1900', t.dt) / @interval) * @interval, '1900')) x(g) 
group by
 x.g
order by
 x.g;
31 май 14, 10:11    [16100434]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
Alexander2
Member

Откуда: Calgary
Сообщений: 1167
invm,

Все это шикарно! А как насчет показа 0 для других (всех интервалов дня) интервалов, где нет продаж/заказов?
31 май 14, 10:31    [16100453]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество заказов в разное время суток?  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Alexander2
А как насчет показа 0 для других (всех интервалов дня) интервалов, где нет продаж/заказов?
Это элементарно решается с помощью таблицы натуральных чисел или рекурсивного CTE. Поиск по форуму вам поможет.
31 май 14, 11:06    [16100497]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить