Question

我想写的是按一栏进行有条件计算的一种储存程序。理想的做法是,如果可能的话,执行《纲领》。如果不是基本干 d是PogreSQL(v8.4),则优先。

正在询问的基本表格就是如此:

CREATE TABLE treatment_def (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL
                 );

CREATE TABLE foo_group_def (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL
                 );

CREATE TABLE foo (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL,
                    trtmt_id   INT REFERENCES treatment_def(id) ON DELETE RESTRICT,
                    foo_grp_id INT REFERENCES foo_group_def(id) ON DELETE RESTRICT,
                    is_male    BOOLEAN NOT NULL,
                    cost       REAL NOT NULL
             );

我想写出一份特别计划,其中列出以下表格结果:

treatment_name, foo_group_name, averaged_cost

平均成本的计算方式不同,取决于浏览场是否真的或假的。

为这一问题的目的,请允许我假设,如果真的是正式旗帜,那么平均成本是按组别成本值的SUM计算,如果是正式国旗被伪造,则计算成本价值为集团成本值的AVERAGE。

(Obviously) the data is being grouped by trmt_id, foo_grp_id (and is_male?).

I have a rough idea about how to to write the SQL if there was no conditional test on the is_male flag. However, I could do with some help in writing the SP as defined above.

这是我第一次尝试:

CREATE TYPE FOO_RESULT AS (treatment_name VARCHAR(16), foo_group_name VARCHAR(64), averaged_cost DOUBLE);      

// Outline plpgsql (Pseudo code)

CREATE FUNCTION somefunc() RETURNS SETOF FOO_RESULT AS $$
BEGIN
   RETURN QUERY SELECT t.name treatment_name, g.name group_name, averaged_cost  FROM foo f 
                     INNER JOIN treatment_def t ON t.id = f.trtmt_id
                     INNER JOIN foo_group_def g ON g.id = f.foo_grp_id
                GROUP BY f.trtmt_id, f.foo_grp_id;
END;
$$ LANGUAGE plpgsql;

I would appreciate some help on how to write this SP correctly to implement the conditional calculation in the column results

Answer 1

::

CREATE FUNCTION somefunc()
 RETURNS TABLE (
  treatment_name varchar(16)
, foo_group_name varchar(16)
, averaged_cost double precision)
AS
$BODY$

    SELECT t.name          -- AS treatment_name
         , g.name          -- AS group_name
         , CASE WHEN f.is_male THEN sum(f.cost)
                               ELSE avg(f.cost) END -- AS averaged_cost  
    FROM   foo f 
    JOIN   treatment_def t ON t.id = f.trtmt_id
    JOIN   foo_group_def g ON g.id = f.foo_grp_id
    GROUP  BY 1, 2, f.is_male;

$BODY$ LANGUAGE sql;

Major points

我使用了<代码>sql功能,而不是plpgsql。你们都可以使用,我只是为了缩短法典。 <代码>plpgsql可能稍快一些,因为查询计划已经过时。
I skipped the custom composite type. You can do that simpler with RETURNS TABLE.
我一般建议使用text,而不是varchar(n)。使你们的生活更加容易。
谨慎地避免在职能机构使用RETURN参数的名称而不作表-量化(tbl.col),或你将制造点名冲突。因此,我评论了其中的内容。
页: 1 按条款分列的组别。原来的工作。 (The one does the one in @Ken s response.)

Answer 2

您可使用<代码>CASE:

SELECT t.name treatment_name, g.name group_name, 
  CASE is_male WHEN true then SUM(cost)
   ELSE AVG(cost) END AS averaged_cost  
FROM foo f 
  INNER JOIN treatment_def t ON t.id = f.trtmt_id
  INNER JOIN foo_group_def g ON g.id = f.foo_grp_id
GROUP BY 1, 2, f.is_male;

I m不熟悉PLpgSQL,因此,I m不能确定BOOLEAN栏的准确辛醇,但以上至少应当由您从正确方向着手。

Major points

友情链接