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

ReSQL.ru

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

Гость

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

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

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

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

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

25 сообщений из 37, страница 1 из 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

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.

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

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

25 сообщений из 37, страница 1 из 2

все

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&tid=1994878]:	0ms
get settings:	7ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	36ms
get topic data:	9ms
get forum data:	2ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	274ms

total:	406ms