sql - 当 distinct 不是连接选项时我该怎么办？

Question

我正在处理一个相当大的数据集，现在遇到了一个问题，我不确定当前的方法是否可以解决。我很好地发布了这个，因为我没有想出最初的例子，但我的任务是把它捡起来。在这一点上重新设计逻辑将是一个相当大的步骤。

该项目在数据仓库上运行报告，但为了让事情更友好，我创建了一个示例来说明我遇到的问题。

CREATE TEMPORARY TABLE test_customers2 (
    id              integer PRIMARY KEY,
    first_name      varchar(40) NOT NULL,
    last_name       varchar(40) NOT NULL,
    newsletter      integer NOT NULL,
    vipmember       integer NOT NULL
);

INSERT INTO test_customers2 VALUES(1, 'Reed', 'Richards', 1, 1);
INSERT INTO test_customers2 VALUES(2, 'Johnny', 'Storm', 0, 1);
INSERT INTO test_customers2 VALUES(3, 'Peter', 'Parker', 1, 0);

CREATE TEMPORARY TABLE test_purchases (
    id        integer CONSTRAINT firstkey2 PRIMARY KEY,
    cid       integer NOT NULL
);

INSERT INTO test_purchases VALUES(1, 1);
INSERT INTO test_purchases VALUES(2, 2);
INSERT INTO test_purchases VALUES(3, 2);
INSERT INTO test_purchases VALUES(4, 3);

SELECT 
    COUNT(distinct c.id) as "Total Customers"
    ,COUNT(distinct p.id) as "Total Sales"
    ,COUNT(distinct p.id)::decimal/COUNT(distinct c.id)::decimal as "Sales per customer"
    ,SUM(c.newsletter) as "Subscribed"
    ,SUM(c.newsletter)::decimal/COUNT(c.newsletter)::decimal as "Pct Subscribed"
    ,SUM(c.vipmember) as "VIP"
    ,SUM(c.vipmember)::decimal/COUNT(c.vipmember)::decimal as "Pct VIP"
FROM test_customers2 c
    INNER JOIN test_purchases p ON c.id = p.cid

最后执行 SELECT 时，会得到结果:

3 | 4 | 1.33... | 2 | 0.50... | 3 | 0.75...

问题是，由于连接，它丢弃了我的结果，因为我真的在寻找这些结果:

3 | 4 | 1.33... | 2 | 0.66... | 2 | 0.66...

distinct 有助于唯一值，但 bool 值(在本例中字面意思是 int，未指定为 bool 值)不适用于该方法，因为它们只有可选值 1、0 或 null。我想我可能需要为此进行子查询，但除了性能下降之外，不得不重写大量代码会有点打击。还有其他更好的方法我可能会遗漏吗？

Answer 1

问题是您执行连接只是为了将不同表中的列放入您的行集中——您实际上并没有利用两个源表之间的关系，您也不想做。它总体上有意义只是因为您想要关联聚合 数据的各个方面，那是您应该加入的数据。

我建议在单独的内联 View /CTE 中计算单表统计信息，然后(交叉)连接两个单行结果以获得另一个单行，从中执行最终选择。像这样的东西，例如:

SELECT 
    c.c_count as "Total Customers"
    ,p.p_count as "Total Sales"
    ,p.p_count::decimal/c.c_count::decimal as "Sales per customer"
    ,c.nl_sum as "Subscribed"
    ,c.nl_sum::decimal/c.c_count::decimal as "Pct Subscribed"
    ,c.vipsum as "VIP"
    ,c.vipsum::decimal/c.c_count::decimal as "Pct VIP"
FROM
  (
    SELECT
      count(*) as c_count,
      sum(newsletter) as nl_sum,
      sum(vipmember) as vip_sum
    FROM test_customers2
  ) c
  CROSS JOIN
  (
    SELECT COUNT(*) AS p_count FROM test_purchases
  ) p