Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- max("cdata".date) AS date
- CREATE MATERIALIZED VIEW cdataview AS
- SELECT md5(ROW("cdata".acc_id, "cdata".adgcid, "cdata".adgid, "cdata".kid, "cdata".sterm, "cdata".mtype)::text) AS id,
- "cdata".acc_id,
- "cdata".kword,
- "cdata".kid,
- "cdata".url,
- "cdata".adgcid,
- "cdata".adgname,
- "cdata".adgid,
- "cdata".adgstatus,
- "cdata".sterm,
- "cdata".mtype,
- sum("cdata".cost) AS total_cost,
- sum("cdata".cval) AS cval,
- sum("cdata".impr) AS impr,
- sum("cdata".clicks) AS clicks,
- sum("cdata".convcl) AS convcl,
- ( SELECT accs.name
- FROM accs
- WHERE accs.id = "cdata".acc_id) AS accname,
- ( SELECT cmps.name
- FROM cmps
- WHERE cmps.id = "cdata".adgcid) AS campname,
- ( SELECT kpr.qscore
- FROM kpr
- WHERE kpr.id = "cdata".kid
- ORDER BY kpr.date DESC
- LIMIT 1) AS qscore,
- round(
- CASE
- WHEN sum("cdata".impr) = 0 THEN 0::numeric
- ELSE sum("cdata".clicks)::numeric / sum("cdata".impr)::numeric
- END, 3) AS ctr,
- round(
- CASE
- WHEN sum("cdata".clicks) = 0 THEN 0::numeric
- ELSE sum("cdata".cost) / sum("cdata".clicks)::numeric
- END, 3) AS cpc,
- round(
- CASE
- WHEN sum("cdata".convcl) = 0::numeric THEN 0::numeric
- ELSE sum("cdata".cost) / sum("cdata".convcl)
- END, 3) AS cpa,
- round(
- CASE
- WHEN sum("cdata".impr) = 0 THEN 0::numeric
- ELSE sum("cdata".impr::numeric * "cdata".avgpos) / sum("cdata".impr)::numeric
- END, 2) AS avgpos,
- max("cdata".date) AS date
- FROM sqpr "cdata"
- LEFT JOIN kpr kpr ON "cdata".kid = kpr.id AND kpr.date = (( SELECT max(kpr.date) AS max
- FROM kpr
- WHERE kpr.id = "cdata".kid))
- GROUP BY "cdata".kword, "cdata".kid, "cdata".acc_id, "cdata".adgstatus, "cdata".adgcid, "cdata".adgname, "cdata".adgid, "cdata".sterm, "cdata".mtype, "cdata".url
- select min("cdata".url) as url
- order by date asc
- last("cdata".url) AS url
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement