英文:
Grouping into interval of 2 minutes within a time range
问题
以下是翻译好的内容：
我在将数据分组为2分钟间隔时遇到了一些问题。
这是我的表格数据：
| 股票 | 开盘价 | 最高价 | 最低价 | 收盘价 | 时间 |
|---|---|---|---|---|---|
| 苹果 | 1940 | 1950 | 1935 | 1948 | 2023-03-20 11:00:00 |
| 苹果 | 1950 | 1960 | 1945 | 1958 | 2023-03-20 11:01:00 |
| 苹果 | 1960 | 1970 | 1955 | 1968 | 2023-03-20 11:02:00 |
| 苹果 | 1970 | 1980 | 1965 | 1978 | 2023-03-20 11:03:00 |
| 苹果 | 1980 | 1990 | 1985 | 1998 | 2023-03-20 11:04:00 |
| 苹果 | 1990 | 2000 | 1975 | 1988 | 2023-03-20 11:05:00 |
我的查询语句：
select
开盘价,
MAX(最高价) as 最高价,
MIN(最低价) as 最低价,
收盘价,
时间,
FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(时间)/120 ) * 120) AS 二分钟间隔时间
from 表名
where 股票 = 'APPL'
group by 二分钟间隔时间
order by 二分钟间隔时间 ASC
预期输出：
| 股票 | 开盘价 | 最高价 | 最低价 | 收盘价 | 时间 |
|---|---|---|---|---|---|
| 苹果 | 1940 | 1960 | 1935 | 1958 | 2023-03-20 11:00:00 |
| 苹果 | 1960 | 1980 | 1955 | 1978 | 2023-03-20 11:02:00 |
| 苹果 | 1980 | 2000 | 1975 | 1988 | 2023-03-20 11:04:00 |
我只有在获取范围内的最新收盘价方面存在问题。目前从第一行获取，而不是最后一行在2分钟内。
英文:
i have some issue with grouping the data in interval of 2 minutes.
here is my table data
| ticker | open | high | low | close | time |
|---|---|---|---|---|---|
| APPL | 1940 | 1950 | 1935 | 1948 | 2023-03-20 11:00:00 |
| APPL | 1950 | 1960 | 1945 | 1958 | 2023-03-20 11:01:00 |
| APPL | 1960 | 1970 | 1955 | 1968 | 2023-03-20 11:02:00 |
| APPL | 1970 | 1980 | 1965 | 1978 | 2023-03-20 11:03:00 |
| APPL | 1980 | 1990 | 1985 | 1998 | 2023-03-20 11:04:00 |
| APPL | 1990 | 2000 | 1975 | 1988 | 2023-03-20 11:05:00 |
My query:
select
open,
MAX(high) as high,
MIN(low) as low,
close,
time,
FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(time)/120 ) * 120) AS quaterhour
from tableName
where ticker = 'APPL'
group by quaterhour
order by quaterhour ASC
Expected output:
| ticker | open | high | low | close | time |
|---|---|---|---|---|---|
| APPL | 1940 | 1960 | 1935 | 1958 | 2023-03-20 11:00:00 |
| APPL | 1960 | 1980 | 1955 | 1978 | 2023-03-20 11:02:00 |
| APPL | 1980 | 2000 | 1975 | 1988 | 2023-03-20 11:04:00 |
I only have issues with getting the latest close price with in range. right now getting from first row not last row in 2 minute.
答案1
得分: 1
尝试这个，我希望没有拼写错误
select
open,
MAX(high) as high,
MIN(low) as low,
MAX(IF((minute(time) & 1),close,0)),
time,
time - INTERVAL (minute(time) & 1) MINUTE AS quaterhour
from tableName
where ticker = 'APPL'
group by quaterhour
order by quaterhour ASC;
英文:
Try this, i hope there is no typo
select
open,
MAX(high) as high,
MIN(low) as low,
MAX(IF((minute(time) & 1),close,0)),
time,
time - INTERVAL (minute(time) & 1) MINUTE AS quaterhour
from tableName
where ticker = 'APPL'
group by quaterhour
order by quaterhour ASC;
答案2
得分: 0
以下是要翻译的内容：
当禁用ONLY_FULL_GROUP_BY时，MySQL允许省略非聚合分组列，此时它会返回一个随机行（通常是遇到的第一行）而不是进行聚合。您的查询就是这样。
列出所有在分组中不进行聚合的列：
select
open,
MAX(high) as high,
MIN(low) as low,
close,
FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(time)/120 ) * 120) AS quaterhour
from tableName
where ticker = 'APPL'
group by open, close, quaterhour
order by quaterhour ASC
您必须从选择中移除time以便按时间进行聚合。
此外，“quarter”不是一个合适的名称选择，用于表示2分钟的时间段。
英文:
When ONLY_FULL_GROUP_BY is disabled, MySQL allows non-aggregate grouping columns to be omitted, in which case it returns a random row (usually the first row encountered) instead of aggregating. Your query does this.
List all non-aggregating columns in the group by:
select
open,
MAX(high) as high,
MIN(low) as low,
close,
FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(time)/120 ) * 120) AS quaterhour
from tableName
where ticker = 'APPL'
group by open, close, quaterhour
order by quaterhour ASC
You must remove the time from the select to aggregate over time.
Also, "quarter" is a poor name choice for a 2-minute period.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论