kpushalex564657498765453
1.
IFNULL(item.item_group_id,offer_item_group.item_group_id) AS item_group_id,
для вычисления offer_item_group.item_group_id нихилый такой подзапрос...
это точно надо?
иногда добавляют строчку счета не из заявки, (т.е. equipment.item с item_group_id Null окажется)
когда например доставку в счет вставляют или сборку или хрень какую то дополнительную.
Тогда делаем предположение, что расход по этому должен нести тот кто заказывал все остальное в этом счете.
Такая же конструкция с доставками, нам все, что вошло в один счет и попало в одну доставку "вешается" на один заказ.
Пробовал это выкидывать на уровень верхних (не указанных здесь запросов)
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
(select
item_offer.id as item_offer_id,
offer_item_group.item_group_id as assumption_item_group_id,
round(if(item_offer.vat = 0, item_offer.amount*1.18, item_offer.amount),2) as "val_0_sum",
0 as "val_1_sum",
0 as "val_2_sum",
date(offer.last_accept_date) as `date`
FROM equipment.item_offer item_offer
join equipment.offer on (offer.id = item_offer.offer_id)
left join
( select offer_accept.offer_id as offer_id
from equipment.offer_accept
where offer_accept.allow <> 1) offer_accepted on offer_accepted.offer_id = offer.id
join equipment.item on (item.id = item_offer.item_id)
left join ( SELECT
item_offer.offer_id as offer_id,
max(item.item_group_id) as item_group_id
FROM equipment.item_offer item_offer
join equipment.item item on (item.id = item_offer.item_id)
group by item_offer.offer_id )offer_item_group on
(offer_item_group.offer_id = item_offer.offer_id)
where
offer.deleted = 0 and
item_offer.state not in ('DECLINED','MISSED') and
offer_accepted.offer_id is null and
offer.last_accept_date is not null
)
union all (SELECT
item_offer.id as item_offer_id,
offer_item_group.item_group_id as assumption_item_group_id,
0 as "val_0_sum",
round(if(item_offer.vat = 0, item_offer.amount*1.18, item_offer.amount)*offer_payment.amount/offer_sum.offer_sum,2) as "val_1_sum",
0 as "val_2_sum",
offer_payment.payment_date as `date`
FROM equipment.item_offer item_offer
join equipment.offer on (offer.id = item_offer.offer_id)
join (SELECT
offer.id as offer_id,
sum(if(item_offer.vat = 0, item_offer.amount*1.18, item_offer.amount)) as offer_sum
FROM equipment.item_offer item_offer
join equipment.offer offer on (item_offer.offer_id = offer.id )
where
offer.deleted = 0 and
item_offer.state not in ('DECLINED','MISSED')
group by offer.id)offer_sum on (offer_sum.offer_id = item_offer.offer_id)
join equipment.offer_payment offer_payment on (offer_payment.offer_id = item_offer.offer_id)
left join ( SELECT
item_offer.offer_id as offer_id,
max(item.item_group_id) as item_group_id
FROM equipment.item_offer item_offer
join equipment.item item on (item.id = item_offer.item_id)
group by item_offer.offer_id )offer_item_group on
(offer_item_group.offer_id = item_offer.offer_id)
where
offer.deleted = 0 and
item_offer.state not in ('DECLINED','MISSED') and
offer_payment.payment_date is not null
#delivery
)
UNION ALL
(
SELECT
item_offer.id as item_offer_id,
delivery_item_group.item_group_id as assumption_item_group_id,
0 as "val_0_sum",
0 as "val_1_sum",
round(if(item_offer.vat = 0, item_offer.price*delivery_item.delivered_count*1.18, item_offer.price*delivery_item.delivered_count),2) as "val_2_sum",
date(delivery.date) as `date`
FROM equipment.item_offer item_offer
join equipment.delivery_item delivery_item on (item_offer.id = delivery_item.item_offer_id)
join equipment.delivery delivery on (delivery.id = delivery_item.delivery_id)
left join ( SELECT
delivery_item.delivery_id as delivery_id,
item_offer.offer_id as offer_id,
max(item.item_group_id) as item_group_id
FROM equipment.delivery_item delivery_item
join equipment.item item on (item.id = delivery_item.item_id)
join equipment.item_offer item_offer on (item_offer.id = delivery_item.item_offer_id)
join equipment.delivery delivery on (delivery.id = delivery_item.delivery_id)
group by delivery_item.delivery_id, item_offer.offer_id )delivery_item_group on
(delivery_item_group.delivery_id = delivery_item.delivery_id) and (delivery_item_group.offer_id = item_offer.offer_id)
where delivery.delivered = 1
and delivery_item.delivered_count > 0
)
, скорость не изменилась.
Сейчас выкинул вообще эти под запросы, стало 7 секунд, а отдельные запросы вместе взятые меньше 1 секунды.
Придумываю, как этим воспользоваться, что бы улучшить показатели.
