0

I am trying to create a view based on a specific case. Following is my table:

Table Name: Calls

CustomerName | CallID | Timestamp | Quality | PacketLoss | Jitter

The goal is to create a view that can do the following:

  1. SELECT * FROM [VIEW NAME] WHERE TIMESTAMP BETWEEN {Start Time} AND {End Time}
  2. At this point the database need to go through the table and find top 5 CustomerNames with the most amount of rows where the Quality was marked as "Bad".
  3. The database will use the return as a filter and send me back all the rows that had one of the CustomerName's listed in top 5.

I tried this:

CREATE VIEW "Calls_View" 
AS (
SELECT * 
FROM "Calls" 
WHERE "CustomerName" IN (SELECT "CustomerName" 
                         FROM "Calls" 
                         WHERE "Quality"='Bad' 
                         GROUP BY "CustomerName" 
                         ORDER BY COUNT(*) DESC LIMIT 5) 
  AND "Quality"='Bad');

Is my logic correct?

  • Given your description this looks OK to me. However, you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run than they are worth it – a_horse_with_no_name Jun 7 '19 at 5:59
  • This will find the customers with the worst customer experiences of all time, and return their calls within a given time frame. If you want the worst experience only within the given time frame, that query won't work. For that you would need a set-returning function. – jjanes Jun 7 '19 at 11:01
  • thanks you for the comments folks. @jjanes, what you described is exactly what I am looking for. I would like to see the worst experiences only within the given time frame. Is there a sample query that you can share with me? I just don't know how I can apply the same time constraints to all the SELECT statements that I have in my query. Thanks – Arda Savran Jun 7 '19 at 13:18
0

You can't do what you want with a view. You have to use a function. Note that I didn't retain all your double quoting of identifiers, so you will have to put them back if you insist.

CREATE OR REPLACE FUNCTION public.calls_view(timestamp with time zone, timestamp with time zone)
 RETURNS SETOF calls
 LANGUAGE sql
AS $function$
SELECT *
FROM Calls
WHERE CustomerName IN (SELECT CustomerName
                         FROM Calls
                         WHERE Quality='Bad' and timestamp between $1 and $2
                         GROUP BY CustomerName
                         ORDER BY COUNT(*) DESC LIMIT 5)
  AND Quality='Bad' and timestamp between $1 and $2 
$function$

You can use it kind of like a view, but you must supply your parameters in parentheses, not a WHERE clause:

select * from calls_view(now() - interval '1 day', now());
| improve this answer | |
  • Thank you. I will give it a shot – Arda Savran Jun 7 '19 at 14:00
  • I still cannot figure how I can interact with it using the BI tool I have remotely. The BI tool I have is time-based. It can only send queries with a single where clause for the timestamp column. That's why I wanted to wrap something up on the database side so the only thing BI needs to do SELECT * FROM XXX WHERE Timestamp between XXX AND YYY – Arda Savran Jun 7 '19 at 15:30
  • Sorry, I don't know how to help that. Seems like your BI tool is just not fit for use. Perhaps you can select the raw table by time slice, then get the BI tool to do the aggregation and subselect for you, since it won't let PostgreSQL do it. – jjanes Jun 7 '19 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.