SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 27.05.2011 13:35:47

AvtoNom
Завсегдатай
Зарегистрирован: 20.11.2008
Сообщений: 33

Пивот (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

Отредактированно AvtoNom (27.05.2011 17:27:28)


Похоже я здесь надолго
=8-D

Неактивен

 

#2 27.05.2011 15:38:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6578

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

Можете вытащить полный список характеристик для всех 10 товаров,
потом подготовить запрос (PREPARE) и выполнить его.

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

Неактивен

 

#3 27.05.2011 17:30:17

AvtoNom
Завсегдатай
Зарегистрирован: 20.11.2008
Сообщений: 33

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

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


Похоже я здесь надолго
=8-D

Неактивен

 

#4 28.05.2011 22:55:56

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

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

AvtoNom у вас какая то конкретная ситуация или просто теоретически интересуетесь ?
Если конкретная, то опишите её, возможно есть другие более практичные варианты.

Если теоретически интересуетесь, то действительно можно сделать такой некий механизм кеша.

Создать процедуру создающую динамичную пивот таблицу. Для лучшей производительности это может даже MEMORY TABLE.

Повесить триггер на изменение таблиц товаров что бы дергал эту процедуру пересчета

Пересоздавать её при каждом изменении ? А если изменения полетят один за другим ? Ваши тяжёлые селекты положат mysql.

Наверно тут более правильным решением будет по триггеру обновлять дату последнего обновления хранящеюся в отдельной таблице.
А потом крон бегающий раз в 5 минут, будет проверять дату, и запускать вашу процедуру если есть надобность.

Неактивен

 

#5 02.06.2011 14:48:49

AvtoNom
Завсегдатай
Зарегистрирован: 20.11.2008
Сообщений: 33

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

evgeny написал:

у вас какая то конкретная ситуация или просто теоретически интересуетесь ?

Практическая. Есть таблицы зданий и помещений у них динамическое количество характеристик => 2 таблицы самих свойств и 2 таблицы для хранения значений свойств для зданий и помещений.
Проблема в создании таблицы каталога в которой есть фильтр и сортировка по любому из свойств.

evgeny написал:

Создать процедуру создающую динамичную пивот таблицу. Для лучшей производительности это может даже MEMORY TABLE.

пока сделал пивот MEMORY TABLE.
если она пустая то скрипт генерирует пивот запрос и заполняет её. Я решил не нагружать триггером БД.
Жаль что в MEMORY TABLE нельзя сделать LIKE по индексу из-за ограничений этого типа.

Ещё не уверен в правильности решения. И ещё не придумал что делать при обновлении значения в любой из двух таблиц.


Буду всем благодарен за советы!


Похоже я здесь надолго
=8-D

Неактивен

 

#6 02.06.2011 16:53:53

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6578

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

Почему нельзя? Сделайте ключик BTREE и используйте?

Неактивен

 

#7 09.09.2016 17:41:50

mo0n
Участник
Зарегистрирован: 09.09.2016
Сообщений: 6

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

Здравствуйте! Подскажите пожалуйста как можно перевернуть данные?
Простейший пример:
ID     VALUE             NAME
555     тестовый       комментарий
555        158           какое-то число     
555    09.09.2016     текущая дата
Необходимо получить:
ID        VALUE             комментарий    какое-то число     текущая дата
555         ?                    тестовый               158              09.09.2016 
Если можно, то с комментариями, а то никак не догоню, как все-таки это сделать.
Сразу скажу, что для каждого ID будет одинаковое кол-о строк, в данном примере 3. Сколько же всего будет даных неизвестно, они обновляются постоянно.
Спасибо.

Неактивен

 

#8 09.09.2016 17:54:48

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

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

http://easy4web.ru/?p=1099 смотрели?

Неактивен

 

#9 12.09.2016 11:38:24

mo0n
Участник
Зарегистрирован: 09.09.2016
Сообщений: 6

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

vasya написал:

http://easy4web.ru/?p=1099 смотрели?

Прочитал, большое спасибо, хоть представление появилось. А не подскажите тоже самое, только для текстовых значений? С числами все получилось.
Я использовал заместо sum - max, но не уверен, что это правильно,хотя все получилось.
UPD: Получилось только для одной записи, для списка к сожалению не работает sad

Отредактированно mo0n (12.09.2016 12:54:53)

Неактивен

 

#10 12.09.2016 16:51:39

mo0n
Участник
Зарегистрирован: 09.09.2016
Сообщений: 6

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

Разобрался таки, помогла инфа отсюда: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Мой селект:
select
t1.ID,
t1.IBLOCK_ID,
t1.DATE_CREATE,
t1.CREATED_BY,
t4.NAME,
t4.LAST_NAME,
t1.TIMESTAMP_X,
t5.NAME,
t5.LAST_NAME,
t1.IBLOCK_ID,
t2.ID,
t2.IBLOCK_PROPERTY_ID,
t2.VALUE,
t3.NAME,
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=814, t2.VALUE, NULL)) as ФИО,
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=815, t2.VALUE, NULL)) as 'Дата рождения',
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=817, t2.VALUE, NULL)) as Телефон,
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=820, t2.VALUE, NULL)) as Регион,
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=821, t2.VALUE, NULL)) as Подключено,
GROUP_CONCAT(if(t2.IBLOCK_PROPERTY_ID=822, t2.VALUE, NULL)) as Комментарий
from b_iblock_element as t1
left join b_iblock_element_property t2 on t1.ID = t2.IBLOCK_ELEMENT_ID
left join b_iblock_property t3 on t2.IBLOCK_PROPERTY_ID = t3.ID
left join b_user t4 on t1.CREATED_BY = t4.ID
left join b_user t5 on t1.MODIFIED_BY = t5.ID
/*where t1.ID = 97527*/
where t1.IBLOCK_ID=252
GROUP BY t1.ID

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson