Question 89563 in PostgreSQL Question posted in
The official documentation can be found here.

Select multiple columns from a table but group by one which is always unique – Postgresql

In the UI I have a table where I’m showing data grouped by shipday which is days of the week. To do that in the hasura I’ve created a PostgreSQL view –

CREATE
OR REPLACE VIEW "public"."view_shipday" AS
SELECT
  shipdata.shipday,
  count(*) AS count,
  sum(shipdata.delivered) AS delivered,
  sum(shipdata.transit) AS transit,
  sum(shipdata.refused) AS refused,
  sum(shipdata.undeliverable) AS undeliverable,
  sum((shipdata.undeliverable + shipdata.refused)) AS total_exceptions
FROM
  shipdata
GROUP BY
  shipdata.shipday;

Now on the UI, I’ve two filters carrier and shipdate when the user selects filter like shipdate or carrier or both I want to group the data by selected filter with shipday but I want shipday to be always unique(this is important). I’ve tried creating below view but this creates duplicate shipday .

CREATE
OR REPLACE VIEW "public"."view_shipday_and_filter" AS
SELECT
  shipdata.shipday,
  date(shipdata.shipdate),
  shipdata.carrier,
  count(*) AS count,
  sum(shipdata.delivered) AS delivered,
  sum(shipdata.transit) AS transit,
  sum(shipdata.refused) AS refused,
  sum(shipdata.undeliverable) AS undeliverable,
  sum((shipdata.undeliverable + shipdata.refused)) AS total_exceptions
FROM
  shipdata
GROUP BY
  shipdata.shipday,
  date(shipdata.shipdate),
  shipdata.carrier;

AFAIK hasura doesn’t allow to do group by with their graphql queries.
I don’t want to do the grouping in the client side since the data size is really big and it will slow down the app. So in the database I want to create one or multiple(if needed) views that will group the data handling above mentioned cases so that shipday always remains unique. If there is other option to achieve this without creating view I’m open for that too.

PS I’m using hasura graphql queries. I don’t want to run separate SQL statement in the client side to fetch data.

Tags:

2

Answers


  1. 0

    There is no performance difference between querying a view and running the query that defines the view. In fact, PostgreSQL replaces the view with its definition when it runs the query.

    You could use the view at the end of your question and add an additional GROUP BY and summation to the query that uses the view, but that would make the GROUP BY in the view pointless and will actually make the query slower.

    My opinion is that you have nothing to gain by using a view in this case. Since the filter condition needs to be applied before grouping, your only option is to directly query shipdata and do the grouping and summation in your application query.

    You mention nothing about that, but if part of your problem is that the performance of this query is too slow, you have to use a different tool than a view. In that case, an option is to use a materialized view defined using the "partially aggregated" query and do the final filtering and grouping in your application query. The pre-aggregated materialized view will be smaller than the base table, so that query will perform better. The downside is that the materialized view doesn’t change if the data in shipdata do, so you have to REFRESH it regularly and live with slightly stale data in between.

    Login or Signup to reply.
  2. 0

    To get an unfiltered shipday report you must use this query (see sample data below)

    select shipday, count(*)
from shipday
group by 1 order by 1;

shipday|count|
-------+-----+
      1|    2|
      2|    1|

    To get a report with a filter say for carrier = 'a' you must add a where predicate but still group by shipday

    select shipday, count(*)
from shipday
where carrier = 'a'
group by 1 order by 1;
shipday|count|
-------+-----+
      1|    1|
      2|    1|

    There is no possibility AFAIK to get those two queries in a view in PostgreSQL. You will need a feature called parametrised view provided by some other RDBMS.

    Anyway you may use a function to elegantly cover this feature.

    create or replace function select_shipday(p_carrier varchar(10) default null)
returns table (shipday int, cnt int) language sql as $$
    select shipday, count(*) cnt
    from shipday
    where carrier = p_carrier or p_carrier is null
   group by 1;
$$;

    The function gets a parameter to filter the carrier (if defined) the default is to get all carriers.

    Sample calls

    -- all carriers
select * from select_shipday();
-- only carrier a
select * from select_shipday('a');

    This is of course the simplest example, but hopefully you get the idea how to add other paramaters or other function for more advanced filters.

    Also you may want to add an if then logic in the function and define dedicated queries for different kind of filters.

    Sample data

    create table shipday as
select * from (values 
(1, 'a'),
(1, 'b'),
(2, 'a')
) tab (shipday, carrier)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search