Postgresql view how to do the query

postgresql

I have 4 tables:

Project: id name

Keyword: id, name

ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects)

ProjectReport: project_id, keyword_id, position(keyword, project has many project_reports)

ProjectCompetitorReport: project_report, keyword_id, position, competitor_idposition`(keyword, project, competitor has many project_reports)

With the help of those tables I need to produce something like this output:

Keyword.name, project_report.position, project_competitor_report.position

The problem that I have is repetition, I don't now how to group the result.

SELECT
    k.name,
    pr1.position,
    pr2.position
from
    project_keywords p1
    JOIN
    keywords k on p1.keyword_id = k.id
    JOIN
    project_reports pr1 on
        p1.project_id = pr1.project_id AND p1.keyword_id = pr1.keyword_id
    JOIN
    project_competitor_reports pr2 on
        p1.project_id = pr2.project_id AND p1.keyword_id = pr2.keyword_id;

I need to group the result produced by that query so that for each keyword i get all the positions from project_reports and all positions from project_position_reports.

As an exemple:
keyword1 has position with value: 1 and 2 on project_report
keyword1 has position whth value: 2 on project_competitor_reports

For this the output should be:

keyword1 1,2 2

At the moment I get them like this:

keyword1 1, 2

keyword1 2, 2

Best Answer

Use array_agg or string_agg

select
    k.name,
    array_agg(pr.position order by pr.position) as pr_position,
    array_agg(pcr.position order by pcr.position) as pcr_position
from
    project_keywords pk
    inner join
    keywords k on pk.keyword_id = k.id
    inner join
    project_reports pr using (project_id,  keyword_id)
    inner join
    project_competitor_reports pcr using (project_id, keyword_id)
group by k.name
;

Related Solutions

Postgresql – how to chain postgres RULEs

Next time, please include the EXPLAIN output rather than making us dig for it in your scripts. There's no guarantee my system is using the same plan as yours (although with your test data it is likely).

The rule system here is working properly. First, the I want to include my own diagnostic queries (note I did not run EXPLAIN ANALYSE since I was just interested in what query plan was generated):

rulestest=# explain DELETE FROM user_hits WHERE day = '2013-03-16';
                                              QUERY PLAN                        

--------------------------------------------------------------------------------
----------------------
 Delete on application_hits  (cost=0.00..3953181.85 rows=316094576 width=24)
   ->  Nested Loop  (cost=0.00..3953181.85 rows=316094576 width=24)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..128.53 rows=6352 width=22)
               ->  Nested Loop  (cost=0.00..96.78 rows=6352 width=22)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..2.49 rows=16 width=16)
                           ->  Nested Loop  (cost=0.00..2.41 rows=16 width=16)
                                 ->  Seq Scan on application_hits  (cost=0.00..1
.10 rows=4 width=10)
                                       Filter: (day = '2013-03-16'::date)
                                 ->  Materialize  (cost=0.00..1.12 rows=4 width=
10)
                                       ->  Seq Scan on client_hits  (cost=0.00..
1.10 rows=4 width=10)
                                             Filter: (day = '2013-03-16'::date)

 Delete on client_hits  (cost=0.00..989722.41 rows=79023644 width=18)
   ->  Nested Loop  (cost=0.00..989722.41 rows=79023644 width=18)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..43.83 rows=1588 width=16)
               ->  Nested Loop  (cost=0.00..35.89 rows=1588 width=16)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..1.12 rows=4 width=10)
                           ->  Seq Scan on client_hits  (cost=0.00..1.10 rows=4 
width=10)
                                 Filter: (day = '2013-03-16'::date)

 Delete on project_hits  (cost=0.00..248851.80 rows=19755911 width=12)
   ->  Nested Loop  (cost=0.00..248851.80 rows=19755911 width=12)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..16.91 rows=397 width=10)
               ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 width=10
)
                     Filter: (day = '2013-03-16'::date)

 Delete on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
   ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
         Filter: (day = '2013-03-16'::date)
(39 rows)

rulestest=# select distinct day from application_hits;
    day     
------------
 2013-03-15
 2013-03-16
(2 rows)

rulestest=# select count(*), day from application_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from client_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from project_hits group by day;
 count |    day     
-------+------------
   397 | 2013-03-15
   397 | 2013-03-16
(2 rows)

If your data is anything like your existing data, neither rules nor triggers will work very well. Better will be a stored procedure which you pass a value and it deletes everything you want.

First let's note that indexes here will get you nowhere because in all cases you are pulling half of the tables (I did add indexes on day on all tables to help the planner but this made no real difference).

You need to start with what you are doing with RULEs. RULEs basically rewrite queries and they do so using ways that are as robust as possible. Your code also doesn't match your example though it matches your question better. You have rules on tables which cascade to rules on other tables which cascade to rules on other tables

Therefore when you delete from user_hits where [criteria], the rules transform this into a set of queries:

DELETE FROM application_hits 
 WHERE day IN (SELECT day FROM client_hits 
               WHERE day IN (SELECT day FROM user_hits WHERE [condition]));
DELETE FROM client_hits
  WHERE day IN (SELECT day FROM user_hits WHERE [condition]);
DELETE FROM user_hits WHERE [condition];

Now, you might think we could skip the scan on client_hits in the first, but that isn't what happens here. The problem is that you could have days in user_hits and application_hits that are not in client_hits so you really have to scan all tables.

Now here there is no magic bullet. A trigger isn't going to work much better because, while it gets to avoid scanning every table, it gets fired every row that gets deleted so you basically end up with the same nested loop sequential scans that are currently killing performance. It will work a bit better because it will delete rows along the way rather than rewriting the query along the way, but it isn't going to perform very well.

A much better solution is to just define a stored procedure and have the application call that. Something like:

CREATE OR REPLACE FUNCTION delete_stats_at_date(in_day date) RETURNS BOOL 
LANGUAGE SQL AS
$$
DELETE FROM application_hits WHERE day = $1;
DELETE FROM project_hits WHERE day = $1;
DELETE FROM client_hits WHERE day  = $1;
DELETE FROM user_hits WHERE day = $1;
SELECT TRUE;
$$;

On the test data this runs in 280 ms on my laptop.

One of the hard things regarding RULEs is remembering what they are and noting that the computer cannot, in fact, read your mind. This is why I would not consider them a beginner's tool.

PostgreSQL Data Retrieval – How to Pull Data from Multiple Tables in a View or Function

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

PostgreSQL Crosstab Query

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 a set of dates you are interested in (today, yesterday etc.). One row per date.
LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.
Cross-tabulate to get a single row with one position column per given date.
Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

Aside: "name" is not a good name. I'd use something descriptive instead.

Best Answer

Related Solutions

Postgresql – how to chain postgres RULEs

PostgreSQL Data Retrieval – How to Pull Data from Multiple Tables in a View or Function

Explain

Related Question