SELECT * FROM something [INNER/LEFT] JOIN LATERAL subquery;
Dla obrazowania problemu stworzyłam kilka małych tabel: users (imię dziecka z informacją o kraju z którego pochodzi), toys (nazwa zabawi z jego ceną), user_to_toys (połączenie dwóch poprzednich tabel relacją wiele do wielu - które dziecko ma jaką zabawkę), country_rise (procentowa wartość podwyżki cen zabawek dla danego kraju).
Interesują mnie następujące informacje:
- które dziecko jakie dostało zabawki,
- ile było tych zabawek,
- ile wyniósł koszt kupionych zabawek,
- ile rodzice by zapłacili po podwyżce.
W głównym podzapytaniu wyciągam informacje o dzieciach, zabawkach i sumie obecnych kosztów tych zabawek:
select
u.id,
u.name,
u.country,
string_agg(t.name, ',') as toys_names,
sum(t.price) as price_sum,
count(t.id) as count_toys
from users u
LEFT JOIN user_to_toy ut ON u.id = ut.userid
LEFT JOIN toys t ON t.id = ut.toyid
group by u.id, u.name, u.country
Podwyżkę kosztów chce obliczyć JOINując odpowiednią tabelę, która także jest podzapytaniem:
select
cr.rise,
round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price
from country_rise cr
where cr.country = foo1.country
Oto całe moje zapytanie:
test=# select
foo1.name,
foo1.toys_names,
foo1.price_sum,
foo1.count_toys,
foo2.rise || '%' as rise,
foo2.new_price
from (
select
u.id,
u.name,
u.country,
string_agg(t.name, ',') as toys_names,
sum(t.price) as price_sum,
count(t.id) as count_toys
from users u
LEFT JOIN user_to_toy ut ON u.id = ut.userid
LEFT JOIN toys t ON t.id = ut.toyid
group by u.id, u.name, u.country
) foo1
JOIN (
select
cr.rise,
round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price
from country_rise cr
where cr.country = foo1.country
) foo2 ON country;
ERROR: invalid reference to FROM-clause entry for table "foo1"
LINE 1: ...y) foo1 JOIN (select cr.rise, round( CAST(float8 (foo1.price...
^
HINT: There is an entry for table "foo1", but it cannot be referenced from this part of the query.
Wyświetlany błąd, wynika z tego że nie możemy odwoływać się do tabel zewnętrznych, które są podzapytaniami w łączeniach. W takim przypadku musimy użyć właśnie LATERAL. Bez tego słowa kluczowego, każde zapytanie jest oceniane niezależnie i nie możemy się odwoływać do żadnego elementu w FROM.
A to już poprawione nieco powyższe zapytanie:
test=# select
foo1.name,
foo1.toys_names,
foo1.price_sum,
foo1.count_toys,
foo2.rise || '%' as rise,
foo2.new_price
from (
select
u.id,
u.name,
u.country,
string_agg(t.name, ',') as toys_names,
sum(t.price) as price_sum,
count(t.id) as count_toys
from users u
LEFT JOIN user_to_toy ut ON u.id = ut.userid
LEFT JOIN toys t ON t.id = ut.toyid
group by u.id, u.name, u.country
) foo1,
LATERAL (
select
cr.rise,
round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price
from country_rise cr
where cr.country = foo1.country) foo2;
name | toys_names | price_sum | count_toys | rise | new_price
-------+------------------------------------------------------+-----------+------------+------+-----------
Kasia | Kolorowanka | 6 | 1 | 10% | 6.60
Marta | | | 0 | 10% |
Ula | Pluszowa Żyrafa,Pluszowa Żabka,Samochodzik,Gryzaczek | 69 | 4 | 10% | 75.90
Beata | Kolorowanka,Pluszowa Żyrafa | 16 | 2 | 10% | 17.60
Ela | Gryzaczek,Pluszowa Żyrafa | 15 | 2 | 35% | 20.25
Kuba | Pluszowa Żabka | 20 | 1 | 35% | 27.00
Ola | Samochodzik,Pluszowa Żabka | 54 | 2 | 50% | 81.00
(7 rows)
Według dokumentacji (którą możecie przeczytać pod tym linkiem), teraz możemy się odwoływać w sub-SELECT do elementów kolumn z FROM, które pojawiają się przed nim w tej liście (każdy wiersz z tabeli źródłowej (FROM) jest wykorzystywany do oszacowania wiersza w LATERAL. Utworzony w ten sposób wiersz(e) są joinowane).
Sprawdźmy jeszcze koszt takiego zapytania:
test=# explain select foo1.name, foo1.toys_names, foo1.price_sum, foo1.count_toys, foo2.rise || '%' as rise, foo2.new_price
test-# from (
test(# select
test(# u.id,
test(# u.name,
test(# u.country,
test(# string_agg(t.name, ',') as toys_names,
test(# sum(t.price) as price_sum,
test(# count(t.id) as count_toys
test(# from users u
test(# LEFT JOIN user_to_toy ut ON u.id = ut.userid
test(# LEFT JOIN toys t ON t.id = ut.toyid
test(# group by u.id, u.name, u.country
test(# ) foo1,
test-# LATERAL (
test(# select
test(# cr.rise,
test(# round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price
test(# from country_rise cr
test(# where cr.country = foo1.country) foo2;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Hash Join (cost=124.18..199.38 rows=1141 width=568)
Hash Cond: (cr.country = foo1.country)
-> Seq Scan on country_rise cr (cost=0.00..26.30 rows=1630 width=16)
-> Hash (cost=122.43..122.43 rows=140 width=576)
-> Subquery Scan on foo1 (cost=119.28..122.43 rows=140 width=576)
-> HashAggregate (cost=119.28..121.03 rows=140 width=1056)
Group Key: u.id, u.name, u.country
-> Hash Left Join (cost=26.30..96.81 rows=1498 width=1056)
Hash Cond: (ut.toyid = t.id)
-> Hash Right Join (cost=13.15..67.56 rows=1498 width=536)
Hash Cond: (ut.userid = u.id)
-> Seq Scan on user_to_toy ut (cost=0.00..31.40 rows=2140 width=8)
-> Hash (cost=11.40..11.40 rows=140 width=532)
-> Seq Scan on users u (cost=0.00..11.40 rows=140 width=532)
-> Hash (cost=11.40..11.40 rows=140 width=524)
-> Seq Scan on toys t (cost=0.00..11.40 rows=140 width=524)
(16 rows)
Poprawione nieco zapytanie (tak abyśmy mogli zobaczyć jakieś wyniki i ocenić jego koszt) bez użycia LATERAL:
test=# select name, toys_names, price_sum, count_toys, rise || '%' as rise, round( CAST(float8 (price_sum+(price_sum*rise)/100.0) as numeric),2) as new_price
from (
select
u.id,
u.name,
u.country,
string_agg(t.name, ',') as toys_names,
sum(t.price) as price_sum,
count(t.id) as count_toys, rise
from users u
LEFT JOIN user_to_toy ut ON u.id = ut.userid
LEFT JOIN toys t ON t.id = ut.toyid
LEFT JOIN country_rise cr ON cr.country = u.country
group by u.id, u.name, u.country, cr.rise) foo1;
name | toys_names | price_sum | count_toys | rise | new_price
-------+------------------------------------------------------+-----------+------------+------+-----------
Ula | Samochodzik,Pluszowa Żyrafa,Gryzaczek,Pluszowa Żabka | 69 | 4 | 10% | 75.90
Ola | Pluszowa Żabka,Samochodzik | 54 | 2 | 50% | 81.00
Ela | Pluszowa Żyrafa,Gryzaczek | 15 | 2 | 35% | 20.25
Kuba | Pluszowa Żabka | 20 | 1 | 35% | 27.00
Kasia | Kolorowanka | 6 | 1 | 10% | 6.60
Beata | Kolorowanka,Pluszowa Żyrafa | 16 | 2 | 10% | 17.60
Marta | | | 0 | 10% |
(7 rows)
test=# explain select name, toys_names, price_sum, count_toys, rise || '%' as rise, round( CAST(float8 (price_sum+(price_sum*rise)/100.0) as numeric),2) as new_price
from (
select
u.id,
u.name,
u.country,
string_agg(t.name, ',') as toys_names,
sum(t.price) as price_sum,
count(t.id) as count_toys, rise
from users u
LEFT JOIN user_to_toy ut ON u.id = ut.userid
LEFT JOIN toys t ON t.id = ut.toyid
LEFT JOIN country_rise cr ON cr.country = u.country
group by u.id, u.name, u.country, cr.rise) foo1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Subquery Scan on foo1 (cost=6793.36..7647.99 rows=12209 width=568)
-> GroupAggregate (cost=6793.36..7190.15 rows=12209 width=1060)
Group Key: u.id, u.name, u.country, cr.rise
-> Sort (cost=6793.36..6823.88 rows=12209 width=1060)
Sort Key: u.id, u.name, u.country, cr.rise
-> Hash Right Join (cost=84.39..287.63 rows=12209 width=1060)
Hash Cond: (ut.userid = u.id)
-> Hash Left Join (cost=13.15..67.56 rows=2140 width=528)
Hash Cond: (ut.toyid = t.id)
-> Seq Scan on user_to_toy ut (cost=0.00..31.40 rows=2140 width=8)
-> Hash (cost=11.40..11.40 rows=140 width=524)
-> Seq Scan on toys t (cost=0.00..11.40 rows=140 width=524)
-> Hash (cost=56.97..56.97 rows=1141 width=536)
-> Hash Right Join (cost=13.15..56.97 rows=1141 width=536)
Hash Cond: (cr.country = u.country)
-> Seq Scan on country_rise cr (cost=0.00..26.30 rows=1630 width=16)
-> Hash (cost=11.40..11.40 rows=140 width=532)
-> Seq Scan on users u (cost=0.00..11.40 rows=140 width=532)
(18 rows)
Wyniki są takie same (choć inna kolejność), ale koszt zapytania o wiele większy!
Oczywiście zawsze można by napisać lepsze zapytania, przy którym okazało by się, że koszt nie jest tak wysoki a i nie było by konieczne użycie LATERAL JOINa, ale w tym poście nie o to chodziło :-)
Brak komentarzy:
Prześlij komentarz