SQL选择数据并按日期范围分组

Question

我有一张包含如下数据的表格

价格	日期	类型
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

我想进行如下数据查询

价格	开始日期	结束日期	类型
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

我的当前SQL查询语句如下，但完全不正确：

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

- banku

---

你的 MySQL 版本是什么？ - Arun Palanisamy

你是如何确保数据按照这个顺序出现的？ - Regressor

如果有一个空隙没有填充另一条记录，比如记录（1300，2021-03-16，A）不存在，那会怎么样？ - Turo

Answer 1

这是一个“间隙和群岛”问题，解决它的一种简单方法是使用“行号之差”的方法。假设您正在使用MySQL 8+，则以下内容应该可以解决：

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;

演示

- Tim Biegeleisen

---

它不能检测未填充其他价格的空隙，例如如果您删除价格为1300的记录。这是故意的吗？ - Turo

@Turo，你在OP中看到了一些我没有完全注意到的边缘情况。在进行编辑之前，我会等待OP的反馈意见。 - Tim Biegeleisen

我希望你能发布一个解决方案;-) 我还没有找到它... - Turo

@Turo……问题中的日期没有间隙。OP没有解释在这种情况下该怎么做。我认为，这个回答解决了问题，是解决问题的最佳方法之一。（我可以想到一个简化，但不值得回答。） - Gordon Linoff

Answer 2

如果不使用CTE，您可以使用以下查询：

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
)

1. 获取每个时间段的较小和较大日期。
2. 连接这些表。
3. 获取在较小和较大日期之间的行。

DB Fiddle

- etsuhisa

---

@banku. . . 这绝对不是解决问题的最简单方案。 - Gordon Linoff

Answer 3

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

这个查询可能适用于按日期升序排列数据

- Priyanka Nigam

---

太简单了，没有产生预期的结果。 - P.Salmon