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

Откуда:
Сообщений: 139
Помогите пожалуйста с запросом. Звучит очень просто, но как подступится к нему не пойму.

Имеется таблица с данными такого формата:

DateTime SomeData
01.01.2017 10:02....
01.01.2017 11:20....
01.01.2017 11:20....
01.01.2017 11:21....


Нужно разбить каждый день на отрезки в 30 минут и для каждого дня посчитать количество строк с данными.
Т.е. чтобы в итоге было так:

date timeslice count
01.01.201700:00-00:300
01.01.201700:30-01:000
01.01.201710:00-10:301
.........
01.01.201711:00-11:303
.........
02.01.201700:00-00:300
.........


Примерный шаги запроса понимаю:
- сгенерировать получасовые отрезки в рамках одного дня
- заджоинить таблицу с данными разбив по получасовым отрезкам
- ну и сгруппировать по count()

но вот написать SQL не могу, идей нет. Хэлп плиз.
12 июл 17, 13:52    [20636954]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
msLex
Member

Откуда:
Сообщений: 8065
TsHeloWorlder
Примерный шаги запроса понимаю:
- сгенерировать получасовые отрезки в рамках одного дня
- заджоинить таблицу с данными разбив по получасовым отрезкам
- ну и сгруппировать по count()



на каком из этапов возникла проблема?
12 июл 17, 13:54    [20636964]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
TsHeloWorlder
- сгенерировать получасовые отрезки в рамках одного дня
Используйте таблицу чисел, генерируйте отрезки командой dateadd
TsHeloWorlder
- заджоинить таблицу с данными разбив по получасовым отрезкам
- ну и сгруппировать по count()
Дальше уже просто, join, count и group by
12 июл 17, 13:57    [20636983]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
TsHeloWorlder
Member

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

на первом. и потом скорее всего на втором будут - нужно будет джоинить согласно отрезкам.
думаю только с последним этапом проблем не будет ))

вообще я редко пишу запросы, Entity Framework отучает от этого дела :( Отвык уже на чистом SQL писать и когда такие нестандартные задачи возникают, задумываюсь.
12 июл 17, 14:03    [20637020]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
msLex
Member

Откуда:
Сообщений: 8065
два ваших первых шага можно заменить на "округление" времени до получаса

dateadd(mi, datediff(mi, 0, datetimefield) / 30 *30, 0)

и уже относительно него группировать
12 июл 17, 14:19    [20637094]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
msLex
два ваших первых шага можно заменить на "округление" времени до получаса

dateadd(mi, datediff(mi, 0, datetimefield) / 30 *30, 0)

и уже относительно него группировать
Нельзя. Так отсутствующие получасовки не получишь!
12 июл 17, 14:44    [20637208]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
msLex
Member

Откуда:
Сообщений: 8065
iap
msLex
два ваших первых шага можно заменить на "округление" времени до получаса

dateadd(mi, datediff(mi, 0, datetimefield) / 30 *30, 0)

и уже относительно него группировать
Нельзя. Так отсутствующие получасовки не получишь!


да, на count = 0 не обратил внимания
12 июл 17, 14:49    [20637223]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
очевидно, нужна таблица-циферблат со значениями от 00:00 до 23:59
12 июл 17, 15:16    [20637387]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
MyNiGoo
очевидно, нужна таблица-циферблат со значениями от 00:00 до 23:59

омг, уже ответили давно
автор
Используйте таблицу чисел

какой ещё циферблат, табло и тд...
12 июл 17, 15:18    [20637396]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
Massa52
Member

Откуда:
Сообщений: 379
TsHeloWorlder,
+

DECLARE @sd date = '20170101';
DECLARE @ed date = '20170102';
DECLARE @step int = 30;
DECLARE @s datetime = @sd;
DECLARE @i int = 1;
DECLARE @t TABLE (id int, timeslise datetime);
WHILE @s < @ed
BEGIN
  INSERT @t (id, timeslise) VALUES (@i, @s); 
  SET @s = DATEADD(minute, @step, @s);
  SET @i = @i + 1;
END
--SELECT * FROM @t;
DECLARE @tbl TABLE (d datetime, somedata varchar(10))
INSERT @tbl VALUES
('20170101 10:02:00', '1'),
('20170101 11:20:00', '2'),
('20170101 11:20:00', '3'),
('20170101 11:21:00', '4')
SELECT   t1.timeslise, count(d) n 
FROM @t t1 LEFT JOIN @tbl t2 ON t2.d >= t1.timeslise AND t2.d < DATEADD(minute, @step, t1.timeslise)  
GROUP BY t1.timeslise

12 июл 17, 15:26    [20637437]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4200
Есть нетривиальное решение
Помня о том, что при конвертации даты в флоат время находится в дробной части числа
declare @half float = 0.0208333333357587;

((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=      0  and CAST([DateTime] as float) - CAST([DateTime] as int) < @half)   as [00-30],
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=   @half and CAST([DateTime] as float) - CAST([DateTime] as int) < 2*@half) as [01-00],
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >= 2*@half and CAST([DateTime] as float) - CAST([DateTime] as int) < 3*@half) as [01-30],
...
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=46*@half and CAST([DateTime] as float) - CAST([DateTime] as int) <47*@half) as [23-30]


Надеюсь принцип понятен.
Хотя в будущем может перестать работать.

При желании можно оптимизировать, загнать в CTE, сделать проще.
12 июл 17, 15:45    [20637514]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
msLex
Member

Откуда:
Сообщений: 8065
SQL2008
Есть нетривиальное решение
Помня о том, что при конвертации даты в флоат время находится в дробной части числа
declare @half float = 0.0208333333357587;

((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=      0  and CAST([DateTime] as float) - CAST([DateTime] as int) < @half)   as [00-30],
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=   @half and CAST([DateTime] as float) - CAST([DateTime] as int) < 2*@half) as [01-00],
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >= 2*@half and CAST([DateTime] as float) - CAST([DateTime] as int) < 3*@half) as [01-30],
...
((select count(1) from t where CAST([DateTime] as float) - CAST([DateTime] as int) >=46*@half and CAST([DateTime] as float) - CAST([DateTime] as int) <47*@half) as [23-30]



Надеюсь принцип понятен.
Хотя в будущем может перестать работать.

При желании можно оптимизировать, загнать в CTE, сделать проще.



12 июл 17, 15:48    [20637525]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
TsHeloWorlder
Member

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

Спасибо !
12 июл 17, 15:53    [20637553]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Massa52,

если уже генерируете таблицу, то почему сразу не получать 2 значения и потом не делать вычисления на каждую строку источника
12 июл 17, 15:59    [20637587]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
Massa52
Member

Откуда:
Сообщений: 379
TaPaK,
Действительно. Надо было и второе значение сгенерить. Соображалка подвела.
12 июл 17, 16:26    [20637705]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
Massa52
Member

Откуда:
Сообщений: 379
DECLARE @tbl TABLE (d datetime, somedata varchar(10))
INSERT @tbl VALUES
('20170101 10:02:00', '1'),
('20170101 11:20:00', '2'),
('20170101 11:20:00', '3'),
('20170101 11:21:00', '4');

WITH Numbers(N) AS
(
   SELECT 1 UNION ALL SELECT N+1 FROM Numbers WHERE N < 48
), c AS
(   SELECT *, DATEADD(minute, @step * (N - 1), @s) t1, DATEADD(minute, @step * N, @s) t2 
   FROM Numbers 
)
SELECT   t1.t1, MAX(t1.t2) t2, count(d) n 
FROM c t1 LEFT JOIN @tbl t2 ON t2.d >= t1.t1 AND t2.d < t1.t2 
GROUP BY t1.t1  
13 июл 17, 04:28    [20638996]     Ответить | Цитировать Сообщить модератору
 Re: Помогит с запросом.  [new]
TsHeloWorlder
Member

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

Ок ! )
13 июл 17, 10:30    [20639677]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить