SQL select data and grouping data by date range

Question

I have table with data like this

price	date	type
1000	2021-03-13	A
1000	2021-03-14	A
1000	2021-03-15	A
1300	2021-03-16	A
1000	2021-03-17	A
1300	2021-03-18	A
1000	2021-03-19	A
1500	2021-03-20	A
1500	2021-03-21	A
1500	2021-03-22	A
2000	2021-03-13	B
2000	2021-03-14	B
2000	2021-03-15	B
2000	2021-03-16	B
2000	2021-03-17	B

I want to query data like this

price	startDate	endDate	type
1000	2021-03-13	2021-03-15	A
1300	2021-03-16	2021-03-16	A
1000	2021-03-17	2021-03-17	A
1300	2021-03-18	2021-03-18	A
1000	2021-03-19	2021-03-19	A
1500	2021-03-20	2021-03-22	A
2000	2021-03-13	2021-03-17	B

my current sql below but it is not correct at all

SELECT MIN(date) as startDate, MAX(date) as endDate, price, type                                      
                            FROM prices 
                            GROUP BY type, price
                            ORDER BY type, MIN(date)

What's with a gap not filled with another record, i.e. when record (1300,2021-03-16,A) wouldn't exist? — Turo
– Turo, Commented Mar 12, 2021 at 8:05

Tim Biegeleisen · Accepted Answer · 2021-03-12 08:00:40Z

2

This is a gaps and islands problem, and one simple way to handle it uses the difference in row numbers method. Assuming you are using MySQL 8+, the following should work:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY type ORDER BY date) rn1,
              ROW_NUMBER() OVER (PARTITION BY type, price ORDER BY date) rn2
    FROM prices
)

SELECT price, MIN(date) AS startDate, MAX(date) AS endDate, type
FROM cte
GROUP BY price, type, rn1 - rn2
ORDER BY type, startDate;

Demo

edited Mar 12, 2021 at 8:00

answered Mar 12, 2021 at 7:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Turo Over a year ago

It doesn't detect gaps not filled with other prices, i.e. if you remove the records with price 1300. Is this intended?

Tim Biegeleisen Over a year ago

@Turo You see some edge case which is not all visible to me in the OP. I will wait for feedback from the OP before making an edit.

Turo Over a year ago

I hoped you'd post a solution ;-) I couldn't find it yet...

Gordon Linoff Over a year ago

@Turo . . . The dates have no gaps in the question. The OP does not explain what to do in that case. In my opinion, this answers the question and is one of the best ways to solve it. (I can think of one simplification but it is not worth an answer.)

etsuhisa · Accepted Answer · 2021-03-12 12:33:49Z

0

If not using CTE, you can work the following query:

SELECT w1.price, w1.date, w2.date, w1.type FROM
(
  SELECT * FROM mytable t1
  WHERE NOT EXISTS (
    SELECT 1 FROM mytable t2
    WHERE 
      t1.price = t2.price AND
      t1.type = t2.type AND
      DATEDIFF(t2.date, t1.date) = -1
  )
) w1
INNER JOIN
(
  SELECT * FROM mytable t1
  WHERE NOT EXISTS (
    SELECT 1 FROM mytable t2
    WHERE 
      t1.price = t2.price AND
      t1.type = t2.type AND
      DATEDIFF(t2.date, t1.date) = +1
  )
) w2
ON
w1.price = w2.price AND
w1.type = w2.type AND
w1.date <= w2.date AND
NOT EXISTS (
  SELECT * FROM mytable t1
  WHERE NOT EXISTS (
    SELECT 1 FROM mytable t2
    WHERE 
      t1.price = t2.price AND
      t1.type = t2.type AND
      DATEDIFF(t2.date, t1.date) = +1
  )
  AND
  w1.price = t1.price AND
  w1.type = t1.type AND
  w1.date <= t1.date AND t1.date < w2.date
)

Getting the smaller and larger dates of each period.
Joining these tables.
Getting rows between smaller and larger dates.

DB Fiddle

edited Mar 12, 2021 at 12:33

answered Mar 12, 2021 at 9:17

etsuhisa

1,7881 gold badge7 silver badges8 bronze badges

1 Comment

Gordon Linoff Over a year ago

@banku. . . This is definitely not the simplest solution to the problem.

Arun Palanisamy · Accepted Answer · 2021-03-12 07:57:55Z

-1

SELECT MIN(date) as startDate, MAX(date) as endDate, price, type    
FROM prices
GROUP BY type, price
ORDER BY MIN(date) ASC

This query might work in arranging data in ascending order of date

edited Mar 12, 2021 at 7:57

Arun Palanisamy

5,4898 gold badges34 silver badges57 bronze badges

answered Mar 12, 2021 at 7:36

Priyanka Nigam

145 bronze badges

1 Comment

P.Salmon Over a year ago

Too simple and doesn't produce expected result

Collectives™ on Stack Overflow

SQL select data and grouping data by date range

3 Answers 3

Demo

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related