与 GROUP BY 组合的第一条记录

Question

假设我得到了一个包含字段的表"值" ID (整数( 姓名(瓦尔查尔( 值(浮点数( 时间戳(整数(

现在，我想计算整个值表上每个名称的最高最低值和第一个值(基于时间戳(。

这是否可以在单个高性能查询中实现？我偶然发现了"first_value"功能，但那个功能似乎不起作用。我尝试了以下查询，使用连接，但没有成功。

SELECT 
a.name, 
b.value as open, 
MIN(a.value) as low, 
MAX(a.value) as high
FROM values a 
LEFT JOIN values b 
ON a.name = b.name AND b.id = MIN(a.id) 
GROUP BY a.name;

难道没有某种功能可以使类似的事情成为可能吗？

SELECT 
name, 
FIRST_VALUE(value) as open, 
MIN(value) as low, 
MAX(value) as high
FROM values 
GROUP BY name 
ORDER BY timestamp ASC;

示例数据

id  name    value   timestamp
1   USD     3       16540
2   EUR     5       16540
3   GBP     4       16540
4   EUR     2       16600
5   USD     4       16600
6   GBP     5       16600
7   USD     6       16660
8   EUR     7       16660
9   GBP     6       16660
10  USD     5       16720
11  EUR     5       16720
12  GBP     7       16720
13  EUR     8       16780
14  USD     7       16780
15  GBP     8       16780

示例输出

name    open    low     high
USD     3       3       7
EUR     5       2       8
GBP     4       4       8

我正在使用MySQL客户端版本：5.6.39 平局应该是不可能的，如果有，我不在乎选择哪个值。

Answer 1

如果您运行的是MySQL 8.0，则可以使用窗口函数轻松解决此问题：

select name, value open, low, high
from (
select
name,
value,
min(value) over(partition by name) low,
max(value) over(partition by name) high,
row_number() over(partition by name order by timestamp) rn
from mytable
) x
where rn = 1

DB小提琴上的演示：

| name | open | low | high |
| ---- | ---- | --- | ---- |
| EUR  | 5    | 2   | 8    |
| GBP  | 4    | 4   | 8    |
| USD  | 3    | 3   | 7    |

---

在早期版本中，您可以：

• 使用相关子查询筛选每个名称的第一条记录
使用聚合查询
• 联接表，该聚合查询计算每个名称的最小值和最大值

查询：

select 
t.name,
t.value open,
t0.low,
t0.high
from 
mytable t
inner join (
select name, min(value) low, max(value) high from mytable group by name
) t0 on t0.name = t.name
where t.timestamp = (
select min(t1.timestamp) from mytable t1 where t1.name = t.name
);

MySQL 5.6 DB Fiddle上的演示：结果与上述相同

这也可以使用内联子查询(实际上可能表现得更好(来实现：

select 
t.name,
t.value open,
(select min(value) from mytable t1 where t1.name = t.name) low,
(select max(value) from mytable t1 where t1.name = t.name) high
from 
mytable t
where timestamp = (
select min(t1.timestamp) from mytable t1 where t1.name = t.name
)

MySQL 5.6 DB Fiddle 上的演示

Answer 2

在

单个高性能查询中

从逻辑上做，让DBMS担心性能。 如果这还不够快，请检查您的索引。

与第一个时间戳关联的值需要联接。 您可以轻松找到第一个时间戳。 从与给定行关联的行中获取值：这就是连接的目的。

因此，我们有：

SELECT 
name, 
value as open, 
v1.low
v1.high
FROM values as v join (
select name, 
min(timestamp) as timestamp, 
min(value) as low, 
max(value) as high
FROM values
GROUP BY name 
) as v1
on v.name = v1.name and v.timestamp = v1.timestamp

Answer 3

此解决方案似乎具有最佳性能。

SELECT 
name, 
CAST(SUBSTRING_INDEX(GROUP_CONCAT(CAST(value AS CHAR) ORDER BY TIMESTAMP ASC), ',', 1) AS DECIMAL(10, 6)) AS open, 
MIN(value) AS low, 
MAX(value) AS high
FROM mytable
GROUP BY name 
ORDER BY name ASC