程序员经验分享-hwhale

如何使用 Group By 和自连接返回每日最低、最高、开盘价和收盘价结果集？

2023-11-29

SOLVED

堆栈溢出万岁！

当我离开时，人们留下了 2 个解决方案（谢谢大家——为两个可行的解决方案分发业力的协议是什么？）

这是我回来发布的解决方案。它源自另一个 StackOver 解决方案：

如何使用聚合函数在MySQL查询中获取分组记录的第一条和最后一条记录？

...我的适应是：

SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price,
SUBSTRING_INDEX( 
    GROUP_CONCAT(
        PRICE
        ORDER BY DTE ASC
        )
, ',', 1 ) AS opn_price,
SUBSTRING_INDEX( 
    GROUP_CONCAT(
        PRICE
        ORDER BY DTE DESC
        )
, ',', 1 ) AS cls_price 
FROM `CHART_DATA` 
GROUP BY trading_day
;

上面“Q”开头的数据是我试图结束的数据。希望这对其他人有帮助，因为我怀疑我的日志相当常见。

我愿意打赌这三种解决方案之一具有性能优势。如果有人碰巧了解 MySQL 的内部工作原理和查询优化并愿意推荐“首选”解决方案，那么了解这些在将来将会很有用。

结束解决

更新#2

尝试使用以下方法从另一个方向来解决这个问题：

http://forums.mysql.com/read.php?65,363723,363723

I get:

SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price,
(SELECT opn_price FROM 
    (SELECT 
    DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day,    
    PRICE AS opn_price,
    MIN(DTE) AS opn
    FROM `CHART_DATA` 
    GROUP BY a_day
    ORDER BY opn ASC LIMIT 1) AS tblO) AS opnqt,
(SELECT cls_price FROM 
    (SELECT 
    DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day,    
    PRICE AS cls_price,
    MIN(DTE) AS cls
    FROM `CHART_DATA` 
    GROUP BY a_day
    ORDER BY cls DESC LIMIT 1) AS tblC) AS clsqt    
FROM `CHART_DATA` cht
GROUP BY trading_day;

这与下面第一个更新中的查询存在类似的功能障碍；返回的“clsqt”（cls_price）值是数据中找到的最后收盘价。布莱赫。

另外，我们开始再次进入“极其复杂”的查询空间，这对性能没有好处。

但如果有人看到“clsqt”值的修复，我会很乐意接受它并在稍后解决性能问题。 :)

结束更新 #2

UPDATE

如此接近......这就是我今天所处的位置：

SELECT 
DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`cht1`.`PRICE`) AS min_price,
MAX(`cht1`.`PRICE`) AS max_price,
    MIN(cht1.DTE) AS opn_date1,
    MIN(cht1.DTE) AS opn_date2,
    `cht2`.`PRICE` AS opn_price,
        MAX(cht1.DTE) AS cls_date1,
        MAX(cht3.DTE) AS cls_date3,
        `cht3`.`PRICE` AS cls_price
FROM `CHART_DATA` cht1
     LEFT JOIN `CHART_DATA` cht2 
     ON cht2.DTE = cht1.DTE
     LEFT JOIN `CHART_DATA` cht3 
     ON cht3.DTE = cht1.DTE
GROUP BY trading_day
HAVING opn_date1 = opn_date2
AND cls_date1 = cls_date3
;

这会正确地重新调整所有内容，但正确的“cls_price”（它返回与“opn Price”相同的“cls_price”值）。

但是，“cls_date1”和“cls_date3”是正确的值，所以我必须接近。

有人看到我不是什么吗？

结束更新

我一直在仔细研究关于左连接和自连接的内容……我必须承认我并没有摸索。

我发现这个“Q”似乎非常接近我想要的：mysql中右表的左连接条件

我想做的是从单个表中检索开盘价、收盘价、最低价格和最高价格天数（下面的示例数据）。

最小值和最大值很简单：

SELECT 
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price
FROM `CHART_DATA` 
GROUP BY trading_day;

我希望按日期分组返回结果，例如：

'trading_day' 'opn_price' 'min_price' 'max_price' 'cls_price'

好吧，所以我尝试只用一个连接来“婴儿学步”......

SELECT 
DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`cht1`.`PRICE`) AS min_price,
MAX(`cht1`.`PRICE`) AS max_price,
`cht2`.`PRICE` AS opn_price
FROM `CHART_DATA` cht1
 LEFT JOIN `CHART_DATA` cht2 
 ON cht2.DTE = MIN(cht1.DTE)
GROUP BY trading_day;

...我收到消息“组功能使用无效”

当然，删除“GROUP BY”没有帮助，因为我需要返回聚合列。

我有一个非常复杂的解决方案，可以获取打开和关闭结果，但不能获取最小值和最大值 - 并且它们位于单独的结果集中。我感觉我已经让这个变得比必要的更加复杂，并且我可以掌握上面引用的“Q”中引用的自连接发生了什么，我的整体编码将得到不可估量的改进。但上周末我在这上面花了大约 12 个小时，我比以往任何时候都更加困惑。

此时欢迎所有见解、解释和观察......

/* SAMPLE TABLE AND DATA */

CREATE TABLE `CHART_DATA` (
  `ID` varchar(10) DEFAULT NULL,
  `DTE` datetime DEFAULT NULL,
  `PRICE` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `CHART_DATA` */

INSERT INTO `chart_data`
            (`id`,`dte`,`price`)
VALUES      ('1','2011-01-01 00:10:00',0.65),
            ('2','2011-01-01 06:10:00',0.92),
            ('3','2011-01-01 12:10:00',0.59),
            ('4','2011-01-01 18:10:00',0.16),
            ('5','2011-01-02 00:10:00',0.28),
            ('6','2011-01-02 06:10:00',0.12),
            ('7','2011-01-02 12:10:00',0.92),
            ('8','2011-01-02 18:10:00',0.1),
            ('9','2011-01-03 00:10:00',0.34),
            ('10','2011-01-03 06:10:00',0.79),
            ('11','2011-01-03 12:10:00',1.23),
            ('12','2011-01-03 18:10:00',1.24),
            ('13','2011-01-04 00:10:00',1.12),
            ('14','2011-01-04 06:10:00',0.8),
            ('15','2011-01-04 12:10:00',0.65),
            ('16','2011-01-04 18:10:00',0.78),
            ('17','2011-01-05 00:10:00',0.65),
            ('18','2011-01-05 06:10:00',1.19),
            ('19','2011-01-05 12:10:00',0.89),
            ('20','2011-01-05 18:10:00',1.05),
            ('21','2011-01-06 00:10:00',0.29),
            ('22','2011-01-06 06:10:00',0.43),
            ('23','2011-01-06 12:10:00',0.26),
            ('24','2011-01-06 18:10:00',0.34),
            ('25','2011-01-07 00:10:00',0.22),
            ('26','2011-01-07 06:10:00',0.37),
            ('27','2011-01-07 12:10:00',1.22),
            ('28','2011-01-07 18:10:00',1.16),
            ('29','2011-01-08 00:10:00',0.3),
            ('30','2011-01-08 06:10:00',1.17),
            ('31','2011-01-08 12:10:00',0.62),
            ('32','2011-01-08 18:10:00',0.86),
            ('33','2011-01-09 00:10:00',0.84),
            ('34','2011-01-09 06:10:00',1.11),
            ('35','2011-01-09 12:10:00',0.92),
            ('36','2011-01-09 18:10:00',1.03),
            ('37','2011-01-10 00:10:00',1.13),
            ('38','2011-01-10 06:10:00',0.58),
            ('39','2011-01-10 12:10:00',1.03),
            ('40','2011-01-10 18:10:00',0.21),
            ('41','2011-01-11 00:10:00',0.12),
            ('42','2011-01-11 06:10:00',1.01),
            ('43','2011-01-11 12:10:00',0.19),
            ('44','2011-01-11 18:10:00',1.14),
            ('45','2011-01-12 00:10:00',0.55),
            ('46','2011-01-12 06:10:00',0.75),
            ('47','2011-01-12 12:10:00',0.66),
            ('48','2011-01-12 18:10:00',1.1),
            ('49','2011-01-13 00:10:00',0.68),
            ('50','2011-01-13 06:10:00',0.3),
            ('51','2011-01-13 12:10:00',0.9),
            ('52','2011-01-13 18:10:00',0.88),
            ('53','2011-01-14 00:10:00',0.64),
            ('54','2011-01-14 06:10:00',1.06),
            ('55','2011-01-14 12:10:00',1.12),
            ('56','2011-01-14 18:10:00',0.76),
            ('57','2011-01-15 00:10:00',0.18),
            ('58','2011-01-15 06:10:00',1.08),
            ('59','2011-01-15 12:10:00',0.66),
            ('60','2011-01-15 18:10:00',0.38),
            ('61','2011-01-16 00:10:00',1),
            ('62','2011-01-16 06:10:00',1.18),
            ('63','2011-01-16 12:10:00',1.15),
            ('64','2011-01-16 18:10:00',0.58),
            ('65','2011-01-17 00:10:00',1.04),
            ('66','2011-01-17 06:10:00',0.81),
            ('67','2011-01-17 12:10:00',0.35),
            ('68','2011-01-17 18:10:00',0.91),
            ('69','2011-01-18 00:10:00',0.14),
            ('70','2011-01-18 06:10:00',0.13),
            ('71','2011-01-18 12:10:00',1.03),
            ('72','2011-01-18 18:10:00',0.16),
            ('73','2011-01-19 00:10:00',1.05),
            ('74','2011-01-19 06:10:00',1.13),
            ('75','2011-01-19 12:10:00',1.21),
            ('76','2011-01-19 18:10:00',0.34),
            ('77','2011-01-20 00:10:00',0.63),
            ('78','2011-01-20 06:10:00',0.62),
            ('79','2011-01-20 12:10:00',0.19),
            ('80','2011-01-20 18:10:00',1.21),
            ('81','2011-01-21 00:10:00',0.83),
            ('82','2011-01-21 06:10:00',0.99),
            ('83','2011-01-21 12:10:00',0.83),
            ('84','2011-01-21 18:10:00',0.21),
            ('85','2011-01-22 00:10:00',0.8),
            ('86','2011-01-22 06:10:00',0.69),
            ('87','2011-01-22 12:10:00',0.87);

所以你要：

  • 别名C对应于给定日期组中的所有行，因此您可以使用MAX() and MIN()覆盖该组中的行。
  • 别名C2对应于给定日期的最后一行。
  • 别名C3对应于晚于的一行C2在同一天。如果没有找到，即C3.*为 NULL，那么C2是当天最新的。

这通常被标记为greatest-n-per-group查询，它经常出现在 Stack Overflow 上。这是我针对您的测试数据测试的解决方案，但您可以按照我添加到您的问题中的标签来获取其他解决方案和讨论。

edit:我错过了开盘价和收盘价的要求。现编辑如下。

SELECT DATE_FORMAT(C.`DTE`, '%m/%d/%Y') AS trading_day, 
  MIN(C.`PRICE`) AS min_price, 
  MAX(C.`PRICE`) AS max_price, 
  Copen.`PRICE` AS opening_price,
  Cclose.`PRICE` AS closing_price 
FROM `CHART_DATA` AS C 
INNER JOIN `CHART_DATA` AS Cclose 
  ON DAY(C.`DTE`) = DAY(Cclose.`DTE`) 
LEFT OUTER JOIN `CHART_DATA` AS Cclose_later 
  ON DAY(C.`DTE`) = DAY(Cclose_later.`DTE`) AND Cclose.`DTE` < Cclose_later.`DTE`
INNER JOIN `CHART_DATA` AS Copen 
  ON DAY(C.`DTE`) = DAY(Copen.`DTE`) 
LEFT OUTER JOIN `CHART_DATA` AS Copen_earlier 
  ON DAY(C.`DTE`) = DAY(Copen_earlier.`DTE`) AND Copen.`DTE` < Copen_earlier.`DTE`
WHERE Cclose_later.`DTE` IS NULL AND Copen_earlier .`DTE` IS NULL 
GROUP BY trading_day;
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

join

greatestnpergroup

selfjoin

如何使用 Group By 和自连接返回每日最低、最高、开盘价和收盘价结果集？ 的相关文章

  • Mysql 时间匹配连接

    我有两个表cpuinfo和jobinfo 我想使用这两种数据创建报告 tabes CREATE TABLE cpuinfo id int 11 NOT NULL AUTO INCREMENT usagetime datetime DEFAU

  • POINT 列上的 MySQL INSERT/UPDATE

    我正在尝试用我国家的地理位置填充我的数据库 我的一张表有 4 个字段 ID PK 纬度 经度和地理点 EDIT SCDBs Punto Geografico SET lat 18 469692 SET lon 63 93212 SET g

  • MySQL：计算日期/时间之间的差异 - 仅在周一至周五“工作周”期间

    我需要计算开始日期 时间和结束日期 时间之间的差异 但是 我只想在 5 天的工作周内执行此操作 不包括周六 周日 做这个的最好方式是什么 我的想法是 从日期开始 我必须获取星期几 如果是工作日 那么我将添加到累加器中 如果不是 那么我不会添

  • 显示标准化数据

    跟进问题 添加 2 个不同表的总和 https stackoverflow com questions 39717541 adding sum from 2 different tables 我创建了3个表 members videos v

  • MySql - 复制监控工具[关闭]

    Closed 此问题正在寻求书籍 工具 软件库等的推荐 不满足堆栈溢出指南 help closed questions 目前不接受答案 我有一个主 从 MySql 复制 我正在寻找一个允许我监视复制的工具 查看它没有错误 检查滞后等 我更喜

  • 海量记录的bulk_create最佳实践

    I use bulk create将 1 mio 记录插入到新表中 需要 80 秒 Django 只使用一个 CPU 核心 大约 25 CPU 但没有一个核心达到 100 我相信有改进的潜力 这是代码 class Stock models

  • 如何在 MySQL 中求和时间？

    正如您在图片中看到的 我有一份停机报告 显示了所选工厂在选定日期的停机时间 现在我想添加所有的值 Time Duration 列并将其显示在附近的单独显示中 TOTAL TIME DURATION 例如 在图像中 所选日期为 2015 年

  • MySQL 通过 current_timestamp 选择上个月的数据

    直到今天 当我使用 MySQL 并需要对日期 时间执行操作时 我使用带有 unix 时间戳的 int 列 没有出现任何问题 但今天在阅读了一些指南后 我决定默认使用 current timestamp 测试时间戳列 所以我感兴趣如何按列选择

  • 合并两个 MYSQL SELECT 查询[重复]

    这个问题在这里已经有答案了 可能的重复 如何将两个 Post Category 表 MYSQL SELECT 查询合并为一个 https stackoverflow com questions 12972130 how to combine

  • Google Cloud SQL 在重新启动时卡住

    我的云 sql 实例长时间处于重新启动状态 在操作窗格中 重新启动的状态显示为待处理 并且还发生了导出 其状态仍为Running 有没有办法可以强制重新启动或取消重新启动或从常规备份中恢复数据 不 没有办法 如果您向 Google 支付高级

  • 使用Perl/DBI/MySQL/InnoDB查找外键信息

    我想以编程方式查找 MySQL 数据库中特定 InnoDB 表的外键 我正在使用 Perl 我偶然发现 dbh gt foreign key info 我刚刚尝试使用它 但似乎有点错误 它不会返回 ON DELETE 和 ON UPDATE

  • 错误代码：1305。函数或过程不存在

    因此 我在 MySQL 中创建一个函数 然后尝试向用户授予使用该函数的权限 但我无法这样做 这就是我正在做的 DELIMITER USE rxhelp36 scbn DROP FUNCTION IF EXISTS businessDayDi

  • MySQL 错误 1172 - 结果包含多行

    在存储过程中运行查询时 我从 MySQL 收到此错误 错误代码 1172 结果包含多行 我理解错误 我正在做一个SELECT INTO var list 因此查询需要返回单行 当我使用LIMIT 1 or SELECT DISTINCT 错

  • MySQL 排序顺序 - 排序规则？

    我在对 MySQL 中的 char 字段进行排序时遇到困难 问题是重音字符与非重音字符混淆 例如 Abc bd Acc 我认为这可能与整理有关 所以我将表格的排序规则更改为utf8 ut8 bin 看完之后这个帖子 https stacko

  • 如何在 MySQL 中测试 Select for Update

    我正在表演SELECT FOR UPDATE或 InnoDB 表的行级锁定 我的目的是只有一个请求可以读取同一行 因此 如果两个用户同时请求相同的数据 其中只有一个人获取数据 即第一个触发查询的人 但是我如何测试锁定是否已放置 因为我正在通

  • 用 pandas DataFrame 替换 mysql 数据库表中的行

    Python 版本 2 7 6 熊猫版本 0 17 1 MySQLdb 版本 1 2 5 在我的数据库中 PRODUCT 我有一张桌子 XML FEED 表 XML FEED 很大 数百万条记录 我有一个 pandas DataFrame

  • PHP MySQL 使用选项/选择 HTML 表单标签进行多重搜索查询

    我正在尝试使用两个搜索字段设置基本的 MySQL LIKE 搜索 我不想拥有它 所以它有多个可选搜索字段 例如if isset POST city isset POST name 我不知道如何用 HTML 来做到这一点

  • MaxListenersExceededWarning：检测到可能的 EventEmitter 内存泄漏。添加了 11 条消息列表。使用emitter.setMaxListeners()来增加限制

    我知道这可能会标记为重复的解决方案 但堆栈溢出的解决方案对我不起作用 Problem node 5716 MaxListenersExceededWarning Possible EventEmitter memory leak detec

  • 学说迁移后备

    我们正在使用原则迁移 当迁移包含多个操作并且其中一个操作失败时 通常会出现问题 例如 如果迁移添加了 5 个外键 其中第 5 个失败 而字段长度不同 则修复字段错误并重新生成迁移不会not修复整个问题 而现在出现一个与 4 个密钥已存在有关

  • 快速将列的副本添加到 MySQL 表

    我需要一种快速的方法来复制表中的 DATETIME 列并为其指定一个新名称 我的表中有一个名为 myDate 的列 名为 myResults 我需要一个查询来在名为 newDate 的表中创建一个新列 该列的数据与 myDate 列完全相同

随机推荐

热门标签