Guest User

Untitled

a guest
Mar 20th, 2019
56
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