Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
LIMIT 50
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.