表格

每一行代表在特定日期的特定时间播出的视频。每天大约有 1600 个视频。

CREATE TABLE `air_video` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `time` TIME NOT NULL,
    `duration` TIME NOT NULL,
    `asset_id` INT(10) UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `status` VARCHAR(100) NULL DEFAULT NULL,
    `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `date_2` (`date`, `time`),
    INDEX `date` (`date`),
    INDEX `status` (`status`),
    INDEX `asset_id` (`asset_id`)
)
ENGINE=InnoDB

任务

有两个条件。

1. 每个视频每天的播放次数不得超过 24 次。
2. 每个视频的轮播时间不得超过 72 小时。

In rotation 表示第一时间和最后一次播放视频之间的时间跨度。

因此，我需要根据用户指定的日期范围选择所有违反这些条件的视频。

结果必须按天和 asset_id(视频 ID)分组。例如:

      date      asset_id          name           dailyCount      rotationSpan
   2012-04-27      123      whatever_the_name         35              76
   2012-04-27      134      whatever_the_name2        39              20
   2012-04-28      125      whatever_the_name3        26              43

查询

现在我已经写了这个查询:

SELECT
    t1.date, t1.asset_id, t1.name,
    (SELECT 
        COUNT(t3.asset_id) 
        FROM air_video AS t3 
        WHERE t2.asset_id = t3.asset_id AND t3.date = t1.date
    ) AS 'dailyCount',
    MIN(CONCAT(t2.date, ' ', t2.time)) AS 'firstAir',
    MAX(CONCAT(t2.date, ' ', t2.time)) AS 'lastAir',
    ROUND(TIMESTAMPDIFF(
        MINUTE,
        MIN(CONCAT(t2.date, ' ', t2.time)),
        MAX(CONCAT(t2.date, ' ', t2.time)) 
    ) / 60) as 'rotationSpan'
FROM
    air_video AS t1
INNER JOIN
    air_video AS t2 ON
        t1.asset_id = t2.asset_id 
WHERE
    t1.status NOT IN ('bumpers', 'clock', 'weather')
    AND t1.date BETWEEN '2012-04-01' AND '2012-04-30'
GROUP BY
    t1.asset_id, t1.date
HAVING
    `rotationSpan` > 72
    OR `dailyCount` > 24
ORDER BY
    `date` ASC,
    `rotationSpan` DESC,
    `dailyCount` DESC    

问题

1. 用户指定日期之间的范围越大 - 完成查询所需的时间越长(对于一个月的范围，大约需要 9 秒)
2. lastAir 时间戳不是视频在特定日期播出的最晚时间，而是它一起播出的最晚时间。

最佳答案

如果您需要加快查询速度，您需要删除第 3 行的 select 子查询。 要仍然拥有该计数，您可以使用您最初使用的确切参数在 from 子句中再次内部加入它。它应该是这样的:

SELECT
    t1.date, t1.asset_id, t1.name,
    COUNT(t3.asset_id) AS 'dailyCount',
    MIN(CONCAT(t2.date, ' ', t2.time)) AS 'firstAir',
    MAX(CONCAT(t2.date, ' ', t2.time)) AS 'lastAir',
    ROUND(TIMESTAMPDIFF(
        MINUTE,
        MIN(CONCAT(t2.date, ' ', t2.time)),
        MAX(CONCAT(t2.date, ' ', t2.time)) 
    ) / 60) as 'rotationSpan'
FROM
    air_video AS t1
INNER JOIN
    air_video AS t2 ON
        (t1.asset_id = t2.asset_id)
INNER JOIN
    air_video AS t3 
        ON (t2.asset_id = t3.asset_id AND t3.date = t1.date)
WHERE
    t1.status NOT IN ('bumpers', 'clock', 'weather')
    AND t1.date BETWEEN '2012-04-01' AND '2012-04-30'
GROUP BY
    t1.asset_id, t1.date
HAVING
    `rotationSpan` > 72
    OR `dailyCount` > 24
ORDER BY
    `date` ASC,
    `rotationSpan` DESC,
    `dailyCount` DESC

由于 t2 不受日期限制，您显然是在查看整个表格，而不是日期范围。

编辑: 由于有很多日期绑定(bind)，查询仍然运行得太慢。然后我采取了不同的方法。我创建了 3 个 View (你显然可以在没有 View 的情况下组合成一个普通查询，但我更喜欢最终结果查询)

--T1--

CREATE VIEW t1 AS select date,asset_id,name from air_video where (status not in ('bumpers','clock','weather')) group by asset_id,date 按日期排序；

--T2--

CREATE VIEW t2 AS select t1.date,t1.asset_id,t1.name,min(concat(t2.date,' ',t2.time)) AS 'firstAir',max(concat(t2. date,' ',t2.time)) AS 'lastAir',round((timestampdiff(MINUTE,min(concat(t2.date,' ',t2.time))),max(concat(t2.date,' ', t2.time)))/60),0) AS 'rotationSpan' from (t1 join air_video t2 on((t1.asset_id = t2.asset_id))) group by t1.asset_id,t1.date;

--T3--

CREATE VIEW t3 AS 选择 t2.date,t2.asset_id,t2.name,count(t3.asset_id) AS 'dailyCount',t2.firstAir,t2.lastAir,t2.rotationSpan AS rotationSpan from (t2将 air_video t3 on(((t2.asset_id = t3.asset_id) 和 (t3.date = t2.date)))) 按 t2.asset_id,t2.date 分组；

然后您可以从那里运行以下查询:

SELECT
    date,
    asset_id, 
    name,
    dailyCount,
    firstAir,
    lastAir,
    rotationSpan
FROM
    t3
WHERE
    date BETWEEN '2012-04-01' AND '2012-04-30'
    AND (
        rotationSpan > 72
        OR 
        dailyCount > 24
    )
ORDER BY
    date ASC,
    rotationSpan DESC,
    dailyCount DESC

关于mysql - 如何优化复杂的 MySQL 查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10633649/