Geek asks and answers

Update row with average value from another table based on source row

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

I have a customer purchase table "t1" with purchase information: customer ID, date, boolean, if there was only one customer, and the purchase amount. The second table t2has another list of customer identifiers with a date and a logical expression of whether they were alone.

I want to update the second table with the ENVIRONMENT of the values ​​of previous purchases x that they made before this date, regardless of whether they were alone.

I am setting up 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 av3 - , 2 3. ( , av2 av3), " , , x.

, :

cid d 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

:

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

, -, ( 2 - 2 3 av, x ( ). WHERE, , :

UPDATE t2 SET 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);

? , ? -? ?

,

+3
1

-

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 |
+------+------------+------+------+------+

: av3 cid = 1, d = 2001-01-08, = 1 30, rigth?... 40.

+1

More articles:


All Articles