Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

    Unanswered: UPDATE using an OLAP specification

    Quote Originally Posted by bono56 View Post
    hi
    i have table keep track of user points like this:

    id--user_id--points--points_prev
    1--10------15-----0
    2--10------18-----15
    3--10------20-----18

    as you maybe guess `points_prev` is points user earned in previous step.
    i wonder if there is a approach to update `points_prev` field with update query or a sp?
    tanX
    When I saw this issue on MySQL forum,
    (Although LAG is not supported in MySQL)
    I thought a solution using UPDATE (fullselect /*using a LAG OLAP specification*/ ), like

    Test data: Add more samples.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE points
    ( id          INT NOT NULL
    , userid      INT NOT NULL
    , points      INT NOT NULL
    , prev_points INT
    , PRIMARY KEY (id , userid)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO points
    (id , userid , points)
    VALUES
      (  1 , 10 , 15 )
    , (  2 , 10 , 18 )
    , (  3 , 10 , 20 )
    , (  4 , 20 , 05 )
    , (  5 , 20 , 15 )
    , (  6 , 20 , 20 )
    , (  7 , 20 , 18 )
    , (  8 , 20 , 07 )
    , (  2 , 30 , 20 )
    , (  4 , 30 , 10 )
    , (  5 , 30 , 07 )
    , (  7 , 30 , 18 )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points;
    ------------------------------------------------------------------------------
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           -
              2          10          18           -
              3          10          20           -
              4          20           5           -
              5          20          15           -
              6          20          20           -
              7          20          18           -
              8          20           7           -
              2          30          20           -
              4          30          10           -
              5          30           7           -
              7          30          18           -
    
      12 record(s) selected.

    UPDATE statement and the result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE (
    SELECT p.*
         , LAG(points , 1 , 0)
              OVER( PARTITION BY userid
                        ORDER BY id
                  ) AS calculated_prev_points
     FROM  points p
    )  AS  target
       SET prev_points = calculated_prev_points
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points;
    ------------------------------------------------------------------------------
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           0
              2          10          18          15
              3          10          20          18
              4          20           5           0
              5          20          15           5
              6          20          20          15
              7          20          18          20
              8          20           7          18
              2          30          20           0
              4          30          10          20
              5          30           7          10
              7          30          18           7
    
      12 record(s) selected.

    Then, I thought if the LAG could be used directly and it seems work well.
    Code:
    ----------------------------------------------- Commands Entered ------------------------------
    UPDATE points
       SET prev_points = NULL;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------- Commands Entered ------------------------------
    UPDATE points
       SET prev_points
         = LAG(points , 1 , 0)
              OVER( PARTITION BY userid
                        ORDER BY id
                  )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points;
    ------------------------------------------------------------------------------
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           0
              2          10          18          15
              3          10          20          18
              4          20           5           0
              5          20          15           5
              6          20          20          15
              7          20          18          20
              8          20           7          18
              2          30          20           0
              4          30          10          20
              5          30           7          10
              7          30          18           7
    
      12 record(s) selected.
    Last edited by tonkuma; 06-05-12 at 18:23. Reason: Add "(Although LAG is not supported in MySQL)"

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    good, thx for sharing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •