2

I have two tables Keywords, ProjectReports:

http://sqlfiddle.com/#!15/06ae3/1

As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know how.

  1. In the position column I would like to display only the smallest value and just one value instead of that array. ex: [1, 2, 3] will be 1
  2. As you can see there is the keyword with id 4 which doesn't have any data in the project_reports table, in this case I would like to add the value 0 in the position column and add the current day date in the CREATED_AT column.
  3. Also I would like to display the position value for the same keywords, where created_at column from project_reports table had the value now() - interval '1 day'; now() - interval '1 week'; now() - interval '1 mon', if there is no position for those days add 0.

Expected output:

Keywords:

 ID: 1 name: test
 ID: 2 name: test1
 ID: 3 name: test2

Project_reports:

 ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
 ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'
 ID: 3, keyword_id: 1, project_id: 1, position: 3, created_at: '2014-07-10'
 ID: 5, keyword_id: 1, project_id: 1, position: 4, created_at: '2014-07-10'
 ID: 6, keyword_id: 2, project_id: 1, position: 1, created_at: '2014-07-09'

After query

k_name: test, k_id: 1, p_id: 1, position 3, yesterday_position: 1, created_at: '2014-07-10'
k_name: test1, k_id: 2, p_id: 1, position 0, yesterday_position: 1, created_at: '2014-07-10'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-10'
k_name: test, k_id: 1, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test1, k_id: 2, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-09'
  • I don't see primary keys in your example. Those make a difference. Can we assume the id columns to be primary keys? – Erwin Brandstetter Jul 9 '14 at 15:41
  • yes the id is the primary key, sorry for that. – alexsmn Jul 9 '14 at 15:42
  • OK, then your test data are flawed. Duplicate keys in project_reports. Please clarify. – Erwin Brandstetter Jul 9 '14 at 15:53
  • I'm not sure, which keys are duplicated? – alexsmn Jul 9 '14 at 16:00
  • 1
    Found this by chance. Check out how @replies works. – Erwin Brandstetter Jul 11 '14 at 13:54
3

Note the slightly modified schema of my test in the fiddle.
Using actual primary keys and proper column names instead of id.

Also, you seem to be operating with dates exclusively. So I suggest to convert your timestamp columns to date.

Items 1 and 2

SELECT k.keyword_id
     , k.name
     , pr.project_id
     , COALESCE(min(pr.position), 0) AS pos
     , COALESCE(pr.created_at, now()::date) AS created_at
FROM   keyword             k 
LEFT   JOIN project_report pr USING (keyword_id)
GROUP  BY k.keyword_id, pr.project_id, pr.created_at
ORDER  BY keyword_id, created_at
;
  • In Postgres 9.1 or later the pk column covers the whole table in GROUP BY.
  • Use COALESCE to replace possible NULL values.

A guess at item 3

WITH cte AS (
   SELECT k.keyword_id
        , k.name
        , pr.project_id
        , COALESCE(min(pr.position), 0) AS pos
        , COALESCE(pr.created_at, now()::date) AS created_at
   FROM   keyword   k 
   LEFT   JOIN project_report pr USING (keyword_id)
   GROUP  BY k.keyword_id, pr.project_id, pr.created_at
   )
, x AS (
   SELECT DISTINCT ON (keyword_id, project_id) *
   FROM   cte
   ORDER  BY keyword_id, project_id, created_at DESC
   )
SELECT x.*
     , COALESCE(y.pos, 0) AS yesterday_pos 
     , COALESCE(w.pos, 0) AS week_pos 
     , COALESCE(m.pos, 0) AS month_pos 
FROM   x
LEFT   JOIN  cte y ON y.keyword_id = x.keyword_id
                  AND y.project_id = x.project_id
                  AND y.created_at = x.created_at - interval '1 day'
LEFT   JOIN  cte w ON w.keyword_id = x.keyword_id
                  AND w.project_id = x.project_id
                  AND w.created_at = x.created_at - interval '1 week'
LEFT   JOIN  cte m ON m.keyword_id = x.keyword_id
                  AND m.project_id = x.project_id
                  AND m.created_at = x.created_at - interval '1 month'
;

Explain

  1. In CTE cte produce daily aggregates per (k.keyword_id, pr.project_id).
  2. In CTE x pick the latest day per (k.keyword_id, pr.project_id).
  3. In the outer query LEFT JOIN the latest day x to cte multiple times to retrieve past values from the same (k.keyword_id, pr.project_id) for 1 day / week / month earlier.

SQL Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.