Обсуждение: 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

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.