问题描述
我正在使用原始adwords数据,其中包含广告系列和营销效果数据。我需要根据符合特定 WHERE条件的列进行计算。举个例子;如果在广告相关性列下,该行=低于平均水平,则应返回一列作为低于平均广告相关性展示次数,则此情况的计算应为展示次数* 1。我还需要进行其他计算,例如,无论出现在何处,都在每一行中发生;栏 QS *展示次数。最后,我将需要将两列连接在一起,然后再将其与另一个数据集循环。
I am working with raw adwords data which contains campaigns and marketing performance data. I need to make calculations based on columns that fit a certain "WHERE" criteria. As an example; If under the column "Ad Relevance" the row = "Below average" then a column should be returned as "Below Average Ad Relevance Impressions" the calculation for this occurrence should be Impressions * 1. I will also be needing to make other calculations that occur throughout every row regardless of at where criteria, as an example; Column "QS" * "Impressions". I finally will need to concatenate a two columns together that will in turn be looped with another data set.
在不创建视图的情况下似乎可以实现吗?我曾尝试使用 Where查询创建一个查询,但是由于只能基于一个条件并且只能基于一个条件,因此似乎只能提取多少。
Does this appear to be possible without creating a view? I have tried creating one while using "Where" queries however it seems to be limited with how much I can withdraw as it can be based on one and only one condition.
当前表
Campaign|Adgroup|Expected clickthrough rate|QS|Impressions|
--------+-------+--------------------------+--+-----------+
USA-EN |watches|Average |3 |1000 |
DE-DE |rings |Below Average |5 |1300 |
CH-DE |belts |Average |6 |1234 |
所需结果(这些是附加的/计算的列,但目标是将顶部的列包括在
Desired Results (these are the additional / computed columns but the goal is to include the columns at top within the new table as well)
Key |Impressions with average ctr |Weighted QS |
**(Campaign and |(Impressions *1 where "Expected |(QS * Impressions |
adgroup concatenated)|clickthroughrate =average) | |**
---------------------+--------------------------------+------------------+
USA-ENwatches |1000 | |
DE-DErings |0 | |
CH-DEbelts |1234 | |
推荐答案
对于IF-THEN逻辑,请使用用例语句。 b $ b对于字符串的连接,请使用 ||
For IF - THEN logic, use case statements.For concatenation of strings use "||"
以下是基于当前表和所需结果的示例:
Here is an example based on your "Current Table" and "Desired Results":
Select (Campaign || '-' || Adgroup) AS Key
,CASE [Expected clickthrought rate]
When 'Average' then (Impressions * 1)
Else 0
End AS [Impressions with average ctr]
,(QS * Impressions) AS [Weighted QS]
From MyTable
这篇关于创建计算列,创建视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!