sql语句实现多列前缀匹配求比值

最近有一个需求，前缀匹配求比值，用sql语句实现起来比其他方法简单，所以就写了一个sql语句。如下：

数据库设计如下：

|fs_page_turn_multi_num_data | CREATE TABLE `fs_page_turn_multi_num_data` (
  `day` int(11) default NULL,
  `instance_id` int(11) default NULL,
  `turn1ID` int(11) default NULL,
  `turn2ID` int(11) default NULL,
  `turn3ID` int(11) default NULL,
  `turn4ID` int(11) default NULL,
  `turn5ID` int(11) default NULL,
  `num` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

turn1ID不能为空，turn2ID到turn5ID都可以为空，且如果turnXID和turnYID，如果X<Y，则在turnYID不为空的情况下turnXID不为空。

如果数据表中存储数据如下：

a null null null null num1 
a a null null null num2

a a a null null num3

a a a a null num4

a a a a a num5

则输出应该是：

a null null null null num1 
a a null null null num2 rate1

a a a null null num3 rate2

a a a a null num4 rate3

a a a a a num5 rate4

且rate1=num2/num1 ，rate2=num3/num2 ............

SELECT t1.day,t1.instance_id,t1.turn1ID, t1.turn2ID, t1.turn3ID,t1.turn4ID,t1.turn5ID,

       (CASE WHEN t2.num IS NULL OR t2.num=0 THEN 0 ELSE t1.num/t2.num END ) AS turn_rate
FROM (
SELECT DAY,instance_id,turn1ID, turn2ID, turn3ID,turn4ID,turn5ID, num,
       (CASE WHEN turn1ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn2ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn3ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn4ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn5ID IS NULL THEN 1 ELSE 0 END) AS null_num,
       CONCAT(CONCAT_WS(',',turn1ID, turn2ID, turn3ID,turn4ID,turn5ID),',') AS con_ws
 FROM `fs_page_turn_multi_num_data` WHERE DAY=day) t1 LEFT JOIN
 (
SELECT DAY,instance_id,turn1ID, turn2ID, turn3ID,turn4ID,turn5ID, num,
       (CASE WHEN turn1ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn2ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn3ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn4ID IS NULL THEN 1 ELSE 0 END)+
       (CASE WHEN turn5ID IS NULL THEN 1 ELSE 0 END) AS null_num,
       CONCAT(CONCAT_WS(',',turn1ID, turn2ID, turn3ID,turn4ID,turn5ID),',') AS con_ws
 FROM `fs_page_turn_multi_num_data` WHERE DAY=day) t2
 ON t1.null_num=t2.null_num-1
 AND t1.con_ws LIKE CONCAT(t2.con_ws,'%')