1 Тема від fed_lviv Востаннє редагувалося fed_lviv (10.10.2015 22:32:43)

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Тема: SQLite віртуальня таблиця (споживання електроенергії по місяцях)

Є таблиця (electric), де записуються показники лічильника електроенергії. Таблиця має такі поля:
-idElectric INTEGER   PRIMARY KEY (звичайна id)
-amount INTEGER   NOT NULL (показник лічильника)
-transmit INTEGER   NOT NULL   DEFAULT 0 (засвідчує, що даний показник був переданий в обленерго)
-finally INTEGER   NOT NULL   DEFAULT 0 (засвідчує, що даний показник був нарахований обленерго)
-change TEXT   NOT NULL   DEFAULT DATETIME('now', 'localtime') (дата запису)

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

CREATE VIEW view1 AS
       SELECT (
              SELECT COUNT(*) 
              FROM electric tab2 
              WHERE tab2.finally AND tab1.idElectric >= tab2.idElectric
              ) AS id,
              tab1.change,
              tab1.amount     
       FROM 
            electric tab1
       WHERE 
             tab1.finally;   

CREATE VIEW view2 AS
       SELECT 
              STRFTIME('%m %Y', tab1.change) AS 'month', 
              tab1.amount ||'-'|| tab2.amount AS 'range', 
              tab2.amount - tab1.amount AS 'value'
       FROM 
            view1 AS tab1, 
            view1 AS tab2
       WHERE 
             tab1.id + 1 = tab2.id
       ORDER BY 
             tab1.change; 
             
SELECT * FROM view2;

http://i11.pixs.ru/storage/5/0/8/1png_4070931_19094508.png

2 Відповідь від fed_lviv Востаннє редагувалося fed_lviv (12.10.2015 11:27:31)

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: SQLite віртуальня таблиця (споживання електроенергії по місяцях)

Думав-думав, та й надумав. Виглядає досить громоздко, але на жаль нічого кращого не придумав:

CREATE VIEW IF NOT EXISTS electricConsumed AS
    SELECT 
        STRFTIME('%Y-%m-01', table1.change) AS 'date', 
        table1.amount AS 'start', 
        table2.amount AS 'stop'
    FROM 
        (SELECT 
            (SELECT 
                COUNT(*) 
            FROM 
                electric table2 
            WHERE 
                table2.finally 
                AND table1.idElectric >= table2.idElectric
            ) AS id,
            table1.change,
            table1.amount     
        FROM 
            electric table1
        WHERE 
            table1.finally
        ) AS table1,
        (SELECT 
            (SELECT 
                COUNT(*) 
            FROM 
                electric table2 
            WHERE 
                table2.finally 
                AND table1.idElectric >= table2.idElectric
            ) AS id,
            table1.change,
            table1.amount     
        FROM 
            electric table1
        WHERE 
            table1.finally
        ) AS table2
    WHERE 
        table1.id + 1 = table2.id
    ORDER BY 
        table1.change DESC;

А ось запит на вибірку:

SELECT 
       date,
       start||'-'||stop AS 'range',     
       stop - start AS 'value'       
FROM 
     electricConsumed;

http://i11.pixs.ru/storage/5/1/1/1png_7417774_19110511.png

3 Відповідь від fed_lviv

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: SQLite віртуальня таблиця (споживання електроенергії по місяцях)

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

4 Відповідь від fed_lviv

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: SQLite віртуальня таблиця (споживання електроенергії по місяцях)

Ох і монстри :)
INSERT

CREATE TRIGGER IF NOT EXISTS inOneTrue BEFORE INSERT ON electric
BEGIN
    SELECT 
        CASE 
            WHEN  
                NEW.finally 
                AND EXISTS(
                    SELECT 
                        *
                    FROM 
                        electric 
                    WHERE 
                        STRFTIME('%Y-%m', change) = STRFTIME('%Y-%m', NEW.change) 
                        AND finally 
                    )           
            THEN       
                RAISE(ABORT, 'Не можна внести даний запис в таблицю.')
        END;
END;

UPDATE

CREATE TRIGGER IF NOT EXISTS upOneTrue BEFORE UPDATE ON electric
BEGIN
    UPDATE 
        electric 
    SET 
        idElectric = NEW.idElectric, 
        amount = NEW.amount, 
        transmit = NEW.transmit,
        finally = NEW.finally,
        change = NEW.change
    WHERE
        idElectric = OLD.idElectric; 
     SELECT 
        CASE 
            WHEN  
                EXISTS(
                    SELECT
                        COUNT(*) AS 'val'      
                    FROM
                        electric
                    WHERE
                        finally        
                    GROUP BY
                        STRFTIME('%Y-%m', change)
                    HAVING 
                        val>1
                    )              
            THEN       
                RAISE(ROLLBACK, 'Не можна змінити даний запис.')
        END;
END;