get_diagram.sql

SELECT  
  cv_basket.td_title1, cv_basket.td_title2,
  cv_basket.cnt AS cv_cnt,
  IF(non_cv_basket.cnt IS NULL,0,non_cv_basket.cnt) AS non_cv_cnt,
  ROUND(1.0*cv_basket.cnt/(cv_basket.cnt+IF(non_cv_basket.cnt IS NULL,0,non_cv_basket.cnt)),3) AS cv_ratio,
  
  cv_total_cnt1,
  cv_total_cnt2,
  
  IF(non_cv_total_cnt1 IS NULL,0,non_cv_total_cnt1) AS non_cv_total_cnt1,
  IF(non_cv_total_cnt2 IS NULL,0,non_cv_total_cnt2) AS non_cv_total_cnt2,
  
  ROUND(cv_simpson_coeff,3) AS cv_simpson_coeff,
  ROUND(cv_cos_coeff,3) AS cv_cos_coeff,
  ROUND(IF(non_cv_simpson_coeff IS NULL,1,non_cv_simpson_coeff),3) AS non_cv_simpson_coeff,
  ROUND(IF(non_cv_cos_coeff IS NULL,1,non_cv_cos_coeff),3) AS non_cv_cos_coeff
FROM
(
  SELECT    
    td_title1, td_title2,
    cnt,
    s1.total_cnt AS cv_total_cnt1,
    s2.total_cnt AS cv_total_cnt2,
    1.0*cnt/IF(s1.total_cnt<s2.total_cnt,s1.total_cnt,s2.total_cnt) AS cv_simpson_coeff,
    1.0*cnt/SQRT(s1.total_cnt*s2.total_cnt) AS cv_cos_coeff
  FROM
  (
    SELECT
      t1.td_title AS td_title1, t2.td_title AS td_title2,
      COUNT(1) AS cnt
    FROM
    (
      SELECT td_client_id, cv_id, node_id, td_title
      FROM itmd_conversion_path
    ) t1
    JOIN
    (
      SELECT td_client_id, cv_id, node_id, td_title
      FROM itmd_conversion_path
    ) t2
    ON
    (t1.td_client_id = t2.td_client_id AND t1.cv_id=t2.cv_id AND t1.node_id=t2.node_id-1)
    GROUP BY t1.td_title, t2.td_title
    HAVING ( t1.td_title != t2.td_title )
  ) basket
  JOIN
  (
    SELECT td_title, COUNT(1) AS total_cnt
    FROM itmd_conversion_path
    GROUP BY td_title
  ) s1
  ON ( basket.td_title1 = s1.td_title )
  JOIN
  (
    SELECT td_title, COUNT(1) AS total_cnt
    FROM itmd_conversion_path
    GROUP BY td_title
  ) s2
  ON ( basket.td_title2 = s2.td_title )
) cv_basket
LEFT OUTER JOIN
(
  SELECT
    td_title1, td_title2,
    cnt,
    s1.total_cnt AS non_cv_total_cnt1,
    s2.total_cnt AS non_cv_total_cnt2,
    1.0*cnt/IF(s1.total_cnt<s2.total_cnt,s1.total_cnt,s2.total_cnt) AS non_cv_simpson_coeff,
    1.0*cnt/SQRT(s1.total_cnt*s2.total_cnt) AS non_cv_cos_coeff
  FROM
  (
    SELECT
      t1.td_title AS td_title1, t2.td_title AS td_title2,
      COUNT(1) AS cnt
    FROM
    (
      SELECT td_client_id, node_id, td_title
      FROM itmd_non_conversion_path
    ) t1
    JOIN
    (
      SELECT td_client_id, node_id, td_title
      FROM itmd_non_conversion_path
    ) t2
    ON
    (t1.td_client_id = t2.td_client_id AND t1.node_id=t2.node_id-1)
    GROUP BY t1.td_title, t2.td_title
    HAVING ( t1.td_title != t2.td_title )
  ) basket
  JOIN
  (
    SELECT td_title, COUNT(1) AS total_cnt
    FROM itmd_non_conversion_path
    GROUP BY td_title
  ) s1
  ON ( basket.td_title1 = s1.td_title )
  JOIN
  (
    SELECT td_title, COUNT(1) AS total_cnt
    FROM itmd_non_conversion_path
    GROUP BY td_title
  ) s2
  ON ( basket.td_title2 = s2.td_title )
) non_cv_basket
ON
( cv_basket.td_title1=non_cv_basket.td_title1 AND cv_basket.td_title2=non_cv_basket.td_title2 )
ORDER BY cv_cnt DESC