0

I have a MySQL table everyday time a new value is inserted I need to count look at the last 100 previous records and count how many times the inserted value appears.

You can assume the structure of my table is:

num (int, primary, auto-increment) | value (varchar) | last_100_repetitions (int)

I need to update last_100_repetitions based on how many times value appears on the last 100 inserted records (where num indicates the order of the records).

  • will you ever use last_100_repetitions for older rows than the last one for each value? – Lennart May 14 '18 at 20:11
1

I find the construction rather error prune, what if a row is deleted, or value is updated, should last_100_repetitions also be updated then? That said, a trigger like:

delimiter //
create trigger trg 
before insert on t 
for each row 
    set new.last_100_repetitions = (select count(1) 
                                    from ( 
                                        select value 
                                        from t 
                                        order by num desc 
                                        limit 100 
                                    ) as x 
                                    where value = new.value ) 
//
delimiter ;

may work. If you are only interested in the last last_100_repetitions for each value you may concider moving this attribute to a separate table.

Test:

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
+----+-------+----------------------+
4 rows in set (0.01 sec)

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
| 16 | A     |                    2 |
| 17 | A     |                    3 |
| 18 | B     |                    1 |
| 19 | C     |                    1 |
+----+-------+----------------------+
8 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.