1 Востаннє редагувалося fed_lviv (11.08.2015 15:02:49)

Тема: MySQL різниця значень за добу

Є таблиця table1, в певний період часу в таблицю додаються записи (наприклад через кожні 30 хв.), в таблиці багато різник показників лічильників (електроенергія, газ, тепло,..)

Приклад:
timed                          val1        val2      val3
2015-08-10 00:01:08   123,12   145,00   129,89   ... 
...
2015-08-10 15:33:01   124,12   146,00   130,89   ...
...
2015-08-10 23:55:48   125,12   147,00   143,89   ...

Потрібно, показати споживання по кожному лічильнику за конкретну добу, роблю запит:

SELECT 
    MAX(table1.val1) - MIN(table1.val1),
    MAX(table1.val2) - MIN(table1.val2),
    MAX(table1.val3) - MIN(table1.val3),
...
FROM 
    table1
WHERE
     timed LIKE '2015-08-10%'

Але мене лякає в цьому запиті максимальне та мінімальне значення, якщо при передачі даних в БД буде збій, і передастся наприклад в якийсь період 0, то це і буде мінімальне значення. Тому і цікаво, як зробити запит, щоб дійсно віднімалися перше значення лічильника за дану добу, останнє значення лічильника за дану добу. Ось наприклад запит, який витягує з БД відсортовані записи, за конкретну дату. Тільки як зформувати, віднімання всіх значень останнього запису від першого запису?

SELECT 
    *
FROM 
    table1
WHERE
     timed LIKE '2015-08-10%'
ORDER BY
             timed

2

Re: MySQL різниця значень за добу

Можливо вкладені запити?
Типу ORDER by timed DESC LIMIT 1 i ORDER by timed ASC LIMIT 1

=)

3

Re: MySQL різниця значень за добу

Про таке задумувався, але цікаво чи це оптимальний варіант, чи є, щось краще.

4

Re: MySQL різниця значень за добу

Якщо десь не може бути нуля, то просто додайте цю умову в WHERE.

5 Востаннє редагувалося Анатолій (11.08.2015 18:36:29)

Re: MySQL різниця значень за добу

fed_lviv написав:

....
Але мене лякає в цьому запиті максимальне та мінімальне значення, якщо при передачі даних в БД буде збій, і передастся наприклад в якийсь період 0, то це і буде мінімальне значення.
.....

Якщо вже говорити про збій передачі даних, то чому власне має бути "0" в таблиці, а не, наприклад, NULL?
Та й питання, а що вже були випадки збою передачі даних?

Збираю знання і... роздаю знання :)

6 Востаннє редагувалося fed_lviv (11.08.2015 18:43:44)

Re: MySQL різниця значень за добу

Torbins написав:

Якщо десь не може бути нуля, то просто додайте цю умову в WHERE.

В тому то і проблема, я не знаю, яке значення заноситься в БД, якщо збій при передачі даних. Там може буте пару варіантів, це ті, що я вже бачив, може записатися значення = 0, може навпаки дуже велике значення = 99999999,99. Ще хібащо NULL не бачив. Тому не знаю, яку умову в WHERE прописувати. Наразі, зупинився, на варіанті VTrimа.

Анатолій написав:

Якщо вже говорити про збій передачі даних, то чому власне має бути "0" в таблиці, а не, наприклад, NULL?
Та й питання, а що вже були випадки збою передачі даних?

Не я це проектував та робив та й в приципі не має в кого запитати, як та передача йде і що відбувається, якщо....
Випадки збою були. Але там теж досить багато варіантів, не правильно підключили дротяри до лічильника, не має напруги і так далі, є взагалі цікаві варіанти: все начебто гуд, але показники в БД йдуть "дивні", а саме головне, що ніхто нічого не може пояснити.

7

Re: MySQL різниця значень за добу

fed_lviv написав:

В тому то і проблема, я не знаю, яке значення заноситься в БД, якщо збій при передачі даних. Там може буте пару варіантів, це ті, що я вже бачив, може записатися значення = 0, може навпаки дуже велике значення = 99999999,99. Ще хібащо NULL не бачив. Тому не знаю, яку умову в WHERE прописувати. Наразі, зупинився, на варіанті VTrimа.

може такий варіант,
якщо по кожному значенню лічильника ти знаєш робочі границі значень val1,val2, val3, то може виключити помилкові/неіснуючі значення (такі як 0, 99999, NULL ?) застосувавши вибірку даних лише в цьому робочому діапазоні,
(вибірка BETWEEN) 

SELECT 
    MAX(table1.val1) - MIN(table1.val1),
    MAX(table1.val2) - MIN(table1.val2),
    MAX(table1.val3) - MIN(table1.val3),
...
FROM 
    table1
WHERE timed LIKE '2015-08-10%'
AND (table1.val1 BETWEEN 100 AND 20000);
Збираю знання і... роздаю знання :)

8

Re: MySQL різниця значень за добу

Тільки вкладеними.

9 Востаннє редагувалося fed_lviv (12.08.2015 08:49:42)

Re: MySQL різниця значень за добу

Vo_Vik правий, дійсно варіант:

SELECT 
    MAX(table1.val1) - MIN(table1.val1),
    MAX(table1.val2) - MIN(table1.val2),
    MAX(table1.val3) - MIN(table1.val3),
...
FROM 
    table1
WHERE timed LIKE '2015-08-10%'
AND (table1.val1 BETWEEN 100 AND 20000);

Нажаль, не проходить, тому залишаюся з "першим" варіантом (в запиті використовуються реальні назви таблиці та полів, прошу не дивуватися):

-- Споживання електроенергії за добу (об'єкт та дата задаються двічі)
SELECT 
    timeStart.IWA - timeStop.IWA AS 'I - активна',
    timeStart.IWR - timeStop.IWR AS 'I - реактивна',
    timeStart.IWRZ - timeStop.IWRZ AS 'I - ген. реактивна',
    timeStart.IIWA - timeStop.IIWA AS 'II - активна',
    timeStart.IIWR - timeStop.IIWR AS 'II - реактивна',
    timeStart.IIWRZ - timeStop.IIWRZ AS 'II - ген. реактивна'
FROM
    (SELECT
        data_tel_new.tit38 AS IWA,
        data_tel_new.tit39 AS IWR,
        data_tel_new.ti40 AS IWRZ,
        data_tel_new.tit32 AS IIWA,
        data_tel_new.tit33 AS IIWR,
        data_tel_new.tit34 AS IIWRZ
    FROM 
        data_tel_new 
    WHERE
        id_ob ='tp30'
        AND timed LIKE '2015-08-11%'
    ORDER BY
        timed DESC
    LIMIT 
        1) AS timeStart,
    (SELECT
        data_tel_new.tit38 AS IWA,
        data_tel_new.tit39 AS IWR,
        data_tel_new.ti40 AS IWRZ,
        data_tel_new.tit32 AS IIWA,
        data_tel_new.tit33 AS IIWR,
        data_tel_new.tit34 AS IIWRZ
    FROM 
        data_tel_new 
    WHERE
        id_ob ='tp30'
        AND timed LIKE '2015-08-11%'
    ORDER BY
        timed ASC
    LIMIT 
        1) AS timeStop;

Всім дякую за допомогу!
P.S. Даним запитом буду вираховувати також споживання за місяць зі зміною в умові WHERE ... timed LIKE '2015-08%'

10 Востаннє редагувалося Vo_Vik (12.08.2015 16:16:14)

Re: MySQL різниця значень за добу

Тут мені прийшло в голову, якщо у вас впринципі можливі збої тільки тоді коли поступить некоректна інформація, і ви впевнені, що вона буде рівна 0, то можна просто ввашому оригінальному запиті добавити

 AND table1.val1 <>0 AND table1.val2 <> 0 AND .....

А по останньому запиті, а якщо перший чи останній запис буде мати інфу зі збоєм?

11

Re: MySQL різниця значень за добу

Про "неправдиві варіанти", які записуються в БД, я писав, вище. В тому то і проблема, що варіанти можуть бути різні.

А по останньому запиті, а якщо перший чи останній запис буде мати інфу зі збоєм?

Про це я вже думав. І все, що прийшло у голову: "Хто робив надсилання інфи в БД без перевірки, той хай і мастить голову."Я звичайно ніколи таким не займався, але чомусь здається, що перед посиланням в БД інформація, яка надходить з лічильника має перевірятися, якщо проблема то тримайте NULL.

12

Re: MySQL різниця значень за добу

fed_lviv написав:

Vo_Vik правий, дійсно варіант:

SELECT 
    MAX(table1.val1) - MIN(table1.val1),
    MAX(table1.val2) - MIN(table1.val2),
    MAX(table1.val3) - MIN(table1.val3),
...
FROM 
    table1
WHERE timed LIKE '2015-08-10%'
AND (table1.val1 BETWEEN 100 AND 20000);

Нажаль, не проходить,

Пане fed_lviv, тестував і не проходить тому що синтаксис не правильний чи в чомусь інша проблема, можна детальніше якщо звісно не тяжко, мені цікаво в чому я був не правий.

І ще, для розмови, якщо брати ситуацію, що я спостерігаю час від часу в себе на роботі в частині Програмно Технічного Комплексу то ні ні, але бувають збої в передачі даних, "підвіс" контроллер чи діагностичний блочок, і просто не передає пакети даних, як результат обладнання, що приймає дані висвітлює "недостовірні" дані, але ніяк не значення "0".  Якщо ж розглядати ситуацію пана fed_lviv, то у випадку збою мені так і кортить сказати що запис в таблицю буде NULL.
І якщо все ж йти в напрямку зручності обробки даних, то цей момент також варто вказувати, що за добу вироблено стількито кКалорій енергії та зафіксовано N збоїв обладнання, де "збої" це саме й були б некоректні дані, поза межами прогнозованих показників.

Знову ж якщо брати приклад з моєї роботи то є модель обєкту і якщо реальні показники відхиляються від показників моделі, висвітлюється попереджувальне повідомлення про некоректну роботу обладнанння.

Що ж до Вашого проекту пане fed_lviv, можливо теж варто застосувати певні перевірки коректності даних?. Правда Ви й так їх виконуєте, але питання перевірка в якому обємі і чи доцільно.

Якщо це просто лабораторну роботу здати тоді питань нема, але якщо це необхідний життєвий проект, то задачка постає справді дуже цікава )

Збираю знання і... роздаю знання :)

13 Востаннє редагувалося fed_lviv (13.08.2015 10:27:45)

Re: MySQL різниця значень за добу

Анатолій написав:

Пане fed_lviv, тестував і не проходить тому що синтаксис не правильний чи в чомусь інша проблема, можна детальніше якщо звісно не тяжко, мені цікаво в чому я був не правий.

З синтаксисом все ОК, результат невірний, якщо я правильно розумію умови в WHERE table1.val1 BETWEEN 100 AND 20000, table1.val2 BETWEEN 100 AND 20000, ... відкидають потрібні рядки, наприклад рядок:
  val1           val2
123,67    99999999,99
І ось даний рядок відкидається, бо val2 значення "паскудне", але на жаль відкидається "нормальне" значення val1.

Анатолій написав:

І ще, для розмови, якщо брати ситуацію, що я спостерігаю час від часу в себе на роботі в частині Програмно Технічного Комплексу то ні ні, але бувають збої в передачі даних, "підвіс" контроллер чи діагностичний блочок, і просто не передає пакети даних, як результат обладнання, що приймає дані висвітлює "недостовірні" дані, але ніяк не значення "0".  Якщо ж розглядати ситуацію пана fed_lviv, то у випадку збою мені так і кортить сказати що запис в таблицю буде NULL.
І якщо все ж йти в напрямку зручності обробки даних, то цей момент також варто вказувати, що за добу вироблено стількито кКалорій енергії та зафіксовано N збоїв обладнання, де "збої" це саме й були б некоректні дані, поза межами прогнозованих показників.

Знову ж якщо брати приклад з моєї роботи то є модель обєкту і якщо реальні показники відхиляються від показників моделі, висвітлюється попереджувальне повідомлення про некоректну роботу обладнанння.

Що ж до Вашого проекту пане fed_lviv, можливо теж варто застосувати певні перевірки коректності даних?. Правда Ви й так їх виконуєте, але питання перевірка в якому обємі і чи доцільно.

Якщо це просто лабораторну роботу здати тоді питань нема, але якщо це необхідний життєвий проект, то задачка постає справді дуже цікава )

Що я тут можу сказати, вчора зіткунвся з черговим варіантом "неправильних" даних, тобто маю вже три варіанти. Припустимо реальний показник лічильника: 12345678,99.
Варіанти:
1. 99999999,99
2. 0
і новий варіант
3. 2471534,88 (тобто не максимальне і не мінімальне число), це взагалі повна ж...
А варіанту NULL, не має і хоч плач.
Тобто, таке враження, що тут жоден запит не допоможе. Оскільки, щоб побачити чи дійсно правдиве значення, потрібно подивитися всі дані за добу і побачити чи не було різкого відхилення.
Я вже казав, як на мою, думку то дані, які передаються в БД, мали б перед цим перевірятися. Чому вони не перевіряються я не знаю! Та й в принципі, як їх перевіряти я теж не знаю, оскільки я таким ніколи не займався. Знаю що для передачі даних з лічильника викоистовуюють інтерфейс RS 485 (гуглив, начебто перевірку можна робити).
Нарахунок проекту. Скажімо так, за освітою та й за посадою по бумажках, я для цієї роботи не гожусь. Але, мені цікаво + керівництво тільки ЗА, якщо кожна людина на роботі в будь-який момент буде мати можливість побачити показники лічильників, зробити звіт показників за добу, місяць. А проблема в тому, що даний проект дуже "сильно розбитий" по службах. За підключення лічильників до диспетчерезації відповідає одна служба, за проектування БД інша, за проектування сайта (десктопа) - по ідеї взагалі інша організація. І якщо виникає проблема, ніхто нічого не знає і фіг кого можна знайти.
P.S. Щойно дізнався думку людей, які безпосередньо беруть участь у даному проекті: "В БД вносяться любі значення, а далі з БД їх має перевіряти на достовірність програма вищого рівня" :(
P.S. Я думаю Ваш запит підійшов би, якби були зпроектовані таблиці для різних лічильників (таблиця1 - електролічильники 1 вводу; таблиця2 - електролічильники 2 вводу; таблиця3 - газові лічильники,...). А в результаті все запхано в одну таблицю, де майже 60 стовбців.

14

Re: MySQL різниця значень за добу

fed_lviv написав:

.....
А проблема в тому, що даний проект дуже "сильно розбитий" по службах. За підключення лічильників до диспетчерезації відповідає одна служба, за проектування БД інша, за проектування сайта (десктопа) - по ідеї взагалі інша організація. І якщо виникає проблема, ніхто нічого не знає і фіг кого можна знайти.
P.S. Щойно дізнався думку людей, які безпосередньо беруть участь у даному проекті: "В БД вносяться любі значення, а далі з БД їх має перевіряти на достовірність програма вищого рівня" :(
P.S. Я думаю Ваш запит підійшов би, якби були зпроектовані таблиці для різних лічильників (таблиця1 - електролічильники 1 вводу; таблиця2 - електролічильники 2 вводу; таблиця3 - газові лічильники,...). А в результаті все запхано в одну таблицю, де майже 60 стовбців.

згоден, якщо проект розбитий на декілька дільниць, то складно все узгодити, - але це залежить від керівництва, якщо необхідно реалізувати саме якісний, зручний і конкурентноспроможний варіант то варто над цим працювати. А буває таке керівництво, що каже вам потрібно ви й робіть. в більшості випадків ніхто не буде перейматись такими дрібязками, ну збойнули дані, та й ладно.

напевно ж якщо справді в одній таблиці аж цілих 60 колонок даних, то людина що проектувала таку таблицю, забула про нормалізацію даних, як на мене зручніше обробляти дані коли вони мають логічну сутність, а не запхали в таблицю все що є.

А так лиш варто вам поспівчувати, що необхідно наводити порядок.

Чи все ж є можливість реструктуризувати таблиці бази даних?

P.S. Щойно дізнався думку людей, які безпосередньо беруть участь у даному проекті: "В БД вносяться любі значення, а далі з БД їх має перевіряти на достовірність програма вищого рівня" :(

нелогічно як на мене, має бути навпаки,  спочатку перевірити коректність даних і потім занести їх в базу даних :)

загалом я так зрозумів "цікавий" розробник проекту реалізував збір даних.

Збираю знання і... роздаю знання :)

15

Re: MySQL різниця значень за добу

Анатолій написав:

А так лиш варто вам поспівчувати, що необхідно наводити порядок.
Чи все ж є можливість реструктуризувати таблиці бази даних?

Дякую, за співчуття, але не варто ;) Я вже писав вище, що "офіційно", я не входжу до когорти людей, які реалізують дану задачу. Я так, по бажанню, зроблю - молодець, не зроблю - нічого страшного. Доступ до пергляду БД отримав пару днів назад, редагувати, змінювати звісно не можу.

Анатолій написав:

нелогічно як на мене, має бути навпаки,  спочатку перевірити коректність даних і потім занести їх в базу даних :)

Якщо дивитися зі сторони, то мені теж так здається. Але я ніколи такого не робив, тому зі 100% впевненістю не можу сказати, що люди роблять щось не те.

16 Востаннє редагувалося fed_lviv (03.09.2015 10:50:45)

Re: MySQL різниця значень за добу

Вийшов з відпустки і з натхненням взявся за роботу :) Нажаль зміни в БД вносити не можу, але ж можу запропонувати внести зміни. І ось мене цікавить, а якщо використовувати триггер при додаванні нових данних, наприклад такий:

CREATE TRIGGER `changeValue` BEFORE INSERT ON `data_tel_new` FOR EACH ROW
BEGIN
    IF(
        (NEW.tit38 <= 0 || NEW.tit38 >= 99999999.99) ||
        (NEW.tit39 <= 0 || NEW.tit39 >= 99999999.99) ||
        (NEW.ti40 <= 0 || NEW.ti40 >= 99999999.99)
    ) THEN
        SET NEW.tit38 = NULL;
        SET NEW.tit39 = NULL;
        SET NEW.ti40 = NULL;
    END IF;
    IF(
        (NEW.tit32 <= 0 || NEW.tit32 >= 99999999.99) ||
        (NEW.tit33 <= 0 || NEW.tit33 >= 99999999.99) ||
        (NEW.tit34 <= 0 || NEW.tit34 >= 99999999.99)
    ) THEN
        SET NEW.tit32 = NULL;
        SET NEW.tit33 = NULL;
        SET NEW.tit34 = NULL;
    END IF;
END

Тільки, цікавить чи не буде сильно тригер приторможувати вставку даних???