|
This is the same result as this KB report - error 1928 with 5.3.3 RC
http://kb.askmonty.org/en/error-1928-with-533-rc
Didnt see any more details or this bug being reported in bugs db so adding this with the info requested of original poster:
------1) Query that caused this problem:
explain
SELECT t1.language_actual, t1.CampaignType, t1.ad_group_name,t1.ad_name, t2.Impressions, t2.Clicks, t2.Cost, t1.Trials,(t1.Trials * 100)/t2.Clicks as 'Clicks/Trials %', t1.Trialsconfirmed, t1.Buyers,(t1.Buyers * 100)/t1.Trials as 'Trials/Buyers %', t2.Cost/t1.Buyers as '$Cost/Buyer', t1.sales_1_month,t1.sales_2_month,t1.sales_3_month, t1.Sales
FROM
(
SELECT
mp.language_id,
dl.language_actual,
t.CampaignType,
adg.ad_group_name,
adg.ad_name,
COUNT(DISTINCT t.member_id) as Trials,
COUNT(DISTINCT if(t.completed=1, t.member_id,null)) as Trialsconfirmed,
COUNT(DISTINCT if(mp.completed=1 and mp.amount>0, mp.member_id,null)) as Buyers,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 33 DAY),mp.amount, null)) AS sales_1_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 66 DAY),mp.amount, null)) AS sales_2_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 99 DAY),mp.amount, null)) AS sales_3_month,
SUM(IF(mp.completed=1 AND mp.amount >0,mp.amount,0)) as Sales
FROM
alldw.etl_all_mem_payments mp
INNER JOIN
(
SELECT member_id,
min(tm_added) as tm_added,
max(completed) as completed,
max(if(data like '%src=gc_Learn%Content%' or language_id=1, 'Content','Search')) as CampaignType,
if((LOCATE('_', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6) - (LOCATE('dilcg&',REVERSE(data)) + 6)) =0,
REVERSE(SUBSTRING(REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7,LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7) - (LOCATE('dilcg&',REVERSE(data)) + 7))),
REVERSE(SUBSTRING(REVERSE(data),LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 ) +1 , LOCATE('',REVERSE(data), LOCATE('',REVERSE(data), LOCATE('dilcg&',REVERSE(data))) + 1) - (LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 )) -1 ))) as adid
from alldw.etl_all_mem_payments where
Date(tm_added) BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
and product_id=1
and data like '%gc_Learn%'
group by member_id
) t
ON mp.member_id = t.member_id INNER JOIN alldw.etl_all_dim_languages dl ON mp.language_id = dl.language_id
LEFT JOIN
(SELECT ad_id,
MAX(ad_group_name ) as ad_group_name,
MAX(ad_name) as ad_name
FROM alldw.ad_daily
GROUP BY ad_id) adg
ON adg.ad_id = t.adid
GROUP BY mp.language_id, dl.language_actual, t.CampaignType, adg.ad_group_name, adg.ad_name
) t1
LEFT JOIN
(
SELECT ad.language_id,
dl.language_actual,
if(adc.campaign_type = 'Search', 'Search', 'Content') AS CampaignType,
ad.ad_group_name,
ad.ad_name,
SUM(ad.impressions) AS Impressions,
SUM(ad.clicks) AS Clicks,
SUM(ad.cost) AS Cost
FROM alldw.ad_daily ad
INNER JOIN alldw.etl_all_dim_languages dl ON ad.language_id = dl.language_id
INNER JOIN alldw.adwords_dictionary_campaigns adc ON adc.campaign_name = ad.campaign_name
WHERE ad.day BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
GROUP BY ad.language_id, CampaignType,ad.ad_group_name, ad.ad_name
) t2
ON t1.language_id = t2.language_id AND t1.CampaignType=t2.CampaignType AND t1.ad_group_name = t2.ad_group_name AND t1.ad_name = t2.ad_name
UNION
SELECT t2.language_actual, t2.CampaignType, t2.ad_group_name, t2.ad_name, t2.Impressions, t2.Clicks, t2.Cost, t1.Trials,(t1.Trials * 100)/t2.Clicks as 'Clicks/Trials %', t1.Trialsconfirmed, t1.Buyers,(t1.Buyers * 100)/t1.Trials as 'Trials/Buyers %',t2.Cost/t1.Buyers as '$Cost/Buyer',t1.sales_1_month,t1.sales_2_month,t1.sales_3_month, t1.Sales
FROM
(
SELECT
mp.language_id,
dl.language_actual,
t.CampaignType,
adg.ad_group_name,
adg.ad_name,
COUNT(DISTINCT t.member_id) as Trials,
COUNT(DISTINCT if(t.completed=1, t.member_id,null)) as Trialsconfirmed,
COUNT(DISTINCT if(mp.completed=1 and mp.amount>0, mp.member_id,null)) as Buyers,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 33 DAY),mp.amount, null)) AS sales_1_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 66 DAY),mp.amount, null)) AS sales_2_month,
SUM(IF(mp.completed=1 AND mp.amount > 0 AND mp.tm_added <= date_add(t.tm_added, INTERVAL 99 DAY),mp.amount, null)) AS sales_3_month,
SUM(if(mp.completed=1 and mp.amount >0,mp.amount,0)) as Sales
FROM
alldw.etl_all_mem_payments mp
INNER JOIN
(
SELECT member_id,
min(tm_added) as tm_added,
max(completed) as completed,
max(if(data like '%src=gc_Learn%Content%' or language_id=1, 'Content','Search')) as CampaignType,
if((LOCATE('_', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6) - (LOCATE('dilcg&',REVERSE(data)) + 6)) =0,
REVERSE(SUBSTRING(REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7,LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 7) - (LOCATE('dilcg&',REVERSE(data)) + 7))),
REVERSE(SUBSTRING(REVERSE(data),LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 ) +1 , LOCATE('',REVERSE(data), LOCATE('',REVERSE(data), LOCATE('dilcg&',REVERSE(data))) + 1) - (LOCATE('', REVERSE(data),LOCATE('dilcg&',REVERSE(data)) + 6 )) -1 ))) as adid
from alldw.etl_all_mem_payments where
Date(tm_added) BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
and product_id=1
and data like '%gc_Learn%'
group by member_id
) t
ON mp.member_id = t.member_id INNER JOIN alldw.etl_all_dim_languages dl ON mp.language_id = dl.language_id
LEFT JOIN
(SELECT ad_id,
MAX(ad_group_name ) as ad_group_name,
MAX(ad_name) as ad_name
FROM alldw.ad_daily
GROUP BY ad_id) adg
ON adg.ad_id = t.adid
GROUP BY mp.language_id, dl.language_actual, t.CampaignType, adg.ad_group_name, adg.ad_name
) t1
Right JOIN
(
SELECT ad.language_id,
dl.language_actual,
if(adc.campaign_type = 'Search', 'Search', 'Content') AS CampaignType,
ad.ad_group_name,
ad.ad_name,
SUM(ad.impressions) AS Impressions,
SUM(ad.clicks) AS Clicks,
SUM(ad.cost) AS Cost
FROM alldw.ad_daily ad
INNER JOIN alldw.etl_all_dim_languages dl ON ad.language_id = dl.language_id
INNER JOIN alldw.adwords_dictionary_campaigns adc ON adc.campaign_name = ad.campaign_name
WHERE ad.day BETWEEN NAME_CONST('vStartDate',_binary'2011-12-01' COLLATE 'binary') AND NAME_CONST('vEndDate',_binary'2011-12-28' COLLATE 'binary')
GROUP BY ad.language_id, CampaignType,ad.ad_group_name, ad.ad_name
) t2
ON t1.language_id = t2.language_id and t1.CampaignType=t2.CampaignType AND t1.ad_group_name = t2.ad_group_name AND t1.ad_name = t2.ad_name
order by language_actual, CampaignType, ad_group_name, ad_name
------ 2) Explain output:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16490420
filtered: 100.00
Extra:
- 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived5>
type: ref
possible_keys: key0
key: key0
key_len: 530
ref: t1.language_id,t1.CampaignType,t1.ad_group_name,t1.ad_name
rows: 10
filtered: 100.00
Extra: Using where
- 3. row ***************************
id: 5
select_type: DERIVED
table: adc
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 34
filtered: 100.00
Extra: Using temporary; Using filesort
- 4. row ***************************
id: 5
select_type: DERIVED
table: ad
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 59981
filtered: 100.00
Extra: Using where; Using join buffer (flat, BNL join)
- 5. row ***************************
id: 5
select_type: DERIVED
table: dl
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: alldw.ad.language_id
rows: 1
filtered: 100.00
Extra: Using index condition
- 6. row ***************************
id: 2
select_type: DERIVED
table: <derived3>
type: ALL
possible_keys: key0
key: NULL
key_len: NULL
ref: NULL
rows: 1649042
filtered: 100.00
Extra: Using temporary; Using filesort
- 7. row ***************************
id: 2
select_type: DERIVED
table: mp
type: ref
possible_keys: language_id,language_id_2
key: language_id
key_len: 4
ref: t.member_id
rows: 1
filtered: 100.00
Extra:
- 8. row ***************************
id: 2
select_type: DERIVED
table: <derived4>
type: ref
possible_keys: key0
key: key0
key_len: 8
ref: t.adid
rows: 10
filtered: 100.00
Extra: Using where
- 9. row ***************************
id: 2
select_type: DERIVED
table: dl
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: alldw.mp.language_id
rows: 1
filtered: 100.00
Extra: Using index condition
- 10. row ***************************
id: 4
select_type: DERIVED
table: ad_daily
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 59981
filtered: 100.00
Extra: Using temporary; Using filesort
- 11. row ***************************
id: 3
select_type: DERIVED
table: etl_all_mem_payments
type: ref
possible_keys: language_id_3
key: language_id_3
key_len: 4
ref: const
rows: 1649042
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
- 12. row ***************************
id: 6
select_type: UNION
table: <derived10>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2039354
filtered: 100.00
Extra:
- 13. row ***************************
id: 6
select_type: UNION
table: <derived7>
type: ref
possible_keys: key0
key: key0
key_len: 533
ref: t2.language_id,t2.CampaignType,t2.ad_group_name,t2.ad_name
rows: 10
filtered: 100.00
Extra:
- 14. row ***************************
id: 10
select_type: DERIVED
table: adc
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 34
filtered: 100.00
Extra: Using temporary; Using filesort
- 15. row ***************************
id: 10
select_type: DERIVED
table: ad
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 59981
filtered: 100.00
Extra: Using where; Using join buffer (flat, BNL join)
- 16. row ***************************
id: 10
select_type: DERIVED
table: dl
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: alldw.ad.language_id
rows: 1
filtered: 100.00
Extra: Using index condition
- 17. row ***************************
id: 7
select_type: DERIVED
table: <derived8>
type: ALL
possible_keys: key0
key: NULL
key_len: NULL
ref: NULL
rows: 1649042
filtered: 100.00
Extra: Using temporary; Using filesort
- 18. row ***************************
id: 7
select_type: DERIVED
table: mp
type: ref
possible_keys: language_id,language_id_2
key: language_id
key_len: 4
ref: t.member_id
rows: 1
filtered: 100.00
Extra:
- 19. row ***************************
id: 7
select_type: DERIVED
table: <derived9>
type: ref
possible_keys: key0
key: key0
key_len: 8
ref: t.adid
rows: 10
filtered: 100.00
Extra: Using where
- 20. row ***************************
id: 7
select_type: DERIVED
table: dl
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: alldw.mp.language_id
rows: 1
filtered: 100.00
Extra: Using index condition
- 21. row ***************************
id: 9
select_type: DERIVED
table: ad_daily
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 59981
filtered: 100.00
Extra: Using temporary; Using filesort
- 22. row ***************************
id: 8
select_type: DERIVED
table: etl_all_mem_payments
type: ref
possible_keys: language_id_3
key: language_id_3
key_len: 4
ref: const
rows: 1649042
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
- 23. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,6>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using filesort
- 1. row ***************************
Level: Note
Code: 1003
Message: select `t1`.`language_actual` AS `language_actual`,`t1`.`CampaignType` AS `CampaignType`,`t1`.`ad_group_name` AS `ad_group_name`,`t1`.`ad_name` AS `ad_name`,`t2`.`Impressions` AS `Impressions`,`t2`.`Clicks` AS `Clicks`,`t2`.`Cost` AS `Cost`,`t1`.`Trials` AS `Trials`,((`t1`.`Trials` * 100) / `t2`.`Clicks`) AS `Clicks/Trials %`,`t1`.`Trialsconfirmed` AS `Trialsconfirmed`,`t1`.`Buyers` AS `Buyers`,((`t1`.`Buyers` * 100) / `t1`.`Trials`) AS `Trials/Buyers %`,(`t2`.`Cost` / `t1`.`Buyers`) AS `$Cost/Buyer`,`t1`.`sales_1_month` AS `sales_1_month`,`t1`.`sales_2_month` AS `sales_2_month`,`t1`.`sales_3_month` AS `sales_3_month`,`t1`.`Sales` AS `Sales` from (select `alldw`.`mp`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,`t`.`CampaignType` AS `CampaignType`,`adg`.`ad_group_name` AS `ad_group_name`,`adg`.`ad_name` AS `ad_name`,count(distinct `t`.`member_id`) AS `Trials`,count(distinct if((`t`.`completed` = 1),`t`.`member_id`,NULL)) AS `Trialsconfirmed`,count(distinct if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`member_id`,NULL)) AS `Buyers`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 33 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_1_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 66 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_2_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 99 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_3_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`amount`,0)) AS `Sales` from `alldw`.`etl_all_mem_payments` `mp` join (select `alldw`.`etl_all_mem_payments`.`member_id` AS `member_id`,min(`alldw`.`etl_all_mem_payments`.`tm_added`) AS `tm_added`,max(`alldw`.`etl_all_mem_payments`.`completed`) AS `completed`,max(if(((`alldw`.`etl_all_mem_payments`.`data` like '%src=gc_Learn%Content%') or (`alldw`.`etl_all_mem_payments`.`language_id` = 1)),'Content','Search')) AS `CampaignType`,if(((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) = 0),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)))),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) + 1),((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`))) + 1)) - locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6))) - 1)))) AS `adid` from `alldw`.`etl_all_mem_payments` where ((`alldw`.`etl_all_mem_payments`.`product_id` = 1) and (cast(`alldw`.`etl_all_mem_payments`.`tm_added` as date) between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`etl_all_mem_payments`.`data` like '%gc_Learn%')) group by `alldw`.`etl_all_mem_payments`.`member_id`) `t` join `alldw`.`etl_all_dim_languages` `dl` left join (select `alldw`.`ad_daily`.`ad_id` AS `ad_id`,max(`alldw`.`ad_daily`.`ad_group_name`) AS `ad_group_name`,max(`alldw`.`ad_daily`.`ad_name`) AS `ad_name` from `alldw`.`ad_daily` group by `alldw`.`ad_daily`.`ad_id`) `adg` on(((`adg`.`ad_id` = `t`.`adid`) and (`t`.`adid` is not null))) where ((`alldw`.`mp`.`member_id` = `t`.`member_id`) and (`alldw`.`mp`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`mp`.`language_id`,`t`.`CampaignType`,`adg`.`ad_group_name`,`adg`.`ad_name`) `t1` left join (select `alldw`.`ad`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content') AS `CampaignType`,`alldw`.`ad`.`ad_group_name` AS `ad_group_name`,`alldw`.`ad`.`ad_name` AS `ad_name`,sum(`alldw`.`ad`.`impressions`) AS `Impressions`,sum(`alldw`.`ad`.`clicks`) AS `Clicks`,sum(`alldw`.`ad`.`cost`) AS `Cost` from `alldw`.`ad_daily` `ad` join `alldw`.`etl_all_dim_languages` `dl` join `alldw`.`adwords_dictionary_campaigns` `adc` where ((`alldw`.`ad`.`day` between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`adc`.`campaign_name` = convert(`alldw`.`ad`.`campaign_name` using utf8)) and (`alldw`.`ad`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`ad`.`language_id`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content'),`alldw`.`ad`.`ad_group_name`,`alldw`.`ad`.`ad_name`) `t2` on(((`t2`.`language_id` = `t1`.`language_id`) and (`t2`.`CampaignType` = `t1`.`CampaignType`) and (`t2`.`ad_group_name` = `t1`.`ad_group_name`) and (`t2`.`ad_name` = `t1`.`ad_name`) and (`t1`.`CampaignType` is not null) and (`t1`.`ad_group_name` is not null) and (`t1`.`ad_name` is not null))) where 1 union select `t2`.`language_actual` AS `language_actual`,`t2`.`CampaignType` AS `CampaignType`,`t2`.`ad_group_name` AS `ad_group_name`,`t2`.`ad_name` AS `ad_name`,`t2`.`Impressions` AS `Impressions`,`t2`.`Clicks` AS `Clicks`,`t2`.`Cost` AS `Cost`,`t1`.`Trials` AS `Trials`,((`t1`.`Trials` * 100) / `t2`.`Clicks`) AS `Clicks/Trials %`,`t1`.`Trialsconfirmed` AS `Trialsconfirmed`,`t1`.`Buyers` AS `Buyers`,((`t1`.`Buyers` * 100) / `t1`.`Trials`) AS `Trials/Buyers %`,(`t2`.`Cost` / `t1`.`Buyers`) AS `$Cost/Buyer`,`t1`.`sales_1_month` AS `sales_1_month`,`t1`.`sales_2_month` AS `sales_2_month`,`t1`.`sales_3_month` AS `sales_3_month`,`t1`.`Sales` AS `Sales` from (select `alldw`.`ad`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content') AS `CampaignType`,`alldw`.`ad`.`ad_group_name` AS `ad_group_name`,`alldw`.`ad`.`ad_name` AS `ad_name`,sum(`alldw`.`ad`.`impressions`) AS `Impressions`,sum(`alldw`.`ad`.`clicks`) AS `Clicks`,sum(`alldw`.`ad`.`cost`) AS `Cost` from `alldw`.`ad_daily` `ad` join `alldw`.`etl_all_dim_languages` `dl` join `alldw`.`adwords_dictionary_campaigns` `adc` where ((`alldw`.`ad`.`day` between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`adc`.`campaign_name` = convert(`alldw`.`ad`.`campaign_name` using utf8)) and (`alldw`.`ad`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`ad`.`language_id`,if((`alldw`.`adc`.`campaign_type` = 'Search'),'Search','Content'),`alldw`.`ad`.`ad_group_name`,`alldw`.`ad`.`ad_name`) `t2` left join (select `alldw`.`mp`.`language_id` AS `language_id`,`alldw`.`dl`.`language_actual` AS `language_actual`,`t`.`CampaignType` AS `CampaignType`,`adg`.`ad_group_name` AS `ad_group_name`,`adg`.`ad_name` AS `ad_name`,count(distinct `t`.`member_id`) AS `Trials`,count(distinct if((`t`.`completed` = 1),`t`.`member_id`,NULL)) AS `Trialsconfirmed`,count(distinct if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`member_id`,NULL)) AS `Buyers`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 33 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_1_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 66 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_2_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0) and (`alldw`.`mp`.`tm_added` <= (`t`.`tm_added` + interval 99 day))),`alldw`.`mp`.`amount`,NULL)) AS `sales_3_month`,sum(if(((`alldw`.`mp`.`completed` = 1) and (`alldw`.`mp`.`amount` > 0)),`alldw`.`mp`.`amount`,0)) AS `Sales` from `alldw`.`etl_all_mem_payments` `mp` join (select `alldw`.`etl_all_mem_payments`.`member_id` AS `member_id`,min(`alldw`.`etl_all_mem_payments`.`tm_added`) AS `tm_added`,max(`alldw`.`etl_all_mem_payments`.`completed`) AS `completed`,max(if(((`alldw`.`etl_all_mem_payments`.`data` like '%src=gc_Learn%_Content%') or (`alldw`.`etl_all_mem_payments`.`language_id` = 1)),'Content','Search')) AS `CampaignType`,if(((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) = 0),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)) - (locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 7)))),reverse(substr(reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6)) + 1),((locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('',reverse(`alldw`.`etl_all_mem_payments`.`data`),locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`))) + 1)) - locate('_',reverse(`alldw`.`etl_all_mem_payments`.`data`),(locate('dilcg&',reverse(`alldw`.`etl_all_mem_payments`.`data`)) + 6))) - 1)))) AS `adid` from `alldw`.`etl_all_mem_payments` where ((`alldw`.`etl_all_mem_payments`.`product_id` = 1) and (cast(`alldw`.`etl_all_mem_payments`.`tm_added` as date) between NAME_CONST('vStartDate',(_binary'2011-12-01' collate binary)) and NAME_CONST('vEndDate',(_binary'2011-12-28' collate binary))) and (`alldw`.`etl_all_mem_payments`.`data` like '%gc_Learn%')) group by `alldw`.`etl_all_mem_payments`.`member_id`) `t` join `alldw`.`etl_all_dim_languages` `dl` left join (select `alldw`.`ad_daily`.`ad_id` AS `ad_id`,max(`alldw`.`ad_daily`.`ad_group_name`) AS `ad_group_name`,max(`alldw`.`ad_daily`.`ad_name`) AS `ad_name` from `alldw`.`ad_daily` group by `alldw`.`ad_daily`.`ad_id`) `adg` on(((`adg`.`ad_id` = `t`.`adid`) and (`t`.`adid` is not null))) where ((`alldw`.`mp`.`member_id` = `t`.`member_id`) and (`alldw`.`mp`.`language_id` = `alldw`.`dl`.`language_id`)) group by `alldw`.`mp`.`language_id`,`t`.`CampaignType`,`adg`.`ad_group_name`,`adg`.`ad_name`) `t1` on(((`t1`.`language_id` = `t2`.`language_id`) and (`t1`.`CampaignType` = `t2`.`CampaignType`) and (`t1`.`ad_group_name` = `t2`.`ad_group_name`) and (`t1`.`ad_name` = `t2`.`ad_name`))) where 1 order by `language_actual`,`CampaignType`,`ad_group_name`,`ad_name`
------- 3) DDL of tables:
USE alldw;
CREATE TABLE IF NOT EXISTS etl_all_dim_languages(
language_id TINYINT(4) UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
database_name VARCHAR(255) DEFAULT NULL,
language_actual VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (language_id)
) ENGINE = MYISAM AVG_ROW_LENGTH = 48 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS ad_daily(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
ad_id BIGINT(20) NOT NULL,
ad_name VARCHAR(255) NOT NULL,
campaign_name VARCHAR(255) NOT NULL,
ad_type VARCHAR(255) NOT NULL,
ad_group_name VARCHAR(255) NOT NULL,
description1 VARCHAR(255) NOT NULL,
description2 VARCHAR(255) NOT NULL,
displayUrl VARCHAR(255) NOT NULL,
status VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
impressions INT(11) NOT NULL,
clicks INT(11) NOT NULL,
cost FLOAT NOT NULL,
day DATE NOT NULL,
language_id INT(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE = MYISAM AUTO_INCREMENT = 60349 AVG_ROW_LENGTH = 217 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS adwords_dictionary_campaigns(
id INT(11) NOT NULL AUTO_INCREMENT,
campaign_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
campaign_type VARCHAR(255) NOT NULL,
language_id INT(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE = MYISAM AUTO_INCREMENT = 36 AVG_ROW_LENGTH = 43 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS etl_all_mem_payments(
payment_id BIGINT(20) NOT NULL,
language_id INT(11) NOT NULL,
member_id INT(11) NOT NULL DEFAULT 0,
payment_id_orig INT(11) NOT NULL,
member_id_orig INT(11) NOT NULL,
product_id INT(11) NOT NULL DEFAULT 0,
begin_date DATE NOT NULL DEFAULT '0000-00-00',
expire_date DATE NOT NULL DEFAULT '0000-00-00',
paysys_id VARCHAR(32) NOT NULL DEFAULT '',
receipt_id VARCHAR(32) NOT NULL DEFAULT '',
amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
completed SMALLINT(6) DEFAULT 0,
remote_addr VARCHAR(15) NOT NULL DEFAULT '',
`data` TEXT DEFAULT NULL,
`time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
aff_id INT(11) NOT NULL DEFAULT 0,
payer_id VARCHAR(255) NOT NULL DEFAULT '',
coupon_id INT(11) NOT NULL DEFAULT 0,
tm_added DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
tm_completed DATETIME DEFAULT NULL,
tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
refund_reason VARCHAR(50) DEFAULT NULL,
refund_amount DECIMAL(12, 2) DEFAULT 0.00,
refund_date DATETIME DEFAULT '0000-00-00 00:00:00',
data_json LONGTEXT NOT NULL,
cancelled_at DATETIME NOT NULL,
PRIMARY KEY (payment_id),
INDEX language_id (member_id, tm_added),
INDEX language_id_2 (member_id, amount),
INDEX language_id_3 (product_id),
INDEX language_id_4 (coupon_id),
INDEX tm_added (tm_added)
) ENGINE = MYISAM AVG_ROW_LENGTH = 848 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
4) Dump of Tables: The main transaction table is LARGE at ~2million rows. If needed I can provide a sample set of data.
|
LPexportBug923919.xml