PostgreSQL – How to Check if Each Value of Array is Equal

arrayoptimizationpostgresql

I try to get d.id only if all rows with same id have same pday:

with dif_zero as (d.id, 
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d 
join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from 
dif_zero 
group by id
having sum(char_sal_dif) = 0 
and avg(pday::int) =  (array_agg(pday))[1]::int 
and avg(pday::int) =  (array_agg(pday))[2]::int -- and so on...

Maby there is other way (more simple) to get to know if all elements in array equals to one another?

Or I can use window functioin in subquery, and after take only rows with value = 1.

select id, count(id) over(partition by id) one_day

Wich way is better? Or maby there is other way to do it more corrent?

Best Answer

If you want to make sure that all days are the same, just compare the min and max. If they are the same, then all values are the same as well.

with dif_zero as (
  select d.id, 
         date_part('day', p.docdate)::int pday
  from sh_billing.dogovor d 
   join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from 
dif_zero 
group by id
having sum(char_sal_dif) = 0 -- where does char_sal_dif come from? 
   and min(pday) = max(pday)

A more costly (=slower) but maybe easier to understand way, is to compare the count with the count of distinct values:

having count(*) = count(distinct pday)`

Related Solutions

PostgreSQL – How to Return Second Group as an Array

Maybe something like this:

with ranked_visits as (
  SELECT w.website_id, 
         v.visitor_id, 
         count(wv.visit_id) as visits,
         row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk
  FROM website_visits wv
    JOIN websites w ON wv.website_id = w.website_id
    JOIN visitors v ON wv.visitor_id = v.visitor_id
  GROUP BY w.website_id, v.visitor_id
  ORDER BY w.website_id ASC, count(wv.visit_id) DESC
)
select website_id, string_agg('visitor_id: '||visitor_id||',visits:'||visits, ', ')
from ranked_visits
where rnk <= 10
group by website_id;

PostgreSQL – Pattern Match Against Array Elements

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum 
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM ( 
                      SELECT lognum, 
                      flightnums, 
                      generate_subscripts(flightnums, 1) AS s 
                      FROM ( 
                             SELECT lognum, 
                                    array_agg(flightnum) AS flightnums 
                                    FROM logs 
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2 
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum 
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=#

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays

Best Answer

Related Solutions

PostgreSQL – How to Return Second Group as an Array

PostgreSQL – Pattern Match Against Array Elements

Related Question