Jak wykonać podzapytanie Postgresql w klauzuli select z klauzulą ​​Join in from, taką jak SQL Server?


83

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ą działałoby 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: "podzapytanie w FROM nie może odnosić się do innych relacji na tym samym poziomie zapytania". Więc utknąłem. Czy ktoś wie, jak mogę to osiągnąć?

Dzięki


Właściwie wygląda na to, że to powinno działać na Postgres (może 6 lat temu nie
zadziałało

Odpowiedzi:


124

Nie jestem pewien, czy doskonale rozumiem twój zamiar, ale być może poniższe byłyby bliskie temu, 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 pierwszego podzapytania według identyfikatora, a także nazwy i identyfikatora autora, co nie wydaje mi się potrzebne. Nie jestem jednak pewien, jak to obejść, ponieważ musisz mieć dostępny identyfikator, aby dołączyć do drugiego podzapytania. Być może ktoś inny wymyśli lepsze rozwiązanie.

Udostępniaj i ciesz się.


Idealny Bob, to naprawdę zadziałało. Wielkie dzięki! Musiałem dokonać niewielkiej zmiany, ponieważ nie potrzebuję połączenia z id, tylko author_id. Więc ostatnie zapytanie to: wybierz 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 internal join (select author_id, count (1) as total_count from names group by author_id) n2 on (n2.author_id = n1.author_id) Teraz, gdy to mam, naprawdę chcę podzielić count_1 przez total_count, aby uzyskać znormalizowaną częstotliwość. = D
Ricardo

ops, właśnie sobie uświadomiłem, że sql nie jest tutaj poprawnie formatowany. :( Podam odpowiedź jako uzupełnienie.
Ricardo

Nie miałem problemu, o którym mówił Ricado, ale ten SQL całkowicie naprawił moje problemy ...: D DZIĘKUJĘ !!!
tftd

16

Uzupełniając odpowiedź @Bob Jarvis i @dmikam , Postgres nie wykonuje dobrego planu, gdy nie używasz LATERAL, poniżej symulacji, w obu przypadkach wyniki zapytania są takie same, ale koszt jest bardzo różny

Struktura tabeli

CREATE TABLE ITEMS (
    N INTEGER NOT NULL,
    S TEXT NOT NULL
);

INSERT INTO ITEMS
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM
    generate_series(1,1000000);

CREATE INDEX N_INDEX ON ITEMS(N);

Wykonywanie JOINz GROUP BYpodzapytaniem bezLATERAL

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)

Za pomocą 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)


3
Dzięki za wskazówkę dotyczącą używania LATERAL !!
leole

14

Wiem, że to jest stare, ale od Postgresql 9.3 istnieje opcja użycia słowa kluczowego "LATERAL", aby użyć PODKREWNYCH podzapytań 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

1
Zastanawiam się, czy wykonanie tych dwóch zapytań ma różnicę, czy też dla postgresql to ten sam plan
deFreitas


13

Odpowiadam tutaj, podając sformatowaną wersję ostatecznej wersji sql, której 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)

2
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 distinct(author_id), count(1) as total_count
              from names) n2
  on (n2.author_id = n1.author_id)
Where true

używane, distinctjeśli więcej sprzężeń wewnętrznych, ponieważ większa wydajność grup sprzężeń jest wolna

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.