mysql - 分组问题

Question

我有两个表，response_archive 27 000 000 行，以及 airports，其中包含 4 000 行。

我尝试选择的字段:

• response_archive.min(价格)
• response_archive.avg(价格)
• response_archive.year_week <-(分组依据，索引)
• response_archive.from(与 airport.IATA 联合)

• response_archive.to(与 airport.IATA 合并)

• response_archive.from 和 response_archive.to 不能相同。

• response_archive.year_week 应在一个范围内，如查询中所示。

我在分组时遇到问题，无法按response_archive.year_week 进行分组并同时获得最低/平均价格。

这是我当前的查询，它远非最佳，执行大约需要 90 秒。

SELECT
    `from`,
    `to`,
    year_week,
    min(price) min_price,
    round(AVG(price)) avg_price
  FROM response_archive
   INNER JOIN airports AS `f` ON (f.IATA = `from`)
   INNER JOIN airports AS `t` ON (t.IATA = `to`)
 WHERE
    year_week < '1310' AND
    year_week > '1210' AND
    returntrip = 1 AND
    adults = 1 AND
    children = 0 AND
    `price` < 70000 AND
    `price` > 80 AND
    f.SweCity = 'Stockholm' AND
    t.CountryCode = 'US'
GROUP BY year_week
ORDER BY year_week;

当前结果:

from   to     year_week min   avg
STO    NYC    1211      3552  6311
ARN    LAX    1212      3097  6496
STO    NYC    1213      3532  7379
ARN    NYC    1214      3584  6635
STO    LAX    1215      3523  5907
STO    FLL    1216      3559  5698
STO    NYC    1217      3642  5919

除了最小值/平均值不正确。

比较第一个值是否正确:

SELECT
    min(price) min,
    '3532' min_expected,
    round(avg(price)) avg,
    '7379' avg_expected
FROM
    response_archive
WHERE
    `from` = 'STO' AND
    `to` = 'NYC' AND
    year_week = '1213' AND
    returntrip = 1 AND
    adults = 1 AND
    children = 0 AND
    `price` < 70000 AND
    `price` > 80;

正确结果:

min     min_exp avg     avg_exp
3532    3532    5955    7379

有人可以给我指明方向或解释为什么它没有给我我想要的结果吗？

Answer 1

这是您的查询的简化版，并添加了缺少的 GROUP BY 字段:

SELECT
    `from`,
    `to`,
    year_week,
    min(price) min_price,
    round(AVG(price)) avg_price
  FROM response_archive
   INNER JOIN airports AS `f` ON (f.IATA = `from`)
   INNER JOIN airports AS `t` ON (t.IATA = `to`)
 WHERE
    year_week < '1310' AND
    year_week > '1210' AND
    returntrip = 1 AND
    adults = 1 AND
    children = 0 AND
    `price` < 70000 AND
    `price` > 80 AND
    f.CountryCode = 'SE' AND
    t.CountryCode = 'US'
GROUP BY `from`, `to`, year_week
ORDER BY year_week;