假设有一个类似于的表
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | date | NO | MUL | NULL | |
| system_value | decimal(16,2) | NO | | NULL | |
| account_id | int(11) | NO | MUL | NULL | |
| growth | tinyint(1) | NO | PRI | 0 | |
+----------------+---------------+------+-----+---------+----------------+
一些数据如下所示。
+----+------------+--------------+------------+--------+
| id | date | system_value | account_id | growth |
+----+------------+--------------+------------+--------+
| 1 | 2020-05-28 | 18.34 | 83266 | 0 |
| 2 | 2020-05-27 | 30.63 | 83266 | 0 |
| 3 | 2020-05-26 | 26.01 | 83266 | 0 |
| 4 | 2020-05-23 | 55.87 | 83266 | 0 |
| 5 | 2020-05-20 | 48.54 | 83266 | 0 |
| 6 | 2020-05-19 | 48.54 | 83266 | 0 |
| 7 | 2020-05-21 | 58.52 | 82155 | 0 |
| 8 | 2020-05-20 | 43.20 | 82155 | 0 |
| 9 | 2020-05-19 | 64.44 | 82155 | 0 |
| 10 | 2020-05-18 | 9.67 | 82155 | 0 |
+----+------------+--------------+------------+--------+
...
值CCD_ 1被假定为由相邻时间的CCD_
for example
account_id 83266
1 the growth of date 2020-05-28 should be 0 as system_value of 2020-05-28 (18.34) is less than
2020-05-27 (30.63)
2 the growth of date 2020-05-27 should be 1 as system_value of 2020-05-27 (30.63) is more than
2020-05-26 (26.01)
3 the growth of date 2020-05-23 should be 1 as there's no value for 2020-05-22 (should be treated as 0)
4
我的问题是如何对表的值growth
进行初始更新?
我正在使用mysql5.7+
感谢
更新
growth
是一个与account_id
和date
相关的值,它只是当天账户与最后一天相比的一个属性
您可以在此处使用LAG()
分析函数:
SELECT *, CASE WHEN date = LAG(date) OVER (PARTITION BY account_id ORDER BY date)
+ INTERVAL 1 DAY AND
system_value > LAG(system_value) OVER (PARTITION BY account_id
ORDER BY date)
THEN 1 ELSE 0 END AS growth
FROM yourTable
ORDER BY account_id, date DESC;
演示
如果您使用的是不支持LAG()
的MySQL早期版本,那么我们可以使用标量相关子查询:
SELECT *, CASE WHEN date = (SELECT t2.date FROM yourTable t2
WHERE t2.account_id = t1.account_id AND
t2.date < t1.date
ORDER BY date DESC LIMIT 1) + INTERVAL 1 DAY AND
system_value > (SELECT t2.system_value FROM yourTable t2
WHERE t2.account_id = t1.account_id AND
t2.date < t1.date
ORDER BY date DESC LIMIT 1)
THEN 1 ELSE 0 END AS growth
FROM yourTable t1
ORDER BY account_id, date DESC;
演示
这里的想法是,如果给定的帐户记录之前有一个早于一天的记录,并且growth
1每天都在增加,则分配growth
值1。否则,我们指定一个growth
为0。
编辑:
如果你真的想更新你的表,那么使用上面的查询和更新联接:
UPDATE yourTable t1
INNER JOIN
(
SELECT *, CASE WHEN date = LAG(date) OVER (PARTITION BY account_id ORDER BY date)
+ INTERVAL 1 DAY AND
system_value > LAG(system_value) OVER (PARTITION BY account_id
ORDER BY date)
THEN 1 ELSE 0 END AS growth
FROM yourTable
) t2
ON t2.id = t1.id
SET
t1.growth = t2.growth;
这看起来像是一个简单的自左联接的例子:
update mysterytablename m
left join mysterytablename m2 on m2.account_id=m.account_id and m2.date=m.date - interval 1 day
set m.growth = m.system_value > coalesce(m2.system_value,0)
虽然我不清楚你想要CCD_ 13还是CCD_。
小提琴