如何简化查询

如何简化查询?这个查询可以简化吗?我尝试了一些 连接,但结果与下面的这个查询不一样。请给我 一些见解。如何简化查询

SELECT trafficbywebsite.`adwordsCampaignID`, 

trafficbywebsite.adwordsAdGroupID, trafficbywebsite.adPlacementDomain,

trafficbywebsite.counts traffic, convertedtrafficbywebsite.counts

convertedclicks

FROM

(

SELECT `adwordsAdGroupID`, `adPlacementDomain`, COUNT(*) counts

FROM

(

SELECT GA_entrances.*

FROM

GA_entrances,

GA_conversions

WHERE

GA_entrances.clientId=GA_conversions.clientId

AND (eventLabel='myurl' OR eventLabel='myotherurl')

AND YEAR(GA_entrances.timestamp)>=2016

AND MONTH(GA_entrances.timestamp)>=6

AND YEAR(GA_conversions.timestamp)>=2016

AND MONTH(GA_conversions.timestamp)>=6

GROUP BY GA_entrances.clientId

) clickers

GROUP BY `adwordsAdGroupID`, `adPlacementDomain`

) convertedtrafficbywebsite

,(

SELECT `adwordsCampaignID`, `adwordsAdGroupID`, adPlacementDomain,

COUNT(*) counts

FROM

GA_entrances

WHERE

YEAR(timestamp)>=2016

AND MONTH(timestamp)>=6

GROUP BY `adwordsAdGroupID`, `adPlacementDomain`

) trafficbywebsite

WHERE

convertedtrafficbywebsite.counts>=(trafficbywebsite.counts/10)

ORDER BY traffic DESC

回答:

没有示例数据很难确定,但看起来你不可能删除其中一个子查询。然而,你可以做的是改善你日期的方式。要避免的是使用数据上的函数来适应您的过滤标准。例如,您希望2016-06-01以后的数据是单一日期,但您正在修改每一行数据以匹配一年和一个月。

AND YEAR(GA_entrances.timestamp) >= 2016 

AND MONTH(GA_entrances.timestamp) >= 6

AND YEAR(GA_conversions.timestamp) >= 2016

AND MONTH(GA_conversions.timestamp) >= 6

;

没有必要对所有这些功能,只是比较单一的日期:

AND GA_entrances.timestamp) >= '2016-06-01' 

AND GA_conversions.timestamp >= '2016-06-01'

;

另外要避免使用逗号,以此来连接表。这种25年以上的ANSI标准语法。这是加盟的古董方式:

FROM GA_entrances, GA_conversions 

WHERE GA_entrances.clientId = GA_conversions.clientId

这被认为是最佳做法:

GA_entrances.* 

FROM GA_entrances

INNER JOIN GA_conversions ON GA_entrances.clientId = GA_conversions.clientId

以上是 如何简化查询 的全部内容, 来源链接: utcz.com/qa/259205.html

回到顶部