深圳智汇创想科技有限公司

001- 反馈人：吴荣发
001+ 反馈人：吴荣发 数据库：zhcxkj_center_adv_amazon 节点ID：223b4653b0a541b2b1e7f7dca4006c0fin01 查询时间：11.7391 扫描行数：791.944 返回行数：395.972 一天执行次数： 283 慢日志时间：2025-07-25 指纹：9a876432cff764786d1b7275b6a9ccb3 SQL语句：with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour >= 2025072405 and cv.own_hour <= 2025072408 and cv.site_date >= '2025-06-10' UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour >= 2025072405 and cv.own_hour <= 2025072408 and cv.site_date >= '2025-06-10' ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku; 负责人参考：钟文生,覃光贤 项目参考：java项目
002- 数据库：zhcxkj_center_adv_amazon
003- 节点ID：223b4653b0a541b2b1e7f7dca4006c0fin01
004- 查询时间：11.7391
005- 扫描行数：791.944
006- 返回行数：395.972
007- 一天执行次数： 283
008- 慢日志时间：2025-07-25
009- 指纹：9a876432cff764786d1b7275b6a9ccb3
010- SQL语句：with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d ,
011- 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour >= 2025072405 and cv.own_hour <= 2025072408 and cv.site_date >= '2025-06-10' UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour >= 2025072405 and cv.own_hour <= 2025072408 and cv.site_date >= '2025-06-10' ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;
012- 负责人参考：钟文生,覃光贤
013- 项目参考：java项目

001- 反馈人：吴荣发<br />
001+ 反馈人：吴荣发<br /> 数据库：zhcxkj_center_adv_amazon<br /> 节点ID：223b4653b0a541b2b1e7f7dca4006c0fin01<br /> 查询时间：11.7391<br /> 扫描行数：791.944<br /> 返回行数：395.972<br /> 一天执行次数： 283<br /> 慢日志时间：2025-07-25<br /> 指纹：9a876432cff764786d1b7275b6a9ccb3<br /> SQL语句：with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= '?' UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= '?' ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , <br /> 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour &gt;= 2025072405 and cv.own_hour &lt;= 2025072408 and cv.site_date &gt;= '2025-06-10' UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour &gt;= 2025072405 and cv.own_hour &lt;= 2025072408 and cv.site_date &gt;= '2025-06-10' ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;<br /> 负责人参考：钟文生,覃光贤<br /> 项目参考：java项目<br />
002- 数据库：zhcxkj_center_adv_amazon<br />
003- 节点ID：223b4653b0a541b2b1e7f7dca4006c0fin01<br />
004- 查询时间：11.7391<br />
005- 扫描行数：791.944<br />
006- 返回行数：395.972<br />
007- 一天执行次数： 283<br />
008- 慢日志时间：2025-07-25<br />
009- 指纹：9a876432cff764786d1b7275b6a9ccb3<br />
010- SQL语句：with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= '?' UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= '?' ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , <br />
011- 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour &gt;= 2025072405 and cv.own_hour &lt;= 2025072408 and cv.site_date &gt;= '2025-06-10' UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 15883 and cv.own_hour &gt;= 2025072405 and cv.own_hour &lt;= 2025072408 and cv.site_date &gt;= '2025-06-10' ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%Y-%m-%d') as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, '%Y%m%d%H'), '%H') as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;<br />
012- 负责人参考：钟文生,覃光贤<br />
013- 项目参考：java项目<br />