Thread: Referncing a calculated column in a select?
I am creating some views, that have columns with fairly complex calculations
in them. The I want to do further calculations using the result of this
calculation. Right now, I am just duplicating the first calculation in the
select fro the 2nd calculated column. There must be a batter way to do
this, right?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
On 9/12/19 2:23 PM, stan wrote: > I am creating some views, that have columns with fairly complex calculations > in them. The I want to do further calculations using the result of this > calculation. Right now, I am just duplicating the first calculation in the > select fro the 2nd calculated column. There must be a batter way to do > this, right? I must be misunderstanding you, because you can just do more of what you're doing now. Here's an example of doing some calculations in the creation of the view, and then more calculations when selecting from the view: test=# create table gargle (i integer, j integer); CREATE TABLE test=# create view v_gargle (i1, j1) as select i*2, j*3 from gargle; CREATE VIEW test=# select * from v_gargle; i1 | j1 ----+---- 4 | 9 (1 row) test=# select i1 - 3, j1 * 8 from v_gargle; ?column? | ?column? ----------+---------- 1 | 72 -- Angular momentum makes the world go 'round.
On 2019-09-12 15:35:56 -0500, Ron wrote:
> On 9/12/19 2:23 PM, stan wrote:
> > I am creating some views, that have columns with fairly complex calculations
> > in them. The I want to do further calculations using the result of this
> > calculation. Right now, I am just duplicating the first calculation in the
> > select fro the 2nd calculated column. There must be a batter way to do
> > this, right?
>
> I must be misunderstanding you, because you can just do more of what you're
> doing now.
>
> Here's an example of doing some calculations in the creation of the view,
> and then more calculations when selecting from the view:
I think he wants to refer to other columns in the view.
Something like
create or replace view v as
select a,
b,
abs(b - a) as abserror,
case when abs(a) > abs(b) then abs(a) else abs(b) end as mag,
abserror / mag as relerror
from t;
Except that this doesn't work.
What you can do is nest views:
create or replace view v as
select a,
b,
abs(b - a) as abserror,
case when abs(a) > abs(b) then abs(a) else abs(b) end as mag
from t;
create or replace view v2 as
select a,
b,
abserror,
mag,
abserror / mag as relerror
from v;
wds=> select * from v2;
╔══════════════╤═════════╤══════════════╤══════════════╤═══════════════════╗
║ a │ b │ abserror │ mag │ relerror ║
╟──────────────┼─────────┼──────────────┼──────────────┼───────────────────╢
║ 2 │ 3 │ 1 │ 3 │ 0.333333333333333 ║
║ -2 │ 3 │ 5 │ 3 │ 1.66666666666667 ║
║ 3.1415926536 │ 2.71828 │ 0.4233126536 │ 3.1415926536 │ 0.134744602587137 ║
╚══════════════╧═════════╧══════════════╧══════════════╧═══════════════════╝
(3 rows)
No idea whether this is more or less efficient than writing the whole
formula for each column.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment
Hello.
At Thu, 12 Sep 2019 23:16:01 +0200, "Peter J. Holzer" <hjp-pgsql@hjp.at> wrote in <20190912211601.GA3842@hjp.at>
> On 2019-09-12 15:35:56 -0500, Ron wrote:
> > On 9/12/19 2:23 PM, stan wrote:
> > > I am creating some views, that have columns with fairly complex calculations
> > > in them. The I want to do further calculations using the result of this
> > > calculation. Right now, I am just duplicating the first calculation in the
> > > select fro the 2nd calculated column. There must be a batter way to do
> > > this, right?
> >
> > I must be misunderstanding you, because you can just do more of what you're
> > doing now.
> >
> > Here's an example of doing some calculations in the creation of the view,
> > and then more calculations when selecting from the view:
>
> I think he wants to refer to other columns in the view.
..
> What you can do is nest views:
Doesn't subquery work?
SELECT x, y, z
FROM (SELECT f * 3 AS x, f AS y, g + 2 AS z
FROM (SELECT a + 3 AS f, b + c AS g
FROM t) AS t1
) AS t2;
t2 uses f in two columns, where f is calculated from t.a.
Or CTE (WITH clause) might look better.
WITH t1 AS (SELECT a + 3 AS f, b + c AS g FROM t),
t2 AS (SELECT f * 3 AS x, f AS y, g + 2 AS z FROM t1)
SELECT x, y, z FROM t2;
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On 2019-09-13 11:49:28 +0900, Kyotaro Horiguchi wrote:
> At Thu, 12 Sep 2019 23:16:01 +0200, "Peter J. Holzer" <hjp-pgsql@hjp.at> wrote in <20190912211601.GA3842@hjp.at>
> > On 2019-09-12 15:35:56 -0500, Ron wrote:
> > > On 9/12/19 2:23 PM, stan wrote:
> > > > I am creating some views, that have columns with fairly complex calculations
> > > > in them. The I want to do further calculations using the result of this
> > > > calculation. Right now, I am just duplicating the first calculation in the
> > > > select fro the 2nd calculated column. There must be a batter way to do
> > > > this, right?
[...]
> > I think he wants to refer to other columns in the view.
> ..
> > What you can do is nest views:
>
> Doesn't subquery work?
Yes, a subquery would work, too.
> Or CTE (WITH clause) might look better.
CTEs (which I quite like in general) are optimisation barriers, so using
them in a view may lead to inefficient plans (depending on what you do
with the view). So, yes, it would work, but you have to check the plans
of your queries (and be prepared to rewrite your view if they aren't
good enough), which is why I didn't suggest it.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>