Update row with average from another table based on the original row

Not sure how to describe what I'm trying to get from this question, but here goes...

I have a table of customer purchases, 't1', with information about the purchase: customer id, date, boolean if the customer was alone, and the purchase amount. In a second table t2 is another list of the same customer ids with a date and a boolean saying whether they were alone.

I want to update the second table with the AVERAGE of values of the previous x purchases they did before that date with whether they were alone.

I setup the tables with:

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (cid INT, d DATE, i INT, v FLOAT);
INSERT INTO t1 (cid, d,i,v) VALUES (1,'2001-01-01', 0, 10);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-02', 1, 20);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-03', 1, 30);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-04', 1, 40);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-05', 0, 50);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-06', 0, 60);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-07', 0, 70);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-08', 1, 80);
INSERT INTO t1 (cid, d,i,v) VALUES  (1,'2001-01-09', 0, 90);
INSERT INTO t1 (cid, d,i,v) VALUES  (2,'2001-01-04', 1, 35);
CREATE TABLE t2 (cid INT, d DATE, i INT, av2 FLOAT, av3 FLOAT);
INSERT INTO t2 (cid, d,i) VALUES  (1,'2001-01-07', 0);
INSERT INTO t2 (cid, d,i) VALUES  (1,'2001-01-08', 1);
INSERT INTO t2 (cid, d,i) VALUES  (2,'2001-01-08', 0);
INSERT INTO t2 (cid, d,i) VALUES  (2,'2001-01-09', 1);

av2 and av3 are the columns where i want the average of the last 2 or 3 transactions. So i need an update statement (two statements really, one for av2 and one for av3) to say "when this customer came in on this date, and they came in alone or not, what was the average of their last x purchases.

So the resulting data should be:

cid d i av2 av3

1 2001-01-07 0 55 40

1 2001-01-08 1 35 40

2 2001-01-08 0 null null

2 2001-01-08 1 35 35

The closest I got was this:

UPDATE t2 SET av=(
SELECT AVG(tcol)
FROM (
SELECT v AS tcol FROM t1 LIMIT 2
) AS tt);

which seems to be going in the right direction (the limit 2 is the 2 or 3 from the av columns. But that just averages x prior purchases (regardless of customer or the boolean). As soon as I put in the WHERE clause to link the data, it chokes:

UPDATE t2 SE开发者_JS百科T av=(
SELECT AVG(tcol)
FROM (
SELECT v AS tcol FROM t1 WHERE t1.d<t2.d and t1.i=t2.i LIMIT 2
) AS tt);

Any ideas? Is there a name for what I'm trying to do? Do I need to describe this differently? Any suggestions?

Thanks, Philip

Try this one -

SET @r = 0;
SET @cid = NULL;
SET @i = NULL;

UPDATE t2 JOIN (
  SELECT t.cid, t.d, t.i, AVG(IF(t.r < 3, t.v, NULL)) av2, AVG(IF(t.r < 4, t.v, NULL)) av3 FROM (
    SELECT t.*, IF(@cid = t.cid AND @i = t.i, @r := @r + 1, @r := 1) AS r, @cid := t.cid, @i := t.i FROM (
      SELECT t2.*, t1.v FROM t2
        JOIN t1
          ON t1.cid = t2.cid AND t1.d < t2.d AND t1.i = t2.i
      ORDER BY t2.cid, t2.i, t1.d DESC
      ) t
    ) t
  GROUP BY t.cid, t.i, t.d
  ) t
  ON t2.cid = t.cid AND t2.i = t.i AND t2.d = t.d
SET t2.av2 = t.av2, t2.av3 = t.av3;

SELECT * FROM t2;
+------+------------+------+------+------+
| cid  | d          | i    | av2  | av3  |
+------+------------+------+------+------+
|    1 | 2001-01-07 |    0 |   55 |   40 |
|    1 | 2001-01-08 |    1 |   35 |   30 |
|    2 | 2001-01-08 |    0 | NULL | NULL |
|    2 | 2001-01-09 |    1 |   35 |   35 |
+------+------------+------+------+------+

Note: The value av3 for cid=1, d=2001-01-08, i=1 should be 30, rigth? ...not 40.