程序员经验分享-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 和自连接返回每日最低、最高、开盘价和收盘价结果集？ 的相关文章

  • 如何使用php在mysql数据库中添加照片？ [关闭]

    这个问题不太可能对任何未来的访客有帮助 它只与一个较小的地理区域 一个特定的时间点或一个非常狭窄的情况相关 通常不适用于全世界的互联网受众 为了帮助使这个问题更广泛地适用 访问帮助中心 help reopen questions 我对 PH

  • postgresql 选择不同的最新记录

    我有一个像这样的表 id fkey srno remark date 1 A001 1 2 A001 2 3 A002 1 4 A003 1 5 A002 2 我想要基于 max srno 的不同最新记录 例如 2 A001 2 4 A00

  • 数据读取过程中遇到致命错误

    我正在进行定期更新表扫描 Using connect1 As New MySqlConnection ConnectLocalhost serverString connect1 Open Dim cmd New MySqlCommand

  • 根据最大值连接表

    这是我正在谈论的内容的一个简化示例 Table students exam results id name id student id score date 1 Jim 1 1 73 8 1 09 2 Joe 2 1 67 9 2 09 3

  • 在 Django 查询中使用 .extra(select={...}) 引入的值上使用 .aggregate() ？

    我正在尝试计算玩家每周玩游戏的次数 如下所示 player game objects extra select week WEEK games game date aggregate count Count week 但姜戈抱怨说 Fiel

  • mod_rewrite, .htaccess 连接mysql数据库

    我希望 htaccess 文件中的 mod rewrite 链接到 mysql 数据库以向我提供映射信息 具体来说 我使用单个代码库来托管多个站点 因此 如果用户请求图像 例如 http www example com images car

  • 从 CSV 到 MySQL 的换行问题

    我正在将 csv 文件导入 MySQL 除了文件中的换行符之外 一切正常 我的 csv 行之一如下所示 42 E A R Classic Earplugs ear images ear classic jpg 5 Proven size s

  • 性能：cakephp-mysql 中的 UUID 与自动递增

    我正在搜索 cakePHP 生成的 UUID 32 个字符长 是否比自动增量在性能上更快 插入和选择操作的比较 我应该使用 cakePHP 生成的 UUID 还是使用 MySQL 的简单自动增量生成的 UUID 这是我发现的一个案例研究 但

  • 用于全文搜索和 2 亿多条记录的数据库

    我即将创建一个包含至少 2 亿个条目的庞大数据库 数据库需要可使用全文进行搜索 并且速度应该很快 我的数据库从许多不同的数据源获取数据 我需要定期导入新的或更新的数据 将我的所有数据存储在像 mysql 这样的关系数据库中 然后创建一个 n

  • 如何在没有 DROP 数据库权限的情况下从命令行删除所有 MySQL 表？ [复制]

    这个问题在这里已经有答案了 如何使用命令提示符删除 Windows MySQL 中的所有表 我想这样做的原因是我们的用户有权访问数据库删除 但无权重新创建数据库本身 因此我们必须手动删除表 有没有办法一次删除所有表 请记住 大多数表都与外键

  • Laravel Eloquent 预加载：加入同一个表两次

    我有一个users表和一个约会桌子 在约会表中 我有两个用户 ID customer id staff id 我想检索所有带有客户姓名和员工姓名的约会 users table id name appointments table id st

  • 在 SQL 中如何获得整数的最大值？

    我试图从 MySQL 数据库中找出整数 有符号或无符号 的最大值 有没有办法从数据库本身提取这些信息 是否有我可以使用的内置常量或函数 标准 SQL 或 MySQL 特定的 At http dev mysql com doc refman

  • MySQL 薛定谔表：存在，但不存在

    我遇到了最奇怪的错误 有时 在创建或更改表时 我会收到 表已存在 错误 但是 DROP TABLE 返回 1051 未知表 所以我得到了一个无法创建 无法删除的表 当我尝试删除数据库时 mysql 崩溃了 有时它有助于创建另一个具有不同名称

  • mysql时间比较

    我有 job start 和 job end 时间 timediff 会给我时间差 现在我想看看这项工作是否花费了超过 2 小时 30 分钟 我如何比较它 如果我这样做 我会收到错误 timediff job start job end g

  • Monkeyrunner/jython 中未找到 JDBC 驱动程序错误

    我需要在中插入一些东西DB 我在用着JDBC as a connector jython the script mysql数据库和脚本正在运行CentOS 我的代码看起来像这样 from com android monkeyrunner i

  • MySQL 两种日期格式之间的转换

    用户将以这种格式输入日期 2017 年 2 月 17 日 存储在 mysql 数据库中的日期格式如下 2015 02 17 00 00 00 我想做的是 SELECT FROM insurance where DATE FORMAT in

  • sql直接获取表行数的方法

    stackoverflow 的朋友们大家好 我的例行程序中有一个我认为不必要的步骤 假设您想从图库中获取所有图像 并限制每页一定数量的图像 db PDO object start pagenum x images per page limi

  • 控制数据是否存在于数组中

    我在mysql中有两个不同的表 我正在使用curl从json文件中获取数据 我的第一个表名称是 tblclients 该表存储客户端数据 我的第二个表名称是 tblcustomfieldsvalues 该表使用 tblclients 表的

  • parent_id 是外键（自引用）并且为 null？

    浏览 Bill Karwin 的书 SQL Antipatterns 第 3 章 Naive Trees 邻接表 父子关系 有一个注释表的示例 CREATE TABLE Comments comment id SERIAL PRIMARY

  • Mysql加密/存储敏感数据，

    我的 PHP 网站有以下内容 启用 SSL 饼干 session set cookie params cookieParams lifetime cookieParams path cookieParams domain secure ht

随机推荐

热门标签