Форум программистов, компьютерный форум CyberForum.ru

Сводная таблица - PostgreSQL

Войти
Регистрация
Восстановить пароль
 
Tester64
395 / 356 / 43
Регистрация: 22.05.2013
Сообщений: 2,488
07.12.2016, 14:04     Сводная таблица #1
Гуру, подскажите!

Есть ли ВООБЩЕ готовый/стандартный механизм для создания сводных таблиц на SQL (и в Postgresql в частности)???

Есть таблица на 3 столбца - покупатель/товар/количество

надо создать таблицу где вверху(название столбцов) идут покупатели (я ТОЧНО знаю что их меньше 100) (на английском без пробелов или в виде кодов "cl_1", cl_100")
а строками идут товары и количество под каждый покупателем....

(иногда выводить пользователю такое проще)

такое делается одной командой в Екселе.
Когда-то (долго думал) придумал как подобное сделать сложной функцией в цикле сгенерировав SQL строку, а потом ее выполнив...

а что-нибудь попроще/логичнее есть???
После регистрации реклама в сообщениях будет скрыта и будут доступны все возможности форума.
grgdvo
501 / 442 / 120
Регистрация: 02.09.2012
Сообщений: 1,286
07.12.2016, 14:11     Сводная таблица #2
Если я правильно понял ваш вопрос, то нужен этот модуль tablefunc
Tester64
395 / 356 / 43
Регистрация: 22.05.2013
Сообщений: 2,488
07.12.2016, 23:37  [ТС]     Сводная таблица #3
Цитата Сообщение от grgdvo Посмотреть сообщение
то нужен этот модуль tablefunc
Когда-то видел его...
вот пример с того сайта:
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE sales(YEAR INT, MONTH INT, qty INT);
INSERT INTO sales VALUES(2007, 1, 1000);
INSERT INTO sales VALUES(2007, 2, 1500);
INSERT INTO sales VALUES(2007, 7, 500);
INSERT INTO sales VALUES(2007, 11, 1500);
INSERT INTO sales VALUES(2007, 12, 2000);
INSERT INTO sales VALUES(2008, 1, 1000);
 
SELECT * FROM crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) AS (
  YEAR INT,
  "Jan" INT,
  "Feb" INT,
  "Mar" INT,
  "Apr" INT,
  "May" INT,
  "Jun" INT,
  "Jul" INT,
  "Aug" INT,
  "Sep" INT,
  "Oct" INT,
  "Nov" INT,
  "Dec" INT
);
 YEAR | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | DEC
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 ROWS)
Похоже надо ВРУЧНУЮ задавать список имен столбцов... а хотелось бы прямо из таблицы! если появится "13й месяц" в таблице - хотелось бы его АВТОМАТИЧЕСКИ найти и сделать столбцом! Типа из этой таблицы только 3 столбца можно сделать, а из этой 53...
grgdvo
501 / 442 / 120
Регистрация: 02.09.2012
Сообщений: 1,286
08.12.2016, 02:36     Сводная таблица #4
Можно нагородить работу с курсорами

SQL
1
2
DROP TABLE IF EXISTS sales;
CREATE TABLE sales(customer text, item text, qty INT);
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE FUNCTION mycrosstab(src_table text)
RETURNS refcursor AS
$$
DECLARE
  coldecl text;
  colcur refcursor;
  colrec record;
  cur refcursor;
  src_sql text := 'select item, customer, qty from sales order by 1';
  cat_sql text := 'select distinct customer from sales';
BEGIN
  coldecl := 'as (item text';
  OPEN colcur FOR EXECUTE cat_sql;
  loop
    fetch NEXT FROM colcur INTO colrec;
    exit WHEN NOT found;
    coldecl := coldecl || ', "' || colrec.customer || '" int';
  END loop;
  coldecl := coldecl || ')';
  close colcur;
  --raise notice '%', coldecl; --debug
  
  cur := 'pivot' || src_table;
  OPEN cur FOR EXECUTE 'SELECT * FROM crosstab('''||src_sql||''','''||cat_sql||''')'||coldecl;
  RETURN cur;
END;
$$
LANGUAGE plpgsql;
SQL
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO sales VALUES('Иванов', 'хлеб', 1000);
INSERT INTO sales VALUES('Иванов', 'молоко', 2000);
INSERT INTO sales VALUES('Иванов', 'колбаса', 3000);
INSERT INTO sales VALUES('Иванов', 'мясо', 4000);
INSERT INTO sales VALUES('Иванов', 'водка', 5000);
INSERT INTO sales VALUES('Петров', 'ириска', 6000);
INSERT INTO sales VALUES('Петров', 'водка', 7000);
INSERT INTO sales VALUES('Сидоров', 'хлеб', 8000);
INSERT INTO sales VALUES('Сидоров', 'молоко', 9000);
INSERT INTO sales VALUES('Сидоров', 'колбаса', 1000);
INSERT INTO sales VALUES('Сидоров', 'яблоко', 2000);
INSERT INTO sales VALUES('Сидоров', 'мандарин', 3000);
SQL
1
2
SELECT mycrosstab('sales');
fetch ALL FROM "pivotsales";
item text "Иванов" integer "Петров" integer "Сидоров" integer
водка50007000 
ириска 6000 
колбаса3000 1000
мандарин   
молоко2000 9000
мясо4000  
хлеб1000 8000
яблоко  2000

SQL
1
2
3
INSERT INTO sales VALUES('Новичок', 'арбуз', 1000);
INSERT INTO sales VALUES('Новичок', 'яблоко', 2000);
INSERT INTO sales VALUES('Новичок', 'мандарин', 3000);
SQL
1
2
SELECT mycrosstab('sales');
fetch ALL FROM "pivotsales";
item text "Иванов" integer "Петров" integer "Сидоров" integer "Новичок" integer
арбуз   1000
водка50007000  
ириска 6000  
колбаса3000 1000 
мандарин  30003000
молоко2000 9000 
мясо4000   
хлеб1000 8000 
яблоко  20002000

но как к этом отнесется ваш фронтэнд.
По хорошему курсоры бы закрывать надо, либо учитывать, что курсор сам закрывается при завершении транзакции.
Tester64
395 / 356 / 43
Регистрация: 22.05.2013
Сообщений: 2,488
08.12.2016, 02:49  [ТС]     Сводная таблица #5
Цитата Сообщение от grgdvo Посмотреть сообщение
Можно нагородить работу с курсорами
Все куда проще... можно создать функцию, которая "сделает всю работу за тебя"...

функция может создать строку, в цикле по запросу(select distinct head_field from...) ее "дозаполнить", а потом выполнить как один запрос и вернуть результа...

Я делал подобное! Параметрами функции идут таблица полей для имен, имя для колонки и поле для значений... на выходе получаю SQL строку с запросом на нужное количество left join и "исправленными" (пробелы убрать или в "f_"+count) названиями полей... долго игрался и "засоряю" систему лишней функцией.

Но вопрос был в "решении из коробки"... вчера знакомому дали задание сделать подобное в BigQuery(google) - тоже вручную 30 join рисовал... думал может "конструкцию" какую стандартную не заметил...
grgdvo
501 / 442 / 120
Регистрация: 02.09.2012
Сообщений: 1,286
08.12.2016, 05:40     Сводная таблица #6
Цитата Сообщение от Tester64 Посмотреть сообщение
Но вопрос был в "решении из коробки"
нет, решения из коробки мне не попадались.
вряд ли его в принципе можно ожидать.
- в SQL в принципе не предусмотрена такая возможность. SELECT всегда должен быть однозначным.
- а когда начинаете динамику городить, то нужно думать, как именовать поля, чтобы потом на клиенте срастить выбранные поля-категории. если вернуть из функции через SETOF или TABLE, все равно попросят поименовать поля результата

так что остается либо временная таблица, либо извраты с курсорами
30 джоинов это как-то ну совсем изврат, нельзя такие запросы составлять, уж лучше временная таблица
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
08.12.2016, 13:22     Сводная таблица
Еще ссылки по теме:

MS Excel Сводная таблица
SQL Server Сводная таблица
Сводная таблица MS Excel
Сводная таблица MS Excel
Сводная таблица MS Excel

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
Tester64
395 / 356 / 43
Регистрация: 22.05.2013
Сообщений: 2,488
08.12.2016, 13:22  [ТС]     Сводная таблица #7
Цитата Сообщение от grgdvo Посмотреть сообщение
30 джоинов это как-то ну совсем изврат, нельзя такие запросы составлять, уж лучше временная таблица
BigQuery не поддерживает "временных таблиц", не дает доступа из SQL-query к изменению таблиц, нет пользовательских функций и даже update ограничивает в бетта версии в 500 запросов в сутки! Хелп у них настолько слабый (как и "сообщество") что пробую искать функции сначала в Postgresql, а потом надеяться что подобное есть и у них... пока ТОЛЬКО Join... пока ручками генерим (для тестов) потом на Java генератор запроса писать придется...

...надеялся что найдя что-нибудь "из коробки" в Post... найду подобное и в BQ...
Yandex
Объявления
08.12.2016, 13:22     Сводная таблица
Ответ Создать тему
Опции темы

Текущее время: 19:12. Часовой пояс GMT +3.
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Рейтинг@Mail.ru