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

Откуда:
Сообщений: 3
Есть устройство которое отсыпает на конвейер с заданной скоростью порошок. Этому устройству задают параметры с какой скоростью сыпать. Есть счетчик который шлет данные в базу о том сколько в настоящее время в итоге насыпал. Счетчик обнуляется при задании новых параметров устройству которое отсыпает.

Данные если счетчик не обнулялся выглядят примерно выглядят так:

LocalTime | Value
_____________________________
07.07.2016 14:00:04 | 10
07.07.2016 14:00:13 | 14
07.07.2016 14:02:45 | 34
07.07.2016 14:17:50 | 70
07.07.2016 14:59:45 | 144
07.07.2016 15:01:34 | 145
07.07.2016 15:11:24 | 157
07.07.2016 15:36:34 | 160
07.07.2016 15:59:59 | 180

Нужен часовой отчет например сколько отсыпано порошка в интервал с 14 до 15 часов. Поняно что берем разность максимальных значений в этих часах. Т.е 180-144=36, значит 14ч-15ч отсыпано 36кг.


НО, это была ситуация без обнуления счетчика

Как быть если на деле все вот так:

LocalTime | Value
_____________________________
07.07.2016 13:59:59 | 10
07.07.2016 14:00:00 | 10
07.07.2016 14:00:13 | 14
07.07.2016 14:02:45 | 0
07.07.2016 14:17:50 | 10
07.07.2016 14:59:45 | 34
07.07.2016 15:01:34 | 58
07.07.2016 15:11:24 | 7
07.07.2016 15:36:34 | 12
07.07.2016 15:59:59 | 20

Получается например что в 14 часов по факту устройство отсыпало: 38
Как посчитать если счетчик может обнулиться в любой момент и следующее значение в базе будет меньше предыдущего
7 июл 16, 14:56    [19381284]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
aleks2
Guest
В школе прогуливал арифметку?
1. Взять не только первое и последнее значение из интервала, но и "все значения предшествующие нулям из интервала".
2. Фсе значения сложить, вычесть два раза первое значение интервала.
7 июл 16, 15:02    [19381327]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
VSA1990
Member

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

это понятно, не понятно как в коде реализовать. Не хватает знаний
7 июл 16, 15:26    [19381500]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
TaPaK
Member

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

обнуления у вас странные
07.07.2016 15:01:34 | 58
07.07.2016 15:11:24 | 7
7 июл 16, 15:30    [19381537]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
Добрый Э - Эх
Guest
VSA1990,

Как вариант реализации:
--
-- Тестовый набор данных:
with
  t as
    (
      select cast('07.07.2016 13:59:59'as datetime) as LocalTime, 10 as value union all
      select cast('07.07.2016 14:00:00'as datetime) as LocalTime, 10 as value union all
      select cast('07.07.2016 14:00:13'as datetime) as LocalTime, 14 as value union all
      select cast('07.07.2016 14:02:45'as datetime) as LocalTime,  0 as value union all
      select cast('07.07.2016 14:17:50'as datetime) as LocalTime, 10 as value union all
      select cast('07.07.2016 14:59:45'as datetime) as LocalTime, 34 as value union all
      select cast('07.07.2016 15:01:34'as datetime) as LocalTime, 58 as value union all
      select cast('07.07.2016 15:11:24'as datetime) as LocalTime,  7 as value union all
      select cast('07.07.2016 15:36:34'as datetime) as LocalTime, 12 as value union all
      select cast('07.07.2016 15:59:59'as datetime) as LocalTime, 20 as value
    )
--
-- Основной запрос:
select CAST(floor(CAST(t0.LocalTime AS FLOAT)) AS DATETIME) x_Day
     , datepart(hh,t0.LocalTime) as x_Hour
     , sum(case
             when t0.Value < isnull(v0.PrevValue,0) 
               then t0.Value 
             else t0.Value - isnull(v0.PrevValue,0) 
           end)as x_Value
  from t t0
 outer apply
         (
           select top 1 Value as PrevValue
             from t t1
            where t1.LocalTime < t0.LocalTime
            order by LocalTime desc
         ) v0
 group by CAST(floor(CAST(t0.LocalTime AS FLOAT)) AS DATETIME)
        , datepart(hh,t0.LocalTime)


З.Ы.
Для версии 2012 и выше можно использовать LEAD/LAG вместо OUTER APPLY.
С преобразованиями даты в нужный формат (только порция даты + час, без порции минут/секунд/миллисекунд) разберешься самостоятельно?
8 июл 16, 06:43    [19383863]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
TaPaK
VSA1990,

обнуления у вас странные
07.07.2016 15:01:34 | 58
07.07.2016 15:11:24 | 7

речь про то, что если насыпет после обнуления столько же сколько было в предыдущем или больше то как вы определите, что это обнуление...
8 июл 16, 08:53    [19384101]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @t table (dt datetime, v int);
insert into @t
values
 ('20160101 10:00', 1),
 ('20160101 10:10', 2),
 ('20160101 10:20', 3),
 ('20160101 10:30', 0),
 ('20160101 10:40', 4),
 ('20160101 10:50', 1),
 ('20160101 10:55', 2),
 ('20160101 11:00', 1),
 ('20160101 11:10', 2),
 ('20160101 11:20', 3),
 ('20160101 11:30', 0),
 ('20160101 11:40', 4);

with s as
(
 select
  a.dth,
  case when t.v <= lag(t.v, 1, 0) over (partition by a.dth order by t.dt) then t.v else t.v - lag(t.v, 1, 0) over (partition by a.dth order by t.dt) end as v
 from
  @t t cross apply
  (select dateadd(hour, datediff(hour, '1900', t.dt), '1900')) a(dth)
)
select
 dth, sum(v)
from
 s
group by
 dth
order by
 dth;
8 июл 16, 12:24    [19385346]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с подсчетом  [new]
VSA1990
Member

Откуда:
Сообщений: 3
Всем спасибо за участие, проблема которую я боялся отпала сама. На объекте счетчик работает постоянно на накопление, без обнулений.
11 июл 16, 08:49    [19393265]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить