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

PostgreSQL

Войти
Регистрация
Восстановить пароль
 
Tester64
396 / 357 / 43
Регистрация: 22.05.2013
Сообщений: 2,518
#1

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

07.12.2016, 14:04. Просмотров 286. Ответов 6
Метки нет (Все метки)

Гуру, подскажите!

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

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

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

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

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

а что-нибудь попроще/логичнее есть???
0
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
07.12.2016, 14:04
Здравствуйте! Я подобрал для вас темы с ответами на вопрос Сводная таблица (PostgreSQL):

Таблица для БД - Базы данных
Помогите корректно составить таблицу в БД (внизу я написал примерно как хотел бы сделать, но не знаю правильно это). Потом хочу написать...

Связующая таблица - Базы данных
Добрый день. С моим знанием БД(или точнее, не знанием) возникла проблема ее проектировки. Пока что имеется 3 таблицы: Преподаватель,...

Архив как таблица - Базы данных
Правильно ли создавать таблицу как архив из какой-то существующей таблицы. В эту архивную добавиться ключевое поле и поле даты или лучше...

Является ли таблица Excel бд - Базы данных
В ходе выполнения курсача возникло пару вопросов. У меня имеется таблица: При этом допускается оставлять значение каких либо ячеек...

Cache таблица связка - Базы данных
Здравствуйте. Вопрос следующий. Имеются таблицы: 1. Продавец 2. Покупатель 3. Транспорт 4. Мотоциклы 5. Машины 6. Акт продажи...

Нужна заполненная таблица - Базы данных
Нужно 100 строк и 5-6 колонок с заполненными данными, у кого есть скиньте буду признателен.. Добавлено через 11 часов 44 минуты Все...

Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
grgdvo
558 / 494 / 140
Регистрация: 02.09.2012
Сообщений: 1,443
07.12.2016, 14:11 #2
Если я правильно понял ваш вопрос, то нужен этот модуль tablefunc
1
Tester64
396 / 357 / 43
Регистрация: 22.05.2013
Сообщений: 2,518
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...
0
grgdvo
558 / 494 / 140
Регистрация: 02.09.2012
Сообщений: 1,443
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

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

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

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

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

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

...надеялся что найдя что-нибудь "из коробки" в Post... найду подобное и в BQ...
0
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
08.12.2016, 13:22
Привет! Вот еще темы с ответами:

В БД есть таблица с регистрацией клиентов - Базы данных
В БД есть таблица с регистрацией клиентов вида CustomerId, RegistrationDateTime и таблица с покупками клиентов вида CustomerId,...

Есть ли ошибки?Таблица и связи - Базы данных
Перед тем как сделать нормализацию, хотелось бы узнать о наличии ошибок.

Pivot таблица с динамичными столбцами - PostgreSQL
Помогите пожалуйта, нужно создать функцию, которая возвращяет сводную таблицу с динамичними столбцами. Очень срочно, помогите:cry: ...

Иерархическая таблица и запрос к ней - Базы данных
Здравствуйте. Имеется таблица с иерархией с полями ID Категории, Название категории, ID Родительской категории, Номер уровня. Как нужно...


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

Или воспользуйтесь поиском по форуму:
Yandex
Объявления
08.12.2016, 13:22
Ответ Создать тему
Опции темы

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