0

I built word2vec dictionary as a mysql table. There is millions of rows like this:

ID      CREATED                 UPDATED                 KEYWORD     COUNT   NUMPYBLOB   NUMPYBLOB ... ...
121201  2018-03-08 04:01:23     2018-03-08 21:54:34     iulius         32   
121202  2018-03-08 04:01:23     0000-00-00 00:00:00     admonitivo      1   
121203  2018-03-08 04:01:23     0000-00-00 00:00:00     lulius          1   
121204  2018-03-08 04:01:23     2018-03-08 16:56:14     lugh           20   
121205  2018-03-08 04:01:23     2018-03-08 22:34:31     contingencias  66   
121206  2018-03-08 04:01:23     2018-03-08 19:50:34     liberándola    12   
121207  2018-03-08 04:01:23     0000-00-00 00:00:00     agostillo       1   
121208  2018-03-08 04:01:23     2018-03-08 22:47:39     autonomista   212   
121209  2018-03-08 04:01:23     2018-03-08 05:54:12     astrológicamente4
121210  2018-03-08 04:01:23     2018-03-08 18:54:43     redondearse     5

Now I want to sort this table by word occurrence counter (COUNT) and when the table is sorted rewrite autoincremental ID row from zero to X in order to word ocurrency goes down. I can't use COUNT column as primary key, because it is varies continuously and not unique. How I can do it?

  • I guess I found the solution here. I'll try it in the morning – Dmitry Mar 9 '18 at 4:21
0

ok, I found a great solution here by adding one more column to my table and label ordered items:

alter table `my_table` add `ocid` bigint after `counter`;
set @neword = 0;
update `my_table` set `ocid` = (@neword := @neword + 1)
order by `counter` desc;

and it's worked:

select id,keyword,counter,ocid from spanish order by `ocid` asc limit 0,15;
+-----+---------+---------+------+
| id  | keyword | counter | ocid |
+-----+---------+---------+------+
|  33 | de      | 6786409 |    1 |
|  43 | ,       | 5633255 |    2 |
|  45 | .       | 4504309 |    3 |
|  41 | la      | 3447327 |    4 |
|  97 | '       | 3014551 |    5 |
|  52 | en      | 2748250 |    6 |
| 120 | el      | 2490996 |    7 |
| 316 | y       | 2200112 |    8 |
|  10 | :       | 1578205 |    9 |
|  28 | que     | 1542144 |   10 |
|   2 | a       | 1494838 |   11 |
|  88 | (       | 1375893 |   12 |
| 220 | )       | 1373788 |   13 |
|   6 | los     | 1290613 |   14 |
|  30 | del     | 1226805 |   15 |
+-----+---------+---------+------+
15 rows in set (1.12 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.