samedi 18 juin 2016

MySQL - Get values from previous rows

I am trying to reconstruct data that has a tree structure.

Example - Country / City:

1) USA
1.1) New York
1.2) Chicago 
2) France
2.1) Paris
2.2) Lyon 
3) China

In my database it looks like this:

|  Element | Level | Row |
|:--------:|:-----:|:---:|
|    USA   |   1   |  1  |
| New York |   2   |  2  |
|  Chicago |   2   |  3  |
|  France  |   1   |  4  |
|   Paris  |   2   |  5  |
|   Lyon   |   2   |  6  |
|   China  |   1   |  7  |

Based on the sequence (row) of my entries I can reconstruct the tree structure. For each row I look for the nearest previous row that has Level-1.

max(pre.Row) / pre.Row < cur.Row / pre.Level = cur.Level-1

Following code is working and it returns the right results. My problem is that the table is 7 million rows large and therefore it takes a lot of time. It is like comparing 7 million times 7 million rows...

SELECT cur.`Row`, (
    SELECT max(pre.`Row`)
    FROM `abc`.`def` AS pre
    WHERE pre.`Row` < cur.`Row` 
    AND pre.`Level`=cur.`Level`-1
) AS prev_row
FROM `abc`.`def` AS cur
;

Is there a faster way to implement this?

Maybe with loops or user variables? I could imagine that you actually start from the current row and then test if the previous row meets the conditions otherwise look for the next previous row and so on. This will reduce the opertions to 7 million times ~5. I never worked with loops so I have no clue if this is possible in SQL. Any ideas?

Aucun commentaire:

Enregistrer un commentaire