SELECT по двум таблицам / PostgreSQL

ReSQL.ru

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

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / SELECT по двум таблицам

37 сообщений из 37, показаны все 2 страниц

все

SELECT по двум таблицам

#39907129

AmonRa

Гость

Просьба помочь. В сторону какого оператора копать
Перерыл кучу стаей, нигде не нашел.

есть таблица1, где шапки документов
№-описание-статус

есть таблица 2, где строки документов
код элемента - количество - id_таблицы1

как мне отобрать документы таблицы1, в которых 3 строки и строго только следующие коды элементов (1,12,17).

Код: plsql

1.
2.
3.

SELECT * FROM table1 t1
  join table1 t2 on t1.id = t2.id_table1
  where t2.elementcode = all(array['1','12','17'])

\\выдает пусто

Код: plsql

1.
2.
3.

SELECT * FROM table1 t1
  join table1 t2 on t1.id = t2.id_table1
  where t2.elementcode = any(array['1','12','17'])

или

Код: plsql

  where t2.elementcode in ('1','12','17')

\\выдает больше значений, т.к. между значениями OR

...

Рейтинг:

0 / 0

23.12.2019, 16:34

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

SELECT по двум таблицам

#39907133

PinkCat.000003

Участник

Сообщения: 43

Рейтинг: 0 / 0

AmonRa,

Что есть pos?

...

Рейтинг:

0 / 0

23.12.2019, 16:40

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

SELECT по двум таблицам

#39907137

AmonRa

Гость

PinkCat.000003,
исправил

...

Рейтинг:

0 / 0

23.12.2019, 16:44

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

SELECT по двум таблицам

#39907154

PinkCat.000003

Участник

Сообщения: 43

Рейтинг: 0 / 0

AmonRa,

http://www.postgresqltutorial.com/postgresql-all/

Хотя... скорее всего придется считать в два этапа - первый - подсчет вхождений, второй - отбор по нужному количесtvu.

...

Рейтинг:

0 / 0

23.12.2019, 17:03

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

SELECT по двум таблицам

#39907156

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

AmonRa,

оптимальный зависит от статистики (количеств записей в таблицах и их соотношений)
можно 3 exists--а, можно джойнить на array_agg() | count(distinct ...) where в т.ч. lateral

~~ примерно в эту сторону

Код: sql

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

SELECT t1.*
	-- ,(ut2).* 
 FROM table1 t1
,lateral (select count( distinct t2.elementcode) countMyelement
			--array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where t1.id = t2.id_table1 
		AND t2.elementcode = any(array['1','12','17'])) t2
-- ,lateral unnest(table2_s) AS ut2
WHERE countMyelement = 3

...

Рейтинг:

0 / 0

23.12.2019, 17:04

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

SELECT по двум таблицам

#39907164

AmonRa

Гость

qwwq,
спасибо, но
запрос выдаст все журналы, у которых 3 строки и как минимум один элемент из массива.
но это неверно, т.к. могут быть 3-х строчные журналы включающие 1 или 2 элемента

...

Рейтинг:

0 / 0

23.12.2019, 17:14

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

SELECT по двум таблицам

#39907166

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwq,

хотя оптимально в другую сторону . куда-то туда :

-- не вычитывал

Код: sql

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

SELECT t1.*
	,(ut2).* 
 FROM
	(select
			id_table1
			--,count( distinct t2.elementcode) countMyelement
			,array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where
		t2.elementcode = any(array['1','12','17'])
	group by t2.id_table1
	having count( distinct t2.elementcode) = 3
	) t2 join table1 t1
		on t1.id = t2.id_table1
,lateral unnest(table2_s) AS ut2

...

Рейтинг:

0 / 0

23.12.2019, 17:16

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

SELECT по двум таблицам

#39907167

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

AmonRa

dhfnm nj pfxtv &

...

Рейтинг:

0 / 0

23.12.2019, 17:17

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

SELECT по двум таблицам

#39907317

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwq

qwwq,

хотя оптимально в другую сторону . куда-то туда :

-- не вычитывал и наврал. править надо примерно втуда :

Код: sql

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

SELECT t1.*
	,(ut2).* 
 FROM
	(select
			id_table1
			--,count( distinct t2.elementcode) countMyelement
			,array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where
		t2.elementcode = any(array['1','12','17'])
		-- и только они:
		and not exists(select 1 from table2 t22
				 where not t22.elementcode  = any(array['1','12','17'])
						and t22.id_table1 =t2.id_table1) 
	group by t2.id_table1
	having count( distinct t2.elementcode) = 3 -- все 3 из списка
	) t2 join table1 t1
		on t1.id = t2.id_table1
,lateral unnest(table2_s) AS ut2

...

Рейтинг:

0 / 0

23.12.2019, 22:46

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

SELECT по двум таблицам

#39907339

Troglodit

Гость

qwwq,
Зачем так усложнять с self join
В начале, очевидно, должен быть exists со слов "отобрать документы таблицы1", про таблицу 2 ни слова.
Вот мой тестовый вариант в один проход:

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

with table2 as (
    select '1' as elementcode,1 as qty,1 as id_table1
    union all
    select '17',1,1
    union all 
    select '12',1,1
    union all
    select '1',1,2
    union all
    select '17',1,2
    union all 
    select '12',1,2
    union all
    select '13',1,2
    union all
    select '17',1,3
    union all
    select '17',1,3
    union all
    select '1',1,3
    
),
table1 as(
    select 1 as id,'1' as description,0 as status
    union all
    select 2 as id,'2' as description,0 as status
    union all
    select 3 as id,'3' as description,0 as status
    )
    
select * from table1 where 
not 
exists(
select * from (
select *,count(case when elementcode in ('1','17','12') then 1 else null end) over(partition by id_table1) as cnt_filter,count(*) over(partition by id_table1) as total,
    count(*) over(partition by id_table1,elementcode) as cnt_not_uniq
from table2
) as t where 
    (cnt_filter<>3 or total<>3 or cnt_not_uniq<>1) and 
    table1.id=t.id_table1
)

...

Рейтинг:

0 / 0

24.12.2019, 02:46

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

SELECT по двум таблицам

#39907354

Troglodit

Гость

Не надо писать ерунду ночью.
Зачем здесь окна сам не пойму.
Все гораздо проще.

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

with table2 as (
    select '1' as elementcode,1 as qty,1 as id_table1
    union all
    select '17',1,1
    union all 
    select '12',1,1
    union all
    select '1',1,2
    union all
    select '17',1,2
    union all 
    select '12',1,2
    union all
    select '13',1,2
    union all
    select '17',1,3
    union all
    select '17',1,3
    union all
    select '1',1,3
    
),
table1 as(
    select 1 as id,'1' as description,0 as status
    union all
    select 2 as id,'2' as description,0 as status
    union all
    select 3 as id,'3' as description,0 as status
    )
    
select * from table1 where 
not 
exists(
select 1
from table2
where table1.id=table2.id_table1  
group by id_table1 
having
    count(case when elementcode in ('1','17','12') then 1 else null end)<>3 or count(*)<>3 or count(distinct elementcode)<>3
)

...

Рейтинг:

0 / 0

24.12.2019, 07:55

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

SELECT по двум таблицам

#39907384

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit,

если экономить буковки в селекте, то можно и ещё короче

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

with table2 (elementcode,qty,id_table1) as (values
	( 1,1 ,1 )
	,(17,1,1)
	,(12,1,1)
	,(1,1,2)
	,(17,1,2)
	,(12,1,2)
	,(13,1,2)
	,(17,1,3)
	,(17,1,3)
	,(1,1,3)
	)
,table1 (id ,description) as (VALUES 
	 (1 ,1,0)
	,(2 ,2,0)
	,(3 ,3,0)
	)
-- собсна запрос
select t1.*, (ut).* from table1 t1
join (select id_table1, array_agg(t2.*) t2_s from table2 t2 group by id_table1
	having array_agg(distinct elementcode order by elementcode) = array[1,12,17])t2
on t1.id = t2.id_table1
-- + детали
,lateral unnest(t2_s) ut (elementcode int,qty int, id_tq int)

только в зависимости от кардинальностей оно может оказаццо сильно хуже более длинного буковками запроса (а может и не сильно -- если искомого довольно много от общего, и при этом не так много деталировок по 100500 позиций)

...

Рейтинг:

0 / 0

24.12.2019, 10:13

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

SELECT по двум таблицам

#39907400

Troglodit

Гость

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.

with table2 (elementcode,qty,id_table1) as (values
	( 1,1 ,1 )
	,(17,1,1)
	,(12,1,1)
	,(1,1,2)
	,(17,1,2)
	,(12,1,2)
	,(13,1,2)
	,(17,1,3)
	,(17,1,3)
	,(1,1,3)
	)
,table1 (id ,description) as (VALUES 
	 (1 ,1,0)
	,(2 ,2,0)
	,(3 ,3,0)
	)
    
select * from table1 where 
exists(
select 1
from table2
where table1.id=table2.id_table1  
group by id_table1 
having
    count(case when elementcode in ('1','17','12') then 1 else null end)=3 and count(*)=3 and count(distinct elementcode)=3
)

Код: sql

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

"Values Scan on "*VALUES*"  (cost=0.00..0.58 rows=2 width=12)"
"  Filter: (SubPlan 1)"
"  SubPlan 1"
"    ->  GroupAggregate  (cost=0.00..0.18 rows=1 width=8)"
"          Group Key: "*VALUES*_1".column3"
"          Filter: ((count(CASE WHEN ("*VALUES*_1".column1 = ANY ('{1,17,12}'::integer[])) THEN 1 ELSE NULL::integer END) = 3) AND (count(*) = 3) AND (count(DISTINCT "*VALUES*_1".column1) = 3))"
"          ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.15 rows=1 width=8)"
"                Filter: ("*VALUES*".column1 = column3)"

будет эффективнее вашего

Код: sql

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

"Nested Loop  (cost=0.29..0.85 rows=10 width=24)"
"  ->  Nested Loop  (cost=0.29..0.65 rows=1 width=44)"
"        Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column3)"
"        ->  GroupAggregate  (cost=0.29..0.57 rows=1 width=36)"
"              Group Key: "*VALUES*_1".column3"
"              Filter: (array_agg(DISTINCT "*VALUES*_1".column1 ORDER BY "*VALUES*_1".column1) = '{1,12,17}'::integer[])"
"              ->  Sort  (cost=0.29..0.32 rows=10 width=12)"
"                    Sort Key: "*VALUES*_1".column3"
"                    ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.12 rows=10 width=12)"
"        ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=12)"
"  ->  Function Scan on unnest ut  (cost=0.00..0.10 rows=10 width=12)"

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

...

Рейтинг:

0 / 0

24.12.2019, 10:58

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

SELECT по двум таблицам

#39907412

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit,

ну давайте понудим

ddl + data

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

--- tables
create table table2 
(elementcode int,qty int, id_table1 int)
;
create table table1 
(id int,description text)
;
----------- data --
insert into table2 
SELECT
 (30 *RANDOM() )::INT + ge
, sum(1) as cnt , gI
from generate_series(1,1000000) gi, generate_series(1,4) gE
group by 1,3;

insert into table1 
select id_table1, array_agg (elementcode )::text from TABLE2 group by id_table1 
;
---indexes --
create index on table1 (id);

create index on table2 (id_table1,elementcode);

create index on table2 (elementcode);

array_agg=

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

select t1.*, (ut).* from table1 t1
join (select id_table1, array_agg(t2.*) t2_s from table2 t2 group by id_table1
	having array_agg(distinct elementcode order by elementcode) = array[1,13,17])t2
	on t1.id = t2.id_table1
,lateral unnest(t2_s) ut ;
------------
'Nested Loop  (cost=0.86..2331058.18 rows=102358600 width=28) (actual time=1737.914..6231.921 rows=15 loops=1)'
'  Output: t1.id, t1.description, ut.elementcode, ut.qty, ut.id_table1'
'  Buffers: shared hit=2995734 read=838373 written=11'
'  ->  Merge Join  (cost=0.85..283886.18 rows=1023586 width=48) (actual time=1737.899..6231.843 rows=5 loops=1)'
'        Output: t1.id, t1.description, (array_agg(t2.*))'
'        Merge Cond: (t2.id_table1 = t1.id)'
'        Buffers: shared hit=2995734 read=838373 written=11'
'        ->  GroupAggregate  (cost=0.43..225532.14 rows=1023586 width=36) (actual time=1691.565..6068.506 rows=5 loops=1)'
'              Output: t2.id_table1, array_agg(t2.*)'
'              Group Key: t2.id_table1'
'              Filter: (array_agg(DISTINCT t2.elementcode ORDER BY t2.elementcode) = '{1,13,17}'::integer[])'
'              Rows Removed by Filter: 999995'
'              Buffers: shared hit=2995734 read=829695 written=11'
'              ->  Index Scan using table2_id_table1_elementcode_idx on public.table2 t2  (cost=0.43..181565.47 rows=3815051 width=44) (actual time=0.015..4164.226 rows=3815051 loops=1)'
'                    Output: t2.id_table1, t2.*, t2.elementcode'
'                    Buffers: shared hit=2995734 read=829695 written=11'
'        ->  Index Scan using table1_id_idx on public.table1 t1  (cost=0.42..32009.42 rows=1000000 width=16) (actual time=0.017..111.499 rows=990588 loops=1)'
'              Output: t1.id, t1.description'
'              Buffers: shared read=8678'
'  ->  Function Scan on pg_catalog.unnest ut  (cost=0.00..1.00 rows=100 width=12) (actual time=0.011..0.011 rows=3 loops=5)'
'        Output: ut.elementcode, ut.qty, ut.id_table1'
'        Function Call: unnest((array_agg(t2.*)))'
'Planning time: 0.180 ms'
'Execution time: 6231.957 ms'

exist having

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

select * from table1 where 
exists(
select 1
from table2
where table1.id=table2.id_table1  
group by id_table1 
having
    count(case when elementcode in (1,13,17) then 1 else null end)=3 and count(*)=3 and count(distinct elementcode)=3
);
--------------------------
'Seq Scan on public.table1  (cost=0.00..5487240.77 rows=500000 width=16) (actual time=1957.146..6962.123 rows=5 loops=1)'
'  Output: table1.id, table1.description'
'  Filter: (SubPlan 1)'
'  Rows Removed by Filter: 999995'
'  Buffers: shared hit=5976753 read=857564 written=12'
'  SubPlan 1'
'    ->  GroupAggregate  (cost=0.43..20.59 rows=4 width=8) (actual time=0.007..0.007 rows=0 loops=1000000)'
'          Output: 1, table2.id_table1'
'          Group Key: table2.id_table1'
'          Filter: ((count(CASE WHEN (table2.elementcode = ANY ('{1,13,17}'::integer[])) THEN 1 ELSE NULL::integer END) = 3) AND (count(*) = 3) AND (count(DISTINCT table2.elementcode) = 3))'
'          Rows Removed by Filter: 1'
'          Buffers: shared hit=5976753 read=851538 written=12'
'          ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2  (cost=0.43..20.50 rows=4 width=8) (actual time=0.002..0.005 rows=4 loops=1000000)'
'                Output: table2.id_table1, table2.elementcode'
'                Index Cond: (table2.id_table1 = table1.id)'
'                Heap Fetches: 3815051'
'                Buffers: shared hit=5976753 read=851538 written=12'
'Planning time: 0.140 ms'
'Execution time: 6962.178 ms'

"сложно"

Код: sql

SELECT t1.*
	,(ut2).* 
 FROM
	(select
			id_table1
			,array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where
		t2.elementcode = any(array[1,13,17])
		-- и только они:
		and not exists(select 1 from table2 t22
				 where not t22.elementcode  = any(array[1,13,17])
						and t22.id_table1 =t2.id_table1) 
	group by t2.id_table1
	having count( distinct t2.elementcode) = 3 -- все 3 из списка
	) t2 join table1 t1
		on t1.id = t2.id_table1
,lateral unnest(table2_s) AS ut2
--------------------------
'Nested Loop  (cost=91779.07..91789.13 rows=100 width=28) (actual time=479.945..480.076 rows=15 loops=1)'
'  Output: t1.id, t1.description, ut2.elementcode, ut2.qty, ut2.id_table1'
'  Buffers: shared hit=947835 read=247627'
'  ->  Nested Loop  (cost=91779.07..91787.13 rows=1 width=48) (actual time=479.932..480.045 rows=5 loops=1)'
'        Output: (array_agg(t2.*)), t1.id, t1.description'
'        Buffers: shared hit=947835 read=247627'
'        ->  GroupAggregate  (cost=91778.64..91778.67 rows=1 width=36) (actual time=479.905..479.956 rows=5 loops=1)'
'              Output: t2.id_table1, array_agg(t2.*)'
'              Group Key: t2.id_table1'
'              Filter: (count(DISTINCT t2.elementcode) = 3)'
'              Rows Removed by Filter: 17'
'              Buffers: shared hit=947831 read=247611'
'              ->  Sort  (cost=91778.64..91778.65 rows=1 width=44) (actual time=479.846..479.853 rows=46 loops=1)'
'                    Output: t2.id_table1, t2.*, t2.elementcode'
'                    Sort Key: t2.id_table1'
'                    Sort Method: quicksort  Memory: 28kB'
'                    Buffers: shared hit=947831 read=247611'
'                    ->  Gather  (cost=1000.43..91778.63 rows=1 width=44) (actual time=25.767..479.778 rows=46 loops=1)'
'                          Output: t2.id_table1, t2.*, t2.elementcode'
'                          Workers Planned: 3'
'                          Workers Launched: 3'
'                          Buffers: shared hit=947831 read=247611'
'                          ->  Nested Loop Anti Join  (cost=0.43..90778.53 rows=1 width=44) (actual time=57.059..476.593 rows=12 loops=4)'
'                                Output: t2.id_table1, t2.*, t2.elementcode'
'                                Buffers: shared hit=947509 read=247588'
'                                Worker 0: actual time=8.714..475.627 rows=14 loops=1'
'                                  Buffers: shared hit=239038 read=62744'
'                                Worker 1: actual time=141.433..475.898 rows=11 loops=1'
'                                  Buffers: shared hit=243513 read=63600'
'                                Worker 2: actual time=52.621..475.507 rows=7 loops=1'
'                                  Buffers: shared hit=232273 read=60491'
'                                ->  Parallel Seq Scan on public.table2 t2  (cost=0.00..37543.60 rows=85244 width=44) (actual time=0.034..98.323 rows=67634 loops=4)'
'                                      Output: t2.id_table1, t2.*, t2.elementcode'
'                                      Filter: (t2.elementcode = ANY ('{1,13,17}'::integer[]))'
'                                      Rows Removed by Filter: 886128'
'                                      Buffers: shared hit=5440 read=15182'
'                                      Worker 0: actual time=0.040..97.271 rows=68359 loops=1'
'                                        Buffers: shared hit=1338 read=3872'
'                                      Worker 1: actual time=0.025..95.426 rows=69505 loops=1'
'                                        Buffers: shared hit=1393 read=3898'
'                                      Worker 2: actual time=0.039..98.369 rows=66294 loops=1'
'                                        Buffers: shared hit=1385 read=3648'
'                                ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 t22  (cost=0.43..0.99 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=270538)'
'                                      Output: t22.id_table1, t22.elementcode'
'                                      Index Cond: (t22.id_table1 = t2.id_table1)'
'                                      Filter: (t22.elementcode <> ALL ('{1,13,17}'::integer[]))'
'                                      Rows Removed by Filter: 0'
'                                      Heap Fetches: 88960'
'                                      Buffers: shared hit=942069 read=232406'
'                                      Worker 0: actual time=0.005..0.005 rows=1 loops=68359'
'                                        Buffers: shared hit=237700 read=58872'
'                                      Worker 1: actual time=0.005..0.005 rows=1 loops=69505'
'                                        Buffers: shared hit=242120 read=59702'
'                                      Worker 2: actual time=0.005..0.005 rows=1 loops=66294'
'                                        Buffers: shared hit=230888 read=56843'
'        ->  Index Scan using table1_id_idx on public.table1 t1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=5)'
'              Output: t1.id, t1.description'
'              Index Cond: (t1.id = t2.id_table1)'
'              Buffers: shared hit=4 read=16'
'  ->  Function Scan on pg_catalog.unnest ut2  (cost=0.00..1.00 rows=100 width=12) (actual time=0.004..0.005 rows=3 loops=5)'
'        Output: ut2.elementcode, ut2.qty, ut2.id_table1'
'        Function Call: unnest((array_agg(t2.*)))'
'Planning time: 0.492 ms'
'Execution time: 481.110 ms'

кактотак

...

Рейтинг:

0 / 0

24.12.2019, 11:20

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

SELECT по двум таблицам

#39907425

Troglodit

Гость

qwwq,

Мда... я убедился, но не понял почему.
Это кунгфу мне пока не доступно.

...

Рейтинг:

0 / 0

24.12.2019, 11:53

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

SELECT по двум таблицам

#39907431

Troglodit

Гость

чуть изменил запрос, я кажется понял свою ошибку.

Код: sql

1.
2.
3.
4.
5.

select table1.id,table1.description from table1 
inner join table2 on table1.id=table2.id_table1 and table2.elementcode in (1,13,17) and  
not exists(select 1 from table2 t where t.id_table1=table2.id_table1 and t.elementcode not in (1,13,17))
group by id,description
having count(distinct table2.elementcode)=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.

"GroupAggregate  (cost=87654.77..87654.80 rows=1 width=32) (actual time=363.066..363.100 rows=4 loops=1)"
"  Group Key: table1.id, table1.description"
"  Filter: (count(DISTINCT table2.elementcode) = 3)"
"  Rows Removed by Filter: 24"
"  ->  Sort  (cost=87654.77..87654.78 rows=1 width=20) (actual time=363.041..363.045 rows=58 loops=1)"
"        Sort Key: table1.id, table1.description"
"        Sort Method: quicksort  Memory: 28kB"
"        ->  Nested Loop  (cost=3915.79..87654.76 rows=1 width=20) (actual time=29.967..362.981 rows=58 loops=1)"
"              ->  Gather  (cost=3915.37..87654.27 rows=1 width=8) (actual time=29.956..367.790 rows=58 loops=1)"
"                    Workers Planned: 2"
"                    Workers Launched: 2"
"                    ->  Nested Loop Anti Join  (cost=2915.37..86654.17 rows=1 width=8) (actual time=17.269..345.762 rows=19 loops=3)"
"                          ->  Parallel Bitmap Heap Scan on table2  (cost=2914.94..25103.30 rows=113772 width=8) (actual time=8.838..31.343 rows=90034 loops=3)"
"                                Recheck Cond: (elementcode = ANY ('{1,13,17}'::integer[]))"
"                                Heap Blocks: exact=6175"
"                                ->  Bitmap Index Scan on table2_elementcode_idx  (cost=0.00..2846.67 rows=273052 width=0) (actual time=18.337..18.337 rows=270101 loops=1)"
"                                      Index Cond: (elementcode = ANY ('{1,13,17}'::integer[]))"
"                          ->  Index Only Scan using table2_id_table1_elementcode_idx on table2 t  (cost=0.43..0.64 rows=3 width=4) (actual time=0.003..0.003 rows=1 loops=270101)"
"                                Index Cond: (id_table1 = table2.id_table1)"
"                                Filter: (elementcode <> ALL ('{1,13,17}'::integer[]))"
"                                Rows Removed by Filter: 0"
"                                Heap Fetches: 359831"
"              ->  Index Scan using table1_id_idx on table1  (cost=0.42..0.48 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=58)"
"                    Index Cond: (id = table2.id_table1)"
"Planning Time: 1.149 ms"
"Execution Time: 368.350 ms"

...

Рейтинг:

0 / 0

24.12.2019, 12:05

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

SELECT по двум таблицам

#39907442

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit

чуть изменил запрос, я кажется понял свою ошибку.

<>

ну так тот, что "сложно " именно это и делает.
(конструкции in() | = any(array[]) равносильны -- см план.)
+ зараз выводит деталировку . в качестве рюшечек.

но всё сильно завязано на кардинальности. если всех товаров всего штук 5-6. и средний чек -- ровно 3 товара -- то агрегатить м.б. выгоднее.

...

Рейтинг:

0 / 0

24.12.2019, 12:37

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

SELECT по двум таблицам

#39907458

Troglodit

Гость

qwwq

Troglodit

чуть изменил запрос, я кажется понял свою ошибку.

<>

Я не понял для чего лишние танцы с бубном

Код: sql

1.
2.
3.

array_agg( (t2.*)::table2) 
и 
lateral unnest(table2_s) AS ut2

и изначально я сомневался насчет запроса с

Код: sql

having array_agg(distinct elementcode order by elementcode) = array[1,13,17])t2

...

Рейтинг:

0 / 0

24.12.2019, 13:00

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

SELECT по двум таблицам

#39907472

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit

Я не понял для чего лишние танцы с бубном
array_agg( (t2.*)::table2)
и
lateral unnest(table2_s) AS ut2

qwwq

<>
+ зараз выводит деталировку . в качестве рюшечек.
<>

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

Troglodit

и изначально я сомневался насчет запроса с

Код: sql

having array_agg(distinct elementcode order by elementcode) = array[1,13,17])t2

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

и да , при подобающей кардинальности и наличии соотв. индексов фильтрация в where обыкновенно сильно дешевле, чем по агрегатам в having.

...

Рейтинг:

0 / 0

24.12.2019, 13:35

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

SELECT по двум таблицам

#39907536

AmonRa

Гость

qwwq

~~ примерно в эту сторону

Код: sql

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

SELECT t1.*
	-- ,(ut2).* 
 FROM table1 t1
,lateral (select count( distinct t2.elementcode) countMyelement
			--array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where t1.id = t2.id_table1 
		AND t2.elementcode = any(array['1','12','17'])) t2
-- ,lateral unnest(table2_s) AS ut2
WHERE countMyelement = 3

на самом деле мне это помогло. на этой основе написал. огромное спасибо

...

Рейтинг:

0 / 0

24.12.2019, 15:44

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

SELECT по двум таблицам

#39907614

entrypoint

Гость

AmonRa,

Код: plsql

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.

WITH a AS 
(
	-- подходит под условие
	SELECT 1 AS id, '1' AS elementcode UNION ALL 
	SELECT 1 AS id, '12' AS elementcode UNION ALL 
	SELECT 1 AS id, '17' AS elementcode UNION ALL 
	-- не подходит под условие по количеству (меньше)
	SELECT 2 AS id, '1' AS elementcode UNION ALL 
	SELECT 2 AS id, '12' AS elementcode UNION ALL 
	-- не подходит под условие по составу (есть код 12)
	SELECT 3 AS id, '1' AS elementcode UNION ALL 
	SELECT 3 AS id, '12' AS elementcode UNION ALL 
	SELECT 3 AS id, '13' AS elementcode UNION ALL 
	-- не подходит под условие по количеству (больше) по составу (есть код 12)
	SELECT 4 AS id, '1' AS elementcode UNION ALL 
	SELECT 4 AS id, '12' AS elementcode UNION ALL 
	SELECT 4 AS id, '13' AS elementcode UNION ALL 	
	SELECT 4 AS id, '17' AS elementcode 
), 
b AS 
(
	SELECT 
		id, 
		CASE WHEN elementcode IN ('1', '12', '17') THEN 1 ELSE 0 END AS elementcode, 
		COUNT(1) OVER (PARTITION BY id) AS cnt
		FROM a
)	
SELECT DISTINCT b.id FROM b 
	WHERE b.cnt = 3 
	AND 1 = ALL (SELECT c.elementcode FROM b AS c WHERE c.id = b.id)

...

Рейтинг:

0 / 0

24.12.2019, 17:49

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

SELECT по двум таблицам

#39907667

Troglodit

Гость

entrypoint,

Мне кажется у вас тот же изъян в запросе, что и в моем предыдущем варианте.
Попробуйте на большой случайной выборке из запроса выше, мне кажется сильно удивитесь.

...

Рейтинг:

0 / 0

24.12.2019, 21:40

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

SELECT по двум таблицам

#39907748

entrypoint

Гость

Troglodit,

Никаких изъянов

В задаче не дается тестовый массив данных, поэтому давая решение предполагаю, что источник данных содержит уникальное сочетание id + elementcode, т.е CTE а - это

Код: plsql

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

CREATE TABLE a 
(
id INT NOT NULL, 
elementcode VARCHAR(10) NOT NULL, 
PRIMARY KEY (id, elementcode)
)

во всех остальных случаях источник нужно привести к этому виду

в Вашем случае

Код: plsql

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 table2 AS (
  select '1' as elementcode,1 as qty,1 as id_table1
    union all
    select '17',1,1
    union all 
    select '12',1,1
    union all
    select '1',1,2
    union all
    select '17',1,2
    union all 
    select '12',1,2
    union all
    select '13',1,2
    union all
    select '17',1,3
    union all
    select '17',1,3
    union all
    select '1',1,3
),
a AS 
(
	SELECT DISTINCT elementcode, id_table1 AS id FROM table2 
),
b AS 
(
	SELECT 
		id, 
		CASE WHEN elementcode IN ('1', '12', '17') THEN 1 ELSE 0 END AS elementcode, 
		COUNT(elementcode) OVER (PARTITION BY id) AS cnt
		FROM a
)	
SELECT DISTINCT b.id FROM b 
	WHERE b.cnt = 3 
	AND 1 = ALL (SELECT c.elementcode FROM b AS c WHERE c.id = b.id)

Какой вопрос - такой и ответ

...

Рейтинг:

0 / 0

25.12.2019, 10:02

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

SELECT по двум таблицам

#39907856

Troglodit

Гость

entrypoint,

Вы создайте таблицы как написал выше qwwq, и заполните не 10 записей, а как у него.
И очень сильно удивитесь, когда сделаете explain.
Я прождал пять минут используя ваш вариант, но результата так и не дождался,
хотя мой последний вариант с правками от qwwq или его вариант на этих же данных исполняется менее секунды.

...

Рейтинг:

0 / 0

25.12.2019, 12:53

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

SELECT по двум таблицам

#39907894

entrypoint

Гость

Troglodit,

авторЯ прождал пять минут используя ваш вариант, но результата так и не дождался,
хотя мой последний вариант с правками от qwwq или его вариант на этих же данных исполняется менее секунды.

Думаю - это логично
Автор не предоставил структуру, а я её не придумывал, просто дал одно решение

А Ваш вариант, с Вашей структурой базы данных, с Вашим запросом и с Вашим пониманием предмета и должен работать быстро

...

Рейтинг:

0 / 0

25.12.2019, 14:07

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

SELECT по двум таблицам

#39907899

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit,

на таких кардинальностях, как в моём тестовом наборе (и с теми же индексами) еще быстрее (примерно вдвое) будет примерно так :

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

SELECT t1.*
	--,t23.*
FROM
	(select
			id_table1
	FROM table2 t2 
	where
		t2.elementcode = 1
		and exists(select 1 from table2 t22
				 where t22.elementcode  = 13
						and t22.id_table1 =t2.id_table1)
		and exists(select 1 from table2 t22
				 where t22.elementcode  = 17
						and t22.id_table1 =t2.id_table1)
				-- и только они:
--/*
		and
		not exists(select 1 from table2 t22
				 where not t22.elementcode  = any(array[1,13,17])
						and t22.id_table1 =t2.id_table1) 
--*/
	) t2 join table1 t1
		on t1.id = t2.id_table1
--join table2 t23 on t1.id = t23.id_table1

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

...

Рейтинг:

0 / 0

25.12.2019, 14:12

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

SELECT по двум таблицам

#39907913

Troglodit

Гость

entrypoint

Думаю - это логично
Автор не предоставил структуру, а я её не придумывал, просто дал одно решение

А Ваш вариант, с Вашей структурой базы данных, с Вашим запросом и с Вашим пониманием предмета и должен работать быстро
Структура не моя, но соответствует постановке задачи, это не сферический конь в вакууме, а то что distinct на миллионах записей будет тормозить и так понятно.

...

Рейтинг:

0 / 0

25.12.2019, 15:00

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

SELECT по двум таблицам

#39907956

Troglodit

Гость

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

Код: sql

t22.elementcode  = any(array[1,13,17])

почему не пишете на стандартном sql

Код: sql

t22.elementcode  in (1,13,17)

как бы более стандартно хоть и тоже самое?

...

Рейтинг:

0 / 0

25.12.2019, 16:24

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

SELECT по двум таблицам

#39907960

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Troglodit

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

Код: sql

t22.elementcode  = any($array) -- параметр

почему не пишете на стандартном sql

Код: sql

t22.elementcode  in (?????)

как бы более стандартно хоть и тоже то же самое?

патамушта например набор в in не параметризуецца.

массивы хороши например для хранения в табличках рукопашных "инвертированных индексов". и кучи схожих задач.

...

Рейтинг:

0 / 0

25.12.2019, 16:43

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

SELECT по двум таблицам

#39908519

entrypoint

Гость

Troglodit,

Код: plsql

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

SELECT DISTINCT a.id FROM table1 AS a
	INNER JOIN table2 AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN table2 AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN table2 AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM table2 AS e WHERE e.id_table1 = a.id AND e.elementcode NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

Как ?

...

Рейтинг:

0 / 0

26.12.2019, 15:50

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

SELECT по двум таблицам

#39908712

Troglodit

Гость

entrypoint

Troglodit,

Код: plsql

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

SELECT DISTINCT a.id FROM table1 AS a
	INNER JOIN table2 AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN table2 AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN table2 AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM table2 AS e WHERE e.id_table1 = a.id AND e.elementcode NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

Как ?

У вас просто косметически исправленный последний вариант qwwq, причем на мой взгляд в худшую сторону.
и кстати просветите зачем нужен DISTINCT?

...

Рейтинг:

0 / 0

26.12.2019, 20:59

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

SELECT по двум таблицам

#39908933

entrypoint

Гость

Troglodit,

согласно предложенной Вами структуре сочетание elementcode + id_table1 - не уникально, т.е. возможны дубли по этому сочетанию,
а нам нужны уникальные Id, для этого DISTINCT, иначе увидим кучу дублей

/*

Код: plsql

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

CREATE TABLE public.table2
(
    elementcode integer,
    qty integer,
    id_table1 integer
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE INDEX table2_elementcode_idx
    ON public.table2 USING btree
    (elementcode)
    TABLESPACE pg_default;

CREATE INDEX table2_id_table1_elementcode_idx
    ON public.table2 USING btree
    (id_table1, elementcode)
    TABLESPACE pg_default;

Код: plsql

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

WITH t(id_table1, qty, elementcode) AS 
(
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 17 UNION ALL 	
	SELECT 1, 1, 17 UNION ALL 	
	SELECT 1, 1, 17
)
SELECT /*DISTINCT*/ a.id FROM table1 AS a
	INNER JOIN t AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN t AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN t AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM t AS e WHERE e.id_table1 = a.id AND e.elementcode NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

Кстати нет внешних ключей и поле elementcode поддерживает NULL (!!!!!)
в этом случае правильный скрипт

Код: plsql

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

SELECT DISTINCT a.id FROM table1 AS a
	INNER JOIN table2 AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN table2 AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN table2 AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM table2 AS e WHERE e.id_table1 = a.id AND COALESCE(e.elementcode, -1) NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

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

...

Рейтинг:

0 / 0

27.12.2019, 12:29

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

SELECT по двум таблицам

#39908949

Troglodit

Гость

entrypoint

Код: plsql

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

CREATE TABLE public.table2
(
    elementcode integer,
    qty integer,
    id_table1 integer
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE INDEX table2_elementcode_idx
    ON public.table2 USING btree
    (elementcode)
    TABLESPACE pg_default;

CREATE INDEX table2_id_table1_elementcode_idx
    ON public.table2 USING btree
    (id_table1, elementcode)
    TABLESPACE pg_default;

Код: plsql

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

WITH t(id_table1, qty, elementcode) AS 
(
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 1  UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 13 UNION ALL 
	SELECT 1, 1, 17 UNION ALL 	
	SELECT 1, 1, 17 UNION ALL 	
	SELECT 1, 1, 17
)
SELECT /*DISTINCT*/ a.id FROM table1 AS a
	INNER JOIN t AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN t AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN t AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM t AS e WHERE e.id_table1 = a.id AND e.elementcode NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

Кстати нет внешних ключей и поле elementcode поддерживает NULL (!!!!!)
в этом случае правильный скрипт

Код: plsql

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

SELECT DISTINCT a.id FROM table1 AS a
	INNER JOIN table2 AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN table2 AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN table2 AS d ON d.id_table1 = a.id AND d.elementcode = 17
        LEFT  JOIN LATERAL (SELECT id_table1 FROM table2 AS e WHERE e.id_table1 = a.id AND COALESCE(e.elementcode, -1) NOT IN (1, 13, 17) LIMIT 1) AS e ON true
WHERE e.id_table1 IS NULL

автормой взгляд в худшую сторону

с моей точки зрения в разы понятней, удобней в сопровождении, более расширяемое, красивое и в конце концов правильное

Извините, буду краток.
Для тех кто в танке, постановка задачи автора, это НЕ МОЯ структура.
Кто сказал, что возможны дубли и дубли чего, если дубли по документу, то в результирующую выборку они не по попадут ПО ОПРЕДЕЛЕНИЮ задачи автора.
Продолжайте искать блох на рыбе, я больше в беседе с вами не участвую, контрпродуктивно.

...

Рейтинг:

0 / 0

27.12.2019, 13:06

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

SELECT по двум таблицам

#39908954

entrypoint

Гость

Troglodit,

Невежливо (((

...

Рейтинг:

0 / 0

27.12.2019, 13:12

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

SELECT по двум таблицам

#39908980

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

entrypoint

именно поэтому екзистс, фетчащий не более 1 записи предпочтительнее джойнов с последующим дистинктом.

Код: sql

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

truncate table1;
truncate table2;
------------
insert into table2 
SELECT
 (30 *RANDOM() )::INT + ge
, 1 as cnt , gI
from generate_series(1,1000000) gi, generate_series(1,4) gE
--group by 1,3; -- ~ unique by data generation
;
insert into table1 
select id_table1, array_agg (elementcode )::text from TABLE2 group by id_table1 
;

Код: 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.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.

SELECT t1.*
	--,t23.*
FROM
	(select distinct 
			id_table1
	FROM table2 t2 
	where
		t2.elementcode = 1
		and exists(select 1 from table2 t22
				 where t22.elementcode  = 13
						and t22.id_table1 =t2.id_table1)
		and exists(select 1 from table2 t22
				 where t22.elementcode  = 17
						and t22.id_table1 =t2.id_table1)
				-- и только они:
--/*
		and
		not exists(select 1 from table2 t22
				where t22.id_table1 =t2.id_table1
				and not (t22.elementcode  = any(array[1,13,17])
					or t22.elementcode is null)
					) 
--*/
	) t2 join table1 t1
		on t1.id = t2.id_table1
--join table2 t23 on t1.id = t23.id_table1
;
-------------------------------
'Nested Loop  (cost=51766.30..51774.35 rows=1 width=17) (actual time=88.787..88.810 rows=5 loops=1)'
'  Output: t1.id, t1.description'
'  Buffers: shared hit=65677 read=23831'
'  ->  Unique  (cost=51765.88..51765.88 rows=1 width=4) (actual time=88.767..88.768 rows=5 loops=1)'
'        Output: t2.id_table1'
'        Buffers: shared hit=65658 read=23830'
'        ->  Sort  (cost=51765.88..51765.88 rows=1 width=4) (actual time=88.767..88.767 rows=5 loops=1)'
'              Output: t2.id_table1'
'              Sort Key: t2.id_table1'
'              Sort Method: quicksort  Memory: 25kB'
'              Buffers: shared hit=65658 read=23830'
'              ->  Nested Loop Semi Join  (cost=1001.29..51765.87 rows=1 width=4) (actual time=11.807..88.753 rows=5 loops=1)'
'                    Output: t2.id_table1'
'                    Buffers: shared hit=65658 read=23830'
'                    ->  Nested Loop Semi Join  (cost=1000.86..51763.54 rows=1 width=8) (actual time=11.804..88.735 rows=6 loops=1)'
'                          Output: t2.id_table1, t22.id_table1'
'                          Buffers: shared hit=65635 read=23830'
'                          ->  Gather  (cost=1000.43..51753.73 rows=1 width=4) (actual time=11.800..88.694 rows=7 loops=1)'
'                                Output: t2.id_table1'
'                                Workers Planned: 3'
'                                Workers Launched: 3'
'                                Buffers: shared hit=65609 read=23829'
'                                ->  Nested Loop Anti Join  (cost=0.43..50753.63 rows=1 width=4) (actual time=22.946..86.504 rows=2 loops=4)'
'                                      Output: t2.id_table1'
'                                      Buffers: shared hit=65273 read=23829'
'                                      Worker 0: actual time=34.933..84.867 rows=2 loops=1'
'                                        Buffers: shared hit=16156 read=5871'
'                                      Worker 1: actual time=27.987..86.327 rows=2 loops=1'
'                                        Buffers: shared hit=16214 read=5929'
'                                      Worker 2: actual time=17.275..86.354 rows=2 loops=1'
'                                        Buffers: shared hit=16790 read=6156'
'                                      ->  Parallel Seq Scan on public.table2 t2  (cost=0.00..37751.03 rows=5677 width=4) (actual time=0.046..74.711 rows=4200 loops=4)'
'                                            Output: t2.elementcode, t2.qty, t2.id_table1'
'                                            Filter: (t2.elementcode = 1)'
'                                            Rows Removed by Filter: 995800'
'                                            Buffers: shared hit=188 read=21434'
'                                            Worker 0: actual time=0.031..73.389 rows=4146 loops=1'
'                                              Buffers: shared hit=46 read=5324'
'                                            Worker 1: actual time=0.079..74.586 rows=4176 loops=1'
'                                              Buffers: shared hit=45 read=5322'
'                                            Worker 2: actual time=0.020..74.469 rows=4328 loops=1'
'                                              Buffers: shared hit=50 read=5523'
'                                      ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 t22_2  (cost=0.43..6.49 rows=4 width=4) (actual time=0.003..0.003 rows=1 loops=16798)'
'                                            Output: t22_2.id_table1, t22_2.elementcode'
'                                            Index Cond: ((t22_2.id_table1 = t2.id_table1) AND (t22_2.elementcode IS NOT NULL))'
'                                            Filter: (t22_2.elementcode <> ALL ('{1,13,17}'::integer[]))'
'                                            Rows Removed by Filter: 1'
'                                            Heap Fetches: 8619'
'                                            Buffers: shared hit=65085 read=2395'
'                                            Worker 0: actual time=0.003..0.003 rows=1 loops=4146'
'                                              Buffers: shared hit=16110 read=547'
'                                            Worker 1: actual time=0.003..0.003 rows=1 loops=4176'
'                                              Buffers: shared hit=16169 read=607'
'                                            Worker 2: actual time=0.003..0.003 rows=1 loops=4328'
'                                              Buffers: shared hit=16740 read=633'
'                          ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 t22  (cost=0.43..5.51 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=7)'
'                                Output: t22.id_table1, t22.elementcode'
'                                Index Cond: ((t22.id_table1 = t2.id_table1) AND (t22.elementcode = 13))'
'                                Heap Fetches: 6'
'                                Buffers: shared hit=26 read=1'
'                    ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 t22_1  (cost=0.43..1.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=6)'
'                          Output: t22_1.id_table1, t22_1.elementcode'
'                          Index Cond: ((t22_1.id_table1 = t22.id_table1) AND (t22_1.elementcode = 17))'
'                          Heap Fetches: 5'
'                          Buffers: shared hit=23'
'  ->  Index Scan using table1_id_idx on public.table1 t1  (cost=0.42..8.44 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=5)'
'        Output: t1.id, t1.description'
'        Index Cond: (t1.id = t2.id_table1)'
'        Buffers: shared hit=19 read=1'
'Planning time: 0.715 ms'
'Execution time: 89.959 ms'
-------------------

Код: sql

SELECT DISTINCT on ( a.id) a.* FROM table1 AS a
	INNER JOIN table2 AS b ON b.id_table1 = a.id AND b.elementcode = 1
	INNER JOIN table2 AS c ON c.id_table1 = a.id AND c.elementcode = 13
	INNER JOIN table2 AS d ON d.id_table1 = a.id AND d.elementcode = 17
	LEFT  JOIN LATERAL (SELECT id_table1 FROM table2 AS e
		WHERE e.id_table1 = a.id AND --COALESCE(e.elementcode, -1) NOT IN (1, 13, 17)
		not (e.elementcode  = any(array[1,13,17])
					or e.elementcode is null)
		LIMIT 1

        ) AS e ON true
WHERE e.id_table1 IS NULL;
------------
'Unique  (cost=59380.60..59380.61 rows=1 width=17) (actual time=129.623..129.626 rows=5 loops=1)'
'  Output: a.id, a.description'
'  Buffers: shared hit=118569 read=28124 written=2'
'  ->  Sort  (cost=59380.60..59380.60 rows=1 width=17) (actual time=129.623..129.623 rows=10 loops=1)'
'        Output: a.id, a.description'
'        Sort Key: a.id'
'        Sort Method: quicksort  Memory: 25kB'
'        Buffers: shared hit=118569 read=28124 written=2'
'        ->  Nested Loop Left Join  (cost=1001.71..59380.59 rows=1 width=17) (actual time=15.755..129.608 rows=10 loops=1)'
'              Output: a.id, a.description'
'              Filter: (e.id_table1 IS NULL)'
'              Rows Removed by Filter: 104'
'              Buffers: shared hit=118569 read=28124 written=2'
'              ->  Gather  (cost=1001.28..56885.45 rows=659 width=17) (actual time=4.740..128.719 rows=114 loops=1)'
'                    Output: a.id, a.description'
'                    Workers Planned: 3'
'                    Workers Launched: 3'
'                    Buffers: shared hit=118184 read=28053 written=2'
'                    ->  Nested Loop  (cost=1.28..55819.55 rows=213 width=17) (actual time=7.802..127.119 rows=28 loops=4)'
'                          Output: a.id, a.description'
'                          Join Filter: (b.id_table1 = c.id_table1)'
'                          Buffers: shared hit=117797 read=28053 written=2'
'                          Worker 0: actual time=10.311..126.833 rows=32 loops=1'
'                            Buffers: shared hit=37114 read=8878'
'                          Worker 1: actual time=6.967..126.986 rows=42 loops=1'
'                            Buffers: shared hit=39142 read=9403 written=1'
'                          Worker 2: actual time=9.441..126.316 rows=20 loops=1'
'                            Buffers: shared hit=20769 read=4816 written=1'
'                          ->  Nested Loop  (cost=0.85..55168.59 rows=1098 width=25) (actual time=0.691..126.425 rows=402 loops=4)'
'                                Output: a.id, a.description, b.id_table1, d.id_table1'
'                                Join Filter: (b.id_table1 = d.id_table1)'
'                                Buffers: shared hit=112865 read=28053 written=2'
'                                Worker 0: actual time=0.393..126.096 rows=527 loops=1'
'                                  Buffers: shared hit=35502 read=8878'
'                                Worker 1: actual time=1.819..126.247 rows=539 loops=1'
'                                  Buffers: shared hit=37483 read=9403 written=1'
'                                Worker 2: actual time=0.274..125.635 rows=281 loops=1'
'                                  Buffers: shared hit=19905 read=4816 written=1'
'                                ->  Nested Loop  (cost=0.42..51802.90 rows=5677 width=21) (actual time=0.088..114.087 rows=4200 loops=4)'
'                                      Output: a.id, a.description, b.id_table1'
'                                      Buffers: shared hit=64630 read=24290 written=1'
'                                      Worker 0: actual time=0.092..112.593 rows=5286 loops=1'
'                                        Buffers: shared hit=20287 read=7712'
'                                      Worker 1: actual time=0.101..112.146 rows=5601 loops=1'
'                                        Buffers: shared hit=21421 read=8123 written=1'
'                                      Worker 2: actual time=0.077..114.761 rows=2944 loops=1'
'                                        Buffers: shared hit=11447 read=4156'
'                                      ->  Parallel Seq Scan on public.table2 b  (cost=0.00..37751.03 rows=5677 width=4) (actual time=0.077..99.945 rows=4200 loops=4)'
'                                            Output: b.elementcode, b.qty, b.id_table1'
'                                            Filter: (b.elementcode = 1)'
'                                            Rows Removed by Filter: 995800'
'                                            Buffers: shared hit=13 read=21609'
'                                            Worker 0: actual time=0.073..96.907 rows=5286 loops=1'
'                                              Buffers: shared hit=6 read=6812'
'                                            Worker 1: actual time=0.093..96.141 rows=5601 loops=1'
'                                              Buffers: shared hit=6 read=7090'
'                                            Worker 2: actual time=0.067..102.541 rows=2944 loops=1'
'                                              Buffers: shared read=3816'
'                                      ->  Index Scan using table1_id_idx on public.table1 a  (cost=0.42..2.47 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=16798)'
'                                            Output: a.id, a.description'
'                                            Index Cond: (a.id = b.id_table1)'
'                                            Buffers: shared hit=64617 read=2681 written=1'
'                                            Worker 0: actual time=0.003..0.003 rows=1 loops=5286'
'                                              Buffers: shared hit=20281 read=900'
'                                            Worker 1: actual time=0.002..0.003 rows=1 loops=5601'
'                                              Buffers: shared hit=21415 read=1033 written=1'
'                                            Worker 2: actual time=0.003..0.004 rows=1 loops=2944'
'                                              Buffers: shared hit=11447 read=340'
'                                ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 d  (cost=0.43..0.58 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=16798)'
'                                      Output: d.id_table1, d.elementcode'
'                                      Index Cond: ((d.id_table1 = a.id) AND (d.elementcode = 17))'
'                                      Heap Fetches: 259'
'                                      Buffers: shared hit=48235 read=3763 written=1'
'                                      Worker 0: actual time=0.002..0.002 rows=0 loops=5286'
'                                        Buffers: shared hit=15215 read=1166'
'                                      Worker 1: actual time=0.002..0.002 rows=0 loops=5601'
'                                        Buffers: shared hit=16062 read=1280'
'                                      Worker 2: actual time=0.003..0.003 rows=0 loops=2944'
'                                        Buffers: shared hit=8458 read=660 written=1'
'                          ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 c  (cost=0.43..0.58 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1606)'
'                                Output: c.id_table1, c.elementcode'
'                                Index Cond: ((c.id_table1 = a.id) AND (c.elementcode = 13))'
'                                Heap Fetches: 20'
'                                Buffers: shared hit=4932'
'                                Worker 0: actual time=0.001..0.001 rows=0 loops=527'
'                                  Buffers: shared hit=1612'
'                                Worker 1: actual time=0.001..0.001 rows=0 loops=539'
'                                  Buffers: shared hit=1659'
'                                Worker 2: actual time=0.002..0.002 rows=0 loops=281'
'                                  Buffers: shared hit=864'
'              ->  Limit  (cost=0.43..3.77 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=114)'
'                    Output: e.id_table1'
'                    Buffers: shared hit=385 read=71'
'                    ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 e  (cost=0.43..13.77 rows=4 width=4) (actual time=0.007..0.007 rows=1 loops=114)'
'                          Output: e.id_table1'
'                          Index Cond: ((e.id_table1 = a.id) AND (e.elementcode IS NOT NULL))'
'                          Filter: (e.elementcode <> ALL ('{1,13,17}'::integer[]))'
'                          Rows Removed by Filter: 2'
'                          Heap Fetches: 383'
'                          Buffers: shared hit=385 read=71'
'Planning time: 0.877 ms'
'Execution time: 130.806 ms'

entrypoint

кому и кобыла невеста

...

Рейтинг:

0 / 0

27.12.2019, 13:38

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

SELECT по двум таблицам

#39909007

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwq,
как абычна наврал

Код: sql

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

--вместо ~~
not (e.elementcode  = any(array[1,13,17])
	or e.elementcode is null)
--читать 
	((not e.elementcode  = any(array[1,13,17]))
	or e.elementcode is null)

-- поправил

для планов не принципиально

...

Рейтинг:

0 / 0

27.12.2019, 14:03

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

SELECT по двум таблицам

#39909306

Troglodit

Гость

entrypoint

Troglodit,

Невежливо (((

Я же извинился заранее.
Но все же.
Если прочитать тему с начала я как и вы тоже предложил рабочий но далеко не оптимальный вариант.
qwwq не только предложил лучше и изящнее, но и пояснил почему.
И вместо того, чтобы упираться рогом как вы, я признал что его кунгфу круче.

...

Рейтинг:

0 / 0

27.12.2019, 20:04

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

37 сообщений из 37, показаны все 2 страниц

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / SELECT по двум таблицам

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?all=1&fid=53&tid=1994878]:	0ms
get settings:	11ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	36ms
get topic data:	12ms
get forum data:	2ms
get page messages:	65ms
get tp. blocked users:	2ms
others:	279ms

total:	429ms