Блог

Главная - Вопросы по программированию - Подсчет организаций, с которыми пользователь не связался

Подсчет организаций, с которыми пользователь не связался

#sql #postgresql #count #left-join #inner-join

#sql #postgresql #подсчет #левое соединение #внутреннее соединение

Вопрос:

Я новичок в PostgreSQL. В моей схеме созданы следующие таблицы:

Таблица пользователя:

 CREATE TABLE public.users
(
    user_id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
    first_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    last_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    email citext COLLATE pg_catalog."default" NOT NULL,
    user_password character varying(90) COLLATE pg_catalog."default" NOT NULL,
    bt_id integer,
    reset_password_token character varying COLLATE pg_catalog."default",
    bstage_id integer,
    CONSTRAINT users_pkey PRIMARY KEY (user_id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT bstage_id FOREIGN KEY (bstage_id)
        REFERENCES public.business_stage (bstage_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT bt_id FOREIGN KEY (bt_id)
        REFERENCES public.business_type (bt_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)

Таблица организаций:

 CREATE TABLE public.organization
(
    org_id integer NOT NULL DEFAULT nextval('organization_org_id_seq'::regclass),
    name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    description character varying(90) COLLATE pg_catalog."default" NOT NULL,
    email citext COLLATE pg_catalog."default" NOT NULL,
    phone_number character varying(11) COLLATE pg_catalog."default" NOT NULL,
    bt_id integer NOT NULL,
    bs_id integer NOT NULL,
    is_active boolean NOT NULL,
    org_link character varying COLLATE pg_catalog."default",
    CONSTRAINT organization_pkey PRIMARY KEY (org_id),
    CONSTRAINT bs_id FOREIGN KEY (bs_id)
        REFERENCES public.business_step (bs_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT bt_id FOREIGN KEY (bt_id)
        REFERENCES public.business_type (bt_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)

И, наконец,
Таблица Organization_rating:

 CREATE TABLE public.organization_rating
(
    rating integer NOT NULL,
    user_id integer NOT NULL,
    organization_id integer NOT NULL,
    rating_comment character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT organization_rating_pkey PRIMARY KEY (user_id, organization_id),
    CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT stars CHECK (rating >= 1 AND rating < 5)
)
WITH (
    OIDS = FALSE
)

С помощью этой схемы организация имеет бизнес-тип, в котором предоставляет поддержку пользователю, который также имеет этот бизнес-тип. После предоставления этой поддержки пользователь может оценить эту организацию с помощью organization_rating . Следуя этой логике, я хотел бы выполнить запрос, который дает мне процент организаций, с которыми пользователь связался, по сравнению с организациями, которые пользователь все еще должен оценить. Например, допустим, идентификатор пользователя 1 оценивает организацию 1, которая имеет тот же бизнес-тип, что и пользователь. еще 9 организаций имеют тот же бизнес-тип, но пользователь не оценил эти организации. Этот запрос вернет 10%.

У меня есть следующий запрос для подсчета организаций, с которыми пользователь уже связался:

 Select U.first_name, COUNT(R.rating)
From users as U INNER JOIN organization_rating as R on U.user_id = R.user_id
Inner join organization as O on O.org_id = R.organization_id
Where O.bt_id = 1 AND R.user_id != 62
Group by U.first_name;

Как я могу подсчитать организации, с которыми этот пользователь не связывался, и процентное соотношение с теми, с кем еще предстоит связаться?

Если бы вы могли также направить меня на некоторые сайты, которые могут помочь мне узнать больше о PostgreSQL и различных функциях, я был бы признателен. Заранее благодарю вас!

Комментарии:

1. Вам нужно использовать внешнее соединение, чтобы вернуть все организации независимо от контакта. затем создайте оператор case и агрегацию, которая показывает количество контактов, а не количество контактов, а затем выполните вычисления. Нулевые значения в значениях контактов означают отсутствие контакта и должны рассматриваться как ноль при выполнении математики Помните, что внутренние соединения показывают записи только в том случае, если они существуют в обеих таблицах: что не будет истинным, если контакт не существует. Таким образом, необходим внешний.

Ответ №1:

Вы можете cross join использовать и организации, чтобы получить все возможные комбинации, а затем привести таблицу мостов с помощью a left join . Последний шаг — условная агрегация:

 SELECT u.first_name, 
    COUNT(*) FILTER (WHERE r.user_id IS NULL) as cnt_not_contacted,
    AVG((r.user_id IS NOT NULL)::int) as avg_contacted
FROM users u
CROSS JOIN organization o
LEFT JOIN organization_rating r ON u.user_id = r.user_id AND o.org_id = r.organization_id
WHERE o.bt_id = 1 AND u.user_id <> 62
GROUP BY u.user_id
Метки: ,