Показаны сообщения с ярлыком PIVOT. Показать все сообщения
Показаны сообщения с ярлыком PIVOT. Показать все сообщения

пятница, 13 июля 2012 г.

Пивот (PIVOT) – это разворот таблицы


Хочу поднят один вопрос обсуждаемый больше на англ. языке чем у нас.

Описание принципа здесь http://easy4web.ru/?p=1099

По сути мы столбцы переносим в колонки.
В чем задача? Она схожа с обсуждаемым недавно здесь вопросом http://sqlinfo.ru/forum/viewtopic.php?id=4252 + небольшое отличие
Есть упрощенно такая структура таблиц (каталог товаров, с делением по характеристикам):

CREATE TABLE categories (category_id, ... );
CREATE TABLE products (product_id, category_id,...);
CREATE TABLE properties (property_id, ...);
CREATE TABLE products2properties  (category_id, property_id);
CREATE TABLE properties_values (product_id, property_id, value);
 
products2properties   - моя таблица. Нужна потому что товары из разных категории имеют разный набор характеристик на всю категорию.

Необходимо вывести первые 10 товаров с их характеристиками + сортировка и поиск по любой характеристики.

Решение SELECT .. SELECT ...SELECT конечно же не приемлемо. Решать это средствами PHP накладно.

Давайте вместе подумаем как сделать оптимально!

Решение 1:
http://stackoverflow.com/questions/6498 … lue-schema
Средствами PHP собираем запрос из кучи JOIN.
Я думаю сервер от такого рухнит.

Решение 2:
что то умное предлагаю здесь но я не понимаю сленга)
http://bb.ct.kz/topic/189790-sql-vivest … p__7088923
Цитата того что понимаю:
Если столбцов количество неизвестно то только динамик скул...
Приблизительный алгоритм...
1. Получаем параметры...
2. Создаем запрос...
3. тут можно или представление создать или временную таблицу...
4. если временная таблица... заполняем данными...
5. с клиента пишем select * from vwView(ttTable)

Как вариант... тот же самый пайвотинг инсерт...
Решение 3:
То которое я сейчас рассматриваю...
http://www.orafaq.com/forum/m/30557/684 … #msg_30557
Проблема что оно вроде для ОРАКЛа т.к. у  меня выдает ошибку на название команд.

SQL> CREATE OR REPLACE PACKAGE pkg
  2  IS
  3      TYPE t_rc IS REF CURSOR;
  4  END pkg;
  5  /

Package created.

SQL> CREATE OR REPLACE PROCEDURE dynamic_pivot (rc IN OUT pkg.t_rc)
  2  IS
  3      l_count     NUMBER;
  4      l_sql       VARCHAR2(32000);
  5  BEGIN
  6      SELECT   MAX(COUNT(*))
  7      INTO     l_count
  8      FROM     b
  9      GROUP BY b.id;
 10
 11      l_sql := 'SELECT x.id, x.a_name';
 12
 13      FOR i IN 1..l_count LOOP
 14          l_sql := l_sql
 15                   || ',MAX(DECODE(x.rn,'
 16                   || TO_CHAR(i)
 17                   || ',x.b_value)) val'
 18                   || TO_CHAR(i);
 19      END LOOP;
 20      l_sql := l_sql
 21               || ' FROM (SELECT a.id, a.name a_name'
 22               || ',b.value b_value'
 23               || ',ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY NULL) rn'
 24               || ' FROM a, b WHERE a.id = b.id) x GROUP BY x.id, x.a_name'
 25               || ' ORDER BY x.id';
 26      OPEN rc FOR l_sql;
 27  END dynamic_pivot;
 28  /

Procedure created.

SQL> VARIABLE my_rc REFCURSOR
SQL> EXEC dynamic_pivot(:my_rc);

PL/SQL procedure successfully completed.

SQL> PRINT :my_rc

        ID A_NAM VAL1  VAL2  VAL3  VAL4  VAL5  VAL6  VAL7
---------- ----- ----- ----- ----- ----- ----- ----- -----
       100 test  val1  val2
       101 hello aloha hi    wave  smile hey   hola  g'day



Временный итог:
Думаю может сделать процедуру для Мускула как в решении 3. Повесить триггер на изменение таблиц товаров что бы дергал эту процедуру пересчета (в идеале только того товара/категории которая изменилась). Процедура будет создавать таблицу Х удобную для простой выборки.

Ваше мнение? Как это всё написать? Я ни триггеры ни процедуры ни разу не писал....


Информация по теме:
http://ru.w3support.net/index.php?db=so&id=778079
http://www.sql.ru/faq/faq_topic.aspx?fid=210
http://forums.mysql.com/read.php?98,7000,7000
http://dev.mysql.com/tech-resources/art … rsion.html
http://en.wikibooks.org/wiki/MySQL/Pivot_table