Обсуждение: Overlapping Ranges- Query Alternative

Поиск
Список
Период
Сортировка

Overlapping Ranges- Query Alternative

От
"Ozer, Pam"
Дата:
<div class="WordSection1"><p class="MsoNormal">I have the following problem:<p class="MsoNormal"> <p
class="MsoNormal">Createtemp table ranges (Groups int, ColumnA int);<p class="MsoNormal">Insert into ranges<p
class="MsoNormal">Values(2,45);<pclass="MsoNormal"> <p class="MsoNormal">Select Groups, <p class="MsoNormal">Case when
ColumnAbetween 0 and 19 then 0<p class="MsoNormal">     when ColumnA >=20 then 20<p class="MsoNormal">     when
ColumnA>=30 then 30<p class="MsoNormal">     when ColumnA>=40 then 40<p class="MsoNormal">     when
ColumnA>=50then 50 end MinRange<p class="MsoNormal">from ranges<p class="MsoNormal"> <p class="MsoNormal">Results:
<pclass="MsoNormal">Groups minrange<p class="MsoNormal">2;20<p class="MsoNormal"> <p class="MsoNormal">What I want Is :
Onecolumn can fall into multiple ranges.  For example 45 >20, 30, and 40 so I want the following results<p
class="MsoNormal">2;20<pclass="MsoNormal">2;30<p class="MsoNormal">2;40<p class="MsoNormal"> <p class="MsoNormal">I
knowI could do a union with each range but is there any way to bring back all ranges in one query?  I need to bring
backthe values in one column so having separate columns for each range is not an option.<p class="MsoNormal"> <p
class="MsoNormal">Thankyou in advance for any help<p class="MsoNormal"> <div id="content"><p class="MsoNormal"><b><span
style="font-size:11.5pt;font-family:"Arial","sans-serif";
color:black">Pam Ozer</span></b></div><p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""><br/><br /></span></div>

Re: Overlapping Ranges- Query Alternative

От
Andreas Gaab
Дата:

Hi,

 

the following works:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

     when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

     when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

     when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

 

 

--or even only

 

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

 

 

Best, Andreas

 

Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

 

I have the following problem:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 then 20

     when ColumnA >=30 then 30

     when ColumnA>=40 then 40

     when ColumnA>=50 then 50 end MinRange

from ranges

 

Results:

Groups minrange

2;20

 

What I want Is : One column can fall into multiple ranges.  For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

 

I know I could do a union with each range but is there any way to bring back all ranges in one query?  I need to bring back the values in one column so having separate columns for each range is not an option.

 

Thank you in advance for any help

 

Pam Ozer

 

Re: Overlapping Ranges- Query Alternative

От
"Marc Mamin"
Дата:

or:

 

 

Select Groups, generate_series

FROM

ranges JOIN generate_series(10,50,10)  on ( ColumnA < generate_series)

ORDER by  Groups , generate_series

;

 

regards,

 

Marc Mamin

 

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Freitag, 12. November 2010 09:23
To: 'Ozer, Pam'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Overlapping Ranges- Query Alternative

 

Hi,

 

the following works:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

     when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

     when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

     when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

 

 

--or even only

 

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

 

 

Best, Andreas

 

Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

 

I have the following problem:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 then 20

     when ColumnA >=30 then 30

     when ColumnA>=40 then 40

     when ColumnA>=50 then 50 end MinRange

from ranges

 

Results:

Groups minrange

2;20

 

What I want Is : One column can fall into multiple ranges.  For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

 

I know I could do a union with each range but is there any way to bring back all ranges in one query?  I need to bring back the values in one column so having separate columns for each range is not an option.

 

Thank you in advance for any help

 

Pam Ozer

 

Re: Overlapping Ranges- Query Alternative

От
"Ozer, Pam"
Дата:

Thank you all for your suggestions. I will try each of these and see which one fits my situation best.

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Friday, November 12, 2010 12:23 AM
To: Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Overlapping Ranges- Query Alternative

 

Hi,

 

the following works:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

     when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

     when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

     when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

 

 

--or even only

 

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

 

 

Best, Andreas

 

Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

 

I have the following problem:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

 

Select Groups,

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 then 20

     when ColumnA >=30 then 30

     when ColumnA>=40 then 40

     when ColumnA>=50 then 50 end MinRange

from ranges

 

Results:

Groups minrange

2;20

 

What I want Is : One column can fall into multiple ranges.  For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

 

I know I could do a union with each range but is there any way to bring back all ranges in one query?  I need to bring back the values in one column so having separate columns for each range is not an option.

 

Thank you in advance for any help

 

Pam Ozer

 

force view column varchar(32) to varchar(128)

От
Emi Lu
Дата:
Hello,

Is there a way to force the view column change from varhcar(32) to 
varchar(128)?

Example:
===================
v1 (id varchar(32) ... )

There are more than 1000 other views depend on v1.

Instead of recreating all other 1000 views, is there a way postgresql 
8.3 can do/accept:

create or replace v1 AS

select id::varchar(128), ......

Thanks a lot!
--
Lu Ying

Re: force view column varchar(32) to varchar(128)

От
Andreas Kretschmer
Дата:
Emi Lu <emilu@encs.concordia.ca> wrote:

> Hello,
>
> Is there a way to force the view column change from varhcar(32) to  
> varchar(128)?

No, you have to recreate the view ...


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: force view column varchar(32) to varchar(128)

От
Richard Broersma
Дата:
On Fri, Nov 19, 2010 at 7:02 AM, Emi Lu <emilu@encs.concordia.ca> wrote:

> Is there a way to force the view column change from varhcar(32) to
> varchar(128)?

> Instead of recreating all other 1000 views, is there a way postgresql 8.3
> can do/accept:

Ouch!  That stinks. I feel your pain (well my pain came from just
changing about 15-20 views.  changing 1000 views would make me
cringe).  Its too bad you haven't or can't use a dimensionless varchar
for your id column (I assume this is your primary key).  This would
eliminate the need to re-dimension your varchar columns.

As I see it, you don't have an option.  PgAdmin can be a great help
here.  Open the create script and make the changes and save both.

The only other Option that I can see would be to dump the table and
view defintions. Modify these definitions with sed.  Apply the new
schema, and lastly, use an ETL software to push your data to the new
schema.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug