0

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a single query that I can use as a view (not a materialized view, as this will be real-time data).

The tables are:

CREATE TABLE advert
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE advert_event
(
  code CHAR(1) NOT NULL PRIMARY KEY
);

CREATE TABLE advert_log
(
  advertisement integer NOT NULL REFERENCES advert(id),
  event_code CHAR(1) NOT NULL REFERENCES advert_event(code)
);

And some sample data that covers all possible cases:

INSERT INTO advert VALUES (1);
INSERT INTO advert VALUES (2);
INSERT INTO advert VALUES (3);
INSERT INTO advert VALUES (4);

INSERT INTO advert_event VALUES ('I'); -- Impression
INSERT INTO advert_event VALUES ('C'); -- Click

INSERT INTO advert_log VALUES (1, 'I');
INSERT INTO advert_log VALUES (1, 'C');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'C');
INSERT INTO advert_log VALUES (3, 'I');
INSERT INTO advert_log VALUES (3, 'I');

For references, here is a set of the things I want to count in advert_log:

Query A.

SELECT * FROM advert,advert_event;

Result A.

 id | code
----+------
  1 | I
  1 | C
  2 | I
  2 | C
  3 | I
  3 | C
  4 | I
  4 | C
(8 rows)

Event counts by advertisement:

Query B.

SELECT DISTINCT advertisement,event_code,COUNT(*) OVER (PARTITION BY advertisement,event_code) FROM advert_log;

Result B.

 advertisement | event_code | count
---------------+------------+-------
             1 | I          |     1
             1 | C          |     1
             2 | I          |     2
             2 | C          |     1
             3 | I          |     1
(5 rows)

For any individual advertisement, the correct counts can be obtained by queries like:

Query C1.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='I';
 count
-------
     0
(1 row)

Query C2.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='C';
 count
-------
     0
(1 row)

Of course, my earlier query excludes zero counts, so it doesn't catch either of the above two cases.

Ultimately, what I am attempting to do is to turn the above numbers into the following, using clicks (the 'C' entries) divided by impressions (the 'I' entries) to derive the cpi column:

 advertisement | impressions | clicks | cpi
---------------+-------------+--------+-----
             1 |           1 |     1  | 1.0
             2 |           2 |     1  | 0.5
             3 |           1 |     0  | 0.0
             4 |           0 |     0  | 0.0 <- or NULL, NaN, 1.0, ...

My initial approach was to create a view for queries C1 and C2, and to call that function from a view based on Query A.

I suspect there is a simpler way to accomplish my goal with a single query.

0

I was able to find a solution while I was composing the question, but I've decided to post both the question and answer in case it helps someone else in the future. Alternatively, if anyone has a simpler or better-performing solution to this problem, I would be happy to hear it.

I managed to solve my NULL-counting problem by using a CROSS JOIN prior to the OUTER JOIN:

SELECT * FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code;

 id | code | advertisement | event_code
----+------+---------------+------------
  1 | I    |             1 | I
  1 | C    |             1 | C
  2 | I    |             2 | I
  2 | I    |             2 | I
  2 | C    |             2 | C
  3 | I    |             3 | I
  3 | I    |             3 | I
  3 | C    |               |
  4 | I    |               |
  4 | C    |               |
(10 rows)

The above gave me the intermediate table I needed. Adding grouping and counting, I finally get the numbers I'm looking for:

SELECT advert.id,advert_event.code,COUNT(advert_log.advertisement) FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code GROUP BY advert.id,advert_event.code;

 id | code | count
----+------+-------
  1 | C    |     1
  1 | I    |     1
  2 | C    |     1
  2 | I    |     2
  3 | C    |     0
  3 | I    |     2
  4 | C    |     0
  4 | I    |     0
(8 rows)

Finally, using two sub-SELECTs (one for 'I', one for 'C'), I composed a query to obtain the counts by advertisement:

CREATE VIEW advertisement_dashboard AS
SELECT a.id,i.impressions,c.clicks,c.clicks::float/greatest(i.impressions, 1) AS cpi FROM advert a,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS impressions FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='I'
) i,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS clicks FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='C'
) c WHERE i.id=a.id AND c.id=a.id ORDER BY a.id ASC;

SELECT * FROM advertisement_dashboard;

 id | impressions | clicks | cpi
----+-------------+--------+-----
  1 |           1 |      1 |   1
  2 |           2 |      1 | 0.5
  3 |           2 |      0 |   0
  4 |           0 |      0 |   0
(4 rows)

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.