How to determine whether to increase according to the value of adjacent time?

user_10040873 0
Viewed 36

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

mysql
2 Answers

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;
user_18632290

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 >=.

fiddle

ysth_173890
Related