[GENERAL] Group by on %like%
Jennifer Trey
I would like to run a query and group several rows based on a phone number.
However, the same phone number might have a prefix on occasion, example :
name | phone_number
----------------------
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890
as you can see, the first 2 James seems to belong together.
running
select name, phone_number from relation group by name, phone_number
would not reflect this.
I don't think there is a way to run something similar to this :
select name, phone_number from relation group by name, %phone_number%
// or similar
However, I believe there is a way, so I would like to here it from you :)
Functions, sums .. please let me know..
Thank you in advance / Jennifer
--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Serge Fonville
Serge Fonville
Since I do not believe we should condone this.
This is what I got when I sent a message to the list.
At least, I do not like these personally.
If I was wrong with this, then I apologise up front.
If I need to send these kinds of remarks elsewhere, please provide me
with the correct information
Regards,
Serge Fonville
On Fri, Jul 3, 2009 at 11:52 AM, <ferns...@hotmail.com> wrote:
> Dear Friends:
>
> We are a large wholesaler who mainly sell electrical products
> such as laptop,TV,digital camera, mobile, Digital Video, Mp4, GPS, and so
> on. And our official web is fcxqrz.com We offer you the products with the
> best quality and price .All the items on our website are brand new in sealed
> factory box and offered warranty by the original manufactures .
>
>
>
> Email: fcxq...@188.com
>
> MSN : fcx...@hotmail.com
Guy Flaherty
Hi,
I would like to run a query and group several rows based on a phone number.
However, the same phone number might have a prefix on occasion, example :
name | phone_number
----------------------
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890
as you can see, the first 2 James seems to belong together.
running
select name, phone_number from relation group by name, phone_number
would not reflect this.
I don't think there is a way to run something similar to this :
select name, phone_number from relation group by name, %phone_number%
// or similar
However, I believe there is a way, so I would like to here it from you :)
Functions, sums .. please let me know..
Thank you in advance / Jennifer
You could run a sub-select first to get your results and then group on that, such as:
SELECT name, pn
FROM
(SELECT name, substring(phone_number from length(phone_number)-7) AS pn
FROM relation
WHERE phone_number LIKE '%1234%') AS r
GROUP BY name,pn
The substring bit is the part you will have to work out in order to make sure you get the correct rows returning you are looking for. This is just an example :)
Regards,
GF
Guy Flaherty
Blah, having said that, you are probably looking for something more like this:
SELECT "name", substring(phone_number from length(phone_number)-7) AS pn
FROM relation
GROUP BY name,2
GF
Dimitri Fontaine
Le 3 juil. 09 à 11:44, Jennifer Trey a écrit :
> I would like to run a query and group several rows based on a phone
> number.
> However, the same phone number might have a prefix on occasion,
> example :
>
> name | phone_number
> ----------------------
> james | 123456
> james | 00441234556
> as you can see, the first 2 James seems to belong together.
What I would do is provide a normalize_phone_number(phone_number
text), such as it returns the same phone number when given a number
with or without international prefix.
Then you
SELECT name, normalize_phone_number(phone_numer)
FROM relation
GROUP BY 1, 2;
Now you're left with deciding if you prefer to normalize with the
prefix or with it stripped, and to invent an automated way to detect
international prefixes. The so called prefix project might help you do
this if you have a table of known prefixes to strip (or recognize):
http://prefix.projects.postgresql.org/
http://prefix.projects.postgresql.org/prefix-1.0~rc1.tar.gz
CREATE OR REPLACE FUNCTION normalize_phone_number(text)
RETURNS text
LANGUAGE PLpgSQL
STABLE
AS $f$
DECLARE
v_prefix text;
BEGIN
SELECT prefix
INTO v_prefix
FROM international_prefixes
WHERE prefix @> $1;
IF FOUND
THEN
-- we strip the prefix to normalize the phone number
RETURN substring($1 from length(v_prefix));
ELSE
RETURN $1;
END IF;
END;
$f$;
Note: I typed the function definition directly into the Mail composer,
bugs are yours :)
Regards,
--
dim
nha
Le 3/07/09 12:53, Dimitri Fontaine a �crit :
> Hi,
>
> Le 3 juil. 09 � 11:44, Jennifer Trey a �crit :
>> I would like to run a query and group several rows based on a phone
>> number.
>> However, the same phone number might have a prefix on occasion, example :
>>
>> name | phone_number
>> ----------------------
>> james | 123456
>> james | 00441234556
>> as you can see, the first 2 James seems to belong together.
>
> What I would do is provide a normalize_phone_number(phone_number text),
> such as it returns the same phone number when given a number with or
> without international prefix.
>
> Then you
> SELECT name, normalize_phone_number(phone_numer)
> FROM relation
> GROUP BY 1, 2;
> [...]
The solution suggested by Dimitri Fontaine and based on a customized
function for normalizing phone numbers seems to be a clean one. All the
power is contained in the normalize_phone_number() implementation.
The following query may be an alternative solution that does not require
any tier function except the classic aggregative ones (COUNT(), SUM()):
SELECT P3.name, P3.phone_number
FROM (
SELECT P1.name, P1.phone_number, (
CASE WHEN CHAR_LENGTH(P1.phone_number) >= CHAR_LENGTH(P2.phone_number)
THEN 1
ELSE 0
END
) AS gec
FROM (
SELECT P01.name, P01.phone_number
FROM pnd AS P01
GROUP BY P01.name, P01.phone_number
) AS P1 INNER JOIN (
SELECT P02.name, P02.phone_number
FROM pnd AS P02
GROUP BY P02.name, P02.phone_number
) AS P2
ON P1.name = P2.name
AND (
CASE WHEN CHAR_LENGTH(P1.phone_number) >= CHAR_LENGTH(P2.phone_number)
THEN P1.phone_number LIKE ('%'||P2.phone_number)
ELSE P2.phone_number LIKE ('%'||P1.phone_number)
END
)
) AS P3
GROUP BY P3.name, P3.phone_number
HAVING COUNT(*) = SUM(P3.gec)
"pnd" is assumed to be the main table including "name" and
"phone_number" columns. "pnd" is directly used as a table source in
subqueries aliased P1 and P2 and only for those subqueries.
Assuming the starting values in the table "pnd" as following:
name | phone_number
----------------------
james | 123456
james | 0044123456
james | 555666
sarah | 567890
sarah | 567890
(notice that the phone_number of the 2nd row has been adjusted for
similarity to be effective between row 1 and row 2)
The resulting rows from the overall query will be:
name | phone_number
----------------------
james | 0044123456
james | 555666
sarah | 567890
The choice has been made here to keep the longuest phone_number for each
set of similar phone_numbers. The shortest could also be kept if desired.
The overall query implies a few subqueries. Subquery aliased P3 is a
join between P1 and P2, both corresponding to the same subquery. The
difference is in expressing the join conditions: i) on the commun column
"name"; and ii) on the likelihood between phone numbers according to the
length of these latter. Function CHAR_LENGTH() is used instead of
LENGTH() because the first renders the real number of characters whereas
the second gives the number of bytes used to encode the argument.
Table P3 is composed of couples (X, Y) of "name" and "phone_number".
Each couple is associated to the number "gec" resulting from the
counting of phone_numbers Z similar to Y and with CHAR_LENGTH(Y) greater
or equal to CHAR_LENGTH(Z).
Eventually only the rows of P3 for which the sum of "gec" is equal to
the number of rows of P3 where the value of "phone_number" is the same
are kept.
Hoping this alternative solution will help a little (validated with
PostgreSQL 8.3.1).
Regards.
P-S: I think this question might also have interested the PgSQL-SQL
mailing list and posted there.
--
nha / Lyon / France.