calculating average - advanced

[azraelGG]

i need calculate average of some data ill use simple example table so if you can help me. i will use only 2 users and 2 products for example 

 

it must be ordered by datetime(time in this table) and then get last 2 prices per user_ID

calculate average

 

table name: prices

user_ID     post_ID    price       time

 

1                   1             10       13:00      - pick this row

1                   1             11       12:00

1                   1             12       14:00       - pick this row

3                   1             10       13:00      - pick this row

3                   1             11       12:00

3                   1             12       14:00       - pick this row

1                   2             15       13:00      - pick this row

1                   2             16       12:00

1                   2             17       14:00       - pick this row

3                   2             18       13:00      - pick this row

3                   2             19       12:00

3                   2             12       14:00       - pick this row

 

 /* if there are more users get their last 2 entries  */

 

or just how to query these "- pick this row" rest is not that hard

 

 

final result

post_ID     total_avg_price

    1                    11

    2                   15.5

Edited March 11, 2013 by azraelGG

[Barand]

this should do it

 

include("db_inc.php");
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$db->query("DROP TABLE prices");
$sql = "CREATE TABLE prices (
user_ID INT,
post_ID INT,
price DECIMAL(10,2),
time TIME,
PRIMARY KEY (user_ID, post_ID, time)
)";
$db->query($sql);

$sql = "INSERT INTO prices VALUES
(1  , 1   ,  10.00  , '13:00:00'),
(1  , 1   ,  11.00  , '12:00:00'),
(1  , 1   ,  12.00  , '14:00:00'),
(3  , 1   ,  10.00  , '13:00:00'),
(3  , 1   ,  11.00  , '12:00:00'),
(3  , 1   ,  12.00  , '14:00:00'),
(1  , 2   ,  15.00  , '13:00:00'),
(1  , 2   ,  16.00  , '12:00:00'),
(1  , 2   ,  17.00  , '14:00:00'),
(3  , 2   ,  18.00  , '13:00:00'),
(3  , 2   ,  19.00  , '12:00:00'),
(3  , 2   ,  12.00  , '14:00:00')";
$db->query($sql);

$sql = "SELECT post_ID, AVG(price)
FROM
    (
    SELECT price, time, 
    @row := IF(@prevu <> user_ID OR @prevp <> post_ID, 1, @row+1) as row,
    @prevu := user_ID as user_ID, 
    @prevp := post_ID as post_ID
    FROM prices
        JOIN (SELECT @row:=0, @prevu:=0, @prevp:=0) as init
    ORDER BY post_ID, user_ID, time DESC
    ) calcrow
WHERE row < 3
GROUP BY post_ID";

/** RESULTS ********
1 | 11.0000
2 | 15.5000
*********************/