使用带有联接的SQL查询来计数行

Question

我有以下两张表，我想查询：

create table active_pairs
(
    pair                  text,
    exchange_id           integer
);


create table exchanges
(                        
    exchange_id         integer,
    exchange_full_name  text
);

INSERT INTO active_pairs (pair, exchange_id)

VALUES ('London/Berlin', 2),
       ('London/Berlin', 3),
       ('Paris/Berlin', 4),
       ('Paris/Berlin', 3),
       ('Oslo/Berlin', 2),
       ('Oslo/Berlin', 6),
       ('Huston/Berlin', 2);
       
INSERT INTO exchanges (exchange_id, exchange_full_name)

VALUES (2, 'Exchange 1'),
       (3, 'Exchange 2'),
       (4, 'Exchange 3'),
       (3, 'Exchange 21'),
       (2, 'Exchange 12'),
       (6, 'Exchange 11'),
       (2, 'Exchange 31');  

我使用这些查询列出所有对：

查询列出项目：

WITH pairs AS(SELECT *
FROM common.active_pairs cp
GROUP BY pair, cp.exchange_id, exchange_id, cp.id, cp.min_order_exchange_id, cp.exchange_active, cp.prime_active, cp.otc_active, cp.marketmaking_active, cp.arbitrage_active
HAVING COUNT(pair) = 1)
SELECT p.pair, p.exchange_id, e.exchange_long_name
FROM pairs p
INNER JOIN common.exchanges e ON(p.exchange_id = e.exchange_id);

查询以计数行以计算分页：

WITH pairs AS(SELECT DISTINCT COUNT(*) OVER () counter
FROM common.active_pairs cp
GROUP BY pair, cp.exchange_id, exchange_id
HAVING COUNT(pair) = 1)
SELECT e.exchange_id
FROM pairs p
INNER JOIN common.exchanges e ON(p.exchange_id = e.exchange_id);

我得到了错误[42703] ERROR: column p.exchange_id does not exist的p.exchange_id。

是否有方法编辑查询以使用联接计算行数？

Answer 1

计算每对的行总数和每对不同的exchange_id总数。使用1 inside ()表示所有行。

-- PostgreSQL
select ap.pair
     , COUNT(1) total_exchange_id
     , COUNT(DISTINCT ap.exchange_id) total_distinct_exchange_id
from active_pairs ap
inner join exchanges e
        on ap.exchange_id = e.exchange_id 
       -- and ap.exchange_id = 2 
group by ap.pair

请查一下这个网址https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c7c945ad52da59d307ed7f1c29da9582

计算成对的行和不同的行，然后计算行和，以获得结果。

-- PostgreSQL
SELECT SUM(t.total_exchange_id) total
     , SUM(t.total_distinct_exchange_id) distinct_total
FROM (select ap.pair
           , COUNT(1) total_exchange_id
           , COUNT(DISTINCT ap.exchange_id) total_distinct_exchange_id
      from active_pairs ap
      inner join exchanges e
              on ap.exchange_id = e.exchange_id 
            -- and ap.exchange_id = 2 
      group by ap.pair) t

请查一下这个网址https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5df605cf1544c7433f840f4be5d80642