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

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

Форум программистов > Составление SQL-запросов > [PG]Вычислить максимум повторений из групп записей

Автор: linuxoid 3.8.2009, 16:48

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

До сортировки по дате

---------------------
| id | number |
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
| 15 | 1 |
| 16 | 1 |
| 17 | 1 |
---------------------

После сортировки по дате.
---------------------
| id | number |
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
| 15 | 1 |
| 16 | 1 |
| 17 | 1 |
---------------------

После сортировки ясно, что самая длинная непрерывная группа содержит 5 подряд идущих единиц. Как это реализовать с помощью SQL (с учетом того, что сначала нужно отсортировать по дате)?

P.S. Postgres

Автор: Zloxa 3.8.2009, 19:28

http://www.postgresql.org/docs/8.4/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS работают так же как в оракле то чтото вроде:

Код


Select max(count(*)) max_continuous_group_len from (
  select count(start_of_group) over (order by id) group_id , id
  from  (
    select id, case when lag(number) over (order by id) = number then null else 1 end start_of_group from table
  )
)
group by group_id

Автор: linuxoid 4.8.2009, 20:46

Спасибо за ответ. Я не совсем понял, что Вы написали. Может быть поясните ? К тому же я не знаком с оконными ф-иями и не знаю различия их с ораклом. Проблема в том, что у меня сотни тысяч записей и на данный момент я использую средства PHP. Работает все очень очень медленно, поэтому надеюсь как-то решить проблему исключительно средствами Postgres, если это возможно конечно...

Автор: Zloxa 5.8.2009, 14:11

Цитата(linuxoid @ 4.8.2009, 20:46

)

я не знаком с оконными ф-иями

что мешает http://www.postgresql.org/docs/8.4/static/tutorial-window.htmlи рассказать мне от чего предложенное мною решение Вас не удовлетворило, если это так.

Все использованные мною конструкции и функции я нашел в документации PG, на которую предоставил ссылку. Правда я не искал описания принципов работы этих функций, полагая, что они реализованы так же, как в оракле. Тем не менее, я допускаю что что-то может работать несколько иначе, либо не работать вовсе. С этим предлагаю разобраться Вам. Однако результат Ваших изысканий мне не безынтересен.

Автор: Azket 6.8.2009, 11:03

Допустим твоя таблица "T"

Код


select * into #TMP from T where number=1

alter table #TMP
  add INC int NOT NULL IDENTITY(1, 1)

alter table #TMP
  add DIF int

update #TMP set DIF=INC-ID

select max(CNT) from (select DIF, count(DIF) 'CNT' from #TMP group by DIF) as Q

drop table #TMP

Автор: Zloxa 6.8.2009, 11:33

Azket, идея красивая, браво smile

! только как ты обеспечишь одинаковый порядок сортировки ID и INC? И можешь ли ты закладываться на непрерывность ID? smile

используя твой принцип:

Код


select max(count(*))
from (
  select row_number() over (order by id) - row_number() over (partition by number order by id)  dif
         ,number
  from table 
)
where number =1
group by dif

Добавлено @ 11:34
PS и даже на MS SQL должно работать smile

Автор: Azket 6.8.2009, 12:12

Цитата(Zloxa @ 6.8.2009, 11:33

)

только как ты обеспечишь одинаковый порядок сортировки ID и INC?

в этом случае, а также в случае, если исходная таблица сортируется по другим столбцам, а следовательно поле ID будет не по порядку, то вполне можно добавить в начале поле

Код


alter table T
  add INC0 int NOT NULL IDENTITY(1, 1)

и работать с INC0 вместо ID.

Автор: Zloxa 6.8.2009, 12:31

Azket, а кто гарантирует что порядок inc0 будет совпадать с порядком id? Даже если бы будем использовать order by в select into, где нибудь документально зафиксировано что СУБД нам гарантирует порядок присвоения identity в соответствии с критерием, указанным в order by? Если нет, то использовать такой подход в продуктивном решении просто нельзя..
И еще, есть ли гарантия "бездырочности" при генерации identity?

Автор: Azket 6.8.2009, 13:15

Zloxa,

Цитата(Zloxa @ 6.8.2009, 12:31

)

а кто гарантирует что порядок inc0 будет совпадать с порядком id?

а зачем нам, чтобы они совпадали? Нам на это абсолютно пофиг. На ID, мы вообще не обращаем внимания, мало ли как таблица будет отсортирована. Нам нужно, чтобы:
1) поле INC0 всегда шло по возростающей
2) у всех записей, у которых NUMBER=1 и, которые идут подряд, поле INC0 отличалось на 1 (и наоборот, те между которыми попались записи с NUMBER=0, отличались > чем 1)

В итоге, когда появится поле INC, то DIF=INC0-INC.

Цитата(Zloxa @ 6.8.2009, 12:31

)

И еще, есть ли гарантия "бездырочности" при генерации identity?

хм... ни разу не встречался с такой ситуацией

Автор: Zloxa 6.8.2009, 13:58

Цитата(Azket @ 6.8.2009, 13:15

)

а зачем нам, чтобы они совпадали?

Потому что порядок у нас в исходной задаче определяет ID а не суррогатный INC0, который в твоем скрипте генерится "от балды"
Вообще, я не уверен что ТС правильно обозначил столбик. Скорее всего в заголовочном посте, за столбиком ID прячется порядковый номер колоночке, а порядок, в котором ТС выстраивает свой набор данных определяется упомянутой им вскользь датой.

Цитата(Azket @ 6.8.2009, 13:15

)

хм... ни разу не встречался с такой ситуацией

А меня ни разу не сбивал автомобиль.
Это ведь не значит что переходя дорогу не следует соблюдать осторожности?

Автор: Azket 6.8.2009, 14:24

Цитата(Zloxa @ 6.8.2009, 13:58

)

Потому что порядок у нас в исходной задаче определяет ID

совсем нет, ибо написано

Цитата(linuxoid @ 3.8.2009, 16:48

)

(с учетом того, что сначала нужно отсортировать по дате)

то есть, какой после этого может быть порядок ID? А INC0 предлагается генерить только тогда, когда таблица готова для вычисления (отсортирована как нужно), и вот именно для этой таблицы, INC0 и будет порядком.

Цитата(Zloxa @ 6.8.2009, 13:58

)

А меня ни разу не сбивал автомобиль.Это ведь не значит что переходя дорогу не следует соблюдать осторожности?

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

Автор: Zloxa 6.8.2009, 15:22

Цитата(Azket @ 6.8.2009, 14:24

)

А INC0 предлагается генерить только тогда, когда таблица готова для вычисления (отсортирована как нужно), и вот именно для этой таблицы, INC0 и будет порядком.

В том то и вопрос - как ты собрался его генерировать, используя identity, чтобы его порядок соответствовал нужному, при этом был непрерывен и монотонен.

Цитата(Azket @ 6.8.2009, 14:24

)

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

Да, такая вероятность существует.
В результате ошибки.
Вопрос лишь в том, чьей ошибки. И если от ошибки разработчиков СУБД ты не можешь застраховаться, то собственных ошибок избежать это твоя прямая обязанность как разработчика.

Я не утверждал того, что identity может оставить дырки в последовательности, я не утверждал что порядок присвоенного identity может не соответствовать порядку order by в селекте. Я не знаком столь глубоко с MS. Моя интуиция подсказывает что это очень спорные вопросы и требуют, как минимум исследований. И я лишь спросил, опираешься ли ты на документально заявленный функционал платформы или же опираешься на свои домыслы и надеешься на авось. Ты ответил.