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

Есть таблица со статистикой, в ней несколько числовых полей + поле DateTime

Нужно периодически делать хитрую выборку, чтобы получить усреднённые значения за период времени от текущего момента плюс/минус 30 минут и чтобы день недели равнялся текущему. То есть например, в данный момент, когда я это пишу, нужно выбрать все записи дня недели "среда" и за период с 14:25 по 15:25

Вроде не очень сложно, но чтото не соображу, как с этим полем DateTime совладать. Не умею день недели выковыривать и ещё пугает ситуация вблизи полночи, как часы прибавлять в этот момент.

Спасибо!
1 окт 14, 14:55    [16644775]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
https://www.sql.ru/forum/127456/rekomendacii-po-oformleniu-soobshheniy-v-forume , п.4 и п.6
1 окт 14, 14:59    [16644801]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Assoll
Guest
Прошу прощения!

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (X64)

CREATE TABLE [dbo].[ServStat](
[Serv] [nchar](16) NULL,
[Pid] [int] NULL,
[CpuLoad] [int] NULL,
[MemSize] [int] NULL,
[DateTime] [datetime] NULL

INSERT INTO [dbo].[ServStat]
([Serv], [Pid], [CpuLoad], [MemSize], [DateTime])
VALUES
('Serv1', 3000, 20, 140, '2014-10-01 11:12:35.973')
1 окт 14, 15:13    [16644903]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
Assoll
Не умею день недели выковыривать
DATEPART
Assoll
и ещё пугает ситуация вблизи полночи, как часы прибавлять в этот момент.
Нужно преобразовать [DateTime], обнулив дату (то есть приведя дату к 01.01.1900), и потом сравнивать её с 01.01.1900 14:25 и 01.01.1900 15:25. Тогда проблем не будет с переходом через полночь
1 окт 14, 15:31    [16645034]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Assoll
Guest
Спасибо

А как быть сразу после полуночи? Грубо говоря, в 00:05 я приведу дату к 1.1.1900 и начну вычитать 30 минут. Не будет тут какого нибудь косяка? Или дата без проблем может быть 31.12.1899 23:35 ?
1 окт 14, 15:41    [16645101]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Glory
Member

Откуда:
Сообщений: 104760
Assoll
олучить усреднённые значения за период времени от текущего момента плюс/минус 30 минут и чтобы день недели равнялся текущему.

Т.е. в 00:01 вы хотите выбирать за 1 минуту, а на за 30 что ли ?
1 окт 14, 16:01    [16645298]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Assoll
Guest
И в 00:01, и в 23:59 и в любое другое время я выбираю за плюс/минус 30 минут за все среды
Правда, в эти "плюс/минус 30 минут" иногда придётся захватывать часть четверга или вторника
Не знаю, как быть

Вот что пока накодил))

SELECT *
FROM [dbo].[ServStat]
WHERE
DATEPART (weekday , [DateTime]) = DATEPART (weekday , GETDATE()) AND
DateTime >= DATEADD(minute, -30, GETDATE()) AND
DateTime <= DATEADD(minute, 30, GETDATE())
1 окт 14, 16:11    [16645402]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Glory
Member

Откуда:
Сообщений: 104760
Assoll
DateTime >= DATEADD(minute, -30, GETDATE()) AND
DateTime <= DATEADD(minute, 30, GETDATE())

Это же +-30 минут от текущей датывремени.
Т.е. только за эту среду, а не за все
1 окт 14, 16:25    [16645546]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Assoll
Guest
Glory,
Да, и именно поэтому я тут)))
1 окт 14, 16:26    [16645560]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
Assoll
Спасибо

А как быть сразу после полуночи? Грубо говоря, в 00:05 я приведу дату к 1.1.1900 и начну вычитать 30 минут. Не будет тут какого нибудь косяка? Или дата без проблем может быть 31.12.1899 23:35 ?
Да, может быть 31.12.1899 23:35
В хелпе же написано, что допустимый диапазон дат для datetime от 1 января 1753 года , вы же верите хелпу?
2 окт 14, 09:35    [16648298]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
Assoll
Вот что пока накодил))
SELECT *
FROM [dbo].[ServStat]
WHERE
       DATEPART (weekday , [DateTime]) = DATEPART (weekday , GETDATE()) AND
       DateTime >= DATEADD(minute, -30, GETDATE()) AND
       DateTime <= DATEADD(minute, 30, GETDATE())

Да, только не забудьте о приведении к 1 января 1900 года, как то так:
SELECT *
FROM [dbo].[ServStat]
WHERE
       DATEPART (weekday , [DateTime]) = DATEPART (weekday , GETDATE()) AND
       convert(datetime, convert(time, DateTime)) >= DATEADD(minute, -30, convert(datetime, convert(time, GETDATE()))) AND
       convert(datetime, convert(time, DateTime)) <= DATEADD(minute, 30, convert(datetime, convert(time, GETDATE())))
2 окт 14, 09:38    [16648315]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
declare @dt datetime,  @dt1 datetime,  @dt2 datetime;

set @dt =getdate();
set @dt1=dateadd(minute, -30, @dt);
set @dt2=dateadd(minute,  30, @dt);

select *
  from dbo.ServStat
 where [DateTime] between @dt1 and @dt2;
2 окт 14, 13:57    [16650296]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Wlr-l
declare @dt datetime,  @dt1 datetime,  @dt2 datetime;

set @dt =getdate();
set @dt1=dateadd(minute, -30, @dt);
set @dt2=dateadd(minute,  30, @dt);

select *
  from dbo.ServStat
 where [DateTime] between @dt1 and @dt2;
Тему не читали, да?
Надо записи в этом промежутке времени
и во все такие же дни недели, как сегодня, а не только сегодня!
2 окт 14, 14:02    [16650327]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
iap,
Прочитал еще раз. Возможно, я не прав.
2 окт 14, 14:18    [16650461]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Приведение к 1 января 1900 года задачу не решает:

declare @ServStat table (
 id          int       not null identity(1,1),
 [DateTime]  datetime  not null
);

insert @ServStat ([DateTime])
  values         ('20140930 23:59:00'),
                 ('20141001 00:12:35'),
                 ('20141001 00:14:35'),
                 ('20141001 00:16:35'),
                 ('20141001 14:12:35');

select * from @ServStat;
declare @dt datetime = '20141001 00:12:35';

SELECT *
FROM @ServStat
WHERE
       DATEPART (weekday , [DateTime]) = DATEPART (weekday , @dt) AND
       convert(datetime, convert(time, DateTime)) >= DATEADD(minute, -30, convert(datetime, convert(time,@dt))) AND
       convert(datetime, convert(time, DateTime)) <= DATEADD(minute,  30, convert(datetime, convert(time, @dt)))


id	DateTime
1 2014-09-30 23:59:00.000
2 2014-10-01 00:12:35.000
3 2014-10-01 00:14:35.000
4 2014-10-01 00:16:35.000
5 2014-10-01 14:12:35.000

id DateTime
2 2014-10-01 00:12:35.000
3 2014-10-01 00:14:35.000
4 2014-10-01 00:16:35.000
2 окт 14, 14:48    [16650705]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Assoll
Guest
Wlr-l
Приведение к 1 января 1900 года задачу не решает:
id	DateTime
1 2014-09-30 23:59:00.000
2 2014-10-01 00:12:35.000
3 2014-10-01 00:14:35.000
4 2014-10-01 00:16:35.000
5 2014-10-01 14:12:35.000

id DateTime
2 2014-10-01 00:12:35.000
3 2014-10-01 00:14:35.000
4 2014-10-01 00:16:35.000

Потому что получается по сути сравнение с:
1 1900-01-01 23:59:00.000
2 1900-01-01 00:12:35.000
3 1900-01-01 00:14:35.000
4 1900-01-01 00:16:35.000
5 1900-01-01 14:12:35.000
2 окт 14, 15:59    [16651296]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
aleks2
Guest
Assoll
Потому что получается по сути сравнение с:
1 1900-01-01 23:59:00.000
2 1900-01-01 00:12:35.000
3 1900-01-01 00:14:35.000
4 1900-01-01 00:16:35.000
5 1900-01-01 14:12:35.000


Все дни за 100 лет = 36500 дней.
Смешная цифра.
Проще всего явно генерировать нужные интервалы для каждого нужного дня и фсе.
2 окт 14, 16:18    [16651421]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
С учетом предложения aleks2 (таблица дат, она у меня называется id) возможное решение (можно записать короче, но я постарался подробнее):

declare @dt datetime,  @t time, @dp int, @dlt int;

set @dt   = '20141001 00:12:35';   --заданная дата
set @dlt  = 30;                    --граница диапазона времени

set @t  = cast(@dt as time);     --выделяем время из заданной даты
set @dp = datepart(weekday,@dt); --день недели заданной даты

with A as (
  select --top 10
         d -- дата из таблицы дат без времени
        ,dateadd(ss,datediff(ss,'19000101',@t),d) as dt -- d + @t получаем даты с временем заданной даты
    from dbo.id -- таблица дат
   where datepart(weekday,d)=@dp
)

, B as ( --для каждой даты из А получаем диапазон времени d +- @dlt
  select dt, dateadd(minute, -@dlt, dt) as dt1, dateadd(minute, @dlt, dt) as dt2
    from A
)

, C as ( -- эмуляция исходной таблицы
  select id,cast([datetime] as datetime) as [datetime]
    from (values ( 1, '20140922 00:16:35'),
                 ( 2, '20140923 00:16:35'),
                 ( 3, '20140924 00:16:35'),
                 ( 4, '20140930 23:59:00'),
                 ( 5, '20141001 00:12:35'),
                 ( 6, '20141001 00:14:35'),
                 ( 7, '20141001 00:16:35'),
                 ( 8, '20141002 00:16:35'),
                 ( 9, '20141001 14:12:35')
         ) as T (id,[datetime])
)

--select * from A;
--select * from B;
select id, C.[datetime]
  from C
  join B on C.[datetime] between B.dt1 and B.dt2;


Результат:
id	datetime
3 2014-09-24 00:16:35.000
4 2014-09-30 23:59:00.000
5 2014-10-01 00:12:35.000
6 2014-10-01 00:14:35.000
7 2014-10-01 00:16:35.000
2 окт 14, 17:40    [16651962]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
stdvb
Member

Откуда:
Сообщений: 38
declare @ServStat table (
 id          int       not null identity(1,1),
 [DateTime]  datetime  not null
);

insert @ServStat ([DateTime])
values          ('20140923 00:16:35'),
                ('20140924 00:16:35'),
                ('20140930 23:59:00'),
                ('20141001 00:12:35'),
                ('20141001 00:14:35'),
                ('20141001 00:16:35'),
                ('20141001 14:12:35');

declare @date datetime;
set @date = '20141001 00:05';

select *
from @ServStat
where	cast([DateTime] as float) - (cast([DateTime] as int) / 7) * 7 between
		cast(dateadd(minute, -30, @date) as float) - (cast(dateadd(minute, -30, @date) as int) / 7) * 7 and
		cast(dateadd(minute,  30, @date) as float) - (cast(dateadd(minute,  30, @date) as int) / 7) * 7;

Хотя с интервалами может быть и быстрее.
2 окт 14, 19:56    [16652587]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
stdvb
Member

Откуда:
Сообщений: 38
stdvb,
А нет, и тут проблема на концах интервалов (недель). Но переделать не сложно, хотя дома уже лень думать.
2 окт 14, 21:26    [16652922]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
Wlr-l
Приведение к 1 января 1900 года задачу не решает:

Assoll
Потому что получается по сути сравнение с:
1 1900-01-01 23:59:00.000
2 1900-01-01 00:12:35.000
3 1900-01-01 00:14:35.000
4 1900-01-01 00:16:35.000
5 1900-01-01 14:12:35.000
Да, неправильное получение дня недели.

Можно изменить условие, типа
(DATEPART (weekday , DATEADD(minute, -30, [DateTime])) = DATEPART (weekday , @dt) or DATEPART (weekday , DATEADD(minute, 30, [DateTime])) = DATEPART (weekday , @dt)) 
2 окт 14, 21:28    [16652927]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

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

(DATEPART (weekday , DATEADD(minute, -30, [DateTime])) = DATEPART (weekday , @dt) or DATEPART (weekday , DATEADD(minute, 30, [DateTime])) = DATEPART (weekday , @dt))


И это условие не дает правильного решения на границе суток.
3 окт 14, 11:10    [16654791]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Так никто и не дал решение с использованием дня недели.

Попробуем от интуитивных представлений использования дня недели для решения данной задачи перейти к формальным.

Пусть db и de соответственно начало и конец суток, t1 и t2 - начало и конец интервала времени.
Рассмотрим возможные варианты расположения интервала времени относительно границ суток и найдем в каком дне недели он лежит.

1.Интервал времени целиком лежит в текущих сутках.

   вчера       сегодня       завтра
--------|------.-----.------|--------
db t1 t2 dt

[t1, t2] - день недели даты "сегодня"

2.Интервал времени захватывает текущие и предыдущие сутки.

   вчера       сегодня       завтра
-----.--|---.---------------|--------
t1 db t2 dt

Выделим два подинтервала:
[db, t2] - день недели даты "сегодня"
[t1, db) - день недели даты "вчера"


3.Интервал времени захватывает текущие и следующие сутки.

   вчера       сегодня       завтра
--------|---------------.---|--.----
db t1 dt t2

Выделим два подинтервала:
[t1, de) - день недели даты "сегодня"
[de, t2] - день недели даты "завтра"

Следовательно, составление условия отбора строк с использованием дня недели является достаточно сложной логической задачей. Поэтому все еще не дано решение с использованием дня недели.
7 окт 14, 15:53    [16671926]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
В предыдущем моем решении (генерирование допустимых интервалов) использовалась таблица дат, что затрудняет воспроизведение результатов.
В этом решении нужные даты получим из исходной таблицы и покажем, как можно найти средние значения.

declare @dt datetime,  @t time, @dp int, @dlt int;

set @dt   = '20141001 00:12:35';   --заданная дата
set @dlt  = 30;                    --граница диапазона времени

set @t  = cast(@dt as time);     --выделяем время из заданной даты
set @dp = datepart(weekday,@dt); --день недели заданной даты


with C as ( -- эмуляция исходной таблицы
  select id,cast([datetime] as datetime) as [datetime]
    from (values ( 1, '20140922 00:16:35'),
                 ( 2, '20140923 00:16:35'),
                 ( 3, '20140924 00:16:35'),
                 ( 4, '20140930 23:59:00'),
                 ( 5, '20141001 00:12:35'),
                 ( 6, '20141001 00:14:35'),
                 ( 7, '20141001 00:16:35'),
                 ( 8, '20141002 00:16:35'),
                 ( 9, '20141001 14:12:35')
         ) as T (id,[datetime])
)
, id as (-- находим даты с заданным днем недели без времени
  select distinct cast([datetime] as date) as d
    from C
   where  datepart(weekday,[datetime])=@dp
)
, A as (--добавляем к найденным датам время из заданной даты
        --этот запрос служит исключительно для запроса B
  select d
        ,dateadd(ss,datediff(ss,'19000101',@t),cast(d as datetime)) as dt
    from id
)
, B as (--для каждой даты из А формируем диапазон времени dt +- @dlt
  select d, dateadd(minute, -@dlt, dt) as dt1, dateadd(minute, @dlt, dt) as dt2
    from A
)

select id, d, C.[datetime]
      ,-(dense_rank() over (order by d desc)- 1) as R --на сколько недель отстоит текущая дата от последней
      ,avg(1.0*id)    over (partition by d)      as avgID
      ,avg(1.0*id)    over ()                    as avgTotal
  from C
  join B on C.[datetime] between B.dt1 and B.dt2;


Результат:
id  d            datetime                 R    avgID      avgTotal
3 2014-09-24 2014-09-24 00:16:35.000 -1 3.000000 5.000000
4 2014-10-01 2014-09-30 23:59:00.000 0 5.500000 5.000000
5 2014-10-01 2014-10-01 00:12:35.000 0 5.500000 5.000000
6 2014-10-01 2014-10-01 00:14:35.000 0 5.500000 5.000000
7 2014-10-01 2014-10-01 00:16:35.000 0 5.500000 5.000000
7 окт 14, 16:05    [16672004]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за диапазон времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Wlr-l,

если у двух суток @D1 и @D2 один и тот же день недели, то выполняются вот такие, например, условия:
(@@DATEFIRST+DATEPART(WEEKDAY,@D1))%7=(@@DATEFIRST+DATEPART(WEEKDAY,@D2))%7
DATEDIFF(DAY,0,@D1)%7=DATEDIFF(DAY,0,@D2)%7
А дальше требуется один и тот же момент времени, от которого и в один день,
и в другой надо отнять и прибавить 30 минут для границ периода.
7 окт 14, 16:10    [16672034]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить