ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите сгруппировать серии (где все номера по порядку)
18 сообщений из 18, страница 1 из 1
помогите сгруппировать серии (где все номера по порядку)
    #39134831
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
есть таблица A (дата, номер)
надо получить таблицу B с результатом (дата, начальный номер в серии, конечный номер в серии)
за одну дату может попасть несколько серий, тогда будет несколько таких строк, где серия - это подряд идущие номера без пропуска, если хотя бы один номер отсутствует то начинается новая серия

по сути нужна обратная операция функции generate_series()
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
WITH a AS (
  SELECT '2015-12-15' AS dat, 10 AS Number
  UNION SELECT '2015-12-15', 11
  UNION SELECT '2015-12-15', 12
  UNION SELECT '2015-12-15', 14
  UNION SELECT '2015-12-15', 13
  UNION SELECT '2015-12-15', 16
  UNION SELECT '2015-12-15', 18
  UNION SELECT '2015-12-15', 17
  UNION SELECT '2015-12-16', 20
  UNION SELECT '2015-12-16', 19)
SELECT 
  a.dat,
  ??? AS minnumber,
  ??? AS maxnumber
FROM a
group by  a.dat

что бы получить такой результат
datminnumbermaxnumber'2015-12-15'1014'2015-12-15'1618'2015-12-16'1920
...
Рейтинг: 0 / 0
22.12.2015, 16:31
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134846
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Legushka,

Number - row_number()
...
Рейтинг: 0 / 0
22.12.2015, 16:38
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134862
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
p2., ? как можно сделать с помощью row_number() ?
...
Рейтинг: 0 / 0
22.12.2015, 16:51
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134941
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Legushka,

если не гнаться за вычислительным оптимумом -- то можно в лоб:~

Код: sql
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.
WITH a (dat,Number) AS (
  values ( date'2015-12-15', 10
),( '2015-12-15', 11
),( '2015-12-15', 12
),( '2015-12-15', 14
),( '2015-12-15', 13
),( '2015-12-15', 16
),( '2015-12-15', 18
),( '2015-12-15', 17
),( '2015-12-16', 20
),( '2015-12-16', 19)
)

,pre AS (SELECT  *, number =1+ (lag(number) over(partition by dat ORDER BY number)) nxt

	FROM a)
, b AS (SELECT * , count(CASE WHEN nxt=TRUE THEN NULL ELSE 1 END) over(partition by dat ORDER BY number) AS grp FROM pre )
--/*
SELECT 
  b.dat
  ,b.grp
  ,MIN(b.number)  AS minnumber
  ,MAX(b.number)  AS maxnumber
FROM b
group by  b.dat, grp
--*/



-- как это сделать на проходе без процедурного FOR rec IN SELECT .... ORDER BY -- на одном With -- пока не придумал.
...
Рейтинг: 0 / 0
22.12.2015, 17:35
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134943
ursido
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ursido
Гость
Legushka,

 
Код: sql
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.
WITH a AS (
  SELECT '2015-12-15' AS dat, 10 AS Number
  UNION SELECT '2015-12-15', 11
  UNION SELECT '2015-12-15', 12
  UNION SELECT '2015-12-15', 14
  UNION SELECT '2015-12-15', 13
  UNION SELECT '2015-12-15', 16
  UNION SELECT '2015-12-15', 18
  UNION SELECT '2015-12-15', 17
  UNION SELECT '2015-12-16', 20
  UNION SELECT '2015-12-16', 19)
,
a1 AS (
SELECT 
  lag(number, 1, NULL) OVER(PARTITION BY dat ORDER BY dat, number) AS f1,
  lead(number, 1, NULL) OVER(PARTITION BY dat ORDER BY dat, number) AS f2,
  *
FROM a

),
a2 AS (
SELECT
  CASE WHEN (f1 IS NULL) OR (number - 1 > f1 ) THEN 1 ELSE 0 END AS start, 
  CASE WHEN (f2 IS NULL) OR (number + 1 < f2 ) THEN 1 ELSE 0 END AS finish, 
*
FROM a1
),
a3 AS(
SELECT
  ROW_NUMBER() OVER(ORDER BY number) AS nu,
  dat,
  number
FROM a2
WHERE start = 1
),
a4 AS(
SELECT
  ROW_NUMBER() OVER(ORDER BY number) AS nu,
  dat,
  number
FROM a2
WHERE finish = 1
)

SELECT
  a3.dat,
  a3.number AS minnumber,
  a4.number AS maxnumber
FROM a3, a4
WHERE a3.nu = a4.nu

...
Рейтинг: 0 / 0
22.12.2015, 17:35
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134967
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
ох уж эти усложняторы
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select dat, min(number), max(number)
from (
  SELECT 
    dat,
    number,
    number - row_numbeR() over(partition by dat order by number) r
  FROM a
) t
group by dat, r
order by dat, r;
...
Рейтинг: 0 / 0
22.12.2015, 17:50
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39134984
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.ох уж эти усложняторы
ну да, одну материализацию тот факт что dlt== grp экономит. Но по сравнению с прямым однократным FOR LOOP--ом это крайне не эффективно.
выразительных средств Sql не хватает.
...
Рейтинг: 0 / 0
22.12.2015, 18:02
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135024
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
qwwqp2.ох уж эти усложняторы
ну да, одну материализацию тот факт что dlt== grp экономит. Но по сравнению с прямым однократным FOR LOOP--ом это крайне не эффективно.
выразительных средств Sql не хватает.однократному for-loop нужно на вход подавать отсортированные данные. Получается тот же самый однократный over(), поэтому выражаю сомнение в неэффективной крайности.
Про выразительность, ну вот в оракле ради обработки "серий" припилили такое:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select *
from a
match_recognize (
   partition by dat order by number -- как и over()
   measures -- что возвращать (помимо partition)
      first(number) as minnumber,  -- или min/max
      last(number) as maxnumber
   pattern  -- что есть серия, по типу regexp
      (dummy grp*)
   define -- условные элементы, используемые в pattern, measure или других элементах define
      grp as number = prev(number)+1
)
...
Рейтинг: 0 / 0
22.12.2015, 18:46
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135127
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.qwwqпропущено...

ну да, одну материализацию тот факт что dlt== grp экономит. Но по сравнению с прямым однократным FOR LOOP--ом это крайне не эффективно.
выразительных средств Sql не хватает.однократному for-loop нужно на вход подавать отсортированные данные. Получается тот же самый однократный over(), поэтому выражаю сомнение в неэффективной крайности.
какбе мне агрегировать после "лупа по сорту" (т.е. после овера) не надо.

на этом и экономия.
p2.Про выразительность, ну вот в оракле ради обработки "серий" припилили такое:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select *
from a
match_recognize (
   partition by dat order by number -- как и over()
   measures -- что возвращать (помимо partition)
      first(number) as minnumber,  -- или min/max
      last(number) as maxnumber
   pattern  -- что есть серия, по типу regexp
      (dummy grp*)
   define -- условные элементы, используемые в pattern, measure или других элементах define
      grp as number = prev(number)+1
)


рукалецо.
ара кал всегда страдал крайним лингвистическим идиотизмом в запущенной форме. и состоит из синтаксического мусора чуть более, чем полностью. это когда математексы беруццо язык изо бредать. наняли бы хоть каких--то полиглотов из навахо, чоле.
...
Рейтинг: 0 / 0
22.12.2015, 20:45
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135170
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
qwwqp2.пропущено...
однократному for-loop нужно на вход подавать отсортированные данные. Получается тот же самый однократный over(), поэтому выражаю сомнение в неэффективной крайности.какбе мне агрегировать после "лупа по сорту" (т.е. после овера) не надо.
на этом и экономия.правильно понимаю, что проверка/обновление значения по хешу (груп бай) вместо сравнений с предыдущим (for-loop) это и есть крайняя неэффективность? Думается тут для усугубления крайности заявленной экономии придется тщательно специфицировать соответствующие крайности данных.
qwwqрукалецо.
ара кал всегда страдал крайним лингвистическим идиотизмом в запущенной форме. и состоит из синтаксического мусора чуть более, чем полностью. это когда математексы беруццо язык изо бредать. наняли бы хоть каких--то полиглотов из навахо, чоле.Придать веса критицизму могли бы альтернативные синтаксические изыскания. Есть таковые?
...
Рейтинг: 0 / 0
22.12.2015, 21:52
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135352
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.qwwqпропущено...
какбе мне агрегировать после "лупа по сорту" (т.е. после овера) не надо.
на этом и экономия.правильно понимаю, что проверка/обновление значения по хешу (груп бай) вместо сравнений с предыдущим (for-loop) это и есть крайняя неэффективность? Думается тут для усугубления крайности заявленной экономии придется тщательно специфицировать соответствующие крайности данных.
а овер--ордер--бай у вас бесплатно делаицца ?

-- это уже даже не наглость


p2.qwwqрукалецо.
ара кал всегда страдал крайним лингвистическим идиотизмом в запущенной форме. и состоит из синтаксического мусора чуть более, чем полностью. это когда математексы беруццо язык изо бредать. наняли бы хоть каких--то полиглотов из навахо, чоле.Придать веса критицизму могли бы альтернативные синтаксические изыскания. Есть таковые?
ну какбе есть простые принципы -- всё, что функция -- все от параметров. только от параметров и ничего более, как от параметров.
всё, что не только от параметров -- уже не функция.

за тягу делать из всего синтаксическое подобие вымазанных синтаксическим ара--калом функций -- вешать через расстреляние на месте.

я бы думал на предмет "предложения" ["FOR" a-la "WITH"], а не функции. но мне за это денех не плотют. а вот отстреливать дебилов я и даром могу. или хотя бы тыкать в них пальцем
...
Рейтинг: 0 / 0
23.12.2015, 08:31
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135422
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
qwwqа овер--ордер--бай у вас бесплатно делаицца ?Давай-ка ты приведешь конкретную реализацию предложенного тобой "FOR rec IN SELECT .... ORDER BY", чтобы крайность можно было сравнить более предметно.
И обращаю внимание, что проявление ущемленности, выражаемое коверканьем слов, является нарушением правил форума.

qwwq"FOR" a-la "WITH"with является продюсером строк, а match_recognize оперирует возвращаемым предшествующим from-выражением множеством строк, по типу как group by, только group by относится ко всему from-where. То, что выбран не процедурный, а декларативный язык определения паттернов, вполне согласуется с идеологией sql. С таким же успехом можно предъявлять претензии к select и писать выборки и джоины явными forами по строкам. Наверняка это в каких-то случаях даже будет эффективней. Но ведь никто не запрещает использовать процедуры и в них терпеть только простейший селект-фром. Более того, для тех, кому не хватает выразительности sql, помимо хранимых процедур, есть вставки процедурного языка with function.
...
Рейтинг: 0 / 0
23.12.2015, 09:51
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135464
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2., давай-ка ты мне не будешь указывать., что делать, и я не буду намекать, какой буквы "раз" не хватает в твоём нике за буквами "пи" и "два".
и вообще -- "мы с вами вместе не служили ?"

ps умному -- достаточно.
...
Рейтинг: 0 / 0
23.12.2015, 10:26
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39135790
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
 
Код: sql
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.
CREATE SCHEMA test;

SET search_path TO test,"$user",public,pg_catalog;


CREATE table a (dat date not null, number integer not null);
TRUNCATE a ;
INSERT INTO a (dat,number) 
SELECT date'2015-01-01'+dat ,num from generate_series(1,37*365) dats(dat), generate_series(1,137) nums(num)
WHERE num<>(dat % 73);
ANALYZE a;

CREATE OR REPLACE FUNCTION tst_loop () 
RETURNS TABLE (dat date , fnum integer, lnum integer)
LANGUAGE plpgsql 
AS 
$$
DECLARE 
	_rec record;
	_first boolean:=TRUE;
BEGIN
	FOR _rec IN
		SELECT a.dat,a.number AS num FROM a ORDER BY 1 ,2
	LOOP
		IF _rec.dat = dat AND _rec.num = lnum+1
		THEN
			lnum:= _rec.num;
		ELSIF _first THEN
			_first:= false;
			dat:=_rec.dat ; lnum:= _rec.num ; fnum:=lnum;
		ELSE
			RETURN NEXT;
			dat:=_rec.dat ; lnum:= _rec.num ; fnum:=lnum;
		END IF;		
		
	END LOOP;
	IF FOUND THEN
		RETURN NEXT;
	END IF;
END;
$$ STABLE
;
SELECT * FROM  tst_loop ();
--
Суммарное время выполнения запроса: 3945 ms.
26640 строк получено.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select dat, min(number), max(number)
from (
  SELECT 
    dat,
    number,
    number - row_numbeR() over(partition by dat order by number) r
  FROM a
) t
group by dat, r
order by dat, r
;
--
Суммарное время выполнения запроса: 5158 ms.
26640 строк получено.



-- гири plpgsql -- в довольно большой константе в O(f(N)) перед (f(N)).
до тех пор, пока строк мало -- затраты на этот множитель перевешивают.


а с explain analyze у меня какая--то фигня лезет -- затраты (с окнами) даже больше времени фетча всех данных в разы/на порядок. Пока не скажешь Timing off; Не в первый раз, такая байда в игровом 9.4.1 ,кстати.
...
Рейтинг: 0 / 0
23.12.2015, 12:41
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39136424
Фотография grufos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grufos
Участник
qwwq,

проверил скорость у себя локально на сервере 9.4.4
SELECT * FROM tst_loop (); -- ~ 1800 млс
select dat, min(number), max(number) .... -- ~ 1300 млс
эти результаты стабильны
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
QUERY PLAN
Sort  (cost=313575.77..314034.98 rows=183687 width=16) (actual time=1279.696..1280.288 rows=26640 loops=1)
  Output: a.dat, (min(a.number)), (max(a.number)), ((a.number - row_number() OVER (?)))
  Sort Key: a.dat, ((a.number - row_number() OVER (?)))
  Sort Method: quicksort  Memory: 2850kB
  Buffers: shared hit=8128
  ->  HashAggregate  (cost=295678.32..297515.19 rows=183687 width=16) (actual time=1263.157..1270.618 rows=26640 loops=1)
        Output: a.dat, min(a.number), max(a.number), ((a.number - row_number() OVER (?)))
        Group Key: a.dat, (a.number - row_number() OVER (?))
        Buffers: shared hit=8128
        ->  WindowAgg  (cost=217611.56..258941.02 rows=1836865 width=8) (actual time=280.637..891.599 rows=1836865 loops=1)
              Output: a.dat, a.number, (a.number - row_number() OVER (?))
              Buffers: shared hit=8128
              ->  Sort  (cost=217611.56..222203.72 rows=1836865 width=8) (actual time=280.618..347.275 rows=1836865 loops=1)
                    Output: a.dat, a.number
                    Sort Key: a.dat, a.number
                    Sort Method: quicksort  Memory: 135256kB
                    Buffers: shared hit=8128
                    ->  Seq Scan on test.a  (cost=0.00..26496.65 rows=1836865 width=8) (actual time=0.014..96.441 rows=1836865 loops=1)
                          Output: a.dat, a.number
                          Buffers: shared hit=8128
Planning time: 0.159 ms
Execution time: 1293.423 ms
...
Рейтинг: 0 / 0
23.12.2015, 18:41
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39136471
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
grufos,

и чо ?

вы намерили разницу между C--реализацией WindowAgg и plpgsql реализацией того же самого ("окно" отслеживается чуть сложнее, чем стандартное). При этом затртаты на лишний хеш--агрегат просто потерялись.


PS и да -- я намерил разницу не с хеш--агрегатом. т.к. work_mem был '4MB'. И чо ? это что--то изменяет в алгоритмах ?
...
Рейтинг: 0 / 0
23.12.2015, 19:07
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39136814
Фотография grufos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grufos
Участник
Уважаемый qwwq,

я только лишь хотел показать, что время выполнения может быть другим.
Чтобы у читающих форум сложилось более широкое представление о этих реализациях.
И разнице в их работе.
А вообще спасибо вам большое за проделанную работу в подготовке примера.
Я уверен, что начинающим - это будет очень полезно.
...
Рейтинг: 0 / 0
24.12.2015, 10:38
| Ответить | Цитировать | Написать  
помогите сгруппировать серии (где все номера по порядку)
    #39136863
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
grufos,

1. спасибо за поправку (я было не заметил, что померил не то).
из неё --мораль:

затраты на хеш--агрегат -- это множитель в константе при O(N) -- т.к. в вышеприведенном коде FORLOOP это было бы по 1 операции извлечения из хештаблицы , 2 операции сравнения + 1--2 операции кладки взад в хештаблицу/на шаг цыкла. -- т.е. просто несколько лишних операций сложности O(1). скажем множитель "Ch"
[+отложенное извлечение из хештаблицы в конце цыкла -- т.е. затраты памяти]

-- поэтому, если реализация FORLOOP в plpgsql в "Cplpgsql" раз затратнее того же FORLOOP в С, и Cplpgsql > Ch -- то выгодность алгоритма средствами plpgsql не выявляема (он проигрывает лишним операциям с хешем -- лишними операциями в plpgsql реализации FORLOOP, которых тупо набирается больше)

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

2. то, что внутре WindowAgg нет никакой неонке, а банальная реализация FORLOOP--а надеюсь не озарение даже "для начинающих". не ?
...
Рейтинг: 0 / 0
24.12.2015, 11:19
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите сгруппировать серии (где все номера по порядку)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39135170&tid=1997552]:
 0ms
get settings:
 5ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 152ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 32ms
get tp. blocked users:
 1ms
others: 209ms
total:  420ms
созд. / загр.: 420ms
x
x
Закрыть


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