Как облегчить выбор по атрибутам?

Question

[synapse_people]

select
t1.value_int y,t2.value_int m,t3.value_int d,
t4.value_string utm_source,
k.value_int kind_int,
i.count cnt
from stat_item_attrs k
left join stat_item i on i.id = k.item_id
inner join stat_item_attrs t1 on t1.item_id = k.item_id and t1.attr_type = 1/*y*/
inner join stat_item_attrs t2 on t2.item_id = k.item_id and t2.attr_type = 2/*m*/
inner join stat_item_attrs t3 on t3.item_id = k.item_id and t3.attr_type = 3/*d*/
inner join stat_item_attrs t4 on t4.item_id = k.item_id and t4.attr_type = 4/*utm_source*/
where
	k.attr_type = 0/*kind*/ and k.value_int in(0,1)/*hosts,leads*/
    and
    t1.value_int = 20 and t2.value_int = 4 and t3.value_int = 4 /*20-04-04*/
    and
    t4.value_string = 'google'
;

Есть некая таблица с "количествами" - пусть для лучшего понимания будет количества - ХОСТЫ И ЛИДЫ (stat_item), у каждой записи есть атрибуты (stat_item_attrs), которые ее характеризуют (лежат в отдельной таблице). Каждый атрибут имеет вид (тип) и значение (цифра uint32). Отдельный атрибут kind характеризует тип самой записи в stat_item;
Задача: получить записи stat_item по значениям ее атрибутов.

Выше привел пример как получить интересующие количества по y.m.d и значению utm_source.
Вопрос:
1) Можно ли обойтись без кучи join.
Если нет : то просьба рассчитать приблизительно скорость выборки (? в стиле O...?) и порекомендуйте как нибудь это оптимизировать (думаю лагать будет очень сильно, если атрибутов будет 20+)
2) Как можно транспонировать таблицу?
сейчас получается , что все количества будут идти 1 запись = 1 строка
Получается типа такого:

y m d kind value
20 4 4 0 500
20 4 4 1 10
20 4 3 0 10
20 4 3 1 0

а хотелось бы структуру:
y m d kind0 value0 kind1 value1

Comments

[John Didact]

Скажу сразу, помочь не могу)) но мне просто интересно, EXPLAIN что рассказывает о плане выполнения? Подозреваю, что ничего хорошего... Можно посмотреть?

[John Didact]

Здесь таблица временная будет иметь количество полей в stat_item_attrs умноженное на 5 + количество полей stat_item

Answer 1

[Максим]

select
case when t1.attr_type = 1 and t1.value_int = 20 then t1.value_int end y,
case when t1.attr_type = 2 and t1.value_int = 4  then t1.value_int end m,
case when t1.attr_type = 3 and t1.value_int = 4  then t1.value_int end d,
case when t1.attr_type = 4 and t1.value_string = 'google'  then t1.value_string end utm_source,
k.value_int kind_int,
i.count cnt
from stat_item_attrs k
left join stat_item i on i.id = k.item_id
join stat_item_attrs t1 on t1.item_id = k.item_id and t1.attr_type in (1/*y*/,2/*m*/,3/*d*/4/*utm_source*/) and (t1.value_int in (20, 4 /*20-04-04*/) or t1.value_string = 'google')
where
  k.attr_type = 0/*kind*/ and k.value_int in(0,1)/*hosts,leads*/

Получится разряженная таблица, собрать/сгруперовать ее можно как угодно, max, sum ... например по полю k.item_id. Но тут писать не буду, сами посмотрите, уникальность и все такое. Такими же кейсами Вы можете выделить kind0 value0 kind1 value1, ну и потом сделать группировку. Но лучше сначала видеть данные а не слепо групировать
Кто писал селект который Вы прислали, там логика не та которую Вы написали под запросом. т.е. не понятно почему у Вас left Join к ОСНОВНОЙ по вашему описанию таблице. Почему главная тут stat_item_attrs ?
Я не буду лезть в логику, но тут лучше перейти на stat_item.id и убрать left join.

Comments

[synapse_people]

а хорошая ведь идея, попробую сделать так, отпишусь

[synapse_people]

Да, работает.
Но вопрос насчет кучи join'нов все еще открыт.... Ведь по сути оно будет каждый раз брать всю таблицу item и мерждить с аттрибутами.. Каждый раз
Могут ли какие-нибудь индексы здесь помочь?

inner join stat_item_attrs y on y.item_id = i.id and y.attr_type = 1/*y*/
inner join stat_item_attrs m on m.item_id = i.id and m.attr_type = 2/*m*/
inner join stat_item_attrs d on d.item_id = i.id and d.attr_type = 3/*d*/

Здесь мы видим , что база сначала найдет все атрибуты для Y, потом снова вынуждена будет итерироваться по всем записям, чтобы найти где M и еще раз, чтобы найти где D

[synapse_people]

финальный вариант (разумеется запрос будет длиннее, здесь лишь его часть, которую руками собрал, а полный будет генерироваться в зависимости от нужных аттрибутов):

select
y.value_int, m.value_int, d.value_int,
sum(case when k.value_int = 0 then i.count end) hosts_count,
sum(case when k.value_int = 1 then i.count end) leads_count,
sum(case when k.value_int = 1 then i.amount end) leads_amount
from stat_item i
inner join stat_item_attrs y on y.item_id = i.id and y.attr_type = 1/*y*/
inner join stat_item_attrs m on m.item_id = i.id and m.attr_type = 2/*m*/
inner join stat_item_attrs d on d.item_id = i.id and d.attr_type = 3/*d*/
inner join stat_item_attrs k on (k.item_id = i.id and k.attr_type = 0/*kind*/ and k.value_int in (0,1))
group by y.value_int, m.value_int, d.value_int
order by y.value_int desc, m.value_int desc, d.value_int desc

[synapse_people]

select
(case when a.attr_type = 1 then a.value_int end) y,
(case when a.attr_type = 2 then a.value_int end) m,
(case when a.attr_type = 3 then a.value_int end) d,
sum(case when a.attr_type = 0 and a.value_int = 0 then i.count end) hosts_count,
sum(case when a.attr_type = 0 and a.value_int = 1 then i.count end) leads_count,
sum(case when a.attr_type = 0 and a.value_int = 1 then i.amount end) leads_amount
from stat_item i
inner join stat_item_attrs a on 
a.item_id = i.id and (
	(a.attr_type = 0 and a.value_int in (0,1))
	or
    a.attr_type in (1, 2,3)
)

group by y,m,d

такой вариант не работает из-за того, что y будет заполнен лишь в 1 строке из всех(у строки где атрибут будет m, то Y будет NULL)
Получается с ключевыми атрибутами нужно обязательно выполнить join, а с остальным (которое нужно посчитать) сойдет трюк с in+case..

[Максим]

synapse_people, Если Вы хотите один join то тогда или двойную групировку или оконные фунции partition by i.id

select y, m, d, sum(hosts_count) hosts_count, sum(leads_count) leads_count, sum(leads_amount) leads_amount
from ( select  
          max(case when a.attr_type = 1 then a.value_int end) y,
          max(case when a.attr_type = 2 then a.value_int end) m,
          max(case when a.attr_type = 3 then a.value_int end) d,
          sum(case when a.attr_type = 0 and a.value_int = 0 then i.count end) hosts_count,
          sum(case when a.attr_type = 0 and a.value_int = 1 then i.count end) leads_count,
          sum(case when a.attr_type = 0 and a.value_int = 1 then i.amount end) leads_amount
          from stat_item i
          inner join stat_item_attrs a on 
          a.item_id = i.id and (
            (a.attr_type = 0 and a.value_int in (0,1))
            or
              a.attr_type in (1, 2,3)
                                        )
          group by  i.id) t1
group by y,m,d