Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > запрос с нюансом

Автор: zeltek 14.6.2012, 22:22

Вообщем есть таблица с 4мя полями, к примеру:
марка   цвет  год    цена
ВАЗ       бел    1900  150
ВАЗ       бел    1900  151
ВАЗ       бел    1900  200
ВАЗ       бел    2000  150

нужно организовать выборку с 5ю полями:
марка   цвет  год    цена (с)  цена (до)
ВАЗ       бел    1900  150           151
ВАЗ       бел    2000  200
ВАЗ       бел    2000  150

т.е. выбираюся записи с группировкой по марке,цвету и году, а также мин. и макс. цены по этим аттрибутам
а теперь загвоздка:
если марка, цвет и год идентичны, а список цен примерно такой :150,151,152  то цена с должна быть 150, а цена до 152
а если список такой: 150,151,152,200  то должно быть две записи : первая - первые три поля теже ценас - 150 ценадо 152,
вторая первые три поля теже, ценас -200 ценадо - 0
т.е. пока цена увеличивается на 1, цикл продолжается, а если больше чем на 1, то выводится строка с ценойдо равной последней цене и цикл начинается заново...

надеюсь идея понятна...

Автор: lafisques 15.6.2012, 07:31

Покажите запрос, который составили и не работает. Вообще вот такая конструкция вполне себе должна работать:

Код


select ... , MIN(price), MAX(price)
  where ...
  group by ...

Автор: Akina 15.6.2012, 08:15

Цитата(lafisques @ 15.6.2012, 08:31

)

вот такая конструкция вполне себе должна работать

Нет

Цитата(zeltek @ 14.6.2012, 23:22

)

надеюсь идея понятна

Понятна...

Для MySQL я прредлагаю хранимую процедуру. Открываете курсор с соотв. сортировкой и в цикле обрабатываете записи, сливая найденные диапазоны в выходной поток.

Автор: zeltek 15.6.2012, 08:42

в том то и дело, что мне не нужно просто максимальное и минимальное значение
если делать как Вы предлагаете, то получится такая выборка из примера первого сообщения:
ВАЗ       бел    1900  150           200
ВАЗ       бел    2000  150

а нужно:

ВАЗ       бел    1900  150           151
ВАЗ       бел    1900  200
ВАЗ       бел    2000  150

Автор: Zloxa 15.6.2012, 10:36

Akina, с помощью трюка с переменными MySql можно ли организовать расчет накапливающей суммы?

Суть идеи така: Селф лефт джойн по правая цена = левая цена -1, считаем накапливающей суммой nullы слева - получаем критерий группировки.

на оракле это выглядило бы както то так:

Код


SQL> with t as (
  2            select 'ВАЗ' mark, 150 price from dual
  3  union all select 'ВАЗ' mark, 151 price from dual
  4  union all select 'ВАЗ' mark, 152 price from dual
  5  union all select 'ВАЗ' mark, 200 price from dual
  6  union all select 'ГАЗ' mark, 200 price from dual
  7  )
  8  select mark,min(price),nullif(max(price),min(price))
  9  from (
 10    select t1.mark,t1.price
 11           ,sum(case when t2.mark is null then 1 end) over (order by t1.mark, t1.price)  grp
 12    from t t1
 13    left join t t2 on t1.mark = t2.mark and t1.price = t2.price +1
 14  )
 15  group by mark, grp
 16  ;
 
MARK MIN(PRICE) NULLIF(MAX(PRICE),MIN(PRICE))
---- ---------- -----------------------------
ВАЗ         150                           152
ВАЗ         200 
ГАЗ         200

Автор: Akina 15.6.2012, 10:42

Zloxa, MySQL будет долго таращить глаза на sum() over ()... так не получится.

Автор: Zloxa 15.6.2012, 10:50

еще, как вариант, вычитать из цены порядковый номер записи в группе отсортированной по цене, тоже, наверное можно замутить как-то, используя переменные mysql.

Этот способ кажется проще, не требует селф джойна, но может дать сбой, в случае, если в пределах группы цены не уникальны, потому, перед расчетом порядкового номера желательно бы сгруппироваться.

Цитата

  8  select mark,min(price),nullif(max(price),min(price))
  9  from (
10    select t.*
11           ,price-row_number() over (partition by mark order by price) grp
12    from t
13  )
14  group by mark,grp
15  ;

MARK MIN(PRICE) NULLIF(MAX(PRICE),MIN(PRICE))
---- ---------- -----------------------------
ВАЗ         150                           152
ВАЗ         200
ГАЗ         200

Добавлено @ 10:53

Цитата(Akina @ 15.6.2012, 11:42

)

Zloxa, MySQL будет долго таращить глаза на sum() over ()... так не получится.

Я знаю что он не держит синтаксис, я просто предлагаю подход. Этот синтаксический элемент реализует расчет накапливающей суммы. Я не знаю, можно ли его реализовать используя переменные MySQL. Я знаю ты с ними работал, какие-то трюки с ними демонстрировал, потому и обращаю вопрос к тебе. Быть может как-то можно исхитриться... smile

Добавлено @ 10:58
Вот такой подход сто пудов реализуем на mysql после адаптации синтаксиса. Но имеет малую производительность и требует уникальности цены в пределах группы

Код


 8  select mark,min(price),nullif(max(price),min(price))
  9  from (
 10    select t1.*
 11        ,price-(select count(*) from t t2 where t2.mark = t1.mark and t2.price<t1.price) grp
 12    from t t1
 13  )
 14  group by mark,grp
 15  ;
 
MARK MIN(PRICE) NULLIF(MAX(PRICE),MIN(PRICE))
---- ---------- -----------------------------
ВАЗ         150                           152
ВАЗ         200 
ГАЗ         200

Добавлено через 11 минут и 27 секунд

Цитата(Zloxa @ 15.6.2012, 11:50

)

требует уникальности цены в пределах группы

можно использовать count(distinct t2.price), чтоб избавиться от этого требования smile

Автор: zeltek 15.6.2012, 11:09

всем спасибо, решил проблему непосредственно скриптом php smile