本文介绍了创建计算列,创建视图?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用原始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

这篇关于创建计算列,创建视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 17:55