主页 / dba / 问题 / 224127
Accepted
Parker
Asked: 2018-12-05 10:03:19 +0800 CST

对多个 OUTER JOIN 进行计数和分组

  • 772

我正在创建广告日志视图（展示次数、点击次数和每次展示的点击次数）。我有一个简单的表结构和一些工作查询，但我在将它们组合成一个我可以用作视图（不是物化视图，因为这将是实时数据）的查询时遇到了一些问题。

这些表是：

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)
);

以及涵盖所有可能情况的一些示例数据：

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');

作为参考，这是我要计算的一组内容advert_log：

查询 A。

SELECT * FROM advert,advert_event;

结果A。

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

按广告分类的事件数：

查询 B。

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

结果 B。

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

对于任何单个广告，可以通过以下查询获得正确的计数：

查询 C1。

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

查询 C2。

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

当然，我之前的查询排除了零计数，因此它没有捕获到上述两种情况中的任何一种。

最终，我想要做的是将上述数字转换为以下数字，使用clicks（“C”条目）除以impressions（“I”条目）来导出cpi列：

 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, ...

我最初的方法是为查询 C1 和 C2 创建一个视图，并从基于查询 A 的视图中调用该函数。

我怀疑有一种更简单的方法可以通过单个查询实现我的目标。

postgresql join

1 个回答

  1. Best Answer

    我在撰写问题时找到了解决方案，但我决定同时发布问题和答案，以防将来对其他人有所帮助。或者，如果有人对此问题有更简单或性能更好的解决方案，我会很高兴听到。

    我通过在 OUTER JOIN 之前使用 CROSS JOIN 设法解决了我的 NULL 计数问题：

    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)

    上面给了我我需要的中间表。添加分组和计数，我终于得到了我要找的数字：

    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)

    最后，使用两个子 SELECT（一个用于“I”，一个用于“C”），我编写了一个查询以通过广告获取计数：

    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)
    • 0

相关问题