Проставить row_number, сбрасывая его при каждой смене значений / PostgreSQL

2.0.53

Планшетная версия Контакт Правила FAQ Помощь

Гость

Нов. | Гор. | Избр.

Действия ...

Доб. в избранное
Игнор. тему
Прикреп. тему
Пометить прочит. / непрочит.
Фильтр:
Сообщения автора темы
Сообщение содержит вложения
Сообщение содержит картинки
Сообщение содержит видеоклипы
Сообщение содержит аудиоклипы
Сообщение содержит картинки или видео 18+

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Проставить row_number, сбрасывая его при каждой смене значений / 24 сообщений из 24, страница 1 из 1

01.08.2019, 18:56

#39844284

d20190801

Гость

Проставить row_number, сбрасывая его при каждой смене значений

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

02.08.2019, 01:45

#39844360

Щукина Анна

Участник

Сообщения: 1 496
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

d20190801,

Поищите на оракловой ветке форума - start_of_group
Подход один-в-один реализуется на PG

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

03.08.2019, 16:35

#39844955

d20190801

Гость

Проставить row_number, сбрасывая его при каждой смене значений

Щукина Анна, спасибо!
Разобрался.

p.s. для тех, кто будет читать - нужно использовать оператор CASE и подходящую аналитическую функцию, чтобы вышло, например, такое выражение:

select *,
case when (column_a != lag(column_a) over() OR column_b != lag(column_b) over()) -- это как раз и есть способ разделения значений на группы по своему условию
then '1'
else '0'
end border_flag
from table;

-- затем можно добавить sum(border_flag) over() border_sum и получить одинаковое значение на всю группу, чтобы уже снова применить аналитическую row_number() over (partition by border_sum)

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

04.08.2019, 09:58

#39845038

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

d20190801,

наверное я чего то не понял, но чем задача отличается от :

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

select * ,row_number()over(partition by a,b )
FROM (select a,b
 from generate_series(0,7) a,generate_series(0,3) b,generate_series(0,3) c
 ) foo(a,b)
---------------------------
0,0,1
0,0,2
0,0,3
0,0,4
0,1,1
0,1,2
0,1,3
0,1,4
0,2,1
0,2,2
........
........

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

04.08.2019, 23:28

#39845208

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

qwwq,
тем, что вы не внимательно смотрели:

0,0,1
0,0,2
0,0,3
0,1,1
0,1,2
0,1,3
0,0,1
0,1,1
0,2,1
0,2,2
Например так, т.е. изменение любого из a или б в следующей строке ИСХОДНОГО набора данных
начинает нумерацию с 1.
В вашем случае уже все отсортировано по a,b, в исходном примере другая последовательность строк.
Задача интересная, правда не понимаю прикладной смысл.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

05.08.2019, 14:09

#39845398

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Trogloditqwwq,
тем, что вы не внимательно смотрели:

<>
Например так, т.е. изменение любого из a или б в следующей строке ИСХОДНОГО набора данных
начинает нумерацию с 1.
В вашем случае уже все отсортировано по a,b, в исходном примере другая последовательность строк.
Задача интересная, правда не понимаю прикладной смысл.
снкс, понял

правда "исходного" набора данных какбы теоретицки не существует. если нет ордербая

с некой очевидностью, данный класс задач взывает к обобщению описания окна "оконных" ф-й a-la

Код: sql

over ([[preorder by ....] partition by ...]  [order by ...])

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

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

05.08.2019, 20:47

#39845595

Dany305

Участник

Сообщения: 103
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

d20190801Щукина Анна, спасибо!
Разобрался.

p.s. для тех, кто будет читать - нужно использовать оператор CASE и подходящую аналитическую функцию, чтобы вышло, например, такое выражение:

select *,
case when (column_a != lag(column_a) over() OR column_b != lag(column_b) over()) -- это как раз и есть способ разделения значений на группы по своему условию
then '1'
else '0'
end border_flag
from table;

-- затем можно добавить sum(border_flag) over() border_sum и получить одинаковое значение на всю группу, чтобы уже снова применить аналитическую row_number() over (partition by border_sum)

Код: 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.

with
  data (id, column_a, column_b) as (
    values 
      (1, 'A', 'value_q'),
      (2, 'A', 'value_q'),
      (3, 'A', 'value_q'),
      (4, 'A', 'value_w'),
      (5, 'A', 'value_w'),
      (6, 'A', 'value_q'),
      (7, 'B', 'value_q'),
      (8, 'B', 'value_q'),
      (9, 'B', 'value_w'),
      (10, 'B', 'value_w')
  ),

  data2 as (
    select 
      id, column_a, column_b,
      case 
        when (column_a, column_b) <> lag((column_a, column_b)) over (order by id) then 1
      end as group_start
    from data    
  ),

  data3 as (
    select 
      id, column_a, column_b,
      sum(group_start) over (order by id) as group_num
    from data2    
  )

  select 
    id, column_a, column_b,
    row_number() over (partition by group_num order by id)
  from data3
  order by id

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

06.08.2019, 11:02

#39845748

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Dany305,

это кажется 3 сорта. а бег по курсору -- всего один пресорт. т.е. алгоритмически много проще.

но если не пускаться в фантазии про курсоры , кажется такая шняжка тоже рабочая (могу врать) и на один сорт короче (наверняка где-то вру) :

Код: 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
  data (id, a, b) as (
    values 
      (1, 'A', 'value_q'),
      (2, 'A', 'value_q'),
      (3, 'A', 'value_q'),
      (4, 'A', 'value_w'),
      (5, 'A', 'value_w'),
      (6, 'A', 'value_q'),
      (7, 'B', 'value_q'),
      (8, 'B', 'value_q'),
      (9, 'B', 'value_w'),
      (10, 'B', 'value_w')
  ),
data2 as (
	select 
		a,b, id as rn
		,row_number()over(order by id) - row_number()over(partition by a,b order by id)  as s_rn	--shift
	from data    
)

	select 
		rn as preorder
		,a,b
		,row_number()over(partition by s_rn, a,b order by rn) as wanted
	from  data2 order by rn

тест

Код: 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.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.

with dany as 
(
with
  data ( a, b,rn) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g) rn from generate_series(0,37) as g,generate_series(0,2) as pl
	order by pl,g
  ),

  data2 as (
    select 
      rn, a, b,
      case 
        when (a, b) <> lag((a, b)) over (order by rn) then 1
      end as group_start
    from data    
  ),

  data3 as (
    select 
      rn, a, b,
      sum(group_start) over (order by rn) as group_num
    from data2    
  )

  select 
    rn, a, b,
    row_number() over (partition by group_num order by rn)
  from data3
  order by rn
)
, qwwq as 
(
select 
rn as preorder
,a,b
,row_number()over(partition by s_rn , a,b order by rn) as wanted
from (
select 
a,b,rn
,rn - row_number()over(partition by a,b order by rn)  as s_rn	--shift
 FROM (

select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g) rn from generate_series(0,37) as g,generate_series(0,2) as pl
order by pl,g
) foo order by rn
) foo2
)
(
select * from dany
except 
select * from qwwq
)
union all
(
select * from qwwq
except 
select * from dany
)

order by rn,a,b

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

06.08.2019, 19:54

#39846082

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

Мой вариант:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

with
data (id, a, b) as (
    values 
      (1, 'A', 'value_q'),
      (2, 'A', 'value_q'),
      (3, 'A', 'value_q'),
      (4, 'A', 'value_w'),
      (5, 'A', 'value_w'),
      (6, 'A', 'value_q'),
      (7, 'B', 'value_q'),
      (8, 'B', 'value_q'),
      (9, 'B', 'value_w'),
      (10, 'B', 'value_w')
),
tmp(id,a,b,c) as (
    select id,a,b,case when lag((a,b),1) over()=(a,b) then 1 else 0 end as c from data
)
 select a,b,sum(c) over(partition by a,b,c order by id)+1 as flag from 
 tmp
 order by id

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

07.08.2019, 10:50

#39846213

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

TrogloditМой вариант:

мимо?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

with
  data ( a, b,id) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g)::bigint rn from generate_series(0,3700) as g,generate_series(0,2) as pl
	order by pl,g
  )
select * FROM (--Troglodit
	 select id,a,b,sum(c) over(partition by a,b,c order by id)+1 as flag from 
	 (
	    select id,a,b,case when lag((a,b),1) over(/*order by id*/) is not distinct from(a,b) then 1 else 0 end as c from data
	)tmp
) foo
 order by id

 offset 139 limit 1000
----
140;4;3;7
141;0;3;1
142;0;3;4
143;0;3;5
144;0;4;1
........

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

07.08.2019, 20:29

#39846633

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

qwwq,
Да что то я совсем в молоко попал.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

09.08.2019, 13:53

#39847470

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Troglodit,

тяпничное решение :
(если кляуза не идёт к погромисту , )

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

--drop sequence if exists _ts;
create temporary sequence if not exists _ts;
set max_parallel_workers_per_gather   TO 0;

with
_data ( a, b,id) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g)::bigint rn from generate_series(0,370000) as g,generate_series(0,2) as pl
	order by pl,g
  )

select id,a,b
	,case when lag((a,b))over(order by id) is distinct from (a,b) then setval('_ts',1) else nextval('_ts') end from _data order by id;

-- надо подумать, может ли хвост обогнать тушку, хотя бы теоретиццки

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

09.08.2019, 23:58

#39847725

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

Пятничное решение.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

with
  data ( a, b,id) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g)::bigint rn from generate_series(0,3700) as g,generate_series(0,2) as pl
	order by pl,g
  ),
  tmp (id,a,b,c) as (
	  select id,a,b,case when lag((a,b),1) over()=(a,b) then 1 else 0 end as c from data
  )
select id,a,b,id-max(id) FILTER (where c=0) over(partition by a,b order by id)+1 as flag
from tmp
order by id

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.08.2019, 12:17

#39847765

PgSQLanonymous3

Участник

Сообщения: 123
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Я, может быть, чего-то не вижу... но зачем во всех решениях PARTITION BY?
Разве просто

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

WITH data AS (
SELECT ROW_NUMBER() OVER (ORDER BY pl, g)::bigint AS id,
       (g / 7) % 5 AS a, (g / 13) % 7 AS b
  FROM generate_series(0, 3700) AS g, generate_series(0, 2) AS pl
)
SELECT id, a, b,
       1 + rn - COALESCE(MAX(rn) FILTER (WHERE changed) OVER (ORDER BY id), 1) AS result
  FROM (
       SELECT id, a, b, ROW_NUMBER() OVER w AS rn,
              NULLIF((a, b) <> LAG((a, b), 1, (a, b)) OVER w, false) AS changed
         FROM data
       WINDOW w AS (ORDER BY id)
       ) AS with_flag;

не достаточно?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.08.2019, 13:38

#39847777

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

PgSQLanonymous3,

Да вы правы достаточно, просто переделывал запрос, это хвосты остались.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 11:47

#39848203

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Troglodit,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

WITH data AS (
SELECT ROW_NUMBER() OVER (ORDER BY pl, g)::bigint AS id,
       (g / 7) % 5 AS a, (g / 13) % 7 AS b
  FROM generate_series(0, 370000) AS g, generate_series(0, 2) AS pl
)
SELECT id, a, b,
       1 + rn - COALESCE(MAX(rn)FILTER(WHERE changed)OVER(ORDER BY id), 1) AS result
  FROM (
       SELECT id, a, b, ROW_NUMBER() OVER w AS rn,
              --NULLIF((a, b) <> LAG((a, b), 1, (a, b)) OVER w, false) AS changed
              case when (a, b) is distinct from LAG((a, b), 1, (a, b))OVER w then true end AS changed
         FROM data
       WINDOW w AS (ORDER BY id)
       ) AS with_flag;

похоже, вы накопали решение с одной сортировкой. достойно букваря по окнам d g;. планы почти совпадают ценой с однопроходным. (планировщик обещается обойтись одним сортом по входным, несмотря на вложенность)

Код: sql

1.
2.
3.
4.
5.

"Execution time: 2270.506 ms" 
--"счетчик"
--против 
"Execution time: 2439.906 ms"
-- макс+фильтр

но почему-то полные фетчи данных в пж-одминЪ-3 (с локальной субд) отличаются стабильно в ~3--4 раза

Код: sql

1.
2.
3.
4.
5.
6.
7.

Total query runtime: 6.8 secs
1110003 строки получено.
--для "однопроходки"
-- против
Total query runtime: 23.3 secs
1110003 строки получено.
-- для вашей однооконной

кто б объяснил, почему.
если ширина и типы полей совпадают

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 19:55

#39848508

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

qwwq,

Я попробовал ваш запрос у меня выполняется более 3с., мой 0.13.
Одно замечание: я запускал на pg12 beta.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 22:47

#39848549

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

Trogloditqwwq,

Я попробовал ваш запрос у меня выполняется более 3с., мой 0.13.
Одно замечание: я запускал на pg12 beta.
какой "вашъ" и какой "мой" ?
уточните.
и приведите полный explain analyze обоих.
интересно посмотреть в чем разница.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 22:57

#39848550

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

qwwq,

Ваш вариант.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

WITH data AS (
SELECT ROW_NUMBER() OVER (ORDER BY pl, g)::bigint AS id,
       (g / 7) % 5 AS a, (g / 13) % 7 AS b
  FROM generate_series(0, 370000) AS g, generate_series(0, 2) AS pl
)
SELECT id, a, b,
       1 + rn - COALESCE(MAX(rn)FILTER(WHERE changed)OVER(ORDER BY id), 1) AS result
  FROM (
       SELECT id, a, b, ROW_NUMBER() OVER w AS rn,
              --NULLIF((a, b) <> LAG((a, b), 1, (a, b)) OVER w, false) AS changed
              case when (a, b) is distinct from LAG((a, b), 1, (a, b))OVER w then true end AS changed
         FROM data
       WINDOW w AS (ORDER BY id)
       ) AS with_flag;

Мой

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

with
  data ( a, b,id) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g)::bigint rn from generate_series(0,3700) as g,generate_series(0,2) as pl
	order by pl,g
  ),
  tmp (id,a,b,c) as (
	  select id,a,b,case when lag((a,b),1) over()=(a,b) then 1 else 0 end as c from data
  )
select id,a,b,id-max(id) FILTER (where c=0) over(order by id)+1 as flag
from tmp
order by id

План не смотрел, запускал несколько раз запросы, я на точность не претендую, просто наблюдение.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 23:14

#39848553

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

Ваш:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

WindowAgg  (cost=323658.48..381933.64 rows=1110003 width=24) (actual time=889.817..2044.030 rows=1110003 loops=1)
  ->  WindowAgg  (cost=323658.48..348633.55 rows=1110003 width=25) (actual time=889.809..1598.972 rows=1110003 loops=1)
        ->  Sort  (cost=323658.48..326433.49 rows=1110003 width=16) (actual time=889.792..977.251 rows=1110003 loops=1)
              Sort Key: data.id
              Sort Method: external merge  Disk: 28296kB
              ->  Subquery Scan on data  (cost=148832.23..193232.35 rows=1110003 width=16) (actual time=309.065..746.880 rows=1110003 loops=1)
                    ->  WindowAgg  (cost=148832.23..182132.32 rows=1110003 width=24) (actual time=309.063..680.269 rows=1110003 loops=1)
                          ->  Sort  (cost=148832.23..151607.24 rows=1110003 width=8) (actual time=309.054..408.062 rows=1110003 loops=1)
                                Sort Key: pl.pl, g.g
                                Sort Method: external merge  Disk: 19632kB
                                ->  Nested Loop  (cost=0.01..22200.10 rows=1110003 width=8) (actual time=24.994..169.376 rows=1110003 loops=1)
                                      ->  Function Scan on generate_series pl  (cost=0.00..0.03 rows=3 width=4) (actual time=0.008..0.008 rows=3 loops=1)
                                      ->  Function Scan on generate_series g  (cost=0.00..3700.01 rows=370001 width=4) (actual time=8.329..32.331 rows=370001 loops=3)
Planning Time: 0.301 ms
Execution Time: 2076.234 ms

Мой

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

WindowAgg  (cost=2435.88..2713.46 rows=11103 width=24) (actual time=15.389..19.397 rows=11103 loops=1)
  ->  Sort  (cost=2435.88..2463.64 rows=11103 width=20) (actual time=15.384..15.770 rows=11103 loops=1)
        Sort Key: (row_number() OVER (?))
        Sort Method: quicksort  Memory: 1252kB
        ->  WindowAgg  (cost=968.14..1578.81 rows=11103 width=20) (actual time=5.384..14.132 rows=11103 loops=1)
              ->  WindowAgg  (cost=968.14..1301.23 rows=11103 width=24) (actual time=5.381..8.558 rows=11103 loops=1)
                    ->  Sort  (cost=968.14..995.90 rows=11103 width=8) (actual time=5.376..5.768 rows=11103 loops=1)
                          Sort Key: pl.pl, g.g
                          Sort Method: quicksort  Memory: 905kB
                          ->  Nested Loop  (cost=0.01..222.09 rows=11103 width=8) (actual time=0.608..3.366 rows=11103 loops=1)
                                ->  Function Scan on generate_series pl  (cost=0.00..0.03 rows=3 width=4) (actual time=0.009..0.009 rows=3 loops=1)
                                ->  Function Scan on generate_series g  (cost=0.00..37.01 rows=3701 width=4) (actual time=0.198..0.528 rows=3701 loops=3)
Planning Time: 0.320 ms
Execution Time: 19.752 ms

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

12.08.2019, 23:16

#39848554

Troglodit

Гость

Проставить row_number, сбрасывая его при каждой смене значений

Все понятно, на виртуалке мало ресурсов, он на диск сбрасывает в вашем варианте, поэтому так медленно.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

13.08.2019, 07:31

#39848587

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

TrogloditВсе понятно, на виртуалке мало ресурсов, он на диск сбрасывает в вашем варианте, поэтому так медленно.
дело не в этом.

оба сравнённые вами варианты "ваши".
самое существенное отличие -- размер выборки отличается на 2 порядка.

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

мои же циферки выше по треду -- это сравнение прямого прохода в лоб с корявым но очевидным хаком через сиквенс 21945605 . и "вашего" двухколенчатого, но тоже односортного -- в моей редакции 21946974 . на одинаковых объёмах. они практически совпали.

и ещё -- вы зря вернулись к цте -- у вас появился лишний сорт , по сравнению с редакцией PgSQLanonymous3 . если не вру.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

13.08.2019, 07:33

#39848588

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

qwwq,

соврал. в 10-ке бы появился. в 12 оно кажется не материализует.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

13.08.2019, 10:06

#39848638

qwwq

Участник

Сообщения: 2 880
Рейтинг: 0 / 0

Проставить row_number, сбрасывая его при каждой смене значений

документирую

сиквенсом

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

create temporary sequence if not exists _ts;
set max_parallel_workers_per_gather   TO 0;

with
_data ( a, b,id) as (
	select (g/7) % 5 as a, (g/13)% 7 as b,row_number()over(order by pl,g)::bigint rn from generate_series(0,370000) as g,generate_series(0,2) as pl
	order by pl,g
  )

select id,a,b
	,case when lag((a,b))over(order by id) is distinct from (a,b) then setval('_ts',1) else nextval('_ts') end from _data order by id;

Код: 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.

"WindowAgg  (cost=269325.69..294325.69 rows=1000000 width=24) (actual time=1341.502..2211.468 rows=1110003 loops=1)"
"  Output: _data.id, _data.a, _data.b, CASE WHEN (lag(ROW(_data.a, _data.b)) OVER (?) IS DISTINCT FROM ROW(_data.a, _data.b)) THEN setval('_ts'::regclass, '1'::bigint) ELSE nextval('_ts'::regclass) END"
"  Buffers: shared hit=15, local hit=1110003"
"  CTE _data"
"    ->  WindowAgg  (cost=119667.85..149667.85 rows=1000000 width=24) (actual time=646.927..996.583 rows=1110003 loops=1)"
"          Output: ((g.g / 7) % 5), ((g.g / 13) % 7), row_number() OVER (?), pl.pl, g.g"
"          Buffers: shared hit=3"
"          ->  Sort  (cost=119667.85..122167.85 rows=1000000 width=8) (actual time=646.917..705.544 rows=1110003 loops=1)"
"                Output: pl.pl, g.g"
"                Sort Key: pl.pl, g.g"
"                Sort Method: quicksort  Memory: 95723kB"
"                Buffers: shared hit=3"
"                ->  Nested Loop  (cost=0.01..20010.01 rows=1000000 width=8) (actual time=46.962..231.088 rows=1110003 loops=1)"
"                      Output: pl.pl, g.g"
"                      ->  Function Scan on pg_catalog.generate_series g  (cost=0.00..10.00 rows=1000 width=4) (actual time=46.952..60.635 rows=370001 loops=1)"
"                            Output: g.g"
"                            Function Call: generate_series(0, 370000)"
"                      ->  Function Scan on pg_catalog.generate_series pl  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.000..0.000 rows=3 loops=370001)"
"                            Output: pl.pl"
"                            Function Call: generate_series(0, 2)"
"  ->  Sort  (cost=119657.84..122157.84 rows=1000000 width=16) (actual time=1341.461..1396.901 rows=1110003 loops=1)"
"        Output: _data.id, _data.a, _data.b"
"        Sort Key: _data.id"
"        Sort Method: quicksort  Memory: 95723kB"
"        Buffers: shared hit=6"
"        ->  CTE Scan on _data  (cost=0.00..20000.00 rows=1000000 width=16) (actual time=646.929..1234.123 rows=1110003 loops=1)"
"              Output: _data.id, _data.a, _data.b"
"              Buffers: shared hit=3"
"Planning time: 0.379 ms"
"Execution time: 2263.704 ms"

окном-макс-фильтр

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

WITH data AS (
SELECT ROW_NUMBER() OVER (ORDER BY pl, g)::bigint AS id,
       (g / 7) % 5 AS a, (g / 13) % 7 AS b
  FROM generate_series(0, 370000) AS g, generate_series(0, 2) AS pl
)
SELECT id, a, b,
       1 + rn - COALESCE(MAX(rn)FILTER(WHERE changed)OVER(ORDER BY id), 1) AS result
  FROM (
       SELECT id, a, b, ROW_NUMBER() OVER w AS rn,
              --NULLIF((a, b) <> LAG((a, b), 1, (a, b)) OVER w, false) AS changed
              case when (a, b) is distinct from LAG((a, b), 1, (a, b))OVER w then true end AS changed
         FROM data
       WINDOW w AS (ORDER BY id)
       ) AS with_flag;

Код: 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.

"WindowAgg  (cost=269325.69..321825.69 rows=1000000 width=24) (actual time=1312.467..2312.313 rows=1110003 loops=1)"
"  Output: data.id, data.a, data.b, ((1 + (row_number() OVER (?))) - COALESCE(max((row_number() OVER (?))) FILTER (WHERE (CASE WHEN (ROW(data.a, data.b) IS DISTINCT FROM lag(ROW(data.a, data.b), 1, ROW(data.a, data.b)) OVER (?)) THEN true ELSE NULL::boolean END)) OVER (?), '1'::bigint))"
"  Buffers: shared hit=6"
"  CTE data"
"    ->  WindowAgg  (cost=119667.85..149667.85 rows=1000000 width=24) (actual time=648.381..970.928 rows=1110003 loops=1)"
"          Output: row_number() OVER (?), ((g.g / 7) % 5), ((g.g / 13) % 7), pl.pl, g.g"
"          Buffers: shared hit=3"
"          ->  Sort  (cost=119667.85..122167.85 rows=1000000 width=8) (actual time=648.372..701.564 rows=1110003 loops=1)"
"                Output: pl.pl, g.g"
"                Sort Key: pl.pl, g.g"
"                Sort Method: quicksort  Memory: 95723kB"
"                Buffers: shared hit=3"
"                ->  Nested Loop  (cost=0.01..20010.01 rows=1000000 width=8) (actual time=52.477..243.001 rows=1110003 loops=1)"
"                      Output: pl.pl, g.g"
"                      ->  Function Scan on pg_catalog.generate_series g  (cost=0.00..10.00 rows=1000 width=4) (actual time=52.464..65.580 rows=370001 loops=1)"
"                            Output: g.g"
"                            Function Call: generate_series(0, 370000)"
"                      ->  Function Scan on pg_catalog.generate_series pl  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.000..0.000 rows=3 loops=370001)"
"                            Output: pl.pl"
"                            Function Call: generate_series(0, 2)"
"  ->  WindowAgg  (cost=119657.84..142157.84 rows=1000000 width=25) (actual time=1312.460..1951.216 rows=1110003 loops=1)"
"        Output: data.id, data.a, data.b, row_number() OVER (?), CASE WHEN (ROW(data.a, data.b) IS DISTINCT FROM lag(ROW(data.a, data.b), 1, ROW(data.a, data.b)) OVER (?)) THEN true ELSE NULL::boolean END"
"        Buffers: shared hit=6"
"        ->  Sort  (cost=119657.84..122157.84 rows=1000000 width=16) (actual time=1312.448..1363.262 rows=1110003 loops=1)"
"              Output: data.id, data.a, data.b"
"              Sort Key: data.id"
"              Sort Method: quicksort  Memory: 95723kB"
"              Buffers: shared hit=6"
"              ->  CTE Scan on data  (cost=0.00..20000.00 rows=1000000 width=16) (actual time=648.384..1206.839 rows=1110003 loops=1)"
"                    Output: data.id, data.a, data.b"
"                    Buffers: shared hit=3"
"Planning time: 0.285 ms"
"Execution time: 2364.456 ms"

CTE9.6

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

WITH data AS (
SELECT ROW_NUMBER() OVER (ORDER BY pl, g)::bigint AS id,
       (g / 7) % 5 AS a, (g / 13) % 7 AS b
  FROM generate_series(0, 370000) AS g, generate_series(0, 2) AS pl
)
,with_flag as (
       SELECT id, a, b, ROW_NUMBER() OVER w AS rn,
              --NULLIF((a, b) <> LAG((a, b), 1, (a, b)) OVER w, false) AS changed
              case when (a, b) is distinct from LAG((a, b), 1, (a, b))OVER w then true end AS changed
         FROM data
       WINDOW w AS (ORDER BY id)
       ) 
SELECT id, a, b,
       1 + rn - COALESCE(MAX(rn)FILTER(WHERE changed)OVER(ORDER BY id), 1) AS result
  FROM  with_flag;

Код: 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.

"WindowAgg  (cost=411483.53..433983.53 rows=1000000 width=24) (actual time=2359.851..2738.958 rows=1110003 loops=1)"
"  Output: with_flag.id, with_flag.a, with_flag.b, ((1 + with_flag.rn) - COALESCE(max(with_flag.rn) FILTER (WHERE with_flag.changed) OVER (?), '1'::bigint))"
"  Buffers: shared hit=6"
"  CTE data"
"    ->  WindowAgg  (cost=119667.85..149667.85 rows=1000000 width=24) (actual time=657.680..1003.974 rows=1110003 loops=1)"
"          Output: row_number() OVER (?), ((g.g / 7) % 5), ((g.g / 13) % 7), pl.pl, g.g"
"          Buffers: shared hit=3"
"          ->  Sort  (cost=119667.85..122167.85 rows=1000000 width=8) (actual time=657.671..715.961 rows=1110003 loops=1)"
"                Output: pl.pl, g.g"
"                Sort Key: pl.pl, g.g"
"                Sort Method: quicksort  Memory: 95723kB"
"                Buffers: shared hit=3"
"                ->  Nested Loop  (cost=0.01..20010.01 rows=1000000 width=8) (actual time=47.598..231.702 rows=1110003 loops=1)"
"                      Output: pl.pl, g.g"
"                      ->  Function Scan on pg_catalog.generate_series g  (cost=0.00..10.00 rows=1000 width=4) (actual time=47.587..60.866 rows=370001 loops=1)"
"                            Output: g.g"
"                            Function Call: generate_series(0, 370000)"
"                      ->  Function Scan on pg_catalog.generate_series pl  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.000..0.000 rows=3 loops=370001)"
"                            Output: pl.pl"
"                            Function Call: generate_series(0, 2)"
"  CTE with_flag"
"    ->  WindowAgg  (cost=119657.84..142157.84 rows=1000000 width=25) (actual time=1364.143..2042.938 rows=1110003 loops=1)"
"          Output: data.id, data.a, data.b, row_number() OVER (?), CASE WHEN (ROW(data.a, data.b) IS DISTINCT FROM lag(ROW(data.a, data.b), 1, ROW(data.a, data.b)) OVER (?)) THEN true ELSE NULL::boolean END"
"          Buffers: shared hit=3"
"          ->  Sort  (cost=119657.84..122157.84 rows=1000000 width=16) (actual time=1364.130..1422.194 rows=1110003 loops=1)"
"                Output: data.id, data.a, data.b"
"                Sort Key: data.id"
"                Sort Method: quicksort  Memory: 95723kB"
"                Buffers: shared hit=3"
"                ->  CTE Scan on data  (cost=0.00..20000.00 rows=1000000 width=16) (actual time=657.682..1252.589 rows=1110003 loops=1)"
"                      Output: data.id, data.a, data.b"
"                      Buffers: shared hit=3"
"  ->  Sort  (cost=119657.84..122157.84 rows=1000000 width=25) (actual time=2359.842..2414.620 rows=1110003 loops=1)"
"        Output: with_flag.id, with_flag.a, with_flag.b, with_flag.rn, with_flag.changed"
"        Sort Key: with_flag.id"
"        Sort Method: quicksort  Memory: 116967kB"
"        Buffers: shared hit=6"
"        ->  CTE Scan on with_flag  (cost=0.00..20000.00 rows=1000000 width=25) (actual time=1364.145..2259.908 rows=1110003 loops=1)"
"              Output: with_flag.id, with_flag.a, with_flag.b, with_flag.rn, with_flag.changed"
"              Buffers: shared hit=3"
"Planning time: 0.278 ms"
"Execution time: 2804.798 ms"

---version()
"PostgreSQL 9.6.15 on x86_64-pc-linux-gnu (Ubuntu 9.6.15-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&mobile=1&tid=1995079]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	45ms
get topic data:	14ms
get forum data:	3ms
get page messages:	60ms
get tp. blocked users:	2ms
others:	275ms

total:	433ms