主页 / dba / 问题 / 291055
Accepted
user229251
Asked: 2021-05-06 09:01:09 +0800 CST

没有参数的函数，并在内部调用函数

  • 772

我是 PostgreSQL 的新手，感觉不知所措..

我想要实现的是这样的：

想要的结果

年 描述 百分
2019 字字字 0.10
2018 词词伤心 0.80
2017 字空字 0.15
ETC 字字字 0.41

其中百分比是 function_A 除以 function_B** (function_A / function_B)。

我已经有了这些功能

  • function_A有两个参数：年份（从 status_date 中提取）和代码。返回由先前参数定义的订单数。
  • function_B有一个参数：year（从 status_date 中提取）。按年份返回订单总数。

功能

到目前为止，这就是我所做的错误 - 包括来自 nbk 原始答案的代码：

该函数不应该有任何参数。

CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc text, percentage bigint) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip text DEFAULT 0;
    percent bigint DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM created_dt) as numeric),
                order_desc as text,
                ROUND(function_A(inyear,incode))::decimal / function_B(inyear) as percent
        FROM orders o
        WHERE EXTRACT(YEAR FROM o.creation) = inyear
        AND o.order_code = incode
        AND o.descrip 
        GROUP BY EXTRACT(year FROM o.creation), o.code
        ORDER BY retyear ASC;
END;
$$
LANGUAGE plpgsql;

要调用我希望使用的函数：

SELECT * from summary_code()

我的数据看起来像这样：

“订单”表

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    descript CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

记录

INSERT INTO orders 
(id, code, service_id, descript, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I'm tired','2019-06-16 11:12','2019-06-18 14:08',1);

如果这有点厚颜无耻，我很抱歉，但我不知道从哪里开始。

postgresql

1 个回答

  1. Best Answer
    CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    descript CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

    INSERT INTO orders 
(id, code, service_id, descript, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I''m tired','2019-06-16 11:12','2019-06-18 14:08',1);

    CREATE FUNCTION function_A(in numeric, in int,out noorder int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1 AND "code" = $2 $$
    LANGUAGE SQL;

    CREATE FUNCTION function_B(in numeric,out noorderyear int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1  $$
    LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc TEXT, percentage numeric) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip TeXT DEFAULT 0;
    percent numeric DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM status_date) as numeric) teayear,
                MIN(descript) as descrip,
                ROUND(Max(function_A(CAST(EXTRACT(year FROM status_date) as numeric),code))::decimal / function_B(CAST(EXTRACT(year FROM status_date) as numeric)),2) as percent
        FROM orders o
        GROUP BY teayear
        ORDER BY teayear ASC;
END;
$$
LANGUAGE plpgsql;

    SELECT * FROM summary_orders()

    退休| order_desc | 百分比
------: | :-------------------- | ---------:
   2018 | 完成蓝喷 | 1.00
   2019 | 要求的蓝色套件 | 0.60

    db<>在这里摆弄

    • 0

相关问题