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

Postgresql – Statistics of TRUE/FALSE rows from a multi-BOOLEAN columns TABLE

The table below is a subset of a table from a db I have access to:

CREATE TABLE trips(trip_id INT, foot BOOLEAN, bike BOOLEAN, bus BOOLEAN, 
  car BOOLEAN, metro BOOLEAN, motorcycle BOOLEAN, train BOOLEAN,
  other BOOLEAN)

-- sample values
INSERT INTO trips (trip_id, foot, bike, bus, car, metro, 
  motorcycle, train, other)
VALUES(19,true,false,false,false,false,false,false,false),
      (20,false,false,false,false,false,false,false,false),
      (56,true,false,true,false,false,false,false,false),
      (65,true,false,false,true,false,false,false,false),
      (77,false,false,false,true,false,false,false,false)

Then for example, I want to produce the following statistics above mode of trip in the table.

  • number trips by foot only
  • number of trips by bus only
  • number of trips by car only etc.., then
  • number of trips by foot AND car
  • trips by foot AND bus
  • total trips for which all modes are FALSE.

I produce this db<>fiddle, but not sure how to filter this stats.

Tags:

3

Answers


  1. 0

    Do logic operation

    SELECT * FROM trips WHERE foot = true
AND (bike AND  bus AND  car AND  metro AND 
  motorcycle AND train AND other) = False
    trip_id foot bike bus car metro motorcycle train other
    19 t f f f f f f f
    56 t f t f f f f f
    65 t f f t f f f f 
    SELECT * FROM trips WHERE (foot AND car) = true
AND (bike AND  bus AND  metro AND 
  motorcycle AND train AND other) = False
    trip_id foot bike bus car metro motorcycle train other
    65 t f f t f f f f

    fiddle

    Login or Signup to reply.
  2. 0

    You can construct your queries following the template below

    Foot only

    SELECT count(*) nf 
FROM trips 
WHERE foot 
   AND NOT (bike OR bus OR car OR metro OR motorcycle OR train OR other)

    Foot + car only

    SELECT count(*) nfc
FROM trips 
WHERE foot AND car
AND NOT (bike OR bus OR metro OR motorcycle OR train OR other)

    The same in a single query using a conditional aggregation

    SELECT 
  count(*) filter(where foot 
                       AND NOT (bike OR bus OR car OR metro OR motorcycle OR train OR other)) nf,
  count(*) filter(where foot AND car
                       AND NOT (bike OR bus OR metro OR motorcycle OR train OR other)) nfc 
FROM trips
    Login or Signup to reply.
  3. 0

    Normalize your data by flipping it over to jsonb, and one query can get you all mixes of modes:

    with normed as (
  select trip_id, tmode, used::boolean
    from trips
         cross join lateral jsonb_each(to_jsonb(trips) - 'trip_id') as e(tmode, used)
), modenames as (
  select distinct tmode, 
         (2 ^ (dense_rank() over (order by tmode) - 1))::bigint as mode_val
    from normed
), possible_n as (
  select generate_series(0, max(mode_val::bigint) * 2 - 1) as tripval
    from modenames
), perms as (
  select n.tripval, string_agg(m.tmode, ', ' order by m.mode_val) as tmodes,
         count(1) as n_modes
    from possible_n n 
         join modenames m on m.mode_val & n.tripval > 0
   group by n.tripval
), tvals as (
  select n.trip_id, sum(m.mode_val) as tripval
    from normed n
         join modenames m on m.tmode = n.tmode
   where n.used = true
   group by n.trip_id
)
select p.tmodes, count(v.trip_id) n_trips, 
       nullif(array_agg(v.trip_id), '{NULL}') trips
  from perms p
       left join tvals v on v.tripval = p.tripval
 group by p.n_modes, p.tmodes
 order by p.n_modes;

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search