Skip to main content
3 of 8
added 79 characters in body
Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620

I suggest a radially new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:

SELECT k.name, keyword_id, project_id, now()::date AS the_date
     , t AS today, y As yesterday, w AS week, m AS month
FROM   crosstab(
 $$SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
      VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       ,((now() - interval '1 month')::date)
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at$$

   ,$$VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       , ((now() - interval '1 month')::date)$$
   ) ct (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

Returns:

name    keyword_id   project_id   the_date     today   yesterday   week   month
Cheese  1            1            2014-07-11   1       1           1      0
Monitor 2            1            2014-07-11   2       2           2      0
Apple   3            1            2014-07-11   9       9           9      0
Apple1  4            1            2014-07-11   0       0           0      0
Iphone  5            1            2014-07-11   0       33          34     0

###Explain

  • We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.

  • JOIN that to the list of date you are interested in (today, yesterday, ...)

  • LEFT JOIN to project_reports for said days.

  • Cross tabulate the whole shebang to get one column per date.

  • Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

Can't provide a fiddle, installing additional modules is not allowed there.

Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620