How can I execute a least cost routing query in postgresql, without temporary tables? -


how can execute telecoms least cost routing query in postgresql? purpose generate result set ordered lowest price carriers. table structure 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 check cost call if passed through particular carrier carrier_id query is:

select trf_price, trf_prefix lmp tariffs substring(dialled_number,1, length(trf_prefix)) = trf_prefix , trf_carrier_id = carrier_id  order trf_prefix desc limit 1

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

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

the query works on basis each carrier longest matching prefix dialled number unique , longest. join involving longest prefix , carrier on selection gives set carriers.

i 1 problem query:

i don't want apple(x) query twice

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

there must more elegant way, declaring once , using twice.

what want run query on single carrier each carrier:

select trf_price, trf_prefix lmp tariffs substring(dialled_number,1, length(trf_prefix)) = trf_prefix , trf_carrier_id = carrier_id  order trf_prefix desc limit 1

and combine them 1 set sorted price.

in fact want generalize method such query output various values particular column or set of columns combined 1 set further querying. told ctes way accomplish kind of query find docs rather confusing. easier own use cases.

ps. aware prefix length can precomputed , stored.

common table expressions:

with apple (  select *, length(trf_prefix) lenprefix    tariffs   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 (select banana.longest_prefix, banana.trf_carrier_id_2         (select max(length(trf_prefix)) longest_prefix,                      trf_carrier_id trf_carrier_id_2                 apple               group apple.trf_carrier_id) banana) banana2,      apple banana2.trf_carrier_id_2 = apple.trf_carrier_id   , banana2.longest_prefix = apple.lenprefix order trf_price

you can pull out repeated table definition. if i'm using 1 of sub-select-in-a-from things single time, still use ctes. find style you're using unreadable.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more