时间戳

首页 > 解决方案 > MySQL - 如何找到值变化最大的行？

mysql - MySQL - 如何找到值变化最大的行？

问题描述

我正在尝试搜索具有每日排名关键字（SEO 关键字）的表格。因此，我对每个关键字都有一个 key_id 索引，以及每个关键字的新位置值。

我想了解如何选择价值变化最大的关键字？

MariaDB 表和数据：

CREATE TABLE IF NOT EXISTS `daily_rank` (
  `rankID` int(24) NOT NULL AUTO_INCREMENT,
  `created` timestamp NULL DEFAULT current_timestamp(),
  `key_id` int(100) NOT NULL DEFAULT 0,
  `position` int(12) NOT NULL DEFAULT 0,
  `keyword` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`rankID`),
  KEY `created` (`created`),
  KEY `key_id` (`key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3594 DEFAULT CHARSET=latin1;

INSERT INTO `daily_rank` (`rankID`, `created`, `key_id`, `position`, `keyword`) VALUES
    (3594, '2019-10-09 17:59:07', 53, 4, 'SEO'),
    (3595, '2019-10-09 17:59:07', 100, 3, 'agency'),
    (3596, '2019-10-09 17:59:07', 397, 1, 'bureau marketing'),
    (3597, '2019-10-09 17:59:07', 798, 7, 'marketing agency'),
    (3598, '2019-10-09 17:59:07', 98, 8, 'search engine optimization'),
    (3599, '2019-10-09 17:59:07', 346, 8, 'website optimization'),
    (3600, '2019-10-09 17:59:07', 555, 9, 'agency'),
    (3608, '2019-10-08 18:07:00', 53, 4, 'SEO'),
    (3609, '2019-10-08 18:07:00', 100, 4, 'agency'),
    (3610, '2019-10-08 18:07:00', 397, 3, 'bureau marketing'),
    (3611, '2019-10-08 18:07:00', 798, 1, 'marketing agency'),
    (3612, '2019-10-08 18:07:00', 98, 2, 'search engine optimization'),
    (3613, '2019-10-08 18:07:00', 346, 2, 'website optimization'),
    (3614, '2019-10-08 18:07:00', 555, 2, 'agency'),
    (3615, '2019-10-07 18:07:22', 53, 4, 'SEO'),
    (3616, '2019-10-07 18:07:22', 100, 6, 'agency'),
    (3617, '2019-10-07 18:07:22', 397, 6, 'bureau marketing'),
    (3618, '2019-10-07 18:07:22', 798, 6, 'marketing agency'),
    (3619, '2019-10-07 18:07:22', 98, 4, 'search engine optimization'),
    (3620, '2019-10-07 18:07:22', 346, 6, 'website optimization'),
    (3621, '2019-10-07 18:07:22', 555, 6, 'agency'),
    (3622, '2019-10-07 18:07:22', 53, 5, 'SEO'),
    (3623, '2019-10-07 18:07:22', 100, 4, 'agency'),
    (3624, '2019-10-07 18:07:22', 397, 5, 'bureau marketing'),
    (3625, '2019-10-07 18:07:22', 798, 3, 'marketing agency'),
    (3626, '2019-10-07 18:07:22', 98, 6, 'search engine optimization'),
    (3627, '2019-10-07 18:07:22', 346, 3, 'website optimization'),
    (3628, '2019-10-07 18:07:22', 555, 5, 'agency'),
    (3629, '2019-10-06 18:07:44', 53, 1, 'SEO'),
    (3630, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3631, '2019-10-06 18:07:44', 397, 2, 'bureau marketing'),
    (3632, '2019-10-06 18:07:44', 798, 1, 'marketing agency'),
    (3633, '2019-10-06 18:07:44', 98, 1, 'search engine optimization'),
    (3634, '2019-10-06 18:07:44', 346, 2, 'website optimization'),
    (3635, '2019-10-06 18:07:44', 555, 2, 'agency'),
    (3636, '2019-10-06 18:07:44', 53, 2, 'SEO'),
    (3637, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3638, '2019-10-06 18:07:44', 397, 3, 'bureau marketing'),
    (3639, '2019-10-06 18:07:44', 798, 2, 'marketing agency'),
    (3640, '2019-10-06 18:07:44', 98, 2, 'search engine optimization'),
    (3641, '2019-10-06 18:07:44', 346, 1, 'website optimization'),
    (3642, '2019-10-06 18:07:44', 555, 1, 'agency'),
    (3643, '2019-10-06 18:07:44', 53, 1, 'SEO'),
    (3644, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3645, '2019-10-06 18:07:44', 397, 1, 'bureau marketing'),
    (3646, '2019-10-06 18:07:44', 798, 3, 'marketing agency'),
    (3647, '2019-10-06 18:07:44', 98, 2, 'search engine optimization'),
    (3648, '2019-10-06 18:07:44', 346, 1, 'website optimization'),
    (3649, '2019-10-06 18:07:44', 555, 3, 'agency'),
    (3650, '2019-10-06 18:07:44', 53, 3, 'SEO'),
    (3651, '2019-10-06 18:07:44', 100, 1, 'agency'),
    (3652, '2019-10-06 18:07:44', 397, 2, 'bureau marketing'),
    (3653, '2019-10-06 18:07:44', 798, 3, 'marketing agency'),
    (3654, '2019-10-06 18:07:44', 98, 1, 'search engine optimization'),
    (3655, '2019-10-06 18:07:44', 346, 2, 'website optimization'),
    (3656, '2019-10-06 18:07:44', 555, 1, 'agency');

如何查询，以便我可以获得关键字的最新位置，以及从给定日期起的变化，并排序结果以显示变化最大的关键字？

我想象这样一张桌子：

[Keyword] - [Todays Position] - [Position Change from yesterday]

它按最大变化降序排列

更新：在计算 max-min 时，今天的位置在这个计算范围内，并且会稍微扭曲结果。

在查看今天的排名时，我想查看自比较日期以来排名变化最大的关键字。

标签： mysqlsqlcodeignitermariadb

解决方案

SELECT keyword, MAX(position) max_position,MIN(position) min_position FROM daily_rank GROUP BY keyword;
+----------------------------+--------------+--------------+
| keyword                    | max_position | min_position |
+----------------------------+--------------+--------------+
| agency                     |            9 |            1 |
| bureau marketing           |            6 |            1 |
| marketing agency           |            7 |            1 |
| search engine optimization |            8 |            1 |
| SEO                        |            5 |            1 |
| website optimization       |            8 |            1 |
+----------------------------+--------------+--------------+

推荐阅读