Найдите уникальные значения, подсчитайте дубликаты и ранжируйте их, используя WITH в PostgreSQL 12.

У меня есть 3 сложные таблицы. Для этого вопроса я упрощу обычаи. Мне нужен рейтинг, подсчет (дуплики) и уникальные записи (результат). Он работает с одной таблицей, однако, когда включен другой WITH и указан INNER JOIN, я больше не получаю никаких записей.

Таблицы:

CREATE TABLE public.emails (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  sender            jsonb NOT NULL
);


CREATE TABLE public.contacts (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  email            text NOT NULL,
  full_name            text NOT NULL
);
-- sample data
insert into emails (sender) VALUES ('{"email": "[email protected]", "name": "dennis1"}');
insert into emails (sender) VALUES ('{"email": "[email protected]", "name": "dennis1"}');

insert into contacts (email, full_name) VALUES ('[email protected]', 'dennis1');
insert into contacts (email, full_name) VALUES ('[email protected]', 'dennis1');
insert into contacts (email, full_name) VALUES ('[email protected]', 'dennis5');
insert into contacts (email, full_name) VALUES ('[email protected]', 'john');

Ожидаемый результат:

email                   name        rk      count

[email protected]     dennis1     1       4
[email protected]     dennis5     1       1
[email protected]        john        1       1

Тем не менее, у меня есть 2 проблемы с этим:

  1. INNER JOIN дает нулевой результат
  2. ORDER BY "count" не работает.

Что мне нужно?

Как видите, таблицы разные. Одна таблица имеет столбец jsonb, а другая хранится как text. Итак, я отдельно извлекаю их в каждом запросе SELECT, а затем сравниваю.

Итак, что мне нужно, это получить все электронные адреса и имена, сделать их уникальными, подсчитать их, если они дублируются, и ранжировать их. Мне не нужны повторяющиеся записи, а объединяю их в count.

Как я могу решить эту проблему?

Демо

См. демо здесь: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b79700f77c5f14e5b0d


postgresql sql postgresql-12
person Dennis    schedule 10.05.2020    source источник

Ответы (1)


arrow_upward
4
arrow_downward

извлеките json и объедините два набора данных перед группировкой и применением оконной функции.

WITH united as (
    SELECT email, full_name FROM contacts
    UNION ALL
    SELECT sender->>'email', sender->>'name' FROM emails
)
SELECT
  email
, full_name
, count(*) count, row_number() over (partition by email) rk
FROM united
GROUP BY 1, 2;
        email        | full_name | count | rk
---------------------+-----------+-------+----
 [email protected] | dennis1   |     4 |  1
 [email protected] | dennis5   |     1 |  1
 [email protected]    | john      |     1 |  1
(3 rows)
person Haleemur Ali    schedule 10.05.2020