1

How can I execute a telecoms least cost routing query in PostgreSQL? The purpose is generate a result set with ordered by the lowest price for the carriers. The table structure is below

SQL Fiddle

CREATE TABLE tariffs (
    trf_tariff_id integer,
    trf_carrier_id integer,
    trf_prefix character varying,
    trf_destination character varying,
    trf_price numeric(15,6),
    trf_connect_charge numeric(15,6),
    trf_billing_interval integer,
    trf_minimum_interval integer
);

For instance to check the cost for a call if passed through a particular carrier carrier_id the query is:

SELECT trf_price, trf_prefix as lmp FROM tariffs WHERE SUBSTRING(dialled_number,1, LENGTH(trf_prefix)) = trf_prefix and trf_carrier_id = carrier_id  ORDER BY trf_prefix DESC limit 1

For the cost of the call for each carrier ie the least cost query the query is:

-- select * from tariffs
select distinct banana2.longest_prefix, banana2.trf_carrier_id_2, apple2.trf_carrier_id, apple2.lenprefix, apple2.trf_price, apple2.trf_destination from 
(select banana.longest_prefix, banana.trf_carrier_id_2 from (select  max(length(trf_prefix)) as longest_prefix, trf_carrier_id as trf_carrier_id_2 from  (select *, length(trf_prefix) as lenprefix from tariffs where substring('35567234567', 1, length(trf_prefix) )= trf_prefix) as apple group by apple.trf_carrier_id) as banana) as banana2,

(select *, length(trf_prefix) as lenprefix from tariffs where substring('35567234567', 1, length(trf_prefix) )= trf_prefix) as apple2 -- group by apple2.trf_carrier_id where banana2.trf_carrier_id_2=apple2.trf_carrier_id and banana2.longest_prefix=apple2.lenprefix order by trf_price

The query works on the basis that for each carrier the longest matching prefix for a dialled number is unique and it will be the longest. So a join involving the longest prefix and carrier on the selection gives the set for all the carriers.

I one problem with my query:

I don't want to do the apple(X) query twice

(select *, length(trf_prefix) as lenprefix from tariffs where substring('35567234567', 1, length(trf_prefix) )= trf_prefix) as apple

There must be a more elegant way, probably declaring it once and using it twice.

What I want to do is run the query on the single carrier for each carrier:

SELECT trf_price, trf_prefix as lmp FROM tariffs WHERE SUBSTRING(dialled_number,1, LENGTH(trf_prefix)) = trf_prefix and trf_carrier_id = carrier_id  ORDER BY trf_prefix DESC limit 1

and combine them into one set which will be sorted by price.

In fact I want to generalize the method for any such query where the output for the various values for a particular column or set of columns are combined into one set for further querying. I am told that CTEs are the way to accomplish that kind of query but I find the docs rather confusing. It is much easier with your own use cases.

PS. I am aware that the prefix length can be precomputed and stored.

Improve this question
5
  • What did you try to do here: SUBSTRING(prefix,1, LENGTH(prefix))? And it seems that if you just drop the part about the carrier_id in the WHERE you're gonna have your answer. Commented Jun 14, 2015 at 16:25
  • @JakubKania There was an error in the query It should be SELECT cost, prefix as lmp FROM carrier_prices WHERE SUBSTRING(dialled_number,1, LENGTH(prefix)) = prefix and carrier_id = :carrier_id ORDER BY prefix DESC limit 1. dialled_number is the value in the SUBSTRING function Commented Jun 15, 2015 at 0:39
  • Sample data please - sqlfiddle.com if possible. Commented Jun 15, 2015 at 0:48
  • common table expressions? postgresql.org/docs/9.4/static/queries-with.html Commented Jun 17, 2015 at 15:45
  • 1
    It's probably not the answer to your question but if you want to match phone numbers you might want to use the Prefix Extension (github.com/dimitri/prefix) then you can write simpler queries like "WHERE nr @> '35567234567'" to get all entries with "3", "355", "35567". Commented Jun 17, 2015 at 15:51

1 Answer 1

Reset to default
1

Common Table Expressions:

with apple as (
 select *, length(trf_prefix) as lenprefix
   from tariffs
  where substring('35567234567', 1, length(trf_prefix)) = trf_prefix
)
select distinct banana2.longest_prefix, banana2.trf_carrier_id_2,
       apple.trf_carrier_id, apple.lenprefix, apple.trf_price,
       apple.trf_destination
from (select banana.longest_prefix, banana.trf_carrier_id_2
        from (select max(length(trf_prefix)) as longest_prefix,
                     trf_carrier_id as trf_carrier_id_2
                from apple
              group by apple.trf_carrier_id) as banana) as banana2,
     apple
where banana2.trf_carrier_id_2 = apple.trf_carrier_id
  and banana2.longest_prefix = apple.lenprefix
order by trf_price

You can just pull out the repeated table definition. Even if I'm just using one of those sub-select-in-a-from things a single time, I still use CTEs. I find the style you're using basically unreadable.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I am having a hard time formatting the queries for the SO edit box. Some of the queries don't wrap around as I expect. The reason I asked the question was to be able to use this query for a single carrier SELECT trf_price, trf_prefix as lmp FROM tariffs WHERE SUBSTRING(dialled_number,1, LENGTH(trf_prefix)) = trf_prefix and trf_carrier_id = carrier_id ORDER BY trf_prefix DESC limit 1, combine them for all the carriers and run the price sort on it, as it is more readable. Any thoughts there?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.