Я пользуюсь MySQL, но вопрос общий. Есть таблица scans с тремя колонками:

sensor_id, ts, value

Туда записываются показания нескольких датчиков, sensor_id -- номер датчика, ts -- время измерения, value -- температура к примеру. Надо выбрать таблицу с колонками

sensor_id, derivative

, где derivative — мгновенная скорость изменения температуры в последний момент, т.е. для данного sensor_id:

(последнее по ts значение value — предпоследнее по ts значение value) / (последнее ts — предпоследнее ts)

Пример:

было:
sensor_id, ts, value
1, 10 ms, 400 K
1, 20 ms, 500 K
2, 12 ms, 345 K
2, 20 ms, 410 K
2, 50 ms, 470 K

получаем:
1, 10 K/ms
2, 2 K/ms

Я почти не знаю SQL, не пойму, возможно ли это вообще? Если нет, то как реорганизовать данные?

Здравствуйте, brank, Вы писали:

B>Я почти не знаю SQL, не пойму, возможно ли это вообще?

Возможно:
1 шаг: пишем выборку, кот оставляет только 2 послених измерения по каждому сенсору.
2 шаг: группируе выборку по сенсору и считаем (MAX(value) — MIN(value))/(MAX(time) — MIN(time)).

Но все это будет жутко медленно. Возможно, следует посмотреть, какие аналитические функции поддерживает ваша база и задействовать их.

Здравствуйте, Lloyd, Вы писали:

L>Здравствуйте, brank, Вы писали:

B>>Я почти не знаю SQL, не пойму, возможно ли это вообще?

L>Возможно:
L>1 шаг: пишем выборку, кот оставляет только 2 послених измерения по каждому сенсору.
L>2 шаг: группируе выборку по сенсору и считаем (MAX(value) — MIN(value))/(MAX(time) — MIN(time)).

L>Но все это будет жутко медленно. Возможно, следует посмотреть, какие аналитические функции поддерживает ваша база и задействовать их.

Торможение для меня пока не важно (сенсоров не оч много), но каким образом сделать шаг 1? Я могу написать "SELECT * FROM scans WHERE sensor_id = 1 LIMIT 2;", но не писать же так для каждого sensor_id (их число заранее неизвестно, они живут в отдельной таблице sensors: sensor_id, name).

Здравствуйте, brank, Вы писали:

L>>Но все это будет жутко медленно. Возможно, следует посмотреть, какие аналитические функции поддерживает ваша база и задействовать их.

B>Торможение для меня пока не важно (сенсоров не оч много), но каким образом сделать шаг 1? Я могу написать "SELECT * FROM scans WHERE sensor_id = 1 LIMIT 2;", но не писать же так для каждого sensor_id (их число заранее неизвестно, они живут в отдельной таблице sensors: sensor_id, name).

Просто нужно переформулировать: нужно вернуть записи, для которых (where) кол-во (count) более поздних (b.time > a.time) для указаного сенсора (b.sensor_id = a.sensor_id) не превосходит 1 (<= 1). Делается это подзапрсами.
Судя по LIMIT у вас — mysql, не уверен, что он поддерживает подзапросы.

Здравствуйте, brank, Вы писали:

b> sensor_id, ts, value
b> 1, 10 ms, 400 K
b> 1, 20 ms, 500 K
b> 2, 12 ms, 345 K
b> 2, 20 ms, 410 K
b> 2, 50 ms, 470 K

Возможно, для этой задачи лучше подходит RRD?

brank wrote:

> Я почти не знаю SQL, не пойму, возможно ли это вообще? Если нет, то как
> реорганизовать данные?

Это -- не задача для реляционной СУБД, однозначно.
Решайте её на клиенте, например, при вставке данных.

Здравствуйте, Lloyd, Вы писали:

L>Судя по LIMIT у вас — mysql, не уверен, что он поддерживает подзапросы.

Только хотел похвалить MySQL за поддержку подзапросов, как получил следующее:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Похоже нужные функции данных придётся считать до загрузки в базу и хранить в отдельных полях.

В любом случае спасибо за помощь!

Здравствуйте, Anton Batenev, Вы писали:

AB>Здравствуйте, brank, Вы писали:

b>> sensor_id, ts, value
b>> 1, 10 ms, 400 K
b>> 1, 20 ms, 500 K
b>> 2, 12 ms, 345 K
b>> 2, 20 ms, 410 K
b>> 2, 50 ms, 470 K

AB>Возможно, для этой задачи лучше подходит RRD?

Там не так уж много данных, и все они нужны (с десяток экспериментов по 1000 замеров в каждом), а rrd старые данные выбрасывает, как я понял. Склоняюсь к тому, чтобы подготавливать всю аналитику перлом, а потом уже грузить в базу (теперь, когда я понял, что SQL не silver bullet).

Здравствуйте, brank, Вы писали:

B>Только хотел похвалить MySQL за поддержку подзапросов, как получил следующее:

B>ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Приведите запрос, что вы написали. Возможно, мы друг друга неправильно поняли.

Здравствуйте, Lloyd, Вы писали:

B>>ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

L>Приведите запрос, что вы написали. Возможно, мы друг друга неправильно поняли.

добавил колонку scan_id INT PRIMARY KEY и написал так:

SELECT sensor_id, (MAX(ts) — MIN(ts)) / (MAX(value) — MIN(value)) FROM scans AS result WHERE scan_id IN (SELECT scan_id FROM scans WHERE sensor_id = result.sensor_id ORDER BY ts DESC LIMIT 2) GROUP BY sensor_id;

Здравствуйте, Lloyd, Вы писали:

L>1 шаг: пишем выборку, кот оставляет только 2 послених измерения по каждому сенсору.
L>2 шаг: группируе выборку по сенсору и считаем (MAX(value) — MIN(value))/(MAX(time) — MIN(time)).

вариант 2:
1 шаг: написать ДВЕ выборки, возвращающие последние и предпоследние измерения по каждому сенсору (sensor_id, ts, value) соответственно.
2 шаг: объединяем (join) выборки по sensor_id и считаем (t2.value — t1.value)/(t2.ts — t1.ts)

Здравствуйте, seregaa, Вы писали:

S>вариант 2:
S>1 шаг: написать ДВЕ выборки, возвращающие последние и предпоследние измерения по каждому сенсору (sensor_id, ts, value) соответственно.
S>2 шаг: объединяем (join) выборки по sensor_id и считаем (t2.value — t1.value)/(t2.ts — t1.ts)

Непонятно опять, как сделать шаг 1, а именно, как вернуть предпоследнее. Не могли бы вы привести примерный запрос?

Здравствуйте, brank, Вы писали:

B>Непонятно опять, как сделать шаг 1, а именно, как вернуть предпоследнее. Не могли бы вы привести примерный запрос?

это — даты последних показаний для каждого счетчика

select sensor_id, max(td) as maxtd from table1 group by sensor_id

а это — последние показания для каждого счетчика (при условии, что для каждого счетчика не существует больше одного показания за одно и то же время)

select t1.sensor_id, t1.td, t1.val 
from 
    table1 t1 
        inner join (
            select sensor_id, max(td) as maxtd from table1 
                    group by sensor_id) t2 
        on t1.sensor_id = t2.sensor_id and t1.td = t2.maxtd

для того, чтобы получить _предпоследние_ показания, нужно в предыдущем запросе перед подсчетом max-ов исключить последние показания (выделено италиком)

select t1.sensor_id, t1.td, t1.val 
from 
    table1 t1 
        inner join (
                select sensor_id, max(td) as maxtd from table1 
                    where td < (select max(td) from table1 t3 where t3.sensor_id = table1.sensor_id)
                    group by sensor_id) t2 
        on t1.sensor_id = t2.sensor_id and t1.td = t2.maxtd

здесь я пытался использовать наиболее простой язык запросов, поскольку не очень представляю себе возможности используемой вами версии mysql.

p.s. составной индекс по полям source_id, td очень желателен.

MZ>Это -- не задача для реляционной СУБД, однозначно.
MZ>Решайте её на клиенте, например, при вставке данных.

Обычная задача на джойны

select
  current.sensor_id
  ,(current.value - previous.value) / (current.ts - previous.ts)
from
  scans current
  inner join scans previous on
    previous.sensor_id = current.sensor_id
    and previous.ts < current.ts
    and not exists (
      select * from scans intermediate 
      where intermediate.sensor_id = current.sensor_id and intermediate.ts between previous.ts and current.ts
    ) 
where
  -- опциональный фильтр, показывает только последние показатели, без него будет история скоростей
  not exists (
      select * from scans latest
      where latest.sensor_id = current.sensor_id and latest.ts > current.ts
  )

Соответственно индекс нужен

create index IX_scans_sensor_ts on scans (sensor_id, ts)

Если допускаются засечки с одинаковым временем, то работать будет неправильно

У меня вообще складывается ощущение, что пора уже пример запроса на джойн текущий-предыдущий в какое-нибудь ЧаВо вынести

Здравствуйте, brank, Вы писали:

L>>Приведите запрос, что вы написали. Возможно, мы друг друга неправильно поняли.

B>добавил колонку scan_id INT PRIMARY KEY и написал так:

B>SELECT sensor_id, (MAX(ts) — MIN(ts)) / (MAX(value) — MIN(value)) FROM scans AS result WHERE scan_id IN (SELECT scan_id FROM scans WHERE sensor_id = result.sensor_id ORDER BY ts DESC LIMIT 2) GROUP BY sensor_id;

посмотрите вот эту статью: Emulating Analytic (AKA Ranking) Functions with MySQL, в частости про rank over.

L>посмотрите вот эту статью: Emulating Analytic (AKA Ranking) Functions with MySQL, в частости про rank over.

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

Здравствуйте, brank, Вы писали:

B>Я пользуюсь MySQL

SELECT
    scans.*,
    IF(sensor_id = @id_last, (value - @value_last) / (ts - @ts_last), NULL) +
        LEAST(0, @id_last := sensor_id, @value_last := value, @ts_last := ts) AS rate
FROM
   (SELECT @id_last := NULL, @ts_last := NULL, @value_last := NULL) init,
    scans
ORDER BY
    sensor_id, ts;

(http://rsdn.ru/forum/db/2969570.1.aspx

Автор: Roman Odaisky
Дата: 29.05.08

)

B>но вопрос общий.

WITH scans_numbered AS (
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY sensor_id, ts) AS ix
    FROM
        scans
)
SELECT
    s.*, (s.value - s1.value) / (s.ts - s1.ts) AS rate
FROM
    scans_numbered s INNER JOIN scans_numbered s1 ON s.sensor_id = s1.sensor_id AND s.ix = s1.ix + 1

Проще всего, конечно, в Оракле.

SELECT
    *,
    (value - LAG(value) OVER(ORDER BY sensor_id, ts)) /
        (ts - LAG(ts) OVER(ORDER BY sensor_id, ts)) AS rate
FROM
    scans
WHERE
    sensor_id = LAG(sensor_id) OVER(ORDER BY sensor_id, ts)

Как-то так. Может, можно и поаккуратнее. Два последних не тестировал.

Здравствуйте, avpavlov, Вы писали:

A>У меня вообще складывается ощущение, что пора уже пример запроса на джойн текущий-предыдущий в какое-нибудь ЧаВо вынести :)

Задавать-то вопрос задают, а вот как на него отвечать? В реляционной алгебре вообще нет понятия «предыдущий».