SHARE
TWEET

Untitled

a guest Mar 20th, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. max("cdata".date) AS date
  2.      
  3. CREATE MATERIALIZED VIEW cdataview AS
  4.  SELECT md5(ROW("cdata".acc_id, "cdata".adgcid, "cdata".adgid, "cdata".kid, "cdata".sterm, "cdata".mtype)::text) AS id,
  5.     "cdata".acc_id,
  6.     "cdata".kword,
  7.     "cdata".kid,
  8.     "cdata".url,
  9.     "cdata".adgcid,
  10.     "cdata".adgname,
  11.     "cdata".adgid,
  12.     "cdata".adgstatus,
  13.     "cdata".sterm,
  14.     "cdata".mtype,
  15.     sum("cdata".cost) AS total_cost,
  16.     sum("cdata".cval) AS cval,
  17.     sum("cdata".impr) AS impr,
  18.     sum("cdata".clicks) AS clicks,
  19.     sum("cdata".convcl) AS convcl,
  20.     ( SELECT accs.name
  21.            FROM accs
  22.           WHERE accs.id = "cdata".acc_id) AS accname,
  23.     ( SELECT cmps.name
  24.            FROM cmps
  25.           WHERE cmps.id = "cdata".adgcid) AS campname,
  26.     ( SELECT kpr.qscore
  27.            FROM kpr
  28.           WHERE kpr.id = "cdata".kid
  29.           ORDER BY kpr.date DESC
  30.          LIMIT 1) AS qscore,
  31.     round(
  32.         CASE
  33.             WHEN sum("cdata".impr) = 0 THEN 0::numeric
  34.             ELSE sum("cdata".clicks)::numeric / sum("cdata".impr)::numeric
  35.         END, 3) AS ctr,
  36.     round(
  37.         CASE
  38.             WHEN sum("cdata".clicks) = 0 THEN 0::numeric
  39.             ELSE sum("cdata".cost) / sum("cdata".clicks)::numeric
  40.         END, 3) AS cpc,
  41.     round(
  42.         CASE
  43.             WHEN sum("cdata".convcl) = 0::numeric THEN 0::numeric
  44.             ELSE sum("cdata".cost) / sum("cdata".convcl)
  45.         END, 3) AS cpa,
  46.     round(
  47.         CASE
  48.             WHEN sum("cdata".impr) = 0 THEN 0::numeric
  49.             ELSE sum("cdata".impr::numeric * "cdata".avgpos) / sum("cdata".impr)::numeric
  50.         END, 2) AS avgpos,
  51.     max("cdata".date) AS date
  52.    FROM sqpr "cdata"
  53.      LEFT JOIN kpr kpr ON "cdata".kid = kpr.id AND kpr.date = (( SELECT max(kpr.date) AS max
  54.            FROM kpr
  55.           WHERE kpr.id = "cdata".kid))
  56.   GROUP BY "cdata".kword, "cdata".kid, "cdata".acc_id, "cdata".adgstatus, "cdata".adgcid, "cdata".adgname, "cdata".adgid, "cdata".sterm, "cdata".mtype, "cdata".url
  57.      
  58. select min("cdata".url) as url
  59.      
  60. order by date asc
  61.      
  62. last("cdata".url) AS url
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top