Say there's a table like:
---------------- --------------- ------ ----- --------- ----------------
| 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 | |
---------------- --------------- ------ ----- --------- ----------------
And some data are as below showed.
---- ------------ -------------- ------------ --------
| 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 |
---- ------------ -------------- ------------ --------
...
The value growth is supposed to be value that calculated by the system_value of adjacent time
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
My question is how can I do a Initial update for value growth for my table?
I'm using mysql5.7
Thanks
Update
The growth is a value which is related with account_id and date, It's just an attribute for account at the date comparing with last day
CodePudding user response：
You may use the LAG() analytic function here:
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;
Demo
If you're using an earlier version of MySQL which doesn't support LAG(), then we can use scalar correlated subqueries instead:
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;
Demo
The idea here is to assign a growth value of 1 if a given account record has both a record preceding it which is one day earlier and the system_value has increased day-over-day. Otherwise, we assign a growth of 0.
Edit:
If you really want to update your table, then use the above query with an update join:
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;
CodePudding user response：
This looks like a case for a simple self left-join:
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)
though it isn't clear to me if you want > or >=.