我有一个关于查询的问题,它应该执行以下操作:
- 获取特定记录并计算它们在特定时间段内的某些值
- 计算这些关键字在另一个时间段内的相同值
这应该在1个查询中完成。我已经写了它,但是SUM()返回的值比正常值高得多。我认为这是由于LEFT JOIN引起的。
SELECT SQL_CALC_FOUND_ROWS table1.id, table1.KeywordId, table1.AccountName, table1.CampaignName, table1.AdGroupName, table1.Keyword, table1.MatchType, SUM(table1.Spend)/SUM(table1.Clicks) AS AverageCpc, SUM(table1.Impressions) AS Impressions, (SUM(table1.Clicks)*table1.revenue_price)/SUM(table1.Impressions) AS Ctr, SUM(table1.Impressions*table1.AveragePosition)/SUM(table1.Impressions) AS AveragePosition, SUM(table1.Clicks) AS Clicks, SUM(table1.Spend) AS Spend, SUM(table1.free_joins) AS FreeJoins, SUM(table1.paid_joins) AS PaidJoins, SUM(table1.paid_joins)*table1.revenue_price AS Revenue, (SUM(table1.paid_joins)*table1.revenue_price)-SUM(table1.Spend) AS Profit, (SUM(table1.paid_joins)*table1.revenue_price)/SUM(table1.Clicks) AS RevPerClick, table1.CurrentMaxCpc, SUM(table2.Impressions) AS Impressions_chg, SUM(table2.Clicks) AS Clicks_chg, SUM(table2.Impressions*table2.AveragePosition)/SUM(table2.Impressions) AS AveragePosition_chg, (SUM(table2.Clicks)*table2.revenue_price)/SUM(table2.Impressions) AS Ctr_chg, SUM(table2.Spend)/SUM(table2.Clicks) AS AverageCpc_chg, table2.CurrentMaxCpc as CurrentMaxCpc_chg, SUM(table2.free_joins) AS FreeJoins_chg, SUM(table2.paid_joins) AS PaidJoins_chg
FROM keywords_stats_google_naughtymeetings as table1
LEFT JOIN keywords_stats_google_naughtymeetings as table2
ON table1.keywordId = table2.keywordId
WHERE table1.timeperiod >= '2012-05-21 00:00:00' and table1.timeperiod <= '2012-05-27 00:00:00'
AND table2.timeperiod >= '2012-05-14' and table2.timeperiod <= '2012-05-20'
GROUP BY table1.KeywordId, table1.MatchType, table1.revenue_price, table2.KeywordId, table2.MatchType, table2.revenue_price
ORDER BY FreeJoins
asc
LIMIT 0, 10
有人能给我建议,如何获得正确的SUM结果吗?
JOIN
吗?我认为在这里不需要使用LEFT JOIN
。 - Petar Minchev