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

В соответствии с инструкциями здесь я создал две функции, которые используют EXECUTE FORMAT и возвращают одну и ту же таблицу (int,smallint).

Примеры определений:

CREATE OR REPLACE FUNCTION function1(IN _tbl regclass, IN _tbl2 regclass, 
IN field1 integer) 
RETURNS TABLE(id integer, dist smallint)

CREATE OR REPLACE FUNCTION function2(IN _tbl regclass, IN _tbl2 regclass, 
IN field1 integer) 
RETURNS TABLE(id integer, dist smallint)

Обе функции возвращают одинаковое количество строк. Пример результата (всегда будет упорядочен по расстоянию):

(49,0)
(206022,3)
(206041,3)
(92233,4)

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

Например:

SELECT
function1('tblp1','tblp2',49),function2('tblp1_v2','tblp2_v2',49)

Возвращает что-то вроде:

(49,0)      (49,0)
(206022,3)  (206022,3)
(206041,3)  (206041,3)
(92233,4)   (133,4)

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

1st row 0 = 0,
2nd row 3 = 3,
3rd row 3 = 3,
4th row 4 = 4

несмотря на то, что для 4-й строки 92233!=133

Есть ли способ получить только второе поле каждого результата функции, чтобы сравнить их, например. с чем-то вроде:

SELECT COUNT(*)
FROM 
(SELECT
function1('tblp1','tblp2',49).field2,
function2('tblp1_v2','tblp2_v2',49).field2 ) n2
WHERE  function1('tblp1','tblp2',49).field2 != function1('tblp1','tblp2',49).field2;

Я использую PostgreSQL 9.3.


postgresql sql postgresql-9.3 set-returning-functions
person Alexandros    schedule 02.03.2015    source источник
comment
Вы должны использовать select * from function1('tblp1','tblp2',49) для набора возвращаемых функций. Не помещайте их в список выбора.   -  person a_horse_with_no_name    schedule 02.03.2015
comment
Вы добавили (will be always ordered by dist). Вы уверены, что это не открывает возможностей для ложных срабатываний?   -  person Erwin Brandstetter    schedule 02.03.2015
comment
@ErwinBrandstetter Это запрос topK (ORDERED BY dist LIMIT k), поэтому результаты внутри функций упорядочены по dist. Я хочу проверить на конкретном примере, что я получаю один ответ с dist = 1, два ответа с dist = 3 и один ответ с dist = 4 для k = 4. Обе функции работают с разными таблицами (вторая работает с оптимизированными таблицами), и я хочу убедиться, что обе функции возвращают одно и то же расстояние для всех k результатов. В нашем случае результаты dist 1 и dist 3 всегда будут одинаковыми (другого результата с dist=3 нет), но в случае 4 я могу получить dist =4 для многих результатов.   -  person Alexandros    schedule 02.03.2015
comment
Итак, строки возвращаются в порядке соответствия? Остальное не важно для вопроса.   -  person Erwin Brandstetter    schedule 02.03.2015
comment
@a_horse_with_no_name: На самом деле здесь не хватает сути вопроса.   -  person Erwin Brandstetter    schedule 02.03.2015

Ответы (3)


arrow_upward
1
arrow_downward

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

Во всех следующих ответах предполагается, что строки возвращаются в соответствующем порядке.

Постгрес 9.3

С причудливой функцией разнесения строк из функций SRF, возвращающих одинаковое количество строк параллельно:

SELECT count(*) AS mismatches
FROM  (
   SELECT function1('tblp1','tblp2',49) AS f1
        , function2('tblp1_v2','tblp2_v2',49) AS f2
   ) sub
WHERE  (f1).dist <> (f2).dist;  -- note the parentheses!

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

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

Постгрес 9.4

WITH ORDINALITY для генерации номеров строк на лету

Вы можете использовать WITH ORDINALITY для генерации номера строки на лету, и вам не нужно зависеть от объединения результатов функций SRF в списке SELECT:

SELECT count(*) AS mismatches
FROM      function1('tblp1','tblp2',49)       WITH ORDINALITY AS f1(id,dist,rn)
FULL JOIN function2('tblp1_v2','tblp2_v2',49) WITH ORDINALITY AS f2(id,dist,rn) USING (rn)
WHERE  f1.dist IS DISTINCT FROM f2.dist;

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

Связанный:

ROWS FROM для объединения наборов строк- по ряду

SELECT count(*) AS mismatches
FROM   ROWS FROM (function1('tblp1','tblp2',49)
                , function2('tblp1_v2','tblp2_v2',49)) t(id1, dist1, id2, dist2)
WHERE  t.dist1 IS DISTINCT FROM t.dist2;

Связанный ответ:

Кроме того:
EXECUTE FORMAT не является набором функций plpgsql. RETURN QUERY есть. format() — это просто удобная функция для построения строки запроса можно использовать где угодно в SQL или plpgsql.

person Erwin Brandstetter    schedule 02.03.2015
comment
Вы уверены, что он работает с PostgreSQL 9.3? Я получаю синтаксическую ошибку, и я видел ее только на postgresql.org/ docs/devel/static/functions-srf.html. - person Alexandros; 02.03.2015
comment
@Alexandros Это функция версии 9.4+. - person Clodoaldo Neto; 02.03.2015
comment
@Alexandros Как я понимаю, даже если бы он работал в 9.3, он бы нумеровал набор результатов, возможно, не в том же порядке, что и внутри функции, поэтому номера строк должны быть назначены внутри функции. - person Clodoaldo Neto; 02.03.2015
comment
@Alexandros: Да, извините, WITH ORDINALITY для страницы 9.4, а не 9.3. Я добавил еще немного. - person Erwin Brandstetter; 02.03.2015
comment
@ClodoaldoNeto: число строк WITH ORDINALITY точно так же, как они возвращаются из функции. Так что да, если возвращаемый порядок строк не совпадает, я не вижу способа исправить это постфактум, и нам нужно будет добавить номера строк внутри каждой функции и вернуть их... - person Erwin Brandstetter; 02.03.2015

arrow_upward
1
arrow_downward

Порядок, в котором строки возвращаются из функций, не гарантируется. Если вы можете вернуть row_number() (rn в приведенном ниже примере) из функций тогда:

select
    count(f1.dist is null or f2.dist is null or null) as diff_count
from
    function1('tblp1','tblp2',49) f1
    inner join
    function2('tblp1_v2','tblp2_v2',49) f2 using(rn)
person Clodoaldo Neto    schedule 02.03.2015

arrow_upward
0
arrow_downward

Для дальнейшего использования:

Проверка разницы в количестве строк:

SELECT 
ABS(count(f1a.*)-count(f2a.*))  
FROM
(SELECT f1.dist, row_number()  OVER(ORDER BY f1.dist) rn
FROM
function1('tblp1','tblp2',49) f1)
f1a FULL JOIN 

(SELECT f2.dist, row_number() OVER(ORDER BY f2.dist) rn
FROM
function2('tblp1_v2','tblp2_v2',49) f2) f2a
USING (rn);

Проверка разницы в расстоянии для одинаковых упорядоченных строк:

SELECT 
COUNT(*)  

FROM

(SELECT f1.dist, row_number()  OVER(ORDER BY f1.dist) rn
FROM
function1('tblp1','tblp2',49) f1)
f1a 
(SELECT f2.dist, row_number() OVER(ORDER BY f2.dist) rn
FROM
function2('tblp1_v2','tblp2_v2',49) f2) f2a
WHERE f1a.rn=f2a.rn
AND f1a.distance <> f2a.distance;

Простой OVER() также может работать, поскольку результаты функций уже упорядочены, но добавлены для дополнительной проверки.

person Alexandros    schedule 02.03.2015