Thread: Group by on %like%
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
What is the output you are trying to achieve? > 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-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Could someone look into this? 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, <fernstudios@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: fcxqrz01@188.com > > MSN : fcxqrz@hotmail.com
On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:
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
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
On Fri, Jul 3, 2009 at 8:32 PM, Guy Flaherty <naoshika@gmail.com> wrote:
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
On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote: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
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
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; 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
Hello, 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.
Sorry for taking so long to respond.
The prefix thingy is definetly attractive for future development and I had already discovered them to be a challenge. However, i have noticed all kinds of ways people tend to write their number, including omitting the + or 00 .. so at this time, for this task, I found Guys to be working pretty well.
Thanks all / Jen