[SQL] PostgreSQL array, recursion and more
MESH Data Tree:
example:
Hallux;A01.378.610.250.300.792.380
where:
A01 is Body Regions
A01.378 is Extremities
A01.378.610 is Lower Extremity
A01.378.610.250 is Foot
A01.378.610.250.300 is Forefoot, Human
A01.378.610.250.300.792 is Toes
CREATE OR REPLACE FUNCTION mesh_split(text) RETURNS text[]
AS $$
return [split('\.',$_[0])];
$$ LANGUAGE plperlu;
arancia=# select mesh_split('A01.378.610.250.300.792.380');
mesh_split
---
{A01,378,610,250,300,792,380}
(1 row)
/*
Is it a real array?
If it is, why can I not use index to access its items?
*/
arancia=# select mesh_split('A01.378.610.250.300.792.380')[1];
ERROR: syntax error at or near "["
LINE 1: select mesh_split('A01.378.610.250.300.792.380')[1];
^
/*
but it is an array, it behaves as it is.
*/
arancia=> select array_length(mesh_split('A01.378.610.250.300.792.380'),1);
array_length
--
7
(1 row)
/* How to get access to its items then?
*/
Another problem related:
arancia=> select * from meshtree where code = ANY
mesh_split('A01.378.610.250.300.792.380');
ERROR: syntax error at or near "mesh_split"
LINE 1: select * from meshtree where code = ANY mesh_split('A01.378
^
select * from meshtree,
unnest(mesh_split('A01.378.610.250.300.792.380')) as c where
c=meshtree.code;
parent | id | code |description
+---+--+---
10 |11 | 300 | Dehydroepiandrosterone Sulfate
33 |34 | 250 | Cymarine
48 |49 | 250 | Cymarine
61 |62 | 250 | Dihydrotachysterol
66 |68 | 300 | Calcitriol
65 |69 | 250 | Calcifediol
92 |93 | 380 | Glycodeoxycholic Acid
98 |99 | 250 | Finasteride
111 | 117 | 300 | Chenodeoxycholic Acid
145 | 146 | 300 | Dehydroepiandrosterone Sulfate
180 | 182 | 250 | Ethinyl Estradiol-Norgestrel Combination
190 | 191 | 250 | Desoximetasone
[..]
| 18638 | A01 | Body Regions
[..]
190 | 192 | 300 | Dexamethasone Isonicotinate
195 | 196 | 250 | Clobetasol
199 | 200 | 300 | Fluocinonide
206 | 207 | 250 | Diflucortolone
266 | 267 | 300 | Dexamethasone Isonicotinate
281 | 282 | 250 | Diflucortolone
290 | 293 | 250 | Dehydrocholesterols
305 | 306 | 250 | Dihydrotachysterol
312 | 314 | 300 | Calcitriol
311 | 315 | 250 | Calcifediol
320 | 321 | 250 | Cholestanol
328 | 330 | 300 | Calcitriol
[..]
52135 | 52136 | 250 | Eye Injuries
52136 | 52137 | 250 | Eye Burns
52149 | 52155 | 300 | Hematoma, Epidural, Cranial
52181 | 52196 | 300 | Gallbladder Emptying
52269 | 52277 | 300 | Caplan Syndrome
52360 | 52368 | 300 | Caplan Syndrome
52428 | 52442 | 380 | Hemothorax
52476 | 52491 | 610 | Pneumonia
52534 | 52535 | 380 | Legionnaires' Disease
(2204 rows)
I really want to write better similar query:
arancia=> with recursive t(id,parent,codeparts,idx,last,descriptions) as (
SELECT
id, parent, mesh_split('A01.378.610.250.300.792.380'), 1,
array_length(mesh_split('A01.378.610.250.300.792.380'),1),
ARRAY[description]
FROM meshtree WHERE code='A01'
UNION ALL
SELECT m.id, m.parent, t.codeparts, idx+1, last, descriptions ||
ARRAY[description]
FROM meshtree AS m JOIN t ON (t.id=m.parent)
WHERE idx<=last AND m.code=t.codeparts[idx+1])
SELECT t.* FROM t;
id | parent | codeparts | idx | last |
descriptions
---++---+-+--+...
18638 || {A01,378,610,250,300,792,380} | 1 |7 | {"Body
Regions"}
18675 | 18638 | {A01,378,610,250,300,792,380} | 2 |7 | {"Body
Regions",Extremities}
18676 | 18675 | {A01,378,610,250,300,792,380} | 3 |7 | {"Body
Regions",Extremities,"Lower Extremity"}
18679 | 18676 | {A01,378,610,250,300,792,380} | 4 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot}
18682 | 18679 | {A01,378,610,250,300,792,380} | 5 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human"}
18683 | 18682 | {A01,378,610,250,300,792,380} | 6 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes}
18684 | 18683 | {A01,378,610,250,300,792,380} | 7 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes,Hallux}
(7 rows)
explain analyze with recursive
t(id,parent,codeparts,idx,last,descriptions) as (
select
id,parent,mesh_split('A01.378.610.250.300.792.380'),1,array_length(mesh_split('A01.378.610.250.300.792.380'),1),ARRAY[description]
from meshtree where code='A01'
union all
select m.id,m.parent,t.codeparts,idx+1,last,descriptions ||
ARRAY[description] from meshtree as m join t on (t.id=m.parent) where
id
Re: [SQL] PostgreSQL array, recursion and more
Στις Wednesday 17 November 2010 15:22:34 ο/η Ferruccio Zamuner έγραψε:
>
> /* How to get access to its items then?
> */
select (mesh_split('A01.378.610.250.300.792.380')::text[])[1];
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] obtaining difference between minimum value and next in size
Hi, I was wondering if it is possible to do this with a single query rather
than iterate over all of the rows in an application:
I have a table which for brevity looks like:
create table offers {
integer id;
integer product_id;
double price;
}
where for each product there is a number of offers in this table. Now my
question:
Is it possible to obtain the difference between just the minimum price and the
next one up per product, so say I have the following data:
id, product_id, price
123, 2, 10.01
125, 2, 10.05
128, 2, 11.30
134, 3, 9.45
147, 3, 11.42
157, 3, 12.08
167, 3, 12.09
then I would like the following returned
product_id, difference
2, .04 (10.05-10.01)
3, 1.97 (11.42-9.45)
,etc
Any ideas?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Re: [SQL] obtaining difference between minimum value and next in size
"John Lister" writes: > Is it possible to obtain the difference between just the minimum price and > the next one up per product, If you're using >= 8.4, try a window function. LEAD or LAG ought to do it. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] obtaining difference between minimum value and next in size
John Lister wrote:
> Hi, I was wondering if it is possible to do this with a single query rather
> than iterate over all of the rows in an application:
>
> I have a table which for brevity looks like:
> create table offers {
> integer id;
> integer product_id;
> double price;
> }
>
> where for each product there is a number of offers in this table. Now my
> question:
> Is it possible to obtain the difference between just the minimum price and the
> next one up per product, so say I have the following data:
> id, product_id, price
> 123, 2, 10.01
> 125, 2, 10.05
> 128, 2, 11.30
> 134, 3, 9.45
> 147, 3, 11.42
> 157, 3, 12.08
> 167, 3, 12.09
>
> then I would like the following returned
> product_id, difference
> 2, .04 (10.05-10.01)
> 3, 1.97 (11.42-9.45)
>
> ,etc
>
>
> Any ideas?
Sure, as Tom Lane pointed out, with >= 8.4:
test=*# select * from offers ;
id | product_id | price
-++---
123 | 2 | 10.01
125 | 2 | 10.05
128 | 2 | 11.30
134 | 3 | 9.45
147 | 3 | 11.42
157 | 3 | 12.08
167 | 3 | 12.09
(7 Zeilen)
Zeit: 0,204 ms
test=*# select product_id, price, price - lag(price) over (partition by
product_id order by product_id, price), row_number() over (partition by
product_id)from offers;
product_id | price | ?column? | row_number
+---+--+
2 | 10.01 | | 1
2 | 10.05 | 0.04 | 2
2 | 11.30 | 1.25 | 3
3 | 9.45 | | 1
3 | 11.42 | 1.97 | 2
3 | 12.08 | 0.66 | 3
3 | 12.09 | 0.01 | 4
(7 Zeilen)
Zeit: 0,415 ms
test=*# select product_id, price, difference from (select product_id,
price, price - lag(price) over (partition by product_id order by
product_id, price) as difference, row_number() over (partition by
product_id) from offers) foo where row_number <= 2;
product_id | price | difference
+---+
2 | 10.01 |
2 | 10.05 | 0.04
3 | 9.45 |
3 | 11.42 | 1.97
(4 Zeilen)
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°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] obtaining difference between minimum value and next in size
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they
constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if
it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
- Original Message -
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size
Hi, I was wondering if it is possible to do this with a single query rather
than iterate over all of the rows in an application:
I have a table which for brevity looks like:
create table offers {
integer id;
integer product_id;
double price;
}
where for each product there is a number of offers in this table. Now my
question:
Is it possible to obtain the difference between just the minimum price and
the next one up per product, so say I have the following data:
id, product_id, price
123, 2, 10.01
125, 2, 10.05
128, 2, 11.30
134, 3, 9.45
147, 3, 11.42
157, 3, 12.08
167, 3, 12.09
then I would like the following returned
product_id, difference
2, .04 (10.05-10.01)
3, 1.97 (11.42-9.45)
,etc
Any ideas?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Re: [SQL] obtaining difference between minimum value and next in size
Cheers oliverios and tom for your speedy replies. Unfortunately using v8.3 so
the new functions are out. A big credit to oliverios for his sql fu, that seems
to do exactly what I want and I think I pretty much understand the query. I
always forget the comparison on the rows when thinking about groups.
John
- Original Message -
From: Oliveiros d'Azevedo Cristina
To: John Lister ; pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 4:09 PM
Subject: Re: [SQL] obtaining difference between minimum value and next in size
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they
constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if
it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
- Original Message -
From: John Lister
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size
Hi, I was wondering if it is possible to do this with a single query rather
than iterate over all of the rows in an application:
I have a table which for brevity looks like:
create table offers {
integer id;
integer product_id;
double price;
}
where for each product there is a number of offers in this table. Now my
question:
Is it possible to obtain the difference between just the minimum price and
the next one up per product, so say I have the following data:
id, product_id, price
123, 2, 10.01
125, 2, 10.05
128, 2, 11.30
134, 3, 9.45
147, 3, 11.42
157, 3, 12.08
167, 3, 12.09
then I would like the following returned
product_id, difference
2, .04 (10.05-10.01)
3, 1.97 (11.42-9.45)
,etc
Any ideas?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
