Обсуждение: Subselects to Joins? Or: how to design phone calls database

I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:

CREATE TABLE cdr (call_id serial,phone_number text
);

And I have a table with country call prefixes, that looks like this:

CREATE TABLE prefixes (prefix text,country text
);

And now some test data:

INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');


Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:

SELECTcall_id,phone_number,(SELECT    countryFROM    prefixesWHERE        calls.phone_number LIKE prefix || '%'ORDER BY
  length(prefix) DESC LIMIT 1) AS country
 
FROM calls;


Is there a way I could use join here? I can do something like:

SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'

but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).

Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?
Mario

Hi Mario,
over - partition by will help. Iy that syntax is uncomfortable you can use multiple joins, using maximum length or max(prefix::int) but that will slow down the process. select over - partition by is fastest solution i think.






--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me

I think its definitely better to split phone number in calls table on 2
or even 3 parts... (Country prefix, carrier/area prefix, number)

Though maybe better design would be 3th table with full number as pk:
PhoneNumbers (number, country prefix, optionally carrier/area prefix,
rest of number)

Then you can join calls to phonenumbers on full number string then join
countries on country prefix...

Kind Regards,

Misa

Sent from my Windows Phone From: Mario Splivalo
Sent: 10 December 2011 23:27
To: pgsql-sql@postgresql.org
Subject: [SQL] Subselects to Joins? Or: how to design phone calls
database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:

CREATE TABLE cdr (
    call_id serial,
    phone_number text
);

And I have a table with country call prefixes, that looks like this:

CREATE TABLE prefixes (
    prefix text,
    country text
);

And now some test data:

INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');


Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:

SELECT
    call_id,
    phone_number,
    (SELECT
        country
    FROM
        prefixes
    WHERE
        calls.phone_number LIKE prefix || '%'
    ORDER BY
        length(prefix) DESC LIMIT 1
    ) AS country
FROM calls;


Is there a way I could use join here? I can do something like:

SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'

but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).

Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?

    Mario

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

I think its definitely better to split phone number in calls table on 2
or even 3 parts... (Country prefix, carrier/area prefix, number)

Though maybe better design would be 3th table with full number as pk:
PhoneNumbers (number, country prefix, optionally carrier/area prefix,
rest of number)

Then you can join calls to phonenumbers on full number string then join
countries on country prefix...

Kind Regards,

Misa

Sent from my Windows Phone From: Mario Splivalo
Sent: 10 December 2011 23:27
To: pgsql-sql@postgresql.org
Subject: [SQL] Subselects to Joins? Or: how to design phone calls
database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:

CREATE TABLE cdr (call_id serial,phone_number text
);

And I have a table with country call prefixes, that looks like this:

CREATE TABLE prefixes (prefix text,country text
);

And now some test data:

INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');


Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:

SELECTcall_id,phone_number,(SELECT    countryFROM    prefixesWHERE        calls.phone_number LIKE prefix || '%'ORDER BY
  length(prefix) DESC LIMIT 1) AS country
 
FROM calls;


Is there a way I could use join here? I can do something like:

SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'

but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).

Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?
Mario

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql