7

Вопрос по sql, sqlite – undefined

Я хотел бы создать представление в SQLite, где поле в одной строке зависит от значения поля в предыдущей строке. Я мог бы сделать это в Oracle, используяLAG аналитическая функция, но не уверен, как это сделать в SQLite.

Например, если моя таблица выглядела так:

ITEM        DAY           PRICE
apple       2011-01-07    1.25
orange      2011-01-02    1.00
apple       2011-01-01    1.00
orange      2011-01-03    2.00
apple       2011-01-08    1.00
apple       2011-01-10    1.50

Мне бы хотелось, чтобы мой взгляд выглядел какWHERE item = 'apple':

DAY           PRICE    CHANGE
2011-01-01    1.00     (null)
2011-01-07    1.25     0.25
2011-01-08    2.00     0.75
2011-01-10    1.50     -0.50

Редактировать:

Эквивалент запроса, который я ищу, будет выглядеть в Oracle примерно так (еще не пробовал, но я думаю, что это правильно):

SELECT day, price, 
       price - LAG( price, 1 ) OVER ( ORDER BY day ) AS change
  FROM mytable
 WHERE item = 'apple'
  • Error: User Rate Limit Exceeded

    от eaolson
  • Нет, я расширил пример. "Предыдущий" Я в основном имею в виду строку строки перед той, о которой идет речь, после того, как они были упорядочены.

    от eaolson
  • Определить "заказано". По сути, проблема заключается в том, что базы данных SQL (по определению) не имеют понятия порядка строк кроме того, что вы указываете конкретно, по имени столбца, при извлечении. Как правило, вы можете получить желаемый результат, но вам придется получить & quot; предыдущую строку & quot; через реляционную алгебру в вашем определении представления, и для этого вам нужно очень четко понимать, как вы определяете «предыдущий» простым языком.

    от Larry Lustig
  • Я обновил свой вопрос, чтобы дать вам лучшее представление о том, что я ищу. Я мог бы сделать это с аналитической функцией Oracle, но не уверен, как это сделать в sqlite.

    от eaolson
  • Определить «предыдущий». Похоже, единственная разрешенная запись для даты за один день до текущей записи. Это всегда правда?

    от Larry Lustig
  • 2

    Это должно сделать свое дело для каждого

    item (проверено на SQLite):

    SELECT 
        day
        ,price
        ,price - (SELECT t2.price 
                  FROM mytable t2 
                  WHERE 
                      t2.item = t1.item AND 
                      t2.day < t1.day      
                  ORDER BY t2.day DESC
                  LIMIT 1
                 ) AS change
    FROM mytable t1
    

    Это предполагает сочетание междуday а такжеitem уникален И как это работает, взяв все значенияless than данноеdayсортировкаdescending а потомLIMIT только первое значение, имитирующееLAG функция.

    ДляLEAD поведение, просто переверните< в> а такжеDESC вASC.

  • 2

    Идея та же

    что и у других, но она использует поля вместо rowid. Это именно то, что вы хотите:

    
    CREATE TABLE Prices (
        day DATE,
        price FLOAT
    );
    
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '+1 day'), 0.5);
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '+0 day'), 1);
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-1 day'), 2);
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-2 day'), 7);
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-3 day'), 8);
    INSERT INTO Prices(day, price) VALUES(date('now', 'localtime', '-4 day'), 10);
    
    SELECT p1.day, p1.price, p1.price-p2.price 
    FROM
        Prices p1, Prices p2,
        (SELECT t2.day AS day1, MAX(t1.day) AS day2 
        FROM Prices t1, Prices t2
        WHERE t1.day < t2.day
        GROUP BY t2.day) AS prev
    WHERE p1.day=prev.day1
        AND p2.day=prev.day2
    
    

    Если вы хотите добавитьWHERE item='apple' Я хотел бы добавить это к обоимWHERE статьи.

  • 0

    Предполагая, что вы не удаляете это будет работать:

    
    SELECT t2.DAY, t2.price, t2.price-t1.price 
    FROM TABLENAME t1, TABLENAME t2 
    WHERE t1.rowid=t2.rowid-1
    

    Это работает, потому что каждая строка имеет свой собственный идентификатор строки, даже если вы не указали его вCREATE заявление.

    Если вы удалите, это станет:

    
    SELECT t2.day, t2.price, t2.price-t1.price 
    FROM 
         (SELECT l1.day, l1.price, 
              (SELECT COUNT(*) 
              FROM TABLENAME l2 
              WHERE l2.rowid < l1.rowid) AS count
          FROM TABLENAME l1) AS t1,
         (SELECT l1.day, l1.price, 
              (SELECT COUNT(*) 
              FROM TABLENAME l2 
              WHERE l2.rowid < l1.rowid) AS count
          FROM TABLENAME l1) AS t2
    WHERE t1.count=t2.count-1
    

    Это работает в предположении, что rowids всегда увеличивается.

  • 1

    Oracle эквивалентен правильно. Начиная с

    SQLite 3.25.0 вы могли бы использоватьLAG изначально:

    WITH mytable(ITEM,DAY,PRICE) AS (
        VALUES
        ('apple',  CAST('20110107' AS DATE),    1.25),
        ('orange', CAST('20110102' AS DATE),    1.00),
        ('apple',  CAST('20110101' AS DATE),    1.00),
        ('orange', CAST('20110103' AS DATE),    2.00),
        ('apple',  CAST('20110108' AS DATE),    2.00),
        ('apple',  CAST('20110110' AS DATE),    1.50)
    )
    SELECT day, price, price-LAG(price) OVER (ORDER BY day) AS change
    FROM mytable
    WHERE item = 'apple'
    ORDER BY DAY;