pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubselects to Joins? Or: how to design phone calls database
3+ messages / 3 participants
[nested] [flat]
* Subselects to Joins? Or: how to design phone calls database
@ 2011-12-10 22:24 Mario Splivalo <mario.splivalo@megafon.hr>
2011-12-10 22:51 ` Re: Subselects to Joins? Or: how to design phone calls database Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Splivalo @ 2011-12-10 22:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Subselects to Joins? Or: how to design phone calls database
2011-12-10 22:24 Subselects to Joins? Or: how to design phone calls database Mario Splivalo <mario.splivalo@megafon.hr>
@ 2011-12-10 22:51 ` Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Viktor Bojović @ 2011-12-10 22:51 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
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.
On Sat, Dec 10, 2011 at 11:24 PM, Mario Splivalo
<mario.splivalo@megafon.hr>wrote:
> 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
>
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Subselects to Joins? Or: how to design phone calls database
@ 2011-12-12 09:36 Misa Simic <misa.simic@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Misa Simic @ 2011-12-12 09:36 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; 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:
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-12-12 09:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-10 22:24 Subselects to Joins? Or: how to design phone calls database Mario Splivalo <mario.splivalo@megafon.hr>
2011-12-10 22:51 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-12-12 09:36 Re: Subselects to Joins? Or: how to design phone calls database Misa Simic <misa.simic@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox