Pivot з / без Tablefunc

Зведена таблиця - це потужний інструмент для оцінки, компіляції та перегляду даних, щоб ще легше знаходити закономірності та тенденції. Зведені таблиці можна використовувати для агрегування, сортування, упорядкування, перегрупування, групування, загальної чи середньої кількості даних у наборі даних для справжнього розуміння асоціацій та залежностей даних. Використання зведеної таблиці як ілюстрації - це найпростіший спосіб продемонструвати, як працює цей метод. PostgreSQL 8.3 був випущений кілька років тому, і з'явилася нова версія під назвою 'tablefunc'було додано. Tablefunc є компонентом, що містить кілька методів, що дають таблиці (тобто кілька рядків). Ця модифікація має дуже цікавий спектр функцій. Серед них є метод перехресних таблиць, який буде використовуватися для створення зведених таблиць. Метод перехресних таблиць приймає текстовий аргумент: команда SQL, яка повертає необроблені дані в першому макеті і повертає таблицю в наступному макеті.

Приклад зведеної таблиці без TableFunc:

Щоб почати працювати над обертанням PostgreSQL за допомогою модуля 'tablefunc', вам потрібно спробувати зробити зведену таблицю без нього. Отже, давайте відкриємо оболонку командного рядка PostgreSQL і надамо значення параметрів для необхідного сервера, бази даних, номера порту, імені користувача та пароля. Залиште ці параметри порожніми, якщо ви хочете використовувати вибрані параметри за замовчуванням.

Ми створимо нову таблицю під назвою 'Test' у базі даних 'test' з деякими полями в ній, як показано нижче.

>> Тест СТВОРИТИ ТАБЛИЦЮ (Id int, ім'я varchar (20), sal int, завдання varchar (20));

Після створення таблиці настав час вставити в неї деякі значення, як показано з нижченаведеного запиту.

>> ВСТАВИТИ В ТЕСТ (Ідентифікатор, ім'я, ім'я, робота) ЗНАЧЕННЯ (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, 'Лікар'), (12, 'Раза', 40000, 'Офіцер'), (11, 'Раза', 60000, 'Лікар'), (12, 'Раза', 67000, 'Офіцер'), ( 13, 'Saeed', 85000, 'Writer'), (13, 'Saeed', 69000, 'Officer'), (13, 'Saeed', 90000, 'Doctor');

Ви бачите, що відповідні дані було успішно вставлено. Ви бачите, що ця таблиця містить більше 1 однакових значень для ідентифікатора, імені та завдання.

>> ВИБЕРІТЬ * ВХІД;

Давайте створимо зведену таблицю, яка підсумує запис таблиці "Тест", використовуючи запит нижче. Команда об'єднує однакові значення стовпця "Ідентифікатор" та "Ім'я" в одному рядку, беручи суму значень стовпців "Оклад" для тих самих даних відповідно до "Ідентифікатора" та "Ім'я". Це також говорить про те, скільки разів сталося одне значення в певному наборі значень.

>> ВИБЕРІТЬ ідентифікатор, ім'я, суму (sal) sal, sum ((job = 'Doctor') :: int) Doctor, sum ((job = 'Writer') :: int) Writer, sum ((job = 'Officer ') :: int) "Офіцер" ІЗ тестової групи за ідентифікатором, ім'ям;

Приклад зведеної таблиці з TableFunc:

Почнемо з пояснення нашого головного моменту з реалістичної точки зору, а потім опишемо створення зведеної таблиці за кроками, які нам подобаються. Отже, перш за все, вам потрібно додати три таблиці, щоб працювати над зведенням. Перша таблиця, яку ми збираємося створити, - це «Макіяж», в якій буде зберігатися інформація про основи макіяжу. Спробуйте наведений нижче запит у оболонці командного рядка, щоб створити цю таблицю.

>> СТВОРИТИ ТАБЛИЦЮ, ЯКЩО НЕ ІСНУЄ Макіяж (make_Id int PRIMARY KEY, p_name VARCHAR (100) NOT NULL);

Після створення таблиці "Макіяж", додамо до неї кілька записів. Ми виконаємо вказаний нижче запит у оболонці, щоб додати 10 записів до цієї таблиці.

Нам потрібно створити ще одну таблицю з назвою "користувачі", яка буде зберігати записи користувачів, які використовують ці продукти. Виконайте вказаний нижче запит у оболонці, щоб створити цю таблицю.

>> СТВОРИТИ ТАБЛИЦЮ, ЯКЩО НЕ ІСНУЄ користувачів (user_id int ОСНОВНИЙ КЛЮЧ, u_name varchar (100) НЕ НУЛЬНИЙ);

Ми вставили 20 записів для таблиці "користувачі", як показано на малюнку нижче.

У нас є інша таблиця "makeup_user", яка буде містити взаємні записи як таблиці "Makeup", так і "users". У нього є ще одне поле, "ціна", яке заощадить ціну товару. Таблиця була сформована за допомогою заявленого нижче запиту.

>> СТВОРИТИ ТАБЛИЦЮ, ЯКЩО НЕ ІСНУЄ makeup_user (ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup (make_Id), Uid int NOT NULL REFERENCES користувачі (user_id), ціна десяткова (18,2));

Ми вклали в цю таблицю 56 записів, як показано на малюнку.

Давайте створимо подання далі, щоб використовувати його для створення зведеної таблиці. У цьому поданні використовується ВНУТРІШНЄ Приєднання для збігу значень стовпців первинного ключа всіх трьох таблиць та отримання "імені", "імені_продукту" та "вартості" товару із таблиці "клієнти"

>> СТВОРИТИ ПЕРЕГЛЯД v_makeup_users ЯК ВИБЕРІТЬ c.u_name, стор.p_name, шт.ціна ВІД користувачів c ВНУТРІШНЄ ПРИЄДНАННЯ makeup_user pc ON c.user_id = шт.Uid ВНУТРІШНЄ СПІЛКУВАННЯ макіяж р НА ПК.Середина = с.make_Id;

Щоб використовувати це, вам слід спочатку встановити пакет tablefunc для бази даних, яку ви хочете використовувати. Цей пакет є вбудованим PostgreSQL 9.1 і пізніше випущений за допомогою вказаної нижче команди. Зараз для вас увімкнено пакет tablefunc.

>> СТВОРИТИ РОЗШИРЕННЯ, ЯКЩО НЕ ІСНУЄ tablefunc;

Після створення розширення настав час використовувати функцію Crosstab () для створення зведеної таблиці. Отже, ми будемо використовувати для цього наступний запит у оболонці командного рядка. Цей запит спочатку отримує запис із новоствореного "Перегляду". Ці записи будуть упорядковані та згруповані за зростанням за стовпцями стовпців "ім'я_ім'я" та "ім'я_ p_". У таблиці ми вказали їхню назву косметики для кожного покупця, якого вони придбали, а також загальну вартість придбаних товарів. Ми застосували оператор UNION ALL у стовпці 'p_name' для підсумовування всіх продуктів, придбаних одним замовником окремо. Це підсумує всі витрати на товари, придбані користувачем, в одне значення.

Наша зведена таблиця готова і відображається на зображенні. Ви можете чітко помітити, що деякі простори стовпців порожні під кожним p_name, оскільки вони не придбали цей товар.

Висновок:

Зараз ми блискуче навчилися створювати зведену таблицю для підведення підсумків результатів таблиць з використанням пакету Tablefunc та без нього.