根据原始行从另一个表的平均更新行
不知道如何描述我试图从这个问题得到什么,但这里去...根据原始行从另一个表的平均更新行
我有一张客户购买表' t1',包含关于购买的信息:客户ID,日期,如果客户是独自一人,则为布尔值,以及购买金额。在第二个表中t2
是具有日期和布尔型的相同客户ID的另一个列表,表明他们是否是单独的。
我想更新第二个表格,其中包含他们之前做过的x次购买的值的平均值,以及它们是否是单独的。
我的设置与表:
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二AV2 AV3
1 2001年1月7日0 55 40
1 2001年1月8日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是想通过AV列2
或3
。但是,这只是平均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);
任何想法?有什么我想要做的名字吗?我需要以不同的方式来描述吗?有什么建议么?
谢谢, 菲利普
尝试这一个 -
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 |
+------+------------+------+------+------+
注: 为CID = 1,d = 2001年1月8日的值AV3,I = 1应为30 ,究竟? ...不是40.
哇,这是一个相当的声明。你说的对,它应该是30,而不是40.我会试一试。有一个问题,如果我想添加另一个avg列,比如avg5,就足够了,只需在第一个选择像AVG(IF(t.r user548084 2011-06-06 20:24:56
是的,你可以这样做,因为't'.'r'是一个组中的行数。 – Devart 2011-06-07 10:08:09
如果'i'是一个整数值而不是布尔值,它会有所作为吗?比较仍然是'=',但我改变了查询upa它,它现在似乎在做所有值的平均值(不考虑't.r'约束)。试图找出我导致问题的变化... – user548084 2011-06-07 23:31:18
它被称为“移动平均值”或“滚动平均值”。两个很好的解决方案在这里: [http://*.com/questions/878473/how-do-i-calculate-a-moving-average-using-mysql](http://*.com/questions/878473/我怎么做,我计算移动平均使用MySQL) – jaeheung 2013-08-17 01:53:13