Postgresql – How to do a kind of selective grouping in a SQL result

group bypostgresqlrouting

I have some query as select x1,x2,x3,x4 from foo where bar resulting something like:

CREATE TEMP TABLE foo
AS SELECT * FROM ( VALUES
 ( '00','01','03','AA' ),
 ( '01','03','03','BB' ),
 ( '02','09','05','CC' ),
 ( '03','08','05','DD' ),
 ( '03','08','03','EE' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','09','GG' )
) AS t( x1, x2, x3, x4);

Note the x3 column: we have 03 twice then 05 twice then 03 again then 09. I need to calculate a route using the pgr_ksp function (PostGIS). Imagine the table here is the result and the x3 column is the road names. In this case I start on 03 street, walk on it for 2 segments and take the 05 street just to take the 03 street again after.

My problem is I'm showing this segments to the user and I don't like to. The user know nothing about segments of roads and just want to see take 03 street, 05 street, 03 again and then 09.

If I just group by, I will lost the take 05 step between the two 03 and I'll see the all long 03 segments connected: take 03 street (all 6 segments together), 05 and then 09 and this is wrong.

My question: how can I group the x3 column as the two first 03, then group the two 05 and then group 03 again after 05 and then group all tuples left using this method.

Not so fast! I must have a sum of the x2 column as I group the x3. This will be the geometry column I must compute – concat all segments geometry to have the complete way representation. This is the result I want:

-----------
| x2 | x3 |
|----|----|
| 04 | 03 |
|----|----|
| 17 | 05 |
|----|----|
| 32 | 03 |
|----|----|
| 08 | 09 |
-----------

x2 is the way geometry and x3 is the name of the street or way. In this case I'll have the complete way segment (sum of small pieces) and the way name.

Best Answer

create temp table tbl as 
select * from 
(values (1,01,03),(2,03,03),(3,09,05),(4,08,05),(5,08,03),(6,08,03),(7,08,03),(8,08,03),(9,08,09)) t (id, x2,x3);

First you need to generate a partition by every group of X3 values:

This sentence uses LAG function to mark when X3 changes from the previous row.

select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
from tbl
order by id

Then you can establish a partition using:

select id, x2, x3, sum(cnt.c) over (order by id) as part
from cnt
order by id

Then simply get the sum of X2 grouping by every partition.

with cnt as
(
    select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
    from tbl
    order by id
), cnt1 as
( 
  select id, x2, x3, sum(cnt.c) over (order by id) as part
  from cnt
  order by id
)
select sum(x2) xx2, min(x3) xx3
from cnt1
group by part
order by part
;

The final result:

+-----+-----+
|  X2 |  X3 |
+-----+-----+
| 4   | 3   |
+-----+-----+
| 17  | 5   |
+-----+-----+
| 32  | 3   |
+-----+-----+
| 8   | 9   |
+-----+-----+

Check it here: http://rextester.com/YPU13325

Related Solutions

Postgresql – How to ensure that in a insert trigger a column gets filled before other column

If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM 
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;

Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS 
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM 
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM 
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$ 
LANGUAGE plpgsql
;

the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones

Under the assumptions that you have some columns (say x and y in your example), that you don't know if they are functionally equivalent or not - and that these columns do not have any NULL values (which would complicate things), you can use:

SELECT 
    u, v, w,                    -- the grouping columns

    AVG(z) AS z_avg,            -- the non-functionally dependent

    CASE WHEN MIN(x) = MAX(x)   -- a possibly functionally dependent
      THEN MIN(x) ELSE NULL     -- column
    END AS x,

    CASE WHEN MIN(y) = MAX(y)   -- another one
      THEN MIN(y) ELSE NULL
    END AS y
FROM 
    a
GROUP BY 
    u, v, w ;

If a column is not functionally dependent (with the current data), it will have at least 2 distinct values, so the MIN and MAX will be different and the result will be shown as NULL in that column, in all rows. You can then remove it from the query.

Best Answer

Related Solutions

Postgresql – How to ensure that in a insert trigger a column gets filled before other column

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones

Related Question