上周一同事问我有事做没有，我说非常之闲，闲得抠手指头过日子。于是他给我一个任务，写个报表，用表格和图形显示出来。
反正没事可做，接就接呗，也挺简单的，就是几个计算公式，原字段也没有多少，但却衍生出一倍的字段。
本来这周一就完成了，但统计方法又改变了，原来写的作废了，汗。。。
昨天才给我文档，又要重写，本人偷懒，今天才写，搞了一下午，才把SQL写出来，数了一下，MD，BT，居然有52个字段！靠，还要用 IBATIS把它搞到JSP里。
这是我从离开永和顺后，写的第一个长SQL，虽然逻辑很简单。
SELECT
D.*,
IF( D.REVENUE_SHARE_TOTAL > 0 , ( D.PAY_WATER_COOLER_TOTAL + D.PAY_BURST_MEDIA_TOTAL ) / D.REVENUE_SHARE_TOTAL , 0 ) AS EARNED_TOTAL,
IF( D.REVENUE_SHARE_RANDOM > 0 , ( D.PAY_WATER_COOLER_RANDOM + D.PAY_BURST_MEDIA_RANDOM ) / D.REVENUE_SHARE_RANDOM , 0 ) AS EARNED_RANDOM,
IF( D.REVENUE_SHARE_CAT > 0 , ( D.PAY_WATER_COOLER_CAT + D.PAY_BURST_MEDIA_CAT ) / D.REVENUE_SHARE_CAT , 0 ) AS EARNED_CAT
FROM (
SELECT
C.*,
IF( C.PUB_FLAG = 'W' , C.REVENUE_SHARE_TOTAL * 0.5 , 0 ) AS PAY_WATER_COOLER_TOTAL,
IF( C.PUB_FLAG = 'W' , C.REVENUE_SHARE_RANDOM * 0.5 , 0 ) AS PAY_WATER_COOLER_RANDOM,
IF( C.PUB_FLAG = 'W' , C.REVENUE_SHARE_CAT * 0.5 , 0 ) AS PAY_WATER_COOLER_CAT,
IF( C.PUB_FLAG = 'B' , C.IMPRESSIONS_TOTAL * 1 / 1000 , 0 ) AS PAY_BURST_MEDIA_TOTAL,
IF( C.PUB_FLAG = 'B' , C.IMPRESSIONS_RANDOM * 1 / 1000 , 0 ) AS PAY_BURST_MEDIA_RANDOM,
IF( C.PUB_FLAG = 'B' , C.IMPRESSIONS_CAT * 1 / 1000 , 0 ) AS PAY_BURST_MEDIA_CAT,
IF( C.IMPRESSIONS_TOTAL > 0 , ( C.REVENUE_SHARE_TOTAL * 1000 / C.IMPRESSIONS_TOTAL ) , 0 ) AS ECPM_TOTAL,
IF( C.IMPRESSIONS_RANDOM > 0 , ( C.REVENUE_SHARE_RANDOM * 1000 / C.IMPRESSIONS_RANDOM ) , 0 ) AS ECPM_RANDOM,
IF( C.IMPRESSIONS_RANDOM > 0 , ( C.REVENUE_SHARE_CAT * 1000 / C.IMPRESSIONS_CAT ) , 0 ) AS ECPM_CAT,
IF( C.IMPRESSIONS_TOTAL > 0 , C.REVENUE_SHARE_BY_CLICKS_TOTAL * 1000 / C.IMPRESSIONS_TOTAL , 0 ) AS ECPM_BY_CLICKS_TOTAL,
IF( C.IMPRESSIONS_RANDOM > 0 , C.REVENUE_SHARE_BY_CLICKS_RANDOM * 1000 / C.IMPRESSIONS_RANDOM , 0 ) AS ECPM_BY_CLICKS_RANDOM,
IF( C.IMPRESSIONS_CAT > 0 , C.REVENUE_SHARE_BY_CLICKS_CAT * 1000 / C.IMPRESSIONS_CAT , 0 ) AS ECPM_BY_CLICKS_CAT
FROM(
SELECT
B.*,
IF( B.IMPRESSIONS_TOTAL > 0 ,( B.CLICKS_TOTAL / B.IMPRESSIONS_TOTAL ) , 0 ) AS CTR_TOTAL,
IF( B.IMPRESSIONS_RANDOM > 0 , ( B.CLICKS_RANDOM / B.IMPRESSIONS_RANDOM ) , 0 ) AS CTR_RANDOM,
IF( B.IMPRESSIONS_CAT > 0 , ( B.CLICKS_CAT / B.IMPRESSIONS_CAT ) , 0)AS CTR_CAT,
IF( B.CLICKS_TOTAL > 0 , ( B.TRANS_TOTAL / B.CLICKS_TOTAL ) , 0 ) AS CONVERSION_RATE_TOTAL,
IF( B.CLICKS_RANDOM > 0 , ( B.TRANS_RANDOM / B.CLICKS_RANDOM ) , 0 ) AS CONVERSION_RATE_RANDOM,
IF( B.CLICKS_CAT > 0 , ( B.TRANS_CAT / B.CLICKS_CAT ) , 0 ) AS CONVERSION_RATE_CAT,
IF( B.CLICKS_TOTAL > 0 , B.TRANS_BY_CLICKS_TOTAL / B.CLICKS_TOTAL , 0 ) AS CONVERSION_RATE_BY_CLICKS_TOTAL,
IF( B.CLICKS_RANDOM > 0 , B.TRANS_BY_CLICKS_RANDOM / B.CLICKS_RANDOM , 0 ) AS CONVERSION_RATE_BY_CLICKS_RANDOM,
IF( B.CLICKS_CAT > 0 , B.TRANS_BY_CLICKS_CAT / B.CLICKS_CAT , 0 ) AS CONVERSION_RATE_BY_CLICKS_CAT,
CASE B.ADV_FLAG
WHEN 'R' THEN REVENUE_TOTAL * 0.07
WHEN 'S' THEN REVENUE_TOTAL * 0.1
ELSE 0
END AS REVENUE_SHARE_TOTAL,
CASE B.ADV_FLAG
WHEN 'R' THEN REVENUE_TOTAL * 0.07
WHEN 'S' THEN REVENUE_TOTAL * 0.1
ELSE 0
END AS REVENUE_SHARE_RANDOM,
CASE B.ADV_FLAG
WHEN 'R' THEN REVENUE_CAT * 0.07
WHEN 'S' THEN REVENUE_CAT * 0.1
ELSE 0
END AS REVENUE_SHARE_CAT ,
CASE B.ADV_FLAG
WHEN 'R' THEN B.REVENUE_BY_CLICKS_TOTAL * 0.07
WHEN 'S' THEN B.REVENUE_BY_CLICKS_TOTAL * 0.1
ELSE 0
END AS REVENUE_SHARE_BY_CLICKS_TOTAL,
CASE B.ADV_FLAG
WHEN 'R' THEN B.REVENUE_BY_CLICKS_RANDOM * 0.07
WHEN 'S' THEN B.REVENUE_BY_CLICKS_RANDOM * 0.1
ELSE 0
END AS REVENUE_SHARE_BY_CLICKS_RANDOM,
CASE B.ADV_FLAG
WHEN 'R' THEN B.REVENUE_BY_CLICKS_CAT * 0.07
WHEN 'S' THEN B.REVENUE_BY_CLICKS_CAT * 0.1
ELSE 0
END AS REVENUE_SHARE_BY_CLICKS_CAT
FROM(
SELECT
A.WEEK , A.ADV , A.PUB ,
A.IMPRESSIONS_TOTAL , A.IMPRESSIONS_RANDOM , A.IMPRESSIONS_CAT ,
A.CLICKS_TOTAL , A.CLICKS_RANDOM , A.CLICKS_CAT ,
A.TRANS_TOTAL , A.TRANS_RANDOM , A.TRANS_CAT ,
A.REVENUE_TOTAL , A.REVENUE_RANDOM , A.REVENUE_CAT ,
A.TRANS_BY_CLICKS_TOTAL , A.TRANS_BY_CLICKS_RANDOM , A.TRANS_BY_CLICKS_CAT ,
A.REVENUE_BY_CLICKS_TOTAL , A.REVENUE_BY_CLICKS_RANDOM , A.REVENUE_BY_CLICKS_CAT ,
CASE ADV
WHEN 'Ridegear' THEN 'R'
WHEN 'Stacks N Stacks' THEN 'S'
ELSE 'O'
END AS ADV_FLAG,
CASE PUB
WHEN 'Burst Media' THEN 'B'
WHEN 'Water Cooler' THEN 'W'
ELSE 'O'
END AS PUB_FLAG
FROM
measure A
) B
) C
) D
下面是上周写的，
SELECT
C.*,
IF( C.REVENUE_SHARE > 0, ( IFNULL( C.PAY_WATER_COOLER , 0 ) + IFNULL( C.PAY_BURST_MEDIA , 0 ) ) / C.REVENUE_SHARE , 0) AS EARNED,
IF( C.IMPS > 0, C.REVENUE_SHARE * 1000 / IMPS , 0) AS ECPM
FROM(
SELECT
B.*,
CASE
WHEN B.PUB_NAME = 'Water Cooler' AND B.REVENUE_SHARE THEN B.REVENUE_SHARE * 0.5
ELSE 0
END AS PAY_WATER_COOLER
FROM(
SELECT
A.*,
IF( IMPS > 0 , A.CLICKS / A.IMPS , 0 ) AS CTR,
IF( CLICKS > 0, A.TRANS / A.CLICKS , 0 ) AS CONVERSION_RATE,
IF( TRANS > 0, A.REVENUE / A.TRANS, 0 ) AS AVG_BASKET_SIZE,
CASE
WHEN ADV_NAME = 'Stacks N Stacks' AND REVENUE > 0 THEN REVENUE * 0.1
WHEN ADV_NAME = 'RideGear' AND REVENUE > 0 THEN REVENUE * 0.07
ELSE 0
END AS REVENUE_SHARE,
CASE
WHEN PUB_NAME = 'Burst Media' AND IMPS > 0 THEN IMPS * 1 / 1000
ELSE 0
END AS PAY_BURST_MEDIA
FROM(
SELECT
SUBDATE( DAY , DATE_FORMAT( DAY , '%w' ) - 0 ) AS SUN,
SUBDATE( DAY , DATE_FORMAT( DAY , '%w' ) - 6 ) AS SAT,
CASE
WHEN ADV_ID IN ( 17 , 43 ) THEN 'RideGear'
WHEN ADV_ID IN ( 72 , 82) THEN 'Stacks N Stacks'
ELSE 'Linkshare'
END AS ADV_NAME,
CASE
WHEN PUB_ID = 19 THEN 'Burst Media'
WHEN PUB_ID = 80 THEN 'Water Cooler'
ELSE ''
END AS PUB_NAME,
SUM( IMPS ) AS IMPS,
SUM( CLICKS ) AS CLICKS,
SUM( TRANS ) AS TRANS,
SUM( REVENUE ) AS REVENUE
FROM
measure_report
GROUP BY
CASE
WHEN ADV_ID IN ( 17 , 43 ) THEN 'RideGear'
WHEN ADV_ID IN ( 72 , 82) THEN 'Stacks N Stacks'
ELSE 'Linkshare'
END,
CASE
WHEN PUB_ID = 19 THEN 'Burst Media'
WHEN PUB_ID = 80 THEN 'Water Cooler'
ELSE ''
END,
WEEK ( DAY )
ORDER BY DAY
) A
) B
) C
|
京ICP备05002321号