Re: Approach to Data Summary and Analysis

Things like this. AVG ring time before answer, average connected call
duration. % of calls never answered. % of calls that are answered that are
connected. Number of times John has answered a call versus how many times
we've called him.That sort of stuff.

On Mon, Apr 14, 2014 at 3:34 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> On 04/14/2014 04:22 PM, Robert DiFalco wrote:
>
> But then I lose a bunch of data like the TIMESTAMPTZ of the call, answer,
> connection, etc. Btw, currently these tables never need to be UPDATEd. They
> are immutable in the current design. And in the end I'm not sure how the
> proposal of one table and a state that is updatable changes the basic
> thrust of the question. For example, getting last call, last answered,
> total called, total answered. If the state of a call transitions from
> called to answered then making it a field loses all the data with the
> previous state, make sense?
>
>
> On Mon, Apr 14, 2014 at 2:43 PM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>wrote:
>
>>
>>
>> On Mon, 14 Apr 2014 09:27:29 -0700
>> Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
>>
>> > I have several related tables that represent a call state.
>> >
>> > And so on for calls_connected, calls_completed, call_errors, etc.
>> >
>> > So for my question -- is the choice between these a personal preference
>> > sort of thing or is there a right or wrong approach? Am I missing
>> another
>> > approach that would be better?
>>
>> Hi Robert,
>>
>> I guess a call state is subject to change, in which case you would have
>> to shuffle records between tables when that happens?
>>
>> ISTM you should consider using only a 'calls' table, and add an
>> 'id_call_state' field to it that references the list of possible states.
>> This would make your queries simpler.
>>
>> create table call_state(
>> id_call_state text PRIMARY KEY,
>> libelle text);
>>
>> INSERT INTO call_state (id_call_state, libelle) VALUES
>> ('calls_connected', 'Connected'), ('calls_completed', 'Completed'),
>> ('call_errors', 'Error');
>>
>> > CREATE TABLE calls (
>> > id BIGINT NOT NULL, // sequence generator
>>
>> id_call_state INTEGER NOT NULL REFERENCES call_state,
>>
>> > user_id BIGINT NOT NULL,
>> > called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> >
>> > PRIMARY KEY (id),
>> > FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
>> > );
>>
>>
>> --
>>
>> Salutations, Vincent Veyron
>>
>> http://marica.fr
>> Gestion des contentieux juridiques, des contrats et des sinistres
>> d'assurance
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> I wonder if you really need to place the parts of the call into the
> various tables. ringtime, answertime, closetime and all the values
> associated with those parts of a call are all fundamental to a single call,
> though perhaps collected incrementally. Easy queries, for sure. (Sorry, I
> haven't gone back to see your orig. schema. If it's clear there why these
> are in separate files, say no more)
>
>
>