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 withrow_number()
toproject_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 removern
from the result while being at it.
Can't provide a fiddle, installing additional modules is not allowed there.