CODEWITHSUNDEEP
sqlpostgresql
Ian James
Ian James

Reputation: 51

Creating computed Columns, create a view?

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.

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.

Current table

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                            |                  |

Upvotes: 0

Views: 49

Answers (1)

Degan
Degan

Reputation: 989

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

Upvotes: 1

Related Questions