Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / запрос не для впечатлительных, +21 / 15 сообщений из 15, страница 1 из 1
10.11.2013, 15:53:20
    #38459350
aleXVoipp
aleXVoipp
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Дано :
таблицы
Countries, Customers, Sales, Products
, они имеют ключи
country_id, customer_id, sale_id,product_id
. Отношение Страны - покупатели - один ко многим, покупатели - покупка - один ко многим, продукты - покупка один ко многим. Цена товара
product_price
лежит в Products, там же лежит категории товаров(1,2,3).
Countries содержит так же и имена стран.
Найти :
распределение общего количества покупок категории "1" с распределением по странам (т.е. распределение по странам, в которых проживают клиенты), в конечной выборке оставить те страны, в которых общая сумма покупок выше, чем средняя сумма по всему миру.
Неудачное решение:
Вот код:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT COUNT(p1.product_id), co1.country_id
FROM Countries co1 JOIN Customers cu1 ON co1.country_id=cu1.country_id JOIN Sales s1 ON s1.cust_id=cu1.cust_id JOIN Products p1 ON                             
        p1.prod_id = s1.prod_id  
WHERE p1.category='1' 
AND
       /я не знаю что тут писать/
       SELECT avg(prod_sum) AS average FROM
       SELECT SUM(p1.product_price) AS prod_sum , co1.country_id
       FROM Countries co1 JOIN Customers cu1 ON co1.country_id=cu1.country_id JOIN Sales s1 ON s1.cust_id=cu1.cust_id JOIN Products p1 ON                             
       p1.prod_id = s1.prod_id
       GROUP BY co1.country_id    
GROUP BY co1.country_id



Призыв : помогите!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2013, 19:47:52
    #38459523
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
aleXVoipp,

Первая задача -- подсчитать неколько агрегатов
по странам --- вполне обычное задание -- неколько
джоинтов , SUM, AVG, COUNT и груп бу по стране.

А вот второе задание -- выбрать только те страны у которых
авераже выше среднего -- надо подумать.

Простейшее решение -- материализовать (можно как MEMORY)
таблицу из первого запроса , потом отдельно посчитать
среднее и отдельно отобрать страны которые выше среднего.

Если вам нужен ресультат -- то сделайте через промежуточную таблицу.

Если вы хотите поизврашатся (осторожно, BDSM, +21),
кажется есть варианты где не надо будет материализовать
исходный результат и не надо будет его два раза просчитытвать.

Эти варианты: WITH ROLL UP и/или один-два вложеных запроса
на переменных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2013, 20:08:25
    #38459537
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
javajdbc,

чуть больше года назад решал подобные задачи... не заметил чего-то "сильно сложного"... правда мне 50+ :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2013, 20:37:02
    #38459559
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109,

ок, вот тестовая задача:

имеестся данные которые получаются следуюшим кодом:

select 1 a, sleep(2) b from dual
union
select 2, sleep(2) from dual
union
select 3, sleep(2) from dual

Надо одним селектом выбрать за быстрее чем 6.5 секунд записи
где колонка "а" выше среднего.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 07:26:49
    #38459791
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
javajdbc,

Ну блин встрял... я чё, помню "как"? (50+) :)

Есть дамп с решением (сохранился ещё), на выходных поставлю Мускуль и попробую развернуть, вышлю.
Просто он около 7гектар и на моей машинке будет разворачиваться не меньше суток.
А сейчес - лениво, да и работа в другой тематике (чистое PHP).

Ход рассуждений был примерно такой:

1. Считать среднее всё одно надо, а стало быть без группировки ваших записей - никак. Считать скользящее среднее "на лету" в переменной - бессмысленно, поскольку окончательный результат все одно будет последним.

2. Проблема в том, чтобы пройти одним циклом в одном селекте. Стало быть вместе с подсчетом среднего, надо "как-то" заполучить "что-то ещё" что потом или сразу отдать как результат или отсечь из него то, что будет результатом.

3. Всё что "можно" как "что-то ещё" - GROUP_CONCAT() явно или в переменных. Но, если "чего-то" группировать в текст, то по-сути, мы "потом" будем просматривать его на предмет выборки, что фактически есть материализация, только очень медленно. Проще материализовать и посчитать сначала среднее, потом с ним и пробежаться по времянке.

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

5. К сожалению, скользящее среднее может сильно "упасть" к концу выборки и записи могут быть необосновано пропущены.
Сортировка по возрастанию, приводит к той же проблеме "наоборот". Считать "одновременно" - не получается, сортировка - одна "или та или другая". Стало быть, "потеряшек" как-то надо "возвращать в строй" или "удалять из него" (смотря куда сортируем). Стало быть "результат" - также "копим" в переменной, которую можно чистить. Отсюда - полезна только сортировка по возрастанию. :)

То есть заворачиваем ваш запрос в сортировку по возрастанию и считаем в переменной скользящее среднее, накапливая результат тоже в переменной. Условие отбора - значение меньше-равно (не больше) скользящего среднего (выкашиваем из переменной substring(), locate(), find_in_set(), etc.). В результат выдаем среднее и группированный список оставшихся значений из переменной через запятую. Если надо записи "по отдельности", то джойним с нумерацией и разделяем список на записи по запятым.

Не сказать что "очень быстро" но за один проход. При больших наборах материализация с индексированием может стать быстрее.

Как-то так. (это чтобы не подумалось что "слил" :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 08:25:56
    #38459820
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select aa, av, if(aa=0, @n:= av,@n) nnn 
from
(
  select ifnull(a,0) aa , avg(c) av
  from
  (
    select 1 a, sleep(1) b, 11 c
    union 
    select 2  , sleep(1) b, 12
    union 
    select 3  , sleep(1) b, 13
    union 
    select 4  , sleep(1) b, 14
    union 
    select 5  , sleep(1) b, 15
  ) z
  group by a with rollup
) z2,
(select @n:=0 nn) z3
having (av > nnn)
order by aa



Ответ за 5.01 сек:
4,14,13
5,15,13

Груп бы по ИД с роллапом заменил вашу идею
на переменных собрать среднее значение.
Потом всеже пришлось пройтись переменной --
разнести среднее занчение по всем записям.

Неожидано сработал HAVING без GROUP BY и без агрегатов.
(Надеюсь Яростный Меч и МастерЗив не увидят
этого безобразия :-) )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 10:17:05
    #38459911
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
javajdbc,

Зато я увидел это безобразие. И это действительно оно. Применять having без group by - форменное безобразие. (косяк парсера движка ваще-то!) :)

Это раз. И второе: посмотрите на то задание что выдали мне и свой внутренний селект и найдите разницу. Почто мне выдали без преднумерации? :)

И третье. Красиво в целом. Идея группировки по негруппирующему столбцу и подсчет итогов... не подумал. Да и в моем решении так не использовалось.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 10:19:56
    #38459913
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109,

Кстати WHERE av > @n -- тоже должно сработать. Кузявее. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 10:26:49
    #38459920
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109Применять having без group by - форменное безобразие. (косяк парсера движка ваще-то!) :)Почему косяк? в документации не запрещено.
Косвенно даже разрешено:
http://dev.mysql.com/doc/refman/5.5/en/select.html The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 10:33:59
    #38459930
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
miksoft,

Да завсегда можно обозвать косяк фичей и радоваться. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 10:36:12
    #38459933
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109,

Ну и кстати, селект-то один, а проходов - два. Сначала считаем среднее (через rollup), а потом отсекаем лишнее. У меня - один проход, но со строковыми функциями, что тоже далеко не айс. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 22:35:01
    #38461161
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109Arhat109,

Ну и кстати, селект-то один, а проходов - два. Сначала считаем среднее (через rollup), а потом отсекаем лишнее. У меня - один проход, но со строковыми функциями, что тоже далеко не айс. :)

Ну 1,2,3,4,5 -- это просто ИД, необязательно упорядочмые,
да без них ролап бы не сработал как надо.

А проходов должно быть два полюбому:
собрать среднее и отсечь по среднему.
Даже собирать все в строку -- придется все равно
строку разбирать на больше-меньше.

вопшем, как ТС и заказывал, SQL-порнография +21 :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.11.2013, 07:44:13
    #38461330
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
javajdbc,

Не совсем. В моем варианте - один проход. Там просто гемморой с переменными и их обработка тут же на лету. Сильно не уверен что он быстрее вашего роллапа. Не красивее и сложнее - точно. :)

В целом, да. Скуль-порнография-21+. Но, все равно, "не особо сложно". :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.11.2013, 19:57:00
    #38462599
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
Arhat109,

ok! :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.11.2013, 08:05:32
    #38462949
Arhat109
Arhat109
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос не для впечатлительных, +21
javajdbc,

гы. Развернул таки архив, нашел ... а там сделано через rollup! :) :) ..забыл. Вот оно 50+...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / запрос не для впечатлительных, +21 / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 342ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1835732]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 46ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 31ms
get tp. blocked users:
 1ms
others: 235ms
total:  342ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]