The real problem is with some huge product catalogs. In order to learn how to SQL this properly, I'm testing with this dataset. The jumbled up order of things is intended to help ensure that the logic will be sound.

The following is a listing of the test data.

select * from test;

As you can see the title here are partially named to help correlate with the other values.

The desired output is the following:

At first I tried using window functions. I started with this to understand how the window function works by using an aggregate function to display the contents of the "window".

select string_agg(title, ', ') over (partition by id order by time) from test;

This indicates the desired application of grouping by id and ordering by time. I have gone into this result table and bolded the information that I actually need.

Unfortunately I am at a complete loss as to how to:

• only choose the last of these partition windows
• choose only the first and last items

Furthermore:

• prefer avoiding stitching strings only to split them back out: real titles can have commas, or indeed any separator I might otherwise end up choosing, inside.

A second approach I went ahead and took is the below:

select 
    d.id,
    d.title as title_start,
    test.title as title_finish,
    start,
    finish
from
(
  select 
    c.id,
    title,
    start,
    finish
  from (
    (
        select min(time) as start, id from test group by id
    ) a
    inner join
    (
        select max(time) as finish, id from test group by id
    ) b 
    using (id)
  ) c
  left join test
  on c.id = test.id
  where c.start = test.time
) d
left join test
on d.id = test.id
and d.finish = test.time
order by id;

View on DB Fiddle

Here I was able to get all the way there to my desired result by brute forcing joins. But this SQL code is extremely difficult to follow. If I saw this presented to me as a solution I would feel pretty strongly about rejecting it. I would really like it if a SQL expert could review this and tell me the actual right way to code up this problem. Surely three joins are not needed?

CodePudding user response：

Your SQL based solution was actually pretty good. I reworked it only for readability and to make it a little easier to see where things are coming from and moving to.

select 
    start.id,
    start.title as title_start,
    finish.title as title_finish,
    start.time as start,
    finish.time as finish
from 
    (select test.id, title, sub.time from test
        inner join (select min(time) as time, id from test group by id) sub
            on test.id = sub.id and test.time = sub.time) start
inner join
    (select test.id, title, sub.time from test
        inner join (select max(time) as time, id from test group by id) sub
            on test.id = sub.id and test.time = sub.time) finish
    using(id)
    order by start.id;

Here is the fiddle

CodePudding user response：

This is a gaps-and-islands problem., you can try to use row_number() window functions, The trick point is to subtract row numbers so that consecutive rows fall in the same group (grp) per (id)

Query #2

WITH CTE AS (
    SELECT id,
           MIN (time) t_start,
           MAX (time) t_finish
    FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY id,time) - ROW_NUMBER() OVER(PARTITION BY id ORDER BY time) grp
        FROM test
    ) t1
    GROUP BY id,grp
)
SELECT c.id,
       c.t_start,
       c.t_finish,
       MAX(CASE WHEN c.t_start = t1.time THEN title END),
       MAX(CASE WHEN c.t_finish = t1.time THEN title END)
FROM CTE c 
INNER JOIN test t1 
ON c.id = t1.id
GROUP BY c.id,
       c.t_start,
       c.t_finish
ORDER BY c.id;

Another way I think that will be simpler than the above answer, unnecessary self-join use two ROW_NUMBER in CTE, one is for ORDER BY time, another one is for ORDER BY time DESC, then use the aggregate condition function to get the result.

;WITH CTE AS (
    SELECT time,
           title,
           id,
           ROW_NUMBER() OVER(PARTITION BY id,grp ORDER BY time) first_rn,
           ROW_NUMBER() OVER(PARTITION BY id,grp ORDER BY time DESC) last_rn
    FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY id,time) - ROW_NUMBER() OVER(PARTITION BY id ORDER BY time) grp
        FROM test
    ) t1
)
SELECT id,
       MAX(CASE WHEN first_rn = 1 THEN time END) t_start,
       MAX(CASE WHEN last_rn = 1  THEN time END) t_finish,
       MAX(CASE WHEN first_rn = 1 THEN title END) title_start,
       MAX(CASE WHEN last_rn = 1 THEN title END) title_finish
FROM CTE 
GROUP BY id
ORDER BY ID

View on DB Fiddle