PostgreSQL Select 连接表和一个函数

Question

我的数据库中有一个函数，它返回一个表。我想要做的是选择加入一个表和那个函数。我看到在FROM的地方是不行的，但是如果我把这个函数放在select里面，执行的时间就太长了。我的 SQL 是:

select
    "Grades"."id",
    SUPRIMENTO_LOJA("Grades"."id", 30, 1, 1)
from
    "Grades"

这需要 500 多秒才能执行

我也试过了，但是返回错误

select
    *
from
    "Grades",
    SUPRIMENTO_LOJA("Grades"."id", 30, 1, 1)

使此选择起作用的最佳方法是什么？

编辑:函数是这样的:

CREATE OR REPLACE FUNCTION suprimento_loja(grade_id integer, vendas_dias integer, lojaId integer, embalagem numeric(15, 4)) 
RETURNS TABLE(qtd integer, maximo integer, ultFornecedor character varying(50), 
ultCompra character varying(10), ultVenda character varying(10), classeLoja character varying(1), 
vendas integer, sugestao integer, giroLoja numeric(15, 4))
AS $$
DECLARE     
    qtde1 integer;
    vendasdiae1 integer;
    maximoe1 integer;
    ultvendae1 timestamp;
    ultcomprae1 timestamp;
    ultforne1 varchar(50);
    classee1 varchar(1);
    vendase1 integer;
    qtde2 integer;
    vendasdiae2 integer;
    maximoe2 integer;
    ultvendae2 timestamp;
    ultcomprae2 timestamp;
    ultforne2 varchar(50);
    vendase2 integer;
    qtde3 integer;
    vendasdiae3 integer;
    maximoe3 integer;
    ultvendae3 timestamp;
    ultcomprae3 timestamp;
    ultforne3 varchar(50);
    vendase3 integer;
    giroe1 numeric(15, 4);
    giroe2 numeric(15, 4);
    giroe3 numeric(15, 4);
BEGIN
    with vendas as (
    select
        sum(coalesce("quantidade", 0)) as vendas_periodo,
        "inventario_id"
    from
        "WVItens"
    where
        "WVItens"."ultimoDownload" between current_timestamp - (select to_interval(vendas_dias))
        and current_timestamp and coalesce("status", '') = ''
    group by
        "inventario_id")
    select into qtde1, vendasdiae1, maximoe1, ultvendae1, ultcomprae1, ultforne1, classee1, vendase1, giroe1
        cast("quantidadeAtual" as integer), 
        cast("vendasPorDia" as integer), 
        cast("maximoEmDias" as integer), 
            "dataUltimaVenda", 
        "dataUltimaCompra", 
        "ultimoFornecedor", 
        "classe", 
        cast(vendas_periodo as integer),
        coalesce("giro", 0)
    from 
        "Inventarios", 
        "Lojas",
        vendas
    where 
                "Inventarios"."estoque_id" = "Lojas"."estoque1_id" and 
        coalesce("Inventarios"."status", '') = '' and
        "Inventarios"."id" = vendas."inventario_id" and
        "Lojas"."id" = lojaId and 
        "grade_id" = gradeId;

    with vendas as (
    select
        sum(coalesce("quantidade", 0)) as vendas_periodo,
        "inventario_id"
    from
        "WVItens"
    where
        "WVItens"."ultimoDownload" between current_timestamp - (select to_interval(vendas_dias))
        and current_timestamp and coalesce("status", '') = ''
    group by
        "inventario_id")        
    select into qtde2, vendasdiae2, maximoe2, ultvendae2, ultcomprae2, ultforne2, vendase2, giroe2
        cast("quantidadeAtual" as integer), 
        cast("vendasPorDia" as integer), 
        cast("maximoEmDias" as integer), 
        "dataUltimaVenda", 
        "dataUltimaCompra", 
        "ultimoFornecedor", 
        cast(vendas_periodo as integer),
        coalesce("giro", 0)
    from 
        "Inventarios", 
                "Lojas",
            vendas
    where 
        "Inventarios"."estoque_id" = "Lojas"."estoque2_id" and 
        coalesce("Inventarios"."status", '') = '' and
        "Inventarios"."id" = vendas."inventario_id" and
        "Lojas"."id" = lojaId and 
        "grade_id" = gradeId;   

    with vendas as (
    select
        sum(coalesce("quantidade", 0)) as vendas_periodo,
        "inventario_id"
    from
        "WVItens"
    where
        "WVItens"."ultimoDownload" between current_timestamp - (select to_interval(vendas_dias))
        and current_timestamp and coalesce("status", '') = ''
    group by
        "inventario_id")            
    select into qtde3, vendasdiae3, maximoe3, ultvendae3, ultcomprae3, ultforne3, vendase3, giroe3
        cast("quantidadeAtual" as integer), 
        cast("vendasPorDia" as integer), 
        cast("maximoEmDias" as integer), 
        "dataUltimaVenda", 
        "dataUltimaCompra", 
        "ultimoFornecedor", 
        cast(vendas_periodo as integer),
        coalesce("giro", 0)
    from 
        "Inventarios", 
        "Lojas",
        vendas
    where 
        "Inventarios"."estoque_id" = "Lojas"."estoque2_id" and 
        coalesce("Inventarios"."status", '') = '' and
        "Inventarios"."id" = vendas."inventario_id" and
        "Lojas"."id" = lojaId and 
        "grade_id" = gradeId;   

    qtd := 0;
    IF (qtde1 is not null) THEN
        qtd := qtd + qtde1;
    END IF; 
    IF (qtde2 is not null) THEN
        qtd := qtd + qtde2;
    END IF; 
    IF (qtde3 is not null) THEN
        qtd := qtd + qtde3;
    END IF;         

    giroLoja = (giroe1 + giroe2 + giroe3) / 3;

    maximo := 0;
    IF ((maximoe1 is not null) and (vendasdiae1 is not null)) THEN
        maximo := maximo + (maximoe1 * vendasdiae1);
    END IF;
    IF ((maximoe2 is not null) and (vendasdiae2 is not null)) THEN
        maximo := maximo + (maximoe2 * vendasdiae2);
    END IF;
    IF ((maximoe3 is not null) and (vendasdiae3 is not null)) THEN
        maximo := maximo + (maximoe3 * vendasdiae3);
    END IF;

    IF (qtde1 is null) THEN
        qtde1 := 0;
    END IF; 
    IF (qtde2 is null) THEN
        qtde2 := 0;        
    END IF; 
    IF (qtde3 is null) THEN
        qtde3 := 0;        
    END IF; 
    IF (maximoe1 is null) THEN
        maximoe1 := 0;        
    END IF; 
    IF (maximoe2 is null) THEN
        maximoe2 := 0;
    END IF; 
    IF (maximoe3 is null) THEN
        maximoe3 := 0;                        
    END IF; 
    IF (vendasdiae1 is null) THEN
        vendasdiae1 := 0;                
    END IF; 
    IF (vendasdiae2 is null) THEN
        vendasdiae2 := 0;
    END IF; 
    IF (vendasdiae3 is null) THEN
        vendasdiae3 := 0;                                
    END IF; 
    IF (vendase1 is null) THEN
        vendase1 := 0;                        
    END IF; 
    IF (vendase2 is null) THEN
        vendase2 := 0;
    END IF; 
    IF (vendase3 is null) THEN
        vendase3 := 0;  
    END IF; 

    ultCompra := '';
    ultVenda := '';
    ultFornecedor := '';


    IF (ultcomprae1 is null) THEN
        IF (ultcomprae2 is null) THEN
            IF (ultcomprae3 is not null) THEN
                ultCompra := cast(extract(day from ultcomprae3) || '/' || extract(month from ultcomprae3) || '/' || extract(year from ultcomprae3) as varchar(10));
                ultFornecedor := ultforne3;
            END IF;
        ELSE
            IF ((ultcomprae3 is null) or (ultcomprae2 > ultcomprae3)) THEN
                ultCompra := cast(extract(day from ultcomprae2) || '/' || extract(month from ultcomprae2) || '/' || extract(year from ultcomprae2) as varchar(10));
                ultFornecedor := ultforne2;
            ELSE
                ultCompra := cast(extract(day from ultcomprae3) || '/' || extract(month from ultcomprae3) || '/' || extract(year from ultcomprae3) as varchar(10));
                ultFornecedor := ultforne3;
            END IF;
        END IF;
    ELSE
        IF ((ultcomprae2 is null) or (ultcomprae1 > ultcomprae2)) THEN
            IF ((ultcomprae3 is null) or (ultcomprae1 > ultcomprae3)) THEN
                ultCompra := cast(extract(day from ultcomprae1) || '/' || extract(month from ultcomprae1) || '/' || extract(year from ultcomprae1) as varchar(10));
                ultFornecedor := ultforne1;
            ELSE
                IF (ultcomprae3 is not null) THEN
                    ultCompra := cast(extract(day from ultcomprae3) || '/' || extract(month from ultcomprae3) || '/' || extract(year from ultcomprae3) as varchar(10));
                    ultFornecedor := ultforne3;             
                END IF;
            END IF;
        ELSE
            IF ((ultcomprae3 is null) or (ultcomprae2 > ultcomprae3)) THEN
                ultCompra := cast(extract(day from ultcomprae2) || '/' || extract(month from ultcomprae2) || '/' || extract(year from ultcomprae2) as varchar(10));
                ultFornecedor := ultforne2;
            ELSE
                ultCompra := cast(extract(day from ultcomprae3) || '/' || extract(month from ultcomprae3) || '/' || extract(year from ultcomprae3) as varchar(10));
                ultFornecedor := ultforne3;
            END IF;     
        END IF;
    END IF; 

    IF (ultvendae1 is null) THEN
        IF (ultvendae2 is null) THEN
            IF (ultvendae3 is not null) THEN
                ultVenda := cast(extract(day from ultvendae3) || '/' || extract(month from ultvendae3) || '/' || extract(year from ultvendae3) as varchar(10));
            END IF;
        ELSE
            IF ((ultvendae3 is null) or (ultvendae2 > ultvendae3)) THEN
                ultVenda := cast(extract(day from ultvendae2) || '/' || extract(month from ultvendae2) || '/' || extract(year from ultvendae2) as varchar(10));
            ELSE
                ultVenda := cast(extract(day from ultvendae3) || '/' || extract(month from ultvendae3) || '/' || extract(year from ultvendae3) as varchar(10));
            END IF;
        END IF;
    ELSE
        IF ((ultvendae2 is null) or (ultvendae1 > ultvendae2)) THEN
            IF ((ultvendae3 is null) or (ultvendae1 > ultvendae3)) THEN
                ultVenda := cast(extract(day from ultvendae1) || '/' || extract(month from ultvendae1) || '/' || extract(year from ultvendae1) as varchar(10));
            ELSE
                IF (ultvendae3 is not null) THEN
                    ultVenda := cast(extract(day from ultvendae3) || '/' || extract(month from ultvendae3) || '/' || extract(year from ultvendae3) as varchar(10));
                END IF;
            END IF;
        ELSE
            IF ((ultvendae3 is null) or (ultvendae2 > ultvendae3)) THEN
                ultVenda := cast(extract(day from ultvendae2) || '/' || extract(month from ultvendae2) || '/' || extract(year from ultvendae2) as varchar(10));
            ELSE
                ultVenda := cast(extract(day from ultvendae3) || '/' || extract(month from ultvendae3) || '/' || extract(year from ultvendae3) as varchar(10));
            END IF;     
        END IF;
    END IF;     


    vendas = vendase1 + vendase2 + vendase3;
    classeLoja := classee1;

    IF ((qtde1 + qtde2 + qtde3) > ((maximoe1 * vendasdiae1) + (maximoe2 * vendasdiae2) + (maximoe3 * vendasdiae3))) THEN
        sugestao := 0;
    ELSE
        sugestao := cast(((((maximoe1 * vendasdiae1) + (maximoe2 * vendasdiae2) + (maximoe3 * vendasdiae3)) - (qtde1 + qtde2 + qtde3)) + 0.4) as integer);
    END IF;


    RETURN NEXT;
END
$$ LANGUAGE plpgsql;

我尝试了一些查询，但它太长了，甚至不是我需要的最终查询

    with suprimento as (
        with vendas as (
            select
                sum(coalesce("quantidade", 0)) as vendas_periodo,
                "inventario_id"
            from
                "WVItens"
            where
                "WVItens"."ultimoDownload" between current_timestamp - (select to_interval(30))
                and current_timestamp and coalesce("status", '') = ''
            group by
                "inventario_id")
        select
            "grade_id",
            "estoque_id",
            sum(cast("quantidadeAtual" as integer)) as "quantidade", 
            sum(cast("vendasPorDia" as integer)) as "vendasPorDia", 
            sum(cast("maximoEmDias" as integer)) as "maximoEmDias", 
            max("dataUltimaVenda") as "ultVenda", 
            max("dataUltimaCompra") as "ultCompra", 
            max("ultimoFornecedor") as "ultFornecedor", 
            sum(cast(vendas_periodo as integer)) as "vendasPeriodo",
            max(coalesce("giro", 0)) as "giro"
        from 
            "Inventarios" 
            left outer join vendas on ("Inventarios"."id" = vendas."inventario_id")
        where 
            coalesce("Inventarios"."status", '') = ''           
        group by
            "grade_id", "estoque_id")    

新编辑!!!

我试图只进行一次查询，但它一直运行到现在(超过 800 秒)，查询是:

WITH suprimento_loja as (
WITH suprimento as (
    WITH vendas as (
      SELECT "inventario_id"
        , SUM(coalesce("quantidade", 0)) as vendas_periodo
      FROM "WVItens" vwi
      WHERE vwi."ultimoDownload" between current_timestamp - (SELECT to_interval(30))
        AND current_timestamp
        AND coalesce("status", '') = ''
      GROUP BY "inventario_id"
    )
    SELECT
        "estoque_id",
        "grade_id",
        "classe" as classe
        , cast("quantidadeAtual" as integer) as qtd
        , cast("vendasPorDia" as integer) as vendasDia
        , cast("maximoEmDias" as integer) as maximo
        , coalesce("dataUltimaVenda", timestamp'01.01.1980') as ultVenda
        , "dataUltimaCompra" as ultCompra
        , "ultimoFornecedor" as ultForn
        , cast(vendas_periodo as integer) as vendas_periodo
        , coalesce("giro", 0) as giro
    FROM "Inventarios"  inv
    LEFT OUTER JOIN vendas ve ON inv."id" = ve."inventario_id"
    )
select
    lo."id" as id,
    e1."grade_id" as grade_id,
    e1.ultVenda,
    case
    when e1.ultVenda > coalesce(e2.ultVenda, timestamp'01.01.1980') and e1.ultVenda > coalesce(e3.ultVenda, timestamp'01.01.1980')
    then e1.ultVenda
    else
        case 
        when coalesce(e2.ultVenda, timestamp'01.01.1980') > coalesce(e3.ultVenda, timestamp'01.01.1980')
        then e2.ultVenda
        else e3.ultVenda
        end
    end as ultVenda,
    case
    when e1.ultCompra > coalesce(e2.ultCompra, timestamp'01.01.1980') and e1.ultCompra > coalesce(e3.ultCompra, timestamp'01.01.1980')
    then e1.ultCompra
    else
        case 
        when coalesce(e2.ultCompra, timestamp'01.01.1980') > coalesce(e3.ultCompra, timestamp'01.01.1980')
        then e2.ultCompra
        else e3.ultCompra
        end
    end as ultCompra,
    case
    when e1.ultCompra > coalesce(e2.ultCompra, timestamp'01.01.1980') and e1.ultCompra > coalesce(e3.ultCompra, timestamp'01.01.1980')
    then e1.ultForn
    else
        case 
        when coalesce(e2.ultCompra, timestamp'01.01.1980') > coalesce(e3.ultCompra, timestamp'01.01.1980')
        then e2.ultForn
        else e3.ultForn
        end
    end as ultForn,
    coalesce(e1.vendas_periodo, 0) + coalesce(e2.vendas_periodo, 0) + coalesce(e3.vendas_periodo, 0) as vendas_periodo,
    e1.classe,
    (coalesce(e1.maximo, 0) * coalesce(e1.vendasDia, 0)) + (coalesce(e2.maximo, 0) * coalesce(e2.vendasDia, 0)) + (coalesce(e3.maximo, 0) * coalesce(e3.vendasDia, 0)) as maximo,
    coalesce(e1.giro, 0) as giro,
    coalesce(e1.qtd, 0) + coalesce(e2.qtd, 0) + coalesce(e3.qtd, 0) as qtde,
    case
        when coalesce(e1.giro, 0) = 0
        then 0
        else
        case 
            when ((coalesce(e1.qtd, 0) + coalesce(e2.qtd, 0) + coalesce(e3.qtd, 0)) > ((coalesce(e1.maximo, 0) * coalesce(e1.vendasDia, 0)) + (coalesce(e2.maximo, 0) * coalesce(e2.vendasDia, 0)) + (coalesce(e3.maximo, 0) * coalesce(e3.vendasDia, 0))))
            then 0
            else cast(((((coalesce(e1.maximo, 0) * coalesce(e1.vendasDia, 0)) + (coalesce(e2.maximo, 0) * coalesce(e2.vendasDia, 0)) + (coalesce(e3.maximo, 0) * coalesce(e3.vendasDia, 0))) 
              - (coalesce(e1.qtd, 0) + coalesce(e2.qtd, 0) + coalesce(e3.qtd, 0))) + 0.4) as integer)
        end 
    end as sugestao
from
    "Lojas" lo
    LEFT OUTER JOIN suprimento e2 ON e2."estoque_id" = lo."estoque2_id"
    LEFT OUTER JOIN suprimento e3 ON e3."estoque_id" = lo."estoque3_id"
    JOIN suprimento e1 ON e1."estoque_id" = lo."estoque1_id")

SELECT
    gr."id",
    sl1.*,
    sl2.*,
    sl3.*,
    sl4.*,
    sl5.*,
    sl6.*
FROM
    "Grades" gr
    JOIN suprimento_loja sl1 ON sl1."grade_id" = gr."id"
    JOIN suprimento_loja sl2 ON sl2."grade_id" = gr."id"
    JOIN suprimento_loja sl3 ON sl3."grade_id" = gr."id"
    JOIN suprimento_loja sl4 ON sl4."grade_id" = gr."id"
    JOIN suprimento_loja sl5 ON sl5."grade_id" = gr."id"
    JOIN suprimento_loja sl6 ON sl6."grade_id" = gr."id"
WHERE
    sl1."id" = 1 AND
    sl2."id" = 2 AND
    sl3."id" = 3 AND
    sl4."id" = 4 AND
    sl5."id" = 5 AND
    sl6."id" = 6 

我使用了别名和加入，这样你可以轻松阅读

Answer 1

我让它工作，但以其他方式。如果有人遇到类似的问题，我得到的解决方案是:

SELECT 
    gr.id,
    max(CASE sl.id WHEN 1 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l1,
    max(CASE sl.id WHEN 2 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l2,
    max(CASE sl.id WHEN 3 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l3,
    max(CASE sl.id WHEN 4 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l4,
    max(CASE sl.id WHEN 5 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l5,
    max(CASE sl.id WHEN 6 THEN sl.ultVenda ELSE timestamp'01.01.1980' END) AS ultVenda_l6,
    max(CASE sl.id WHEN 1 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l1,
    max(CASE sl.id WHEN 2 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l2,
    max(CASE sl.id WHEN 3 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l3,
    max(CASE sl.id WHEN 4 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l4,
    max(CASE sl.id WHEN 5 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l5,
    max(CASE sl.id WHEN 6 THEN sl.ultCompra ELSE timestamp'01.01.1980' END) AS ultCompra_l6,
    max(CASE sl.id WHEN 1 THEN sl.ultForn ELSE '' END) AS ultForn_l1,
    max(CASE sl.id WHEN 2 THEN sl.ultForn ELSE '' END) AS ultForn_l2,
    max(CASE sl.id WHEN 3 THEN sl.ultForn ELSE '' END) AS ultForn_l3,
    max(CASE sl.id WHEN 4 THEN sl.ultForn ELSE '' END) AS ultForn_l4,
    max(CASE sl.id WHEN 5 THEN sl.ultForn ELSE '' END) AS ultForn_l5,
    max(CASE sl.id WHEN 6 THEN sl.ultForn ELSE '' END) AS ultForn_l6,
    max(CASE sl.id WHEN 1 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l1,
    max(CASE sl.id WHEN 2 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l2,
    max(CASE sl.id WHEN 3 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l3,
    max(CASE sl.id WHEN 4 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l4,
    max(CASE sl.id WHEN 5 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l5,
    max(CASE sl.id WHEN 6 THEN sl.vendas_periodo ELSE 0 END) AS vendas_periodo_l6,
    max(CASE sl.id WHEN 1 THEN sl.maximo ELSE 0 END) AS maximo_l1,
    max(CASE sl.id WHEN 2 THEN sl.maximo ELSE 0 END) AS maximo_l2,
    max(CASE sl.id WHEN 3 THEN sl.maximo ELSE 0 END) AS maximo_l3,
    max(CASE sl.id WHEN 4 THEN sl.maximo ELSE 0 END) AS maximo_l4,
    max(CASE sl.id WHEN 5 THEN sl.maximo ELSE 0 END) AS maximo_l5,
    max(CASE sl.id WHEN 6 THEN sl.maximo ELSE 0 END) AS maximo_l6,
    max(CASE sl.id WHEN 1 THEN sl.giro ELSE 0 END) AS giro_l1,
    max(CASE sl.id WHEN 2 THEN sl.giro ELSE 0 END) AS giro_l2,
    max(CASE sl.id WHEN 3 THEN sl.giro ELSE 0 END) AS giro_l3,
    max(CASE sl.id WHEN 4 THEN sl.giro ELSE 0 END) AS giro_l4,
    max(CASE sl.id WHEN 5 THEN sl.giro ELSE 0 END) AS giro_l5,
    max(CASE sl.id WHEN 6 THEN sl.giro ELSE 0 END) AS giro_l6,
    max(CASE sl.id WHEN 1 THEN sl.qtde ELSE 0 END) AS qtd_l1,
    max(CASE sl.id WHEN 2 THEN sl.qtde ELSE 0 END) AS qtd_l2,
    max(CASE sl.id WHEN 3 THEN sl.qtde ELSE 0 END) AS qtd_l3,
    max(CASE sl.id WHEN 4 THEN sl.qtde ELSE 0 END) AS qtd_l4,
    max(CASE sl.id WHEN 5 THEN sl.qtde ELSE 0 END) AS qtd_l5,
    max(CASE sl.id WHEN 6 THEN sl.qtde ELSE 0 END) AS qtd_l6,
    max(CASE sl.id WHEN 1 THEN sl.sugestao ELSE 0 END) AS sugestao_l1,
    max(CASE sl.id WHEN 2 THEN sl.sugestao ELSE 0 END) AS sugestao_l2,
    max(CASE sl.id WHEN 3 THEN sl.sugestao ELSE 0 END) AS sugestao_l3,
    max(CASE sl.id WHEN 4 THEN sl.sugestao ELSE 0 END) AS sugestao_l4,
    max(CASE sl.id WHEN 5 THEN sl.sugestao ELSE 0 END) AS sugestao_l5,
    max(CASE sl.id WHEN 6 THEN sl.sugestao ELSE 0 END) AS sugestao_l6
FROM "Grades" gr
INNER JOIN suprimento_loja sl ON sl.grade_id = gr.id
group by gr.id
order by gr.id

View “suprimento_loja”是之前在函数中使用的查询。