ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Составные рейтинги средствами SQL
21 сообщений из 21, страница 1 из 1
Составные рейтинги средствами SQL
    #38699898
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
Друзья, помогите ботанику, пожалуйста.
Как сделать рейтинг вида "Больше всех Черных драконов убил герой Горыныч; Красных - герой Добрыня, ...", понятно даже мне.
А как средствами SQL сделать рейтинг вида "Больше всех Черных драконов убили (в порядке убывания количества убиенных) герои Горыныч, Добрыня и Зингельшухер; Красных - ..."?
С LIMIT в подзапросах меня законно шлют лесом.

Если слишком туманно, то могу привести структуры и пр.
...
Рейтинг: 0 / 0
18.07.2014, 08:36:34
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38700072
/\/\/\/\/\/\
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
/\/\/\/\/\/\
Гость
phdoc,

Так и пишите запрос:

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

При чем здесь PostgreSQL?
...
Рейтинг: 0 / 0
18.07.2014, 10:53:15
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38700960
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
/\/\/\/\/\/\,
Когда я хочу выбрать для каждого вида юнитов по одному рекордсмену, то я так и пишу, спасибо.
Однако, я хочу для каждого вида юнитов выбрать по три рекордсмена, в порядке убывания их рекордов. Пока не получается, хотя чую, что задача почти из учебника.

Имею структуры:
 
Код: 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.
CREATE TABLE heroes
(
  hero_id serial NOT NULL,
  to_thero_id integer,
  level integer,
  CONSTRAINT heroes_pkey PRIMARY KEY (hero_id)
);

CREATE TABLE tunit
(
  tunit_id serial NOT NULL,
  to_race_id integer,
  level integer,
  sub_level integer DEFAULT 1,
  CONSTRAINT tunit_pkey PRIMARY KEY (tunit_id)
);

CREATE TABLE stats_killed_units_details
(
  to_tunit_id integer NOT NULL,
  to_hero_id integer NOT NULL,
  to_battle_id integer NOT NULL,
  counts integer,
  CONSTRAINT killed_units_details_pkey PRIMARY KEY (to_hero_id, to_tunit_id, to_battle_id)
);

Хочу получить:tunit_idhero_idstats_killed_units_details.countsstats_killed_units_details.to_battle_id1hero110010hero2903hero38012hero4100011hero5900110hero680012...
...
Рейтинг: 0 / 0
19.07.2014, 11:47:47
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38700978
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
phdoc,

Вариант 1:
посмотрите в строну group by c последуюшей обработкой результата через window function
http://www.postgresql.org/docs/9.3/interactive/tutorial-window.html
+
http://www.postgresql.org/docs/9.3/interactive/functions-window.html
(и на всякий случай заодно подумайте что вам нужнее row_numer или rank или dense_rank)

Вариант 2:
вместо LIMIT в подзапросах - сделайте тоже самое через LATERAL JOIN (он как раз для задачи когда надо выполнять подзапросы возвращаюшие несколько строк).

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
19.07.2014, 12:50:34
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38701018
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
Maxim Boguk,
Так и подозревал, что всё упрется в оконные функции.
LATERAL, как я понял, пришел к нам в 9.3, а живу пока на 9.1. Хотя, чем не повод переехать.
Спасибо.
...
Рейтинг: 0 / 0
19.07.2014, 15:02:07
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38701630
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdocMaxim Boguk,
Так и подозревал, что всё упрется в оконные функции.
LATERAL, как я понял, пришел к нам в 9.3, а живу пока на 9.1. Хотя, чем не повод переехать.
Спасибо.ну почему же. есть третий путь -- через CTE "WITH RECURSIVE"
вот тут
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=969733&msg=13171811
есть даже некая почти удачная реализация чего-то похожего (если у вас есть "матвью" итогов, индексированное составным, в т.ч. по итогам -- это будет побыстрее. если такого "матвью" нет - оконные над агрегатами, как советуют, - лучший выбор).
...
Рейтинг: 0 / 0
21.07.2014, 10:49:36
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702685
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
Отчитываюсь.
Дано: герои убивают в битвах юнитов разных рас, уровней и подуровней.
Задача: получить для каждого типа юнитов заданной расы трех лучших героев, убивших максимальное их количество в одной конкретной битве.

Данные хранятся в таблицах:
Типы юнитов. 215 строк.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE tunit
(
  unit_type_id serial NOT NULL,
  to_race_id integer,
  level integer,
  sub_level integer,
  CONSTRAINT tunit_pkey PRIMARY KEY (tunit_id)
);


Детальная статистика. 18 миллионов строк.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE stats_killed_units_details
(
  to_hero_id integer NOT NULL,
  to_tunit_id integer NOT NULL,
  to_battle_id integer NOT NULL,
  count integer,
  CONSTRAINT killed_units_details_pkey PRIMARY KEY (to_hero_id, to_tunit_id, to_battle_id)
);


До текущего момента я выбирал все типы юнитов нужной расы (их 14) и для каждого в цикле отдельным запросом получал нужную тройку рекордсменов из детальной таблицы. На всё уходило порядка 30 секунд :о(
Результат, конечно, кэшировал, но был сильно недоволен.

По совету бывалых товарищей (спасибо им огромное) попытался погрузиться в оконные функции и соорудил запрос:
Код: 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.
WITH tu AS ( 
	SELECT	t20.unit_type_id AS uid , t20.level , t20.to_race_id , t20.sub_level
	FROM	tunit AS t20
	WHERE	t20.to_race_id = 1
)
SELECT	res.uid ,
	res.level ,
	res.to_race_id ,
	res.sub_level ,
	res.to_hero_id ,
	res.count AS c ,
	res.to_battle_id ,
	res.pos
FROM	
(
	SELECT	DISTINCT ON ( tu.uid , tu.level , tu.to_race_id , tu.sub_level , d.to_hero_id , d.count , d.to_battle_id )
		tu.uid ,
		tu.level ,
		tu.to_race_id ,
		tu.sub_level ,
		d.to_hero_id ,
		d.count ,
		d.to_battle_id ,
		row_number( ) OVER ( PARTITION BY d.to_tunit_id ORDER BY d.count DESC ) AS pos
	FROM	tu
		INNER JOIN stats_killed_units_details AS d ON ( tu.uid = d.to_tunit_id )
) AS res
WHERE	res.pos < 4
ORDER	BY res.level DESC , res.to_race_id , res.sub_level DESC , res.pos


Вот его explain:
Код: 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.
"Sort  (cost=619302.48..620569.91 rows=506974 width=36) (actual time=20587.929..20587.931 rows=42 loops=1)"
"  Sort Key: res.level, res.to_race_id, res.sub_level, res.pos"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=109081, temp read=28005 written=28005"
"  CTE tu"
"    ->  Seq Scan on tunit t20  (cost=0.00..12.82 rows=15 width=16) (actual time=0.028..0.123 rows=14 loops=1)"
"          Filter: (to_race_id = 1)"
"          Buffers: shared hit=12"
"  ->  Subquery Scan on res  (cost=535508.24..571249.93 rows=506974 width=36) (actual time=19547.515..20587.878 rows=42 loops=1)"
"        Filter: (res.pos < 4)"
"        Buffers: shared hit=109081, temp read=28005 written=28005"
"        ->  Unique  (cost=535508.24..565926.70 rows=1520923 width=32) (actual time=19547.110..20310.391 rows=2387215 loops=1)"
"              Buffers: shared hit=109081, temp read=28005 written=28005"
"              ->  Sort  (cost=535508.24..539310.55 rows=1520923 width=32) (actual time=19547.110..19851.410 rows=2387215 loops=1)"
"                    Sort Key: tu.uid, tu.level, tu.to_race_id, tu.sub_level, d.to_hero_id, d.count, d.to_battle_id"
"                    Sort Method: external sort  Disk: 126024kB"
"                    Buffers: shared hit=109081, temp read=28005 written=28005"
"                    ->  WindowAgg  (cost=362605.80..379335.95 rows=1520923 width=32) (actual time=9823.413..11497.474 rows=2387215 loops=1)"
"                          Buffers: shared hit=109081, temp read=12252 written=12252"
"                          ->  Sort  (cost=362605.80..366408.10 rows=1520923 width=32) (actual time=9823.405..10486.162 rows=2387215 loops=1)"
"                                Sort Key: tu.uid, d.count"
"                                Sort Method: external merge  Disk: 98008kB"
"                                Buffers: shared hit=109081, temp read=12252 written=12252"
"                                ->  Hash Join  (cost=0.08..206433.50 rows=1520923 width=32) (actual time=0.159..4388.110 rows=2387215 loops=1)"
"                                      Hash Cond: (d.to_tunit_id = tu.uid)"
"                                      Buffers: shared hit=109081"
"                                      ->  Seq Scan on stats_killed_units_details d  (cost=0.00..129246.58 rows=20177578 width=16) (actual time=0.010..1793.577 rows=20173682 loops=1)"
"                                            Buffers: shared hit=109069"
"                                      ->  Hash  (cost=0.03..0.03 rows=15 width=16) (actual time=0.140..0.140 rows=14 loops=1)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                            Buffers: shared hit=12"
"                                            ->  CTE Scan on tu  (cost=0.00..0.03 rows=15 width=16) (actual time=0.030..0.135 rows=14 loops=1)"
"                                                  Buffers: shared hit=12"
"Total runtime: 20653.238 ms"


20 секунд - уже лучше. Запрос к базе один, а не 14.
Однако, вопросы:
1. Нет ли ошибок в логике запроса?
2. Что-то где-то можно ли улучшить, кроме как серверное железо?
...
Рейтинг: 0 / 0
22.07.2014, 11:47:15
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702729
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdoc,
т.к. у вас уже есть собранная материализованная статистика, то вместо окон вам нужен составной индекс на статистику по
Код: sql
1.
(level [DESC] , to_race_id [DESC] ,sub_level [DESC]  , count [DESC] )


(комбинацию ASC|DESC -- из ваших хотелок, хотя можно по всякому)
а остальное внутри ключевого набора (рассы,уровня,подуровня) отбирать без всяких окон -- простым селектом ORDER BY ({keys} ,count DESC) limit 3.

как это сделать сразу для кучки (чтобы пройтись вдоль индексов всего по 3 записи -- можете покурить на тему последнего поста по ссылке, но проще функцией с прямым перебором ключевого набора в LOOP, чем через CTE хотя и то -- как видно по ссылке -- возможно).
...
Рейтинг: 0 / 0
22.07.2014, 12:24:13
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702732
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

хотя я кажжется не вчитался, счас пересмотрю
...
Рейтинг: 0 / 0
22.07.2014, 12:25:41
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702742
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
вам, похоже, вообще достаточно индекса по
(to_tunit_id,count [DESC])
+ техники прогона только LIMIT по индексу в рекурсивном cte ли, в лупе по to_tunit_id ли.

извините, что проглядываю наискосок. не вчитываясь
...
Рейтинг: 0 / 0
22.07.2014, 12:31:33
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702817
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
qwwq, спасибо.
Сижу и смотрю в бездну своего незнания SQL.
С ходу тревожит мысль о ресурсоёмкости включения count в индекс на таблице в 20КК записей.
...
Рейтинг: 0 / 0
22.07.2014, 13:18:13
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702856
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdocqwwq, спасибо.
Сижу и смотрю в бездну своего незнания SQL.
С ходу тревожит мысль о ресурсоёмкости включения count в индекс на таблице в 20КК записей.

в любом случае постройте индекс на макете. лямов на 1-2. отработайте простой запрос {LIMIT 3} по unit_id и фиксированному набору ключей. Соорудите CTE или ф-ю с LOOP ... RETURN QUERY -- они строятся идентично, прогоном по таблице юнитов (unit_id), или их подвыборке. Главное -- это запинать (фрагмент) на тот же план, что и индивидуальный запрос по фиксированному unit_id. А уже результат повяжите на справочники.

ну и индекс в бою стройте CONCURRENTLY .
...
Рейтинг: 0 / 0
22.07.2014, 13:48:45
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702892
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
например, (опять не вчитываясь, простите, т.к. "пусть лошадь думает, у ей голова большая) )

перелицовываем механически (массовой заменой) пример по ссылке под вас (вроде бы не вру, но вчитываться ломает -- поползайте с лупой сами):

тест-кейс:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
DROP TABLE IF EXISTS test_table;
--добавим id
CREATE table test_table(id serial, unit_id integer, batle_id integer, count integer);
INSERT into test_table (unit_id,batle_id,count)
  select (random()*10)::integer, (random()*100)::integer, (random()*10000)::integer  from generate_series(1,1000000);
create index test_table_unit_id_key on test_table(unit_id);
create index test_table_batle_id_key on test_table(batle_id);
create index test_table_count_key on test_table(count);

-- наш индекс
create index test_table_low_count_key on test_table(unit_id,count );
analyze test_table;

SELECT count(1) FROM test_table;
-- 1000000



запрос:
 
Код: 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.
WITH RECURSIVE collector AS 
	(
		(SELECT t.*
			, 1 AS n ,1 AS m
		FROM test_table t
		ORDER BY unit_id DESC, count DESC LIMIT 1)
	UNION ALL

		(SELECT *
		 FROM ( WITH c AS (SELECT * FROM COLLECTOR WHERE n=1 ORDER BY unit_id DESC, count DESC LIMIT 1 )
		  (SELECT 
			 t.*
			, 2  AS n
			, c.m AS m
			FROM test_table t		
			INNER JOIN c ON ((SELECT min(c.unit_id) FROM c)=t.unit_id AND  c.count>=t.count)
		--/*
			WHERE t.id <>c.id
				
		--*/							
			ORDER BY t.unit_id DESC, t.count DESC LIMIT 3)
	--/*
			UNION ALL 
			(SELECT 
			 t.*
			, 1  AS n
			, c.m+1 AS m
			FROM test_table t		
			INNER JOIN c ON
			 ((SELECT min(c.unit_id) FROM c)>t.unit_id)
			ORDER BY t.unit_id DESC, t.count DESC LIMIT 1)
	--*/		
			) foo
		--WHERE m<12
		)
	) 
SELECT * FROM collector --WHERE m <12
ORDER BY unit_id DESC,count DESC, n,m;



план:
 
Код: 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.
Sort  (cost=42.81..42.91 rows=41 width=24) (actual time=1.105..1.113 rows=44 loops=1)
  Sort Key: collector.unit_id, collector.count, collector.n, collector.m
  Sort Method: quicksort  Memory: 28kB
  CTE collector
    ->  Recursive Union  (cost=0.42..40.89 rows=41 width=24) (actual time=0.028..1.019 rows=44 loops=1)
          ->  Subquery Scan on "*SELECT* 1"  (cost=0.42..0.48 rows=1 width=16) (actual time=0.025..0.026 rows=1 loops=1)
                ->  Limit  (cost=0.42..0.47 rows=1 width=16) (actual time=0.024..0.024 rows=1 loops=1)
                      ->  Index Scan Backward using test_table_low_count_key on test_table t  (cost=0.42..47505.77 rows=1000000 width=16) (actual time=0.023..0.023 rows=1 loops=1)
          ->  Append  (cost=0.70..3.92 rows=4 width=24) (actual time=0.042..0.079 rows=4 loops=12)
                CTE c
                  ->  Limit  (cost=0.23..0.24 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=12)
                        ->  Sort  (cost=0.23..0.24 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=12)
                              Sort Key: collector_1.unit_id, collector_1.count
                              Sort Method: quicksort  Memory: 25kB
                              ->  WorkTable Scan on collector collector_1  (cost=0.00..0.22 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=12)
                                    Filter: (n = 1)
                                    Rows Removed by Filter: 3
                ->  Limit  (cost=0.46..3.07 rows=3 width=20) (actual time=0.041..0.048 rows=3 loops=12)
                      InitPlan 2 (returns $2)
                        ->  Aggregate  (cost=0.02..0.03 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=12)
                              ->  CTE Scan on c  (cost=0.00..0.02 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=12)
                      ->  Nested Loop  (cost=0.42..26359.51 rows=30303 width=20) (actual time=0.041..0.047 rows=3 loops=12)
                            Join Filter: ((c_2.count >= t_1.count) AND (t_1.id <> c_2.id))
                            Rows Removed by Join Filter: 1
                            ->  Index Scan Backward using test_table_low_count_key on test_table t_1  (cost=0.42..24086.78 rows=90909 width=16) (actual time=0.023..0.028 rows=4 loops=12)
                                  Index Cond: ($2 = unit_id)
                            ->  CTE Scan on c c_2  (cost=0.00..0.02 rows=1 width=12) (actual time=0.000..0.001 rows=1 loops=44)
                ->  Limit  (cost=0.46..0.57 rows=1 width=20) (actual time=0.028..0.028 rows=1 loops=12)
                      InitPlan 3 (returns $3)
                        ->  Aggregate  (cost=0.02..0.03 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=12)
                              ->  CTE Scan on c c_1  (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)
                      ->  Nested Loop  (cost=0.42..38497.17 rows=333333 width=20) (actual time=0.028..0.028 rows=1 loops=12)
                            ->  Index Scan Backward using test_table_low_count_key on test_table t_2  (cost=0.42..30997.17 rows=333333 width=16) (actual time=0.019..0.019 rows=1 loops=12)
                                  Index Cond: ($3 > unit_id)
                            ->  CTE Scan on c c_3  (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10)
  ->  CTE Scan on collector  (cost=0.00..0.82 rows=41 width=24) (actual time=0.031..1.064 rows=44 loops=1)
Total runtime: 1.306 ms

...
Рейтинг: 0 / 0
22.07.2014, 14:07:19
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38702931
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
qwwq, это какая-то магия :о)
Ушел гонять с лупой крестьян на тестовом сервере.
...
Рейтинг: 0 / 0
22.07.2014, 14:21:06
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703074
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdoc,

на самом деле это всё крутится вокруг вот этой техники
http://www.sql.ru/forum/882778/bystryy-podschet-distinct-values-po-indeksirovannym-polyam?mid=11326416#11326416

причем
1. вам дистинкт unit_id извлекать не нужно -- он у вас в справочнике юнитов сидит битым словом.
2. lateral замаскированный в postgresl даже 9.1 есть, просто он выглядит например как
Код: sql
1.
 unnest(ARRAY(SELECT fld FROM slave s WHERE s.key = m.key [ORDER BY ... LIMIT ...]))


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

вся же идея индексации в том, что она нужна внутри этого (так или иначе состряпанного) lateral-подзапроса -- для скорости перебора ORDER BY ..LIMIT . Только и всего.
...
Рейтинг: 0 / 0
22.07.2014, 15:44:38
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703533
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
Докладываю.
Запрос:
 
Код: 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.
WITH RECURSIVE collector AS 
(
	(	SELECT t.*
		, 1 AS n ,1 AS m
		FROM stats_killed_units_details t
		ORDER BY to_tunit_id DESC, count DESC LIMIT 1
	)
	UNION ALL
	(
		SELECT *
		FROM 
		( 
			WITH c AS ( SELECT * FROM COLLECTOR WHERE n=1 ORDER BY to_tunit_id DESC, count DESC LIMIT 1 )
			(	
				SELECT 
				 t.*
				, 2  AS n
				, c.m AS m
				FROM stats_killed_units_details t		
					INNER JOIN c ON ( ( SELECT min( c.to_tunit_id ) FROM c )=t.to_tunit_id AND  c.count>=t.count )
				WHERE 	t.to_battle_id <> c.to_battle_id 
				ORDER BY t.to_tunit_id DESC, t.count DESC LIMIT 2
			)
			UNION ALL 
			(
				SELECT 
				 t.*
				, 1  AS n
				, c.m+1 AS m
				FROM stats_killed_units_details t		
					INNER JOIN c ON ( ( SELECT min( c.to_tunit_id ) FROM c )>t.to_tunit_id )
				ORDER BY t.to_tunit_id DESC, t.count DESC LIMIT 1
			)
		) foo
	)
) 
SELECT * FROM collector
ORDER BY to_tunit_id DESC,count DESC, n,m;

Совершенно безумный план в ~10мс:
 
Код: 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.
"Sort  (cost=84.82..84.90 rows=31 width=24) (actual time=5.975..6.007 rows=615 loops=1)"
"  Sort Key: collector.to_tunit_id, collector.count, collector.n, collector.m"
"  Sort Method: quicksort  Memory: 73kB"
"  Buffers: shared hit=2030"
"  CTE collector"
"    ->  Recursive Union  (cost=0.00..83.99 rows=31 width=24) (actual time=0.015..5.618 rows=615 loops=1)"
"          Buffers: shared hit=2030"
"          ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)"
"                Buffers: shared hit=4"
"                ->  Limit  (cost=0.00..0.02 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)"
"                      Buffers: shared hit=4"
"                      ->  Index Scan using idx_stats_killed_units_details_uid_count on stats_killed_units_details t  (cost=0.00..359036.22 rows=20192029 width=16) (actual time=0.013..0.013 rows=1 loops=1)"
"                            Buffers: shared hit=4"
"          ->  Result  (cost=0.07..8.39 rows=3 width=24) (actual time=0.013..0.026 rows=3 loops=206)"
"                Buffers: shared hit=2026"
"                CTE c"
"                  ->  Limit  (cost=0.06..0.06 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=206)"
"                        ->  Sort  (cost=0.06..0.06 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=206)"
"                              Sort Key: collector.to_tunit_id, collector.count"
"                              Sort Method: quicksort  Memory: 25kB"
"                              ->  WorkTable Scan on collector  (cost=0.00..0.05 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=206)"
"                                    Filter: (n = 1)"
"                ->  Append  (cost=0.01..8.33 rows=3 width=24) (actual time=0.013..0.026 rows=3 loops=206)"
"                      Buffers: shared hit=2026"
"                      ->  Limit  (cost=0.01..8.27 rows=2 width=20) (actual time=0.013..0.015 rows=2 loops=206)"
"                            Buffers: shared hit=1207"
"                            InitPlan 2 (returns $2)"
"                              ->  Aggregate  (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=206)"
"                                    ->  CTE Scan on c  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=206)"
"                            ->  Nested Loop  (cost=0.00..139812.44 rows=33822 width=20) (actual time=0.013..0.014 rows=2 loops=206)"
"                                  Join Filter: ((c.count >= t.count) AND (t.to_battle_id <> c.to_battle_id))"
"                                  Buffers: shared hit=1207"
"                                  ->  Index Scan using idx_stats_killed_units_details_uid_count on stats_killed_units_details t  (cost=0.00..139102.17 rows=101467 width=16) (actual time=0.006..0.008 rows=3 loops=206)"
"                                        Index Cond: ($2 = to_tunit_id)"
"                                        Buffers: shared hit=1207"
"                                  ->  CTE Scan on c  (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=615)"
"                      ->  Limit  (cost=0.01..0.05 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=206)"
"                            Buffers: shared hit=819"
"                            InitPlan 3 (returns $3)"
"                              ->  Aggregate  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=206)"
"                                    ->  CTE Scan on c  (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=206)"
"                            ->  Nested Loop  (cost=0.00..311922.13 rows=6730676 width=20) (actual time=0.010..0.010 rows=1 loops=206)"
"                                  Buffers: shared hit=819"
"                                  ->  Index Scan using idx_stats_killed_units_details_uid_count on stats_killed_units_details t  (cost=0.00..281634.09 rows=6730676 width=16) (actual time=0.008..0.008 rows=1 loops=206)"
"                                        Index Cond: ($3 > to_tunit_id)"
"                                        Buffers: shared hit=819"
"                                  ->  CTE Scan on c  (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=204)"
"  ->  CTE Scan on collector  (cost=0.00..0.06 rows=31 width=24) (actual time=0.017..5.849 rows=615 loops=1)"
"        Buffers: shared hit=2030"
"Total runtime: 6.102 ms"

Правильный подход и правильный индекс правильно используются, да.

В результате мгновенно получаю рейтинги для всех сразу типов юнитов, что великолепно.
Но. Потерялась возможность фильтровать по to_hero_id: "взять только героев 10 уровня" или "взять только варваров". И это невыносимо печально.
Вернее, такая возможность есть, но при попытке прилепить нужную таблицу для фильтра, время запроса приближается в таковому для варианта с оконной функцией.
...
Рейтинг: 0 / 0
23.07.2014, 09:25:27
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703600
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdoc,
вы таки полазьте еще в теме мексима -- чтобы понять, что вам на самом деле важно, а что нет. (я уже писал, что часть механически преобразованного запроса занимается извлечением дистинкта по unit-ам из вашей отдельной таблицы, что вам не нужно.

т.е. в терминах 9.3. у вас был бы идеальный план изначальной задачи (с предложенным индексом) по запросу вида:
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT tunit_id 
,q.to_hero_id
,q.to_battle_id
,q.counts
  FROM tunit t
,LATERAL (SELECT to_hero_id ,to_battle_id,counts 
	FROM stats_killed_units_details s WHERE s.to_tunit_id= t.tunit_id ORDER BY counts DESC LIMIT 3) q  ;


или, для 9.1. ( без последующего расщепления искусственной строки--компаунда [, что несложно])

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT tunit_id 
	,unnest(ARRAY(SELECT to_hero_id||','
		||to_battle_id||','
		||counts
		FROM stats_killed_units_details s WHERE s.to_tunit_id= t.tunit_id
		ORDER BY counts DESC LIMIT 3)  ) AS h_b_c
FROM tunit t




т.е.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT 
	tunit_id 
	,(string_to_array(h_b_c,','))[1] AS h
	,(string_to_array(h_b_c,','))[2] AS b
	,(string_to_array(h_b_c,','))[3] AS c
FROM 
(
SELECT tunit_id 
	,unnest(ARRAY(SELECT to_hero_id||','||to_battle_id||','||counts  FROM stats_killed_units_details s WHERE s.to_tunit_id= t.tunit_id ORDER BY counts DESC LIMIT 3)  ) AS h_b_c
FROM tunit t
) foo



Код: sql
1.
2.
3.
4.
5.
6.
7.
Subquery Scan on foo  (cost=0.00..904.77 rows=10000 width=36) (actual time=0.108..5.326 rows=300 loops=1)
  ->  Seq Scan on tunit t  (cost=0.00..729.77 rows=10000 width=4) (actual time=0.092..3.293 rows=300 loops=1)
        SubPlan 1
          ->  Limit  (cost=0.42..6.78 rows=3 width=12) (actual time=0.019..0.026 rows=3 loops=100)
                ->  Index Scan Backward using stats_killed_units_details_to_tunit_id_counts_idx on stats_killed_units_details s  (cost=0.42..21184.35 rows=10000 width=12) (actual time=0.018..0.024 rows=3 loops=100)
                      Index Cond: (to_tunit_id = t.tunit_id)
Total runtime: 5.448 ms





далее начинаем морщить репу:
--hero-ев у нас до heras, левелов (или рас) мало, т.ч. если нам надо по левелу или еше как (по расе) -- то ничего путного с индексным поиском не получится. (можно правда ожидать , что в поисках по расам если идти влоль нашего индекса, но не на глубину 3, а "до тех пор, пока не" встретится 3 хира нужной расы -- идти придется недалеко. если расы равносильны, и все близки к рекордам. А вот для левела 1 этого ждать явно не приходится [они ожидаются в хвосте])

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

а пока считайте нормальным, что так.


да, и если вам придется считать абсолютных рекодсменов (а не "в одно-батальных" ) -- вам тоже придется свернуть всю вашу таблицу (в 20КК) и только потом окошком просматривать. Если конечно вы не делаете свертку--матвью заранее.
...
Рейтинг: 0 / 0
23.07.2014, 10:27:03
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703695
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
qwwqвы таки полазьте еще в теме мексима -- чтобы понять, что вам на самом деле важно, а что нет. (я уже писал, что часть механически преобразованного запроса занимается извлечением дистинкта по unit-ам из вашей отдельной таблицы, что вам не нужно.Я пытаюсь. Хотя, пара десятков мс меня уже более чем устраивает, сами понимаете.

qwwqпоэтому для таких запросов наш матвью статистики неудачно собран. в него надо добавить уровни и расы в явном виде ( денормализация ). и, соответственно запросам, составные индексы с левельными и расовыми префиксами. (после чего задача сведется к разновидности предыдущей).Да. Тем более, что level там должен бы быть изначально - моя ошибка планирования. У героев он же растет, а в рекорде должен оставаться фиксированным.
Правда, при каждой необходимости в денормализации сердце кровью-то обливается.

qwwqда, и если вам придется считать абсолютных рекодсменов (а не "в одно-батальных" ) -- вам тоже придется свернуть всю вашу таблицу (в 20КК) и только потом окошком просматривать. Если конечно вы не делаете свертку--матвью заранее.Уже делаю, правда с другими целями.
...
Рейтинг: 0 / 0
23.07.2014, 11:34:30
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703746
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
qwwq,
А вот и серебряная пуля - "идеальный план изначальной задачи (с предложенным индексом)", спасибо Вам.
Запрос:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT	unit_type_id ,
	unnest( ARRAY( 
		SELECT	s.to_hero_id || ',' || s.to_battle_id || ',' || s.count
		FROM 	stats_killed_units_details AS s 
			INNER JOIN heroes AS t1 ON ( t1.hero_id = s.to_hero_id )
			INNER JOIN thero AS t2 ON ( t2.thero_id = t1.to_thero_id ) 
		WHERE 	t2.to_race_id = 1
			AND s.to_tunit_id = t.unit_type_id
		ORDER 	BY s.count DESC LIMIT 3 )  
	) AS h_b_c
FROM 	tunit AS t
WHERE	t.to_race_id = 1
ORDER	BY t.unit_type_id DESC

План:
 
Код: 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.
"Sort  (cost=933.63..933.67 rows=15 width=4) (actual time=2.116..2.120 rows=42 loops=1)"
"  Sort Key: t.unit_type_id"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=1108"
"  ->  Seq Scan on tunit t  (cost=0.00..933.34 rows=15 width=4) (actual time=0.252..2.082 rows=42 loops=1)"
"        Filter: (to_race_id = 1)"
"        Buffers: shared hit=1108"
"        SubPlan 1"
"          ->  Limit  (cost=0.00..61.37 rows=3 width=12) (actual time=0.059..0.132 rows=3 loops=14)"
"                Buffers: shared hit=1096"
"                ->  Nested Loop  (cost=0.00..230651.08 rows=11276 width=12) (actual time=0.058..0.131 rows=3 loops=14)"
"                      Join Filter: (t1.to_thero_id = t2.thero_id)"
"                      Buffers: shared hit=1096"
"                      ->  Nested Loop  (cost=0.00..229146.35 rows=101480 width=16) (actual time=0.023..0.108 rows=14 loops=14)"
"                            Buffers: shared hit=1091"
"                            ->  Index Scan using idx_stats_killed_units_details_uid_count on stats_killed_units_details s  (cost=0.00..139120.17 rows=101480 width=12) (actual time=0.017..0.035 rows=14 loops=14)"
"                                  Index Cond: (to_tunit_id = t.unit_type_id)"
"                                  Buffers: shared hit=225"
"                            ->  Index Scan using heroes_pkey on heroes t1  (cost=0.00..0.88 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=190)"
"                                  Index Cond: (hero_id = s.to_hero_id)"
"                                  Buffers: shared hit=866"
"                      ->  Materialize  (cost=0.00..5.07 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=190)"
"                            Buffers: shared hit=5"
"                            ->  Seq Scan on thero t2  (cost=0.00..5.06 rows=2 width=4) (actual time=0.006..0.014 rows=2 loops=1)"
"                                  Filter: (to_race_id = 1)"
"                                  Buffers: shared hit=5"
"Total runtime: 2.217 ms"


Любые нужные рейтинги с любыми нужными фильтрами за крайне вменяемое время. Ура.

Пока не понял, почему иногда тройка рекордсменов бывает таки не отсортирована по count, не смотря на ORDER BY s.count DESC, но, думаю, разберусь.
...
Рейтинг: 0 / 0
23.07.2014, 12:13:08
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703822
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
phdoc<>

Пока не понял, почему иногда тройка рекордсменов бывает таки не отсортирована по count, не смотря на ORDER BY s.count DESC, но, думаю, разберусь.потому что последним в плане у вас идет пересортировка.

Код: plaintext
Sort Key: t.unit_type_id"

выпарсите counts из строки, как под спойлером -- и добавьте его в ключи окончательной сортировки.
...
Рейтинг: 0 / 0
23.07.2014, 13:06:30
| Ответить | Цитировать | Написать  
Составные рейтинги средствами SQL
    #38703880
phdoc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
phdoc
Гость
qwwq,
Уже всё сделал и сижу - кликаю по рейтингам. Довольный как слон.
Спасибо.
...
Рейтинг: 0 / 0
23.07.2014, 13:47:34
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Составные рейтинги средствами SQL
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&tid=1998569]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 455ms
get topic data:
 11ms
get forum data:
 4ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 224ms
total:  792ms
созд. / загр.: 792ms
x
x
Закрыть


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