Обсуждение: Results per letter query
Hi! I'm trying to build a query to get if there is an occurrence for a field for each alphabetical letter. My first thought to know it was to do something like: SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'A%' ) LIMIT 1; SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'B%' ) LIMIT 1; SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'C%' ) LIMIT 1; ... and so on... Is there any way to do it in only one query?? Thank you in advance!
am Thu, dem 21.06.2007, um 11:10:02 +0200 mailte Dani Castaños folgendes: > Hi! > > I'm trying to build a query to get if there is an occurrence for a field > for each alphabetical letter. > My first thought to know it was to do something like: > > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'A%' ) > LIMIT 1; > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'B%' ) > LIMIT 1; > SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'C%' ) > LIMIT 1; > ... > and so on... > > Is there any way to do it in only one query?? I'm not sure if i understand you correctly, sorry, if not. test=*# select * from w; t --------testfoobarfoobar (4 rows) test=*# select chr(x), count(1) from generate_series(65,90) x, w where upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;chr | count -----+-------T | 1B | 1F | 2 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>> Hi! >> >> I'm trying to build a query to get if there is an occurrence for a field >> for each alphabetical letter. >> My first thought to know it was to do something like: >> >> SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'A%' ) >> LIMIT 1; >> SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'B%' ) >> LIMIT 1; >> SELECT COUNT(field) FROM table WHERE UPPER( field ) LIKE UPPER( 'C%' ) >> LIMIT 1; >> ... >> and so on... >> >> Is there any way to do it in only one query?? >> > > I'm not sure if i understand you correctly, sorry, if not. > > test=*# select * from w; > t > -------- > test > foo > bar > foobar > (4 rows) > > test=*# select chr(x), count(1) from generate_series(65,90) x, w where > upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1; > chr | count > -----+------- > T | 1 > B | 1 > F | 2 > (3 rows) > > > > Andreas > It's exactly what i want. Just one more thing... What if i want also the ones that begin by a non-alphabetical character. In your example: test=*# select * from w; t --------testfoobarfoobar1foobar/ertw@weras and have: chr | count -----+-------T | 1B | 1F | 2_ | 3 (4 rows)
am Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes: > >test=*# select chr(x), count(1) from generate_series(65,90) x, w where > >upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1; > > chr | count > >-----+------- > > T | 1 > > B | 1 > > F | 2 > >(3 rows) > > > > > > > >Andreas > > > It's exactly what i want. Just one more thing... What if i want also the > ones that begin by a non-alphabetical character. > In your example: change the generate_series(65,90) to generate_series(32,90) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> am Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes: > >>> test=*# select chr(x), count(1) from generate_series(65,90) x, w where >>> upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1; >>> chr | count >>> -----+------- >>> T | 1 >>> B | 1 >>> F | 2 >>> (3 rows) >>> >>> >>> >>> Andreas >>> >>> >> It's exactly what i want. Just one more thing... What if i want also the >> ones that begin by a non-alphabetical character. >> In your example: >> > > change the generate_series(65,90) to generate_series(32,90) > > > Andreas > With only changing 65 to 32: ERROR: invalid regular expression: parentheses () not balanced I think, it could be a problem with UPPER and non alphabetical chars -- *Dani Castaños Sánchez* dcastanos@androme.es <mailto:dcastanos@androme.es> ANDROME Iberica http://www.androme.es Constança, 5, 08029 Barcelona Tel: +34 934948850 Fax: +34 934196094
am Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes: > >change the generate_series(65,90) to generate_series(32,90) > > > > > >Andreas > > > With only changing 65 to 32: > > ERROR: invalid regular expression: parentheses () not balanced > > I think, it could be a problem with UPPER and non alphabetical chars No, the ~ - operator (Regex), try this: select chr(x), count(1) from generate_series(32,90) x, w where upper(substring (w.t from 1 for 1)) = chr(x) group by 1; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer escribió:
> am Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
>
>>> change the generate_series(65,90) to generate_series(32,90)
>>>
>>>
>>> Andreas
>>>
>>>
>> With only changing 65 to 32:
>>
>> ERROR: invalid regular expression: parentheses () not balanced
>>
>> I think, it could be a problem with UPPER and non alphabetical chars
>>
>
> No, the ~ - operator (Regex), try this:
>
> select chr(x), count(1) from generate_series(32,90) x, w where upper(substring (w.t from 1 for 1)) = chr(x) group by
1;
>
>
> Andreas
>
Thanks Andreas!
I thik i've found a better solution for my problem:
I got another solution, but I've used EXPLAIN ANALYZE, and yours is better:
EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x,
sip_customer_services where upper(substring
(sip_customer_services.service_name from 1 for 1)) = chr(x) group by 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------HashAggregate
(cost=37.00..39.25 rows=150 width=4) (actual
time=1.652..1.716 rows=13 loops=1) -> Hash Join (cost=9.38..36.25 rows=150 width=4) (actual
time=0.979..1.490 rows=18 loops=1) Hash Cond: (chr("outer".x) =
upper("substring"(("inner".service_name)::text, 1, 1))) -> Function Scan on generate_series x
(cost=0.00..12.50
rows=1000 width=4) (actual time=0.114..0.332 rows=59 loops=1) -> Hash (cost=9.30..9.30 rows=30 width=10)
(actual
time=0.647..0.647 rows=18 loops=1) -> Seq Scan on sip_customer_services (cost=0.00..9.30
rows=30 width=10) (actual time=0.295..0.442 rows=18 loops=1)Total runtime: 2.147 ms
(7 rows)
test=# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1,
1)) from sip_customer_services group by upper(substr(service_name, 1,1));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------HashAggregate
(cost=9.60..10.13 rows=30 width=10) (actual
time=0.704..0.766 rows=13 loops=1) -> Seq Scan on sip_customer_services (cost=0.00..9.45 rows=30
width=10) (actual time=0.332..0.530 rows=18 loops=1)Total runtime: 1.065 ms
(3 rows);
Thank you very much, anyway!