Блог

Главная - Вопросы по программированию - Запрос перекрестной таблицы Postgres для подсчета статусов по городу

Запрос перекрестной таблицы Postgres для подсчета статусов по городу

#sql #postgresql #pivot #crosstab

#sql #PostgreSQL #сводная #Перекрестная таблица

Вопрос:

Я пытаюсь выполнить sql-запрос в Postgres, для которого требуется перекрестная таблица. Я никогда раньше не использовал запросы перекрестных таблиц. Мои 3 таблицы показаны ниже:

ТАБЛИЦА местоположений:

location_id, lang_id, path

ТАБЛИЦА crm_statuses:

crm_status_id, crm_status_name

ТАБЛИЦА store_crm:

store_crm_id, status (ссылки в crm_status_id таблицы crm_statuses), location_id (ссылки в location_id таблицы местоположений)

Я хочу получить местоположения в виде столбцов из таблицы locations, присоединившись к таблице store_crm или, по крайней мере, записать их в жестком виде, поскольку их всего 3 (Лондон, Манчестер, Лидс). В виде строк я хочу получить статусы crm. В качестве содержимого я хочу подсчитать, сколько активных, неактивных и ожидающих магазинов есть в каждом местоположении. Активные, неактивные, ожидающие — это мои crm_statuses . Таблица желаемых результатов будет иметь следующий формат.

 Status    London  Manchester Leeds
Active    2       4          5
Inactive  6       1          3
Pending   4       4          5

Как я могу этого добиться?

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

1. Вы не должны использовать перекрестную таблицу для отображения подобной таблицы. Этого можно легко достичь с помощью простого цикла в php.

2. я должен сделать это в перекрестной таблице, потому что он будет выполняться в системе отчетов.

Ответ №1:

Вы можете жестко запрограммировать города как столбцы в следующем запросе:

 SELECT 
    * 
FROM crosstab('
        SELECT 
            cs.crm_status_name, 
            l.path, 
            COUNT(*)  
        FROM store_crm AS sc 
        JOIN locations AS l ON (sc.location_id=l.location_id) 
        JOIN crm_statuses AS cs ON (cs.crm_status_id=sc.status) 
        GROUP BY cs.crm_status_name, l.path 
        ORDER BY 1,2
    ', 
    '
        SELECT 
            path 
        FROM 
            locations
    ') AS f("Status" text, "London" text, "Manchester" text, "Leeds" text);

С результатом:

   Status  | London | Manchester | Leeds 
---------- -------- ------------ -------
 Active   | 1      | 2          | 1
 Inactive | 2      | 1          | 4
 Pending  |        | 1          | 1
(3 rows)

Если у вас есть такая схема базы данных:

 test=# d  locations
                                                    Table "public.locations"
   Column    |  Type   |                            Modifiers                            | Storage  | Stats target | Description 
------------- --------- ----------------------------------------------------------------- ---------- -------------- -------------
 location_id | integer | not null default nextval('locations_location_id_seq'::regclass) | plain    |              | 
 lang_id     | integer |                                                                 | plain    |              | 
 path        | text    |                                                                 | extended |              | 
Indexes:
    "locations_pkey" PRIMARY KEY, btree (location_id)
Referenced by:
    TABLE "store_crm" CONSTRAINT "store_crm_location_id_fkey" FOREIGN KEY (location_id) REFERENCES locations(location_id)

test=# d  crm_statuses
                                                       Table "public.crm_statuses"
     Column      |  Type   |                              Modifiers                               | Storage  | Stats target | Description 
----------------- --------- ---------------------------------------------------------------------- ---------- -------------- -------------
 crm_status_id   | integer | not null default nextval('crm_statuses_crm_status_id_seq'::regclass) | plain    |              | 
 crm_status_name | text    |                                                                      | extended |              | 
Indexes:
    "crm_statuses_pkey" PRIMARY KEY, btree (crm_status_id)
Referenced by:
    TABLE "store_crm" CONSTRAINT "store_crm_status_fkey" FOREIGN KEY (status) REFERENCES crm_statuses(crm_status_id)

test=# d  store_crm
                                                     Table "public.store_crm"
    Column    |  Type   |                            Modifiers                             | Storage | Stats target | Description 
-------------- --------- ------------------------------------------------------------------ --------- -------------- -------------
 store_crm_id | integer | not null default nextval('store_crm_store_crm_id_seq'::regclass) | plain   |              | 
 status       | integer |                                                                  | plain   |              | 
 location_id  | integer |                                                                  | plain   |              | 
Indexes:
    "store_crm_pkey" PRIMARY KEY, btree (store_crm_id)
Foreign-key constraints:
    "store_crm_location_id_fkey" FOREIGN KEY (location_id) REFERENCES locations(location_id)
    "store_crm_status_fkey" FOREIGN KEY (status) REFERENCES crm_statuses(crm_status_id)
Метки: