ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决postgresql - PLpgSQL (or ANSI SQL?) Conditional calculation on a column

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

I want to write a stored procedure that performs a conditional calculation on a column. Ideally the implementation of the SP will be db agnostic - if possible. If not the underlying db is PostgreSQL (v8.4), so that takes precedence.

The underlying tables being queried looks like this:

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
             );

I want to write a SP that returns the following 'table' result set:

treatment_name, foo_group_name, averaged_cost

where averaged cost is calcluated differently, depending on whether the row field *is_male* flag is set to true or false.

For the purpose of this question, lets assume that if the is_male flag is set to true, then the averaged cost is calculated as the SUM of the cost values for the grouping, and if the is_male flag is set to false, then the cost value is calculated as the AVERAGE of the cost values for the grouping.

(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.

Here is my first attempt:

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

sql postgresql stored-procedures plpgsql
|
  this question
asked Jan 16 '12 at 5:31 Homunculus Reticulli 12.1k 43 121 212

 | 

2 Answers
2

解决方法

Could look like this:

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

  • I used an sql function, not plpgsql. You can use either, I just did it to shorten the code. plpgsql might be slightly faster, because the query plan is cached.

  • I skipped the custom composite type. You can do that simpler with RETURNS TABLE.

  • I would generally advise to use the data type text instead of varchar(n). Makes your life easier.

  • Be careful not to use names of the RETURN parameter without table-qualifying (tbl.col) in the function body, or you will create naming conflicts. That is why I commented the aliases.

  • I adjusted the GROUP BY clause. The original didn't work. (Neither does the one in @Ken's answer.)


|
  this answer
edited Jan 16 '12 at 8:24 answered Jan 16 '12 at 7:41 Erwin Brandstetter 248k 39 424 528      +1 for clean simple ANSI SQL that works. Once again, you saved my bacon! :) –  Homunculus Reticulli Jan 16 '12 at 9:12

 | 

You should be able to use a CASE statement:

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 not familiar with PLpgSQL, so I'm not sure of the exact syntax for the BOOLEAN column, but the above should at least get you started in the right direction.


|
  this answer
edited Jan 17 '12 at 4:07 answered Jan 16 '12 at 6:18 Ken White 97.7k 11 117 209

 | 


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。