MySQL GROUP BY YEARWEEK 获取第一个和最后一个不起作用

Question

我试图从我拥有的 MySQL 表中获取股票的每周值(如在周一至周日，由 YEARWEEK 的 mode=1 参数指定)，其中包含每日最低值、最高值、开盘价和收盘价。

例如，以下查询正确地给出了 2012 年 1 月 1 日至 2012 年 3 月 1 日之间的几周的高点和低点:

SELECT date, MIN(low), MAX(high) 
FROM daily_stock_values WHERE stock_id = SOMESTOCK 
AND date BETWEEN '2012-01-01' AND '2012-03-01' GROUP BY YEARWEEK(date, 1)

这正确输出:

+------------+-----------+-----------+
| date       | max(high) | min(low)  |
+-----------+-----------+-----------+
| 2012-01-01 | ......... | ......... |
| 2012-01-02 | ......... | ......... |
| 2012-01-09 | ......... | ......... |
| 2012-01-16 | ......... | ......... |
| 2012-01-23 | ......... | ......... |
| 2012-01-30 | ......... | ......... |
| 2012-02-06 | ......... | ......... |
| 2012-02-13 | ......... | ......... |
| 2012-02-20 | ......... | ......... |
| 2012-02-27 | ......... | ......... |
+------------+-----------+-----------+

如您所见，所有日期都正确指定了星期一(除了第一个是结果范围较低的日期)，这是星期一至星期日的第一天。

但是，当我尝试介绍第一个和最后一个时(如 here 所述):

SELECT date, MIN(low), MAX(high),
SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY date), ',', 1) AS open, 
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY date DESC), ',', 1) AS close
FROM daily_stock_values WHERE stock_id = SOMESTOCK 
AND date BETWEEN '2012-01-01' AND '2012-03-01' GROUP BY YEARWEEK(date, 1)

不幸的是，这弄乱了我的 GROUP BY 并导致日期不再与星期一对齐:

+------------+-----------+-----------+-----------+-----------+
| date       | max(high) | min(low)  | open      | close     |
+------------+-----------+-----------+-----------+-----------+
| 2012-01-01 | ......... | ......... | ......... | ......... |
| 2012-01-08 | ......... | ......... | ......... | ......... |
| 2012-01-15 | ......... | ......... | ......... | ......... |
| 2012-01-22 | ......... | ......... | ......... | ......... |
| 2012-01-29 | ......... | ......... | ......... | ......... |
| 2012-02-05 | ......... | ......... | ......... | ......... |
| 2012-02-12 | ......... | ......... | ......... | ......... |
| 2012-02-19 | ......... | ......... | ......... | ......... |
| 2012-02-26 | ......... | ......... | ......... | ......... |
| 2012-02-27 | ......... | ......... | ......... | ......... |
+------------+-----------+-----------+-----------+-----------+

这是怎么回事？谢谢!

Answer 1

我能想到的最佳解决方案是:
SELECT YEARWEEK(date, 1) AS 忽略，MIN(date) 作为日期

这很好用!