创建计算列，创建视图？ [英] Creating computed Columns, create a view?

问题描述

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

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