ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / запрос не для впечатлительных, +21
15 сообщений из 15, страница 1 из 1
запрос не для впечатлительных, +21
    #38459350
aleXVoipp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleXVoipp
Гость
Дано :
таблицы
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, 15:53:20
| Ответить | Цитировать | Написать  
запрос не для впечатлительных, +21
    #38459523
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
aleXVoipp,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Кстати WHERE av > @n -- тоже должно сработать. Кузявее. :)
...
Рейтинг: 0 / 0
11.11.2013, 10:19:56
| Ответить | Цитировать | Написать  
запрос не для впечатлительных, +21
    #38459920
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
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:26:49
| Ответить | Цитировать | Написать  
запрос не для впечатлительных, +21
    #38459930
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
miksoft,

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

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

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

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

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

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

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

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

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

гы. Развернул таки архив, нашел ... а там сделано через rollup! :) :) ..забыл. Вот оно 50+...
...
Рейтинг: 0 / 0
13.11.2013, 08:05:32
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / запрос не для впечатлительных, +21
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38461330&tid=1835732]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 30ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 204ms
total:  310ms
созд. / загр.: 310ms
x
x
Закрыть


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