Jak zrobić subquery Postgresql w klauzuli select z klauzulą join in from, taką jak SQL Server?
Próbuję napisać następujące zapytanie na postgresql:
select name, author_id, count(1),
(select count(1)
from names as n2
where n2.id = n1.id
and t2.author_id = t1.author_id
)
from names as n1
group by name, author_id
To z pewnością zadziała na Microsoft SQL Server, ale w ogóle nie działa na postegresql. Przeczytałem trochę jego dokumentację i wydaje mi się, że mógłbym ją przepisać jako:
select name, author_id, count(1), total
from names as n1, (select count(1) as total
from names as n2
where n2.id = n1.id
and n2.author_id = t1.author_id
) as total
group by name, author_id
Ale to zwraca następujący błąd w postegresql: "subquery w FROM nie mogą odnosić się do innych relacji tego samego poziomu zapytań". Więc utknąłem. Czy ktoś wie, jak Mogę to osiągnąć?
Dzięki
4 answers
Nie jestem pewien, czy doskonale rozumiem twoje intencje, ale być może poniższe byłoby zbliżone do tego, czego chcesz:
select n1.name, n1.author_id, count_1, total_count
from (select id, name, author_id, count(1) as count_1
from names
group by id, name, author_id) n1
inner join (select id, author_id, count(1) as total_count
from names
group by id, author_id) n2
on (n2.id = n1.id and n2.author_id = n1.author_id)
Niestety dodaje to wymóg grupowania pierwszych zapytań podrzędnych według id, jak również nazwy i author_id, co nie było chyba pożądane. Nie jestem jednak pewien, jak to obejść, ponieważ musisz mieć dostępny identyfikator, aby dołączyć do drugiego zapytania podrzędnego. Może ktoś inny znajdzie lepsze rozwiązanie.
Podziel się i ciesz się.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-09 11:25:29
Właśnie odpowiadam tutaj z sformatowaną wersją końcowego sql, którego potrzebowałem na podstawie odpowiedzi Boba Jarvisa zamieszczonej w moim komentarzu powyżej:
select n1.name, n1.author_id, cast(count_1 as numeric)/total_count
from (select id, name, author_id, count(1) as count_1
from names
group by id, name, author_id) n1
inner join (select author_id, count(1) as total_count
from names
group by author_id) n2
on (n2.author_id = n1.author_id)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-09 12:40:19
@Bob Jarvis i @dmikam odpowiedz, Postgres nie wykonuje dobrego planu, jeśli nie używasz LATERAL, poniżej symulacji, w obu przypadkach wyniki Danych zapytania są takie same, ale koszty są bardzo różne
Struktura tabeli
CREATE TABLE ITEMS (
N INTEGER NOT NULL,
S TEXT NOT NULL
);
CREATE INDEX N_INDEX ON ITEMS(N);
INSERT INTO ITEMS
SELECT
(random()*1000000)::integer AS n,
md5(random()::text) AS s
FROM
generate_series(1,10000000);
Wykonywanie JOIN
z GROUP BY
w subquery Bez LATERAL
EXPLAIN
SELECT
I.*
FROM ITEMS I
INNER JOIN (
SELECT
COUNT(1), n
FROM ITEMS
GROUP BY N
) I2 ON I2.N = I.N
WHERE I.N IN (243477, 997947);
Wyniki
Merge Join (cost=0.87..637500.40 rows=23 width=37)
Merge Cond: (i.n = items.n)
-> Index Scan using n_index on items i (cost=0.43..101.28 rows=23 width=37)
Index Cond: (n = ANY ('{243477,997947}'::integer[]))
-> GroupAggregate (cost=0.43..626631.11 rows=861418 width=12)
Group Key: items.n
-> Index Only Scan using n_index on items (cost=0.43..593016.93 rows=10000000 width=4)
Using LATERAL
EXPLAIN
SELECT
I.*
FROM ITEMS I
INNER JOIN LATERAL (
SELECT
COUNT(1), n
FROM ITEMS
WHERE N = I.N
GROUP BY N
) I2 ON 1=1 --I2.N = I.N
WHERE I.N IN (243477, 997947);
Wyniki
Nested Loop (cost=9.49..1319.97 rows=276 width=37)
-> Bitmap Heap Scan on items i (cost=9.06..100.20 rows=23 width=37)
Recheck Cond: (n = ANY ('{243477,997947}'::integer[]))
-> Bitmap Index Scan on n_index (cost=0.00..9.05 rows=23 width=0)
Index Cond: (n = ANY ('{243477,997947}'::integer[]))
-> GroupAggregate (cost=0.43..52.79 rows=12 width=12)
Group Key: items.n
-> Index Only Scan using n_index on items (cost=0.43..52.64 rows=12 width=4)
Index Cond: (n = i.n)
Moja wersja Postgres to PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-24 03:49:46
Wiem, że to stare, ale ponieważ Postgresql 9.3 Istnieje opcja użycia słowa kluczowego "LATERAL", aby użyć powiązanych zapytań wewnątrz JOINS, więc zapytanie z pytania wyglądałoby następująco:
SELECT
name, author_id, count(*), t.total
FROM
names as n1
INNER JOIN LATERAL (
SELECT
count(*) as total
FROM
names as n2
WHERE
n2.id = n1.id
AND n2.author_id = n1.author_id
) as t ON 1=1
GROUP BY
n1.name, n1.author_id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-19 10:06:21